性能调优工具04–SQLT Diagnostic Tool

SQLT Diagnostic Tool

1.SQLT介绍:SQLTXPLAIN, also known as SQLT, is a tool provided by Oracle Server Technologies Center of Expertise – ST CoE. SQLT inputs one SQL statement and outputs a set of diagnostics files. These files are commonly used to diagnose SQL statements performing poorly. SQLT connects to the database and collects execution plans, Cost-based Optimizer CBO statistics, schema objects metadata, performance statistics, configuration parameters, and similar elements that influence the performance of the SQL being analyzed.

2.安全模式:SQLT在安装的过程中会创建两个用户和一个角色,这些用户和角色的名字都是固定的;

1.SQLTXPLAIN用户:用来管理SQLT repository,SQLT的使用者每次使用SQLT提供的主要方法时都要提供SQLTXPLAIN的密码;SQLTXPLAIN用户被赋予了CREATE SESSION,CREATE TABLE系统权限;
2.SQLTXADMIN用户:用来管理SQLT包含的PL/SQL程序包以及视图,SQLTXADMIN用户处于锁定状态并且由一个随机产生的密码保护;SQLTXADMIN用户被赋予了ADMINISTER SQL MANAGEMENT OBJECT,ADMINISTER SQL TUNING SET,ADVISOR,ALTER SESSION,ANALYZE ANY,SELECT ANY DICTIONARY,SELECT_CATALOG_ROLE系统权限;
3.SQLT_USER_ROLE角色:所有SQLT的使用者在使用SQLT提供的主要方法之前必须被赋予这个角色;SQLT_USER_ROLE角色被赋予了ADVISOR,SELECT_CATALOG_ROLE系统权限;

4.TIPS:12c在缺省情况下SYS用户不能作为SQLT的用户,因为PL/SQL安全模型改变的原因,需要单独对SQLTADMIN授权(GRANT INHERIT PRIVILEGES ON USER SYS TO SQLTXADMIN);

3.安装:

1.下载并解压到相应的目录;(可以查看目录下的sqlt_instructions.html安装文档)

2.安装:conn / as sysdba; SQL> @/tools/sqlt/install/sqcreate.sql;需要提供以下信息:

1.连接标识符(可选,当安装在一个PDB上时是必须的):一般不输入直接回车;

2.SQLTXPLAIN密码;
3.SQLTXPLAIN默认表空间:它必须具有50MB以上的可用空间;
4.SQLTXPLAIN临时表空间;
5.应用程序用户(可选):指定发出要分析SQL语句的用户(EBS中为APPS,Siebel中为SIEBEL,PeopleSoft中为SYSADM),也可以安装之后再添加用户,必须授予其SQLT_USER_ROLE角色;
6.授权的Oracle Pack(T,D,N):T表示Oracle Tuning,D表示Oracle Diagnostic,或N表示None;如果选择T或D,SQLT可以在它生成的诊断文件中包含授权的内容;默认值为T,如果选择N,SQLT将只安装限定的功能;

3.卸载:conn / as sysdba; SQL> @/path/sqlt/install/sqdrop.sql;

4.升级:只需要执行安装的过程即可,如果升级失败,则需要执行卸载然后安装;

5.tips:

1.如果安装时没有设置连接标示符,那么可以之后自己设置:EXEC sqltxadmin.sqlt$a.set_sess_param(‘connect_identifier’, ‘@network_name’);

2.如果安装时没有指定添加用户,那么可以CREATE USER sqlt;GRANT sqlt_user_role TO sqlt;

4.主要方法:

1.XTRACT方法:

1.如果知道待分析SQL的SQL_ID或HASH_VALUE,可以使用此方法,否则使用XECUTE;可以在AWR report中找到SQL_ID,在SQL trace中找到HASH_VALUE(在SQL文本上面,通过”hv=”标记进行标识);
2.如果对SQL进行硬分析时将参数STATISTICS_LEVEL设置为ALL,将可以得到重要的性能统计信息(如每步操作的实际行数);也可以通过在SQL中包括以下CBO提示来生成同样有用的性能统计信息:/*+ GATHER_PLAN_STATISTICS */;在11g中,您可以在SQL中包含以下CBO提示以获得增强的诊断信息:/*+ GATHER_PLAN_STATISTICS MONITOR */;
3.使用方法:SQL> @/path/sqlt/run/sqltxtract.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password];

2.XECUTE方法:

1.与XTRACT方法相比,此方法提供的信息更为详细;正如名称XECUTE所指示的,它将执行正在分析的SQL,然后生成一组诊断文件;

2.它的主要缺点是如果正在分析的SQL需要很长时间来执行,那么该方法也要花费很长的时间;根据经验法则,仅当SQL执行少于1小时的情况下,才考虑使用此方法,否则请使用XTRACT;
3.使用此XECUTE方法之前,必须创建一个包含SQL文本的文本文件;如果SQL包括绑定变量,则您的文件必须包含绑定变量声明和赋值;以sqlt/input/sample/script1.sql为例;您的SQL应该包含标记/* ^^unique_id */;

4.使用方法:SQL> @/path/sqlt/run/sqltxecute.sql /path/scriptname [sqltxplain_password];

3.XTRXEC方法:

1.该方法合并了XTRACT和XECUTE的功能;实际上,XTRXEC连续执行了这两种方法;针对所请求SQL语句找到的开销较大的计划,XTRACT阶段将生成一个包含提取的SQL以及绑定声明和赋值的脚本;然后,XTRXEC使用第一阶段创建的脚本执行XECUTE阶段;
2.SQLT根据在内存中生成开销最大的执行计划时窥视到的值,创建脚本的绑定变量的以供XTRACT使用;判断计划的开销大小的标准是基于这个计划的平均执行时间;
3.如果XTRXEC仅执行了第一个阶段(XTRACT)后就输出错误,您可能需要检查在第二阶段(XECUTE)使用的脚本并相应调整绑定变量;使用不常用数据类型时尤其需要进行调整;

4.使用方法:SQL> @/path/sqlt/run/sqltxtrxec.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password];

4.XTRSBY方法:

1.如果需要分析在DataGuard或备用只读数据库上执行的SQL,请使用该方法;您需要知道要分析的SQL的SQL_ID或HASH_VALUE;
2.在主库上创建一个到备库的database link,连接到的用户需要有访问数据字典的权限,通常都是使用有DBA权限的用户;
3.如果对只读数据库中的SQL进行硬分析时将参数STATISTICS_LEVEL设置为ALL,将可以得到重要的性能统计信息(如每个执行计划操作的实际行数);您也可以通过在SQL中包括以下CBO提示来生成同样有用的性能统计信息:/*+ GATHER_PLAN_STATISTICS */;在11g中,您可以在SQL中包含以下CBO提示以获得改进的诊断信息:/*+ GATHER_PLAN_STATISTICS MONITOR */;

4.使用方法:SQL> @/path/sqlt/run/sqltxtrsby.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password] [DB_LINK];

5.XPLAIN方法:

1.该方法是基于EXPLAIN PLAN FOR命令执行的,因此它将无视您的SQL语句引用的绑定变量;仅当无法使用XTRACT或XECUTE时才使用该方法;
2.使用此XPLAIN方法之前,必须创建一个包含SQL文本的文本文件;如果SQL包括绑定变量,您有两个选择:保持SQL文本”不变”,或谨慎使用相同数据类型的字面值替换该绑定;以sqlt/input/sample/sql1.sql为例;

3.使用方法:SQL> @/path/sqlt/run/sqltxplain.sql /path/scriptname [sqltxplain_password];

6.XPREXT方法:

1.假如您想使用XTRACT同时希望禁用一些SQLT的特性使之执行更快,请使用这个方法;脚本sqlt/run/sqltcommon11.sql显示了哪些特性被禁用;

2.使用方法:SQL> @/path/sqlt/run/sqltxprext.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password];

7.XPREXC方法:

1.假如您想使用XECUTE同时希望禁用一些SQLT的特性使之执行更快,请使用这个方法;脚本sqlt/run/sqltcommon11.sql显示了哪些特性被禁用;

2.使用方法:SQL> @/path/sqlt/run/sqltxprexc.sql /path/scriptname [sqltxplain_password];

8.TIPS:

1.XTRACT,XECUTE,XTRXEC,XTRSBY,XPREXT和XPREXC处理绑定变量和会做bind peeking(绑定变量窥视),但是XPLAIN不会;这是因为XPLAIN是基于EXPLAIN PLAN FOR命令执行的,该命令不做bind peeking;因此,如果可能请避免使用XPLAIN;

2.除了XPLAIN的bind peeking限制外,所有这7种主要方法都可以提供足够的诊断详细信息,对性能较差或产生错误结果集的SQL进行初步评估;如果该SQL仍位于内存中或者AWR中,请使用XTRACT或XTRXEC,其他情况请使用XECUTE;

3.对于DataGuard或备用只读数据库,请使用XTRSBY;

4.仅当其他方法都不可行时,再考虑使用XPLAIN;

5.XPREXT和XPREXC是类似于XTRACT和XECUTE,但为了提高SQLT的性能它们禁了一些SQLT的特性;

5.PROFILE方法:

1.当性能较差的SQL语句正好有已知的更好的执行计划时,在10g使用该PROFILE方法可以提供一个快速修复;

2.这个更好的执行计划可以位于相同或不同系统中的内存中,或者位于相同或不同系统的AWR中;换句话说,如果有更好的计划,该方法允许使用自定义SQL Profile “固定”该计划;使用该方法之前,必须对您要为其提取和固定此计划的SQL使用主要方法中的任何一个;

3.在11g或更高的版本你可以使用SQL Plan Management(SPM)来代替这个方法;

6.可以查看生成文件中的html报告;

7.相关文档:SQLT Diagnostic Tool (Doc ID 215187.1),SQLT 使用指南 (Doc ID 1677588.1(CHN),Doc ID 1614107.1(EN));

royalwzy的学习笔记