优化sql的利器SQLT

Sqlt 适用环境：

1、 sql 优化功底不是很深的同学

2、 sql 太复杂（2000行以上的SQL）

3、 快速优化sql，不考虑业务逻辑

Setup SQLT Method ：

SQL> conn /as sysdba

SQL> @/home/oracle/sqlt/install/sqcreate.sql

…

Define SQLTXPLAIN password ( hidden and case sensitive).

Password for user SQLTXPLAIN:

Re-enter password:

…

Default tablespace [UNKNOWN]: USERS  <== hidden and case sensitive

…

Temporary tablespace [UNKNOWN]: TEMP  <== hidden and case sensitive

…

Main application user of SQLT: doudou     <==administrator;grant SQLT_USER_ROLE

…

Oracle Pack license [T]: T  <==choose

…

SQCREATE completed. Installation completed successfully.

Remove SQLT Method ：

@/home/oracle/sqlt/install/sqdrop.sql

使用方法：

SQL> start sqltxtract.sql 0w6uydn50g8c <=SQL sql_id

附表：

Administrator privilege

grant connect,resource to doudou;

grant SQLT_USER_ROLE to doudou;

What is different SQLT XECUTE Method and SQLT XTRACT Method ?

SQLT XECUTE Method

? Pros

? Accurate 10053 (considers bind peeking)

? Plan execution statistics (sets STATISTICS_LEVEL=ALL)

? Actual Execution Plan (may be different than explain plan)

? Invokes SQL Tuning Advisor

? No need to know, or get before hand, hash_value or sql_id

? 10046 trace and Trace Analyzer (if installed)

? Cons

? SQL is executed (may take long time)

? Need to know the values of bind variables

SQLT XTRACT Method

? Pros

? Child plans and plan statistics (if STATISTICS_LEVEL=ALL)

? Actual Execution Plan (may be different than explain plan)

? Invokes SQL Tuning Advisor

? SQL is not executed

? No need to know the values of bind variables

? Easy to execute (if hash_value or sql_id are known)

? Cons

? 10053 is generated based on EXPLAIN PLAN FOR (may not

be accurate due to binds peeking)

? Requires to know, or get before hand, hash_value or sql_id

总结：

优化SQL的又一利器SQLT（SQLTXPLAIN），SQLT把收集的信息和建议方法都形成了html（main.html;readme.html;lite.html）格式，方便用户查看！

Main.html ：SQLT给出了收集的信息和调优建议

Readme.html ：SQLT给出了具体优化的方法

Lite.html:SQLT 给出了简易的PLANS信息 

参考文献： Document 215187.1