您好,登錄后才能下訂單哦!
優(yōu)化sql的利器SQLT是怎么樣的,相信很多沒有經(jīng)驗(yàn)的人對(duì)此束手無策,為此本文總結(jié)了問題出現(xiàn)的原因和解決方法,通過這篇文章希望你能解決這個(gè)問題。
優(yōu)化sql的利器SQLT
Sqlt適用環(huán)境:
1、sql優(yōu)化功底不是很深的同學(xué)
2、sql太復(fù)雜(2000行以上的SQL)
3、快速優(yōu)化sql,不考慮業(yè)務(wù)邏輯
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
優(yōu)化SQL的又一利器SQLT(SQLTXPLAIN),SQLT把收集的信息和建議方法都形成了html(main.html;readme.html;lite.html)格式,方便用戶查看!
Main.html:SQLT給出了收集的信息和調(diào)優(yōu)建議
Readme.html:SQLT給出了具體優(yōu)化的方法
Lite.html:SQLT給出了簡易的PLANS信息
Document 215187.1
看完上述內(nèi)容,你們掌握優(yōu)化sql的利器SQLT是怎么樣的的方法了嗎?如果還想學(xué)到更多技能或想了解更多相關(guān)內(nèi)容,歡迎關(guān)注億速云行業(yè)資訊頻道,感謝各位的閱讀!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。