优化sql的利器SQLT是怎么样的,相信很多没有经验的人对此束手无策,为此本文总结了问题出现的原因和解决方法,通过这篇文章希望你能解决这个问题。
优化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
看完上述内容,你们掌握优化sql的利器SQLT是怎么样的的方法了吗?如果还想学到更多技能或想了解更多相关内容,欢迎关注亿速云行业资讯频道,感谢各位的阅读!
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。