对于 SQL 性能问题如何收集诊断信息 (Doc ID 2293025.1)

适用于

产品:

  • Oracle Database – 企业版 – 版本 6.0.0.0 及更高版本
  • 本文档内容适用于任何平台

需要的权限:

  • 数据库管理员 – 需要访问数据库来收集数据

系统影响:

  • 无 – 以下的收集指令不会对系统有任何影响
哪些会被收集以及为什么?

提供一步步的指导来对 SQL 性能问题收集诊断日志。

Action Plan

诊断文件列表

 1) 使用 SQL Health Check 检查语句                                  [  ]
 2) 使用 SQLT 的 XTRXEC 模式收集诊断信息                              [  ]
 
 如果不能收集 SQLT
 
 2) 手工收集诊断日志                                                 [  ]
   a) 语句的全文 – 完整的有问题的语句的 SQL 文本                        [  ]
   b) 收集有问题的执行计划                                            [  ]
   c) 收集好的执行计划(如果有的话)                                    [  ]
   d) 收集有问题时的 10046 trace                                     [  ]
   e) 收集没问题时的 10046 trace(如果有的话)                         [  ]

 整理并上传诊断日志

 3) 整理并上传诊断日志                                                [  ]

 

性能问题的诊断信息

 

1) 使用 SQL Health Check 检查语句

下载并运行 SQLHC 工具

# sqlplus / as sysdba
SQL> START sqlhc.sql [T|D|N] [SQL_ID]

关于如何使用 SQLHC 收集 SQL 性能问题的信息,请参照下面的文章:

Document 1903134.1 How to Collect Standard Diagnostic Information Using SQLHC for SQL Performance and Incorrect Result Issues

检查 SQLHC 的推荐

检查 SQL Health Check 工具的推荐,如果文件 sqlhc_IDENTIFIER_1_health_check.html 中的内容不能帮我们解决问题,那么上传 SQLHC 输出到 SR。

 

 

2) 使用 SQLT 的 XTRXEC 模式收集诊断信息

除了 SQLHC 的输出,收集这个语句的 SQLT 输出。你需要先下载并安装 SQLT。以 XTRXEC 模式收集 SQLT,这样同时收集到运行时以及优化器的信息。

注意:如果语句不能在一个合理的时间内运行完毕或者语句会修改数据(比如 INSERT/UPDATE/DELETE 语句),那么以 XTRACT(sqltxtract.sql)模式收集,它不会真的去执行语句,而仅仅收集之前执行产生的信息。

XTRXEC 模式收集足够的信息(包括运行这个语句),如下:

使用 SQL*PLUS 以执行有问题语句的应用账号连接,并执行 sqlt/run/sqltxtrxec.sql 脚本,并提供 SQL_ID 或者 HASH_VALUE。

# cd sqlt/run
# sqlplus apps
SQL> START sqltxtrxec.sql [SQL_ID]|[HASH_VALUE]

如果这个语句有正常的时候(比如在其他的系统上,或者不同版本,或者不同的用户下,或者在不同的参数下),那么也同时收集正常时候的 SQLT 以做对比。

如果如何使用 SQLT 收集 SQL 性能问题的诊断日志,请参考下面的文章:

Document 1683772.1 How to Collect Standard Diagnostic Information Using SQLT for SQL Performance and Incorrect Result Issues

 

 

如果不能收集 SQLT

2) 手工收集诊断日志

SQLT 可以一次运行就收集大部分诊断性能问题所需要的信息,但是如果因为一些原因您不能使用 SQLT,那么您需要手工收集下面的信息。

 

2a) 语句的全文 – 完整的有问题的语句的 SQL 文本

收集问题语句的文本全文

 

2b) 收集有问题的执行计划

在 10.2 及更高版本上,如果语句已经执行了,可以从 library cache 中拿到执行计划(和之前的版本只能收集标准的执行计划相比)。要收集最近一次执行的执行计划,执行下面的命令:

SQL> set linesize 150
SQL> set pagesize 2000
SQL> spool problem_explain.txt

SQL> select * from table(dbms_xplan.display_cursor(null,null, ‘ALL’));

SQL> spool off

关于更多方法,请参考:

Document 1683772.1 How to Obtain a Formatted Explain Plan – Recommended Methods

 

2c) 收集好的执行计划(如果可能的话)

如果有好的执行计划,那么像上面一样收集好的执行计划,spool 到一个”good_explain.txt”的文件中。

 

2d) 收集有问题时候的 10046 trace

在 session level 收集这个语句的 10046 trace:

alter session set tracefile_identifier=’problem_10046′;

alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set max_dump_file_size = unlimited;

oradebug setmypid
oradebug tracefile_name

alter session set events ‘10046 trace name context forever,level 12’;

— 在这里执行需要被 trace 语句或者操作 —

select * from dual;
exit;

trace 文件会显示在”oradebug tracefile_name” 命令的输出中。

注意:如果并未使用绑定变量,那么使用 level 8,因为 level 12 会产生更大的日志。

如果语句无法在合理的时间内完成,那么就 trace 一个合理的时间后,就取消它。

如果 session 不能退出,那么可以执行下面的语句关闭 trace:

alter session set events ‘10046 trace name context off’;

关于更多收集 10046 trace 的方式,请参考文档:

Document 376442.1 Recommended Method for Obtaining 10046 trace for Tuning

 

2e) 收集没问题时候的 10046 trace(如果可能的话)

如果语句有正常的时候,那么也对好的时候收集 10046 trace,使用’good_10046′ 作为 trace 的标识: 

alter session set tracefile_identifier=’good_10046′;

 

 

下一步

3) 整理并上传诊断日志

如果在检查和实施了 SQL Health Check 的推荐后,仍然无法解决问题,那么可以从下面的文档中找到更多分析 SQL 性能问题的方式:

Document 742112.1 Troubleshooting Query Performance Degradation – Recommended Actions

如果无法解决问题,整理并上传诊断日志给技术支持,并且提供一个清晰的问题描述。在大部分情况下这些信息已经足够,但还是有些情况会要收集更多的信息。

SQL Health Check 输出文件:sqlhc_IDENTIFIER_1_health_check.html
语句的 XTRACT 模式的 SQLT 输出
如果不能收集 SQLT,上传

  • 语句的全文
  • 有问题的执行计划
  • 收集好的执行计划(如果有的话)
  • 有问题时候的 10046 trace
  • 没问题时候的 10046 trace(如果有的话)

Comment

*

沪ICP备14014813号

沪公网安备 31010802001379号