如何描述关系数据库中的性能问题? [英] How to describe performance issue in relational database?

问题描述

我有一个查询在关系数据库中运行，无法满足用户的期望.

I have a query running in a relational database that doesn't fulfill the expectation of the users.

我应该提供什么信息，应该避免什么，以便我可以在此站点上获得有效的帮助?

What information should I provide and what should I avoid, so that I can receive an effective help on this site?

推荐答案

对于 Oracle数据库，请提供以下信息:

For Oracle Database provide this information:

描述问题的症状

描述导致问题的行为.查询的行为是稳定的还是仅发生问题? 有时，使用特定参数或简单随机.您可以在IDE(例如SQL Developer)中重现此行为吗?

Describe the behavior that cause the problem. Is the behavior of the query stable or does the problem occurs only sometimes, with specific parameters or simple random. Can you reproduce this behavior in an IDE (e.g. SQL Developer)?

描述环境

定义Oracle的确切版本

Define the exact version of Oracle

 select * from v$version

描述如何连接数据库:驱动程序，ORM，编程语言.提供名称和/或版本号.

Describe how you connect to the database: driver, ORM, programming language. Provide names and/or version numbers.

描述查询

发布查询文本.尝试简化-显示一个可重现的最小示例.

Post the query text. Try to simplify - show a minimal reproducible example.

示例-您有问题的查询联接了10个表.检查在9个或8个联接的查询中是否看到相同的症状. 下台直到发现问题并仅显示减少的查询.

Example - you problematic query joins 10 tables. Check if you see the same symptoms in a query with 9 or 8 joins. Step down until you see the problems and show only the reduced query.

是的，这是昂贵的，但是它大大增加了您获得支持的机会！查询越小，它吸引的越高. 支持者.

Yes, this is costly, but it highly increase the chance that you receives support! The smaller the query is the higher it attracts the supporters.

描述执行计划

要获取执行计划，请运行以下语句(替换您的查询文本)

To get the execution plan run this statement (substitute your query text)

 EXPLAIN PLAN  SET STATEMENT_ID = '<some_id>' into   plan_table  FOR
     select * from ....   -- your query here 
 ;

执行计划存储在PLAN_TABLE中，以查看其运行此查询

The execution plan is stored in the PLAN_TABLE, to see it run this query

 SELECT * FROM table(DBMS_XPLAN.DISPLAY('plan_table', '<some_id>','ALL')); 

显示完整结果(不仅是带有执行计划的表). 最重要的可能是谓词部分和下面的注释.

Show the complete result (not only the table with the execution plan). Extreme important may be the predicate section and the notes bellow.

select * from dual where dummy = :1;

Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / DUAL@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DUMMY"=:1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "DUMMY"[VARCHAR2,1]

请勿剪切并粘贴您的IDE解释计划的图形结果.

此执行计划是真正执行的计划吗?

不幸的是，并非总是如此. 说明执行计划可能有几个原因不同于真实的.

Unfortunately not always. There are several reasons the explained execution plan may differ from the real one.

如果您有疑问(尤其是当您看到一个好的计划，但查询运行不佳时)，则可以 从数据库缓存中提取计划，并提供SQL_ID.

If you are in doubts (especially when you see a good plan, but the query runs bad) you may extract the plan from the DB cache providing a SQL_ID.

 SELECT t.* FROM  table(DBMS_XPLAN.DISPLAY_CURSOR('<SQL_ID>',null,'ALL')) t; 

可以使用以下命令找到当前正在运行的查询的SQL_ID(或正在运行不久但仍在缓存中) 文字比对和/或数据库用户:

The SQL_ID for a query that is currently running (or was running shortly and is still cached) can be found with text match and/or the database user:

select sql_id, sql_fulltext from v$sql a where 
 lower(sql_text) like lower('%<some identifying part of the query text>%') 
  and parsing_schema_name = '<user running the query>';

如果您拥有AWR许可证，甚至可以针对历史记录中运行的查询从那里获得执行计划.

If you have AWR license, you may get the execution plan from there, even for queries running in history.

SELECT t.*
FROM  table(DBMS_XPLAN.DISPLAY_AWR('10u2rj016s96k'  )) t;

SQL_ID可以使用

The SQL_ID can be found using

select sql_id, sql_text 
from dba_hist_sqltext a 
where lower(sql_text) like lower('%<some identifying part of the query text>%')

描述数据

显示表的DDL以及这些表上的索引.

Show the DDL of the tables and indexes on those tables.

提及优化器统计信息是否最近收集并显示使用的dbms_stats收集语句.

Mention if the optimizer statistics are gathered recently and show the used dbms_stats gather statement.

对于关键表，请提供有关段大小，行号，分区等的信息.

For the critical table(s) provide information about segment size, row number, partitioning,...

对于访问或联接中使用的列，请提供有关不同值数量的信息. 值是否均匀分布或偏斜(例如，很少出现且经常出现的少量值 稀有价值). 您定义直方图吗?

For the columns used in access or joins provide information about number of distinct values. Are the values evenly distributed or skew (e.g. a small number of values that occurs very often and a large number of rare values). Do you define histograms?

还有什么?

当然，这只是基础知识，可能仍然需要其他信息，例如系统统计信息或优化器参数. 但是，请再次尝试提供(您所能识别的)问题的最少信息. 根据要求发布其他信息.

Of course this is the basics only and other information may still be required, such as system statistics or optimizer parameters. But once again try to provide the minimal information that (you thing) can identify the problem. Post additional information upon request.

祝你好运！

这篇关于如何描述关系数据库中的性能问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！