目錄：

Oracle數(shù)據(jù)完整性和鎖機(jī)制? 索引及優(yōu)化之表分析? 表分析、約束及表間關(guān)系? Oracle體系結(jié)構(gòu)1 Oracle體系結(jié)構(gòu)2? 海量數(shù)據(jù)庫及分區(qū)1? 海量數(shù)據(jù)庫及分區(qū)2? 海量數(shù)據(jù)庫及分區(qū)3? 海量數(shù)據(jù)庫及分區(qū)4? 高級SQL優(yōu)化(一)?? 高級SQL優(yōu)化(二)?? 高級SQL優(yōu)化(三) 常用優(yōu)化工具? PPT和源碼下載：???http://***/forum/posts/list/6365.html 配套視頻課程 Oracle性能優(yōu)化?http://***/product/601? 海量數(shù)據(jù)庫和高級SQL優(yōu)化?http://***/product/602 SQL*PLUS下使用AUTOTRACE 1.AUTOTRACE簡介?? AUTOTRACE是SQL*Plus的一項(xiàng)功能，其作用是自動跟蹤SQL語句，為SQL 語句生成一個 執(zhí)行計(jì)劃并且提供與 該語句的處理有關(guān)的統(tǒng)計(jì)信息。 SQL*Plus AUTOTRACE 可以用來替代 SQL Trace 使用，AUTOTRACE 的好處是不必設(shè)置跟蹤文件的格式，并且它將自動為 SQL 語句顯示執(zhí)行計(jì)劃。AUTOTRACE與執(zhí)行計(jì)劃的區(qū)別是AUTOTRACE 分析和執(zhí)行語句；而EXPLAIN PLAN僅分析語句，而不負(fù)責(zé)執(zhí)行語句。 AUTOTRACE在SQL*PLUS下執(zhí)行，使用AUTOTRACE不會產(chǎn)生跟蹤文件。 2.配置AUTOTRACE (1).確保表PLAN_TABLE已經(jīng)創(chuàng)建，如果沒有則如下創(chuàng)建： (2).確保角色plustrace已經(jīng)創(chuàng)建，如果沒有則如下創(chuàng)建： ? 3.使用AUTOTRACE 4. AUTOTRACE設(shè)置命令

序號	命令	解釋
1	SET AUTOTRACE OFF	此為默認(rèn)值，即關(guān)閉Autotrace
2	SET AUTOTRACE ON	產(chǎn)生結(jié)果集和解釋計(jì)劃并列出統(tǒng)計(jì)
3	SET AUTOTRACE ON EXPLAIN	顯示結(jié)果集和解釋計(jì)劃不顯示統(tǒng)計(jì)
4	SETAUTOTRACE TRACEONLY	顯示解釋計(jì)劃和統(tǒng)計(jì)，盡管執(zhí)行該語句但將看不到結(jié)果集
5	SET AUTOTRACE TRACEONLY STATISTICS	只顯示統(tǒng)計(jì)

參見每個設(shè)置的現(xiàn)場舉例 ?5. AUTOTRACE STATISTICS含義

序號	列名	解釋
1	recursive call	遞歸調(diào)用SQL的個數(shù)；Oracle在執(zhí)行這個SQL的時候，有時候會生成很多額外的SQL語句，這個就稱為遞歸調(diào)用
2	db block gets	從buffer cache中讀取的block的數(shù)量
3	consistent gets	從buffer cache中讀取的undo數(shù)據(jù)的block的數(shù)量
4	physical reads	從磁盤讀取的block的數(shù)量
5	redo size	DML生成的redo的大小
6	sorts (memory)	在內(nèi)存執(zhí)行的排序量
7	sorts (disk)	在磁盤上執(zhí)行的排序量,如果memory空間使用不足，是會使用disk的空間的
8	bytes sent via SQL*Net to client	利用sql*net傳入到client的字節(jié)數(shù)；
9	bytes received via SQL*Net from client	利用sql*net傳出client的字節(jié)數(shù)；

? 使用SQL調(diào)優(yōu)顧問 刪除掉bigtab和smalltab上的所有索引： ? ? 既然上述語句性能很差，那么怎么優(yōu)化呢？幸運(yùn)的是Oracle提供一個工具稱為SQL調(diào)優(yōu)顧問。從Oracle 10g起，可以使用SQL調(diào)優(yōu)顧問 (SQL Tuning Advisor ，STA)來獲得一個性能很差的語句的優(yōu)化結(jié)果。STA的特點(diǎn)是簡單、智能，DBA只需要調(diào)用函數(shù)就可以給出一個性能很差的語句的優(yōu)化結(jié)果，從而做到有的放矢！ 使用DBMS_SQLTUNE包來創(chuàng)建優(yōu)化任務(wù)并閱讀優(yōu)化建議： 創(chuàng)建完成后驗(yàn)證是否完成： ?創(chuàng)建完成后驗(yàn)證是否完成： ? 單擊該條目查看優(yōu)化結(jié)果 也可以通過SQL語句來查看結(jié)果，此方法是我們最喜歡的方法： ?? 進(jìn)行優(yōu)化： 優(yōu)化后在使用autotrace: 優(yōu)化后在使用autotrace: 結(jié)論：

項(xiàng)目	優(yōu)化前	優(yōu)化后	倍數(shù)
從持久層獲取consistent gets(從buffer cache中讀取的undo數(shù)據(jù)的block的數(shù)量)	21688	6950	3
物理讀physical reads(從磁盤讀取的block的數(shù)量)	21589	6928	3
遞歸調(diào)用recursive call	0	1	N/A
時間(毫秒)	2070	1078	2

使用STA能快速定位性能瓶頸，從而為性能優(yōu)化提供了準(zhǔn)確的依據(jù)！ 實(shí)時SQL監(jiān)視 實(shí)時SQL監(jiān)視(real-time SQL Monitorning)是Oracle 11g的另外一個新功能，其作用是允許用戶監(jiān)視正在執(zhí)行的SQL。默認(rèn)情況下，當(dāng)使用并行查詢、或者當(dāng)SQL執(zhí)行的CPU或I/O超過5秒鐘時會自動啟動。 也可以使用優(yōu)化提示強(qiáng)制使用實(shí)時SQL監(jiān)視功能，如下： select ? count(*) ? from bigtab a, smalltab b ?? where? a.object_name=b.table_name 如果要強(qiáng)制不使用實(shí)時SQL監(jiān)視功能，則也可以使用優(yōu)化提示： select ? count(*) ? from bigtab a, smalltab b ?? where? a.object_name=b.table_name 與實(shí)時SQL監(jiān)視相關(guān)的系統(tǒng)視圖包括： uV$SQL_MONITOR uV$SQL_PLAN_MONITOR uV$ACTIVE_SESSION_HISTORY uV$SESSION uV$SESSION_LONGOPS uV$SQL uV$SQL_PLAN 對于剛剛監(jiān)視的結(jié)果，可以使用DBMS包讀取： select dbms_sqltune.report_sql_monitor from dual; 1.實(shí)時SQL監(jiān)視示例1－執(zhí)行超過5秒的SQL ? ? ?在OEM中查看監(jiān)視結(jié)果，選擇“性能”->“其它監(jiān)視鏈接”->“SQL Monitoring”： ? ?查看具體的監(jiān)視報告(圖形化)： 單擊“文本報告”，則： 使用DBMS包查看監(jiān)視結(jié)果： 2.實(shí)時SQL監(jiān)視示例2－使用優(yōu)化提示強(qiáng)制監(jiān)視 ?? 結(jié)論： 1.實(shí)時SQL監(jiān)視通過OEM查看其監(jiān)視報告時，具有更好的圖形化的展示效果，因此更加直觀 2.如果監(jiān)視的SQL語句發(fā)現(xiàn)具有全表掃描等執(zhí)行計(jì)劃的特征，或者CPU時間和I/O時間比較長，則可以與SQL調(diào)優(yōu)顧問接合起來，不但能獲知性能瓶頸，而且能獲得Oracle推薦的優(yōu)化策略。 3.實(shí)踐中，程序員往往不加思考的按照自己的理解和經(jīng)驗(yàn)編寫SQL，此舉在90%的項(xiàng)目中存在，從而造成項(xiàng)目產(chǎn)品投用后很快就產(chǎn)生各種性能瓶頸，正確的做法應(yīng)該是 在準(zhǔn)備好足夠的測試數(shù)據(jù)，并且監(jiān)視每一條SQL并在開發(fā)的初始階段即 優(yōu)化之。 習(xí)題 1.在對索引的限制中，關(guān)于NOT和不等于的限制在11g數(shù)據(jù)庫CBO模式下還存在嗎，為什么？在RBO模式下呢？ 2.如果某個索引中的列具有可空屬性，則Oracle執(zhí)行類似 is null時不會使用索引，其原因是什么？ 3.Oralce具有那三種訪問路徑，其中最快的兩種是什么？ 4.什么情況下應(yīng)該使用復(fù)合索引，此時使用復(fù)合索引比使用多個單個索引具有哪些優(yōu)勢？ 5.分別配置并使用SQL優(yōu)化常用的三種工具：Autotrace、調(diào)優(yōu)顧問和實(shí)時監(jiān)視顧問，復(fù)習(xí)本課的舉例來加深理解。

轉(zhuǎn)載于:https://www.cnblogs.com/liuzhuqing/archive/2013/02/04/7480625.html

總結(jié)

以上是生活随笔為你收集整理的高级SQL优化(三) 常用优化工具 ——《12年资深DBA教你Oracle开发与优化——性能优化部分》...的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。