SQL語(yǔ)句優(yōu)化使用指南2

1、SQL語(yǔ)句優(yōu)化使用指南1 產(chǎn)品功能Quest Central for Oracle SQL語(yǔ)句優(yōu)化模塊（SQLab Vision）是一種功能強(qiáng)大的SQL語(yǔ)句分析、測(cè)試、優(yōu)化工具，它可以貫穿Oracle數(shù)據(jù)庫(kù)應(yīng)用的整個(gè)生命周期，在開(kāi)發(fā)階段、試運(yùn)行階段和生產(chǎn)階段幫助獲得最佳SQL語(yǔ)句，以提高應(yīng)用系統(tǒng)的整體性能。SQLab Vision的主要功能如下：1) 查找對(duì)性能影響最大SQL語(yǔ)句，以便進(jìn)行有針對(duì)性的性能優(yōu)化。查找工作可以在SGA區(qū)中實(shí)時(shí)進(jìn)行，也可以通過(guò)后臺(tái)進(jìn)程收集一段時(shí)間SQL語(yǔ)句的執(zhí)行狀況和性能分析數(shù)據(jù)，并以緩沖區(qū)讀、磁盤(pán)寫(xiě)、記錄處理等方法加以標(biāo)識(shí)。為了幫助優(yōu)化I/O，SQLab Visio

2、n還可以以I/O瓶頸為基本切入點(diǎn)，查找哪些物理文件和表空間有過(guò)多的I/O問(wèn)題，以及這些I/O問(wèn)題是由哪些SQL語(yǔ)句引起的。2) SQLab Vision提供全面的SQL調(diào)優(yōu)環(huán)境。在SQLab Vision的調(diào)優(yōu)實(shí)驗(yàn)室中，可以提供和SQL語(yǔ)句優(yōu)化相關(guān)的所有信息。包括SQL的執(zhí)行計(jì)劃、涉及的表、相關(guān)的表是否經(jīng)過(guò)分析等等。用戶(hù)可以方便地設(shè)置Hints，自動(dòng)生成不同的優(yōu)化場(chǎng)景，隨意地檢查和比較SQL語(yǔ)句的執(zhí)行，并通過(guò)這種交互，解釋和優(yōu)化存儲(chǔ)過(guò)程與應(yīng)用程序中的SQL代碼。 SQLab Vision上下文相關(guān)的專(zhuān)家建議，幫助檢查和修改不優(yōu)化的SQL。3) SQLab Vision可以建立特定的元數(shù)據(jù)庫(kù)，記

3、錄與特定SQL語(yǔ)句相關(guān)的調(diào)優(yōu)信息，并可重復(fù)使用。SQLab Vision可以同時(shí)連接多個(gè)數(shù)據(jù)庫(kù)，便于DBA捕捉生產(chǎn)數(shù)據(jù)庫(kù)中的SQL語(yǔ)句，并在開(kāi)發(fā)環(huán)境中對(duì)其進(jìn)行調(diào)試。4) 集成了Quest獨(dú)有的StealthCollect 技術(shù)，通過(guò)低負(fù)載的方式從Oracle數(shù)據(jù)庫(kù)和操作系統(tǒng)中收集SQL 運(yùn)行狀態(tài)和資源統(tǒng)計(jì)數(shù)據(jù)，以全天時(shí)自動(dòng)運(yùn)行的方式，診斷可能發(fā)生的問(wèn)題，實(shí)現(xiàn)數(shù)據(jù)庫(kù)系統(tǒng)的監(jiān)控管理。5) 集成SQL Impact強(qiáng)大的應(yīng)用影響分析功能，確定數(shù)據(jù)庫(kù)機(jī)構(gòu)變化對(duì)應(yīng)用程序的影響，找到有問(wèn)題的SQL對(duì)應(yīng)的應(yīng)用程序，進(jìn)行應(yīng)用程序的審計(jì)和分析，預(yù)先找到應(yīng)用程序中存在的問(wèn)題。2 使用指南2.1 查找占用資源的SQ

4、L語(yǔ)句SQLab Vision采用三種方法幫助查找占用資源最多的SQL語(yǔ)句，以便有針對(duì)性地進(jìn)行優(yōu)化。2.1.1 從SGA區(qū)發(fā)現(xiàn)系統(tǒng)內(nèi)最活躍的SQL1. SQLab與數(shù)據(jù)庫(kù)連接后的第一個(gè)界面圖形地顯示了當(dāng)前系統(tǒng)內(nèi)SQL語(yǔ)句的活動(dòng)情況?；蛘唿c(diǎn)擊Tools-SQL Activity Graph，可以啟動(dòng)該界面。2. 在窗口左下角Threshold的入口值輸入A，可以表明了這A條SQL是主要活動(dòng)的語(yǔ)句。SQL活動(dòng)的判斷標(biāo)準(zhǔn)為以下三種：Row Processed(處理的數(shù)據(jù)行，行數(shù)太多可能和索引的使用效率有關(guān))，Disk Reads(對(duì)應(yīng)物理I/O)，Buffer Gets(對(duì)應(yīng)邏輯I/O)。3. 點(diǎn)擊

5、右下角的“Show SQL”按鈕，可以顯示SQLab認(rèn)定的最活動(dòng)的A條SQL語(yǔ)句。2.1.2 定義規(guī)則收集SQL1. 點(diǎn)擊Tools-SQL Collection可以啟動(dòng)一個(gè)窗口，以設(shè)置收集規(guī)則來(lái)收集SQL。例如我們可以指定收集一下用戶(hù)名為“quest”，每次運(yùn)行物理讀大于10000次的SQL語(yǔ)句。收集可以每次手動(dòng)執(zhí)行，也可以調(diào)度每隔10分鐘運(yùn)行1次。2. 在Query Tab中設(shè)置收集系統(tǒng)范圍內(nèi)、某會(huì)話(huà)范圍內(nèi)、存取某數(shù)據(jù)對(duì)象、或某表空間和數(shù)據(jù)文件的SQL語(yǔ)句。同時(shí)，可以設(shè)置僅收集那些活動(dòng)劇烈的SQL，如：I/O、排序操作多的SQL。3. 在Schedule Tab中設(shè)置上述收集規(guī)則應(yīng)用的時(shí)間

6、、中止的時(shí)間、和收集間隔等等規(guī)則。4. SQL Collection可以通過(guò)Oracle Job調(diào)度執(zhí)行，也可以交互啟動(dòng)，在Schedule Tab中點(diǎn)擊“Start Collecting”按鈕，即開(kāi)始收集SQL。5. 點(diǎn)擊Tools-Scheduled SQL Collection，可以查看已經(jīng)調(diào)度執(zhí)行的SQL收集的當(dāng)前狀況和執(zhí)行結(jié)果。2.1.3 I/O 瓶頸識(shí)別點(diǎn)擊Tools-I/O Bottleneck Resolution，可以顯示數(shù)據(jù)庫(kù)中每個(gè)文件、每個(gè)表空間的I/O情況，確定哪些物理文件和表空間有更多的I/O問(wèn)題。通過(guò)進(jìn)一步點(diǎn)擊，可以確定這些數(shù)據(jù)文件和表空間中有哪些表以及哪些SQL

7、占用更多的I/O。2.2 SQL語(yǔ)句優(yōu)化SQLab Vision既可以在上述被收集的SQL語(yǔ)句中通過(guò)單擊指定的SQL進(jìn)入Tuning Session進(jìn)行優(yōu)化，也可以調(diào)優(yōu)用戶(hù)直接提供的SQL。具體過(guò)程如下點(diǎn)擊Tools-Tuning Session。2.2.1 收集所有SQL語(yǔ)句優(yōu)化相關(guān)信息1啟動(dòng)Tuning Session，在SQL Tab的左上角區(qū)域?qū)懭胍韵耂QL語(yǔ)句。select * from scott.dept,scott.emp where dept.deptno=emp.deptno2 擊SQL Tab左上角的“Execute-Execute Current Scenario”按鈕

8、，可以執(zhí)行該SQL，并顯示執(zhí)行結(jié)果。 Statistics Tab顯示SQL語(yǔ)句的執(zhí)行計(jì)劃和每步的花銷(xiāo)。以及SQL執(zhí)行的跟蹤統(tǒng)計(jì)（如果沒(méi)有設(shè)置Trace功能，將沒(méi)有相關(guān)顯示）。包括SQL語(yǔ)句執(zhí)行的CPU、I/O資源占用，SQL處理的數(shù)據(jù)行數(shù)等等。 Results Tab 顯示SQL語(yǔ)句的執(zhí)行結(jié)果。 SQL Tab 顯示和SQL語(yǔ)句優(yōu)化相關(guān)的所有信息，包括SQL語(yǔ)句的執(zhí)行計(jì)劃，相關(guān)的表是否已經(jīng)Analysis，表的數(shù)據(jù)行，表中有哪些索引、這些索引建立在哪些字段上。開(kāi)發(fā)人員可以點(diǎn)擊Chart按鈕獲得更直觀的執(zhí)行計(jì)劃。也可以重建索引或重新進(jìn)行Analysis來(lái)優(yōu)化數(shù)據(jù)庫(kù)的性能。3 擊SQL Tab左

9、上角的“Explain”按鈕，可以獲得該SQL的執(zhí)行計(jì)劃和每一步操作訪問(wèn)的數(shù)據(jù)對(duì)象。2.2.2 專(zhuān)家建議及生成優(yōu)化腳本點(diǎn)擊SQL Tab左上角的“Advise”按鈕，可以獲得工具給出的專(zhuān)家建議。1 家建議在Advise Tab中出現(xiàn)，SQLab Vision會(huì)給出三種類(lèi)型的建議，包括需要重新寫(xiě)新的SQL腳本、需要進(jìn)行一些Schema的變化，以及一些警告類(lèi)型的建議。2開(kāi)發(fā)人員可以選擇對(duì)系統(tǒng)有用的建議（如SQLab Vision建議不要對(duì)某個(gè)表進(jìn)行全表掃描，但此表只有10行紀(jì)錄?？梢圆豢紤]此建議）。在該建議中點(diǎn)擊鼠標(biāo)右鍵Generate Single Scenario，可據(jù)專(zhuān)家建議自動(dòng)產(chǎn)生新的SQ

10、L，并自動(dòng)生成新的場(chǎng)景。對(duì)于需要一些Schema 變化和Warning的 建議內(nèi)容（如對(duì)某個(gè)表進(jìn)行Analysis，也會(huì)自動(dòng)生成相應(yīng)的SQL語(yǔ)句，執(zhí)行這些SQL語(yǔ)句可以獲得優(yōu)化結(jié)果。2.2.3 不同場(chǎng)景的執(zhí)行比較1 以點(diǎn)擊Action-Quick Scenarios可以自動(dòng)幫助建立四個(gè)缺省的場(chǎng)景，分別基于Rule Hints, First_Rows Hints, All_Rows Hints,Ordered RowsHints，點(diǎn)擊某個(gè)Hints 自動(dòng)給出執(zhí)行計(jì)劃等信息，也可以通過(guò)Action-New Scenario建立新的Scenario，在其中加上新的暗示，或完全重新寫(xiě)SQL（你認(rèn)為能更

11、好地完成同樣的工作）。2 點(diǎn)擊Action-Explain All Scenarios將解釋調(diào)優(yōu)會(huì)話(huà)中的所有Scenario。在解釋完成后，點(diǎn)擊窗口中的Comparison View Tab，可以比較不同SQL（不同的暗示或不同的寫(xiě)法）的執(zhí)行計(jì)劃。3 點(diǎn)擊Action-Execute All Scenarios將執(zhí)行所有Scenarios中的SQL。在執(zhí)行完成后，點(diǎn)擊窗口中的Comparison View Tab，可以比較不同SQL（不同的暗示或不同的寫(xiě)法）的執(zhí)行統(tǒng)計(jì)。 Statistics Tab顯示每個(gè)場(chǎng)景的執(zhí)行統(tǒng)計(jì)信息。如查詢(xún)時(shí)間、花銷(xiāo)值、處理的數(shù)據(jù)行，新場(chǎng)景和原始SQL的比較等等。 P

12、lan Tab 顯示每個(gè)場(chǎng)景執(zhí)行計(jì)劃的不同，可以非常方便地進(jìn)行兩兩比較。 SQL Tab顯示每個(gè)場(chǎng)景SQL語(yǔ)句的不同，可以非常方便地進(jìn)行兩兩比較。 Graphs圖形顯示每個(gè)場(chǎng)景的SQL語(yǔ)句的執(zhí)行情況。用戶(hù)可以按照響應(yīng)時(shí)間、掃描的數(shù)據(jù)行、物理I/O、邏輯I/O等調(diào)優(yōu)目標(biāo)進(jìn)行比較，從這些場(chǎng)景中選擇最優(yōu)化的情況。2.2.4 調(diào)優(yōu)暗示(Hints)點(diǎn)擊Action-New Scenarios，SQLab Vision 將生成一個(gè)新的場(chǎng)景，可以在這個(gè)場(chǎng)景中加入很多Hints。Hints 會(huì)決定SQL語(yǔ)句的執(zhí)行計(jì)劃，如我們想把dept表一直Cache在內(nèi)存中，可點(diǎn)擊Hints-Cache(tbl)。如果想

13、讓SQL語(yǔ)句按照自己的步驟來(lái)執(zhí)行，如先通過(guò)Dept表的索引而不是全表掃描。可以非常方便地通過(guò)SQLab Vision Hints來(lái)實(shí)現(xiàn)。SQLab Vision Hints為高級(jí)開(kāi)發(fā)人員提供了更便捷的方式來(lái)決定SQL語(yǔ)句的執(zhí)行計(jì)劃和方式。而且生成的新場(chǎng)景可以和原始SQL執(zhí)行比較，見(jiàn)2.3.3。2.2.5 執(zhí)行帶參數(shù)的SQL語(yǔ)句啟動(dòng)另外一個(gè)Tuning Session，在SQL Tab的左上角區(qū)域?qū)懭胍韵耂QL語(yǔ)句。select * from scott.dept,scott.emp where dept.deptno=:b1點(diǎn)擊SQL Tab左上角的“Execute-Execute Curre

14、nt Scenario”按鈕，可以執(zhí)行該SQL，并顯示執(zhí)行結(jié)果。在執(zhí)行過(guò)程中SQLab Vision提示輸入b1的值，輸入10然后按Execute按鈕。帶參數(shù)的SQL語(yǔ)句優(yōu)化和不帶參數(shù)的SQL語(yǔ)句優(yōu)化相同，參見(jiàn)3.2.1-3.2.4。2.3 使用StealthCollect AgentStealthCollect Agent安裝在Oracle數(shù)據(jù)庫(kù)服務(wù)器上，通過(guò)低負(fù)載的方式從數(shù)據(jù)庫(kù)外部收集SQL 運(yùn)行狀態(tài)和資源統(tǒng)計(jì)數(shù)據(jù)，以全天時(shí)自動(dòng)運(yùn)行的方式，診斷可能發(fā)生的問(wèn)題，實(shí)現(xiàn)數(shù)據(jù)庫(kù)系統(tǒng)的監(jiān)控管理。2.3.1 StealthCollect Agent的啟動(dòng)、關(guān)閉和連接點(diǎn)擊StealthCollect程序

15、組中的“Service Manager”， 可以啟動(dòng)和關(guān)閉StealthCollect Agent。StealthCollect Agent 必須啟動(dòng)，才能夠使用StealthCollect收集數(shù)據(jù)。進(jìn)入SQLab Vision,選擇Conect Using StealthCollect，或者進(jìn)入SQLab Vision后，點(diǎn)擊File-Connect，可以通過(guò)StealthCollect進(jìn)行聯(lián)系。也可以同時(shí)以O(shè)racle的方式進(jìn)行連接。2.3.2 收集當(dāng)前SQL語(yǔ)句的運(yùn)行狀況1 點(diǎn)擊Tools-Current Session Activity，在窗口的上方顯示當(dāng)前所有的Session以及每個(gè)

16、Session的活動(dòng)狀況，如Session的啟動(dòng)時(shí)間、資源使用、運(yùn)行程序?？梢赃x擇Pin Session把Session釘住，不管這個(gè)Session資源使用的情況，SQLab Vision將一直進(jìn)行跟蹤。2 查找占用資源最多的Session，點(diǎn)擊鼠標(biāo)右鍵 Show Session，可以顯示Session資源使用的詳細(xì)信息。 Session Activity 的上半部分顯示了所選Session中SQL語(yǔ)句的實(shí)際運(yùn)行情況，包括運(yùn)行時(shí)間，資源占用（CPU、I/O等待、磁盤(pán)讀）等等、SQL語(yǔ)句腳本。通過(guò)它我們可以對(duì)系統(tǒng)中SQL語(yǔ)句的運(yùn)行情況了如指掌。 Session Activity 的中部顯示了整個(gè)

17、Session在在時(shí)間標(biāo)記中系統(tǒng)資源的占用情況，包括CPU、I/O等等?？梢詫?duì)照SQL語(yǔ)句的實(shí)際運(yùn)行情況，判斷什么時(shí)間這個(gè)Session占用了更多的系統(tǒng)資源，而在這個(gè)時(shí)間點(diǎn)上哪些SQL在運(yùn)行。在通過(guò)Tuning Session對(duì)這些SQL進(jìn)行優(yōu)化。 Session Activity 的下半部分顯示了所選SQL的相關(guān)信息，便于查找這個(gè)SQL運(yùn)行過(guò)程中可能存在的問(wèn)題，進(jìn)行SQL語(yǔ)句的優(yōu)化。這些信息包括SQL的腳本，SQL排序的情況、SQL Redo Entry使用的情況等等，非常詳細(xì)。 SQL Activity 的從SQL語(yǔ)句執(zhí)行的角度進(jìn)行了統(tǒng)計(jì)，列出SQL語(yǔ)句的執(zhí)行次數(shù)、全表掃描、磁盤(pán)讀、索引使

18、用等信息。我們可以根據(jù)上述所有信息進(jìn)行排隊(duì)。從SQL的角度判斷需要調(diào)整的地方。選擇一個(gè)SQL，點(diǎn)擊鼠標(biāo)右鍵 Show Selected SQL，SQLab Vision將列出所有SQL，單擊某個(gè)SQL，可以進(jìn)行Tuning Session進(jìn)行優(yōu)化。 SQL Activity 也可以通過(guò)圖形方式表示SQL語(yǔ)句的運(yùn)行情況，點(diǎn)擊Activity Graphic Tab, 可以按照Buffer Gets,Disk Reads,DB Block Changed 等方式顯示前A個(gè)SQL占用資源的情況。2.3.3 收集歷史SQL語(yǔ)句的運(yùn)行狀況1.點(diǎn)擊Tools-Historical Collection，在

19、窗口的上方顯示歷史時(shí)間標(biāo)記中SQL語(yǔ)句對(duì)系統(tǒng)資源的使用情況，包括CPU、I/O等待、Buffer Gets、DB Block Change等等。可以簡(jiǎn)單地拖動(dòng)鼠標(biāo)根據(jù)資源使用情況選擇我們所查找的范圍。2.窗口的下方用圖形方式顯示歷史時(shí)間標(biāo)記中SQL語(yǔ)句對(duì)系統(tǒng)資源的使用情況，如CPU、I/O等待等等。選擇Graphic Tab，雙擊進(jìn)入資源使用的分析屏幕，我們可以根據(jù)SQL、用戶(hù)、程序、機(jī)器分析資源的使用情況，進(jìn)而確定在指定時(shí)間標(biāo)記中占用資源的SQL、應(yīng)用程序或用戶(hù)。選擇SQL Statement,找到占資源最大的SQL，雙擊可進(jìn)入SQLab Tuning Session進(jìn)行優(yōu)化。3在Historical Collecdtion窗口的下方同時(shí)用圖表方式顯示歷史時(shí)間標(biāo)記中SQL語(yǔ)句對(duì)系統(tǒng)資源的使用情況。選擇Table Tab，可以顯示SQL語(yǔ)句的執(zhí)行次數(shù)、全表掃描、磁盤(pán)讀、索引使用等信息。我們可以根據(jù)上述所有信息進(jìn)行排隊(duì)。從SQL的角度判斷需要調(diào)整的地方。我們可以選擇多個(gè)SQL，點(diǎn)擊鼠標(biāo)右鍵 Show Selected SQL，SQLab Vision將列出所有SQL，單擊某個(gè)SQL，可以進(jìn)行Tuning Sess