優化sql的利器SQLT

lovehewenyu發表於2013-10-15

優化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]:<==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的又一利器SQLTSQLTXPLAIN),SQLT把收集的資訊和建議方法都形成了htmlmain.html;readme.html;lite.html)格式,方便使用者檢視!

Main.htmlSQLT給出了收集的資訊和調優建議

Readme.htmlSQLT給出了具體優化的方法

Lite.html:SQLT給出了簡易的PLANS資訊 

 

參考文獻:Document 215187.1

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26442936/viewspace-774380/,如需轉載,請註明出處,否則將追究法律責任。

相關文章