Oracle11g性能優(yōu)化-執(zhí)行計(jì)劃

OracleERP最正確技術(shù)實(shí)踐E-BusinessSuiteOracle性能優(yōu)化之執(zhí)行方案Author: 南生: nansheng.su*hand-china.CreationDate: October19,2014LastUpdated: October19,2014DocumentRef: <DocumentReferenceNumber>Version: DRAFT1AApprovals:<Approver1><Approver2>autote*t"PICOracleLogo"\*Mergeformat CopyNumber _____DocumentControlChangeRecordDateAuthorVersionChangeReference2014-10-19snansDraft1aNoPreviousDocumentReviewersNamePositionDistributionCopyNo.NameLocationLibraryMasterProjectLibraryProjectManagerNoteToHolders:Ifyoureceiveanelectroniccopyofthisdocumentandprintitout,pleasewriteyournameontheequivalentofthecoverpage,fordocumentcontrolpurposes.Ifyoureceiveahardcopyofthisdocument,pleasewriteyournameonthefrontcover,fordocumentcontrolpurposes.ContentsTOC\o"1-4"\h\z\uDocumentControlii1.概要21.1SQL性能優(yōu)化概要21.2SQL語句緩存21.3驅(qū)動表21.4組合索引32.執(zhí)行方案52.1執(zhí)行方案概要52.2執(zhí)行方案案例一個(gè)52.3執(zhí)行方案步驟62.4全表掃描72.5通過ROWID的表存取82.6索引掃描82.74種類型的索引掃描93.未完待續(xù)113.OpenandClosedIssuesforthisDeliverable12OpenIssues12ClosedIssues12概要1.1SQL性能優(yōu)化概要在Oracle數(shù)據(jù)庫應(yīng)用系統(tǒng)中幾乎有80%的性能問題是有糟糕的SQL語句引起的。優(yōu)化是選擇最有效的執(zhí)行方案來執(zhí)行SQL語句的過程，這是在處理任何數(shù)據(jù)的語句中的一個(gè)重要步驟。對Oracle來說，執(zhí)行這樣的語句有許多不同的方法，譬如說，將隨著以什么順序訪問哪些表或索引的不同而不同。所使用的執(zhí)行方案可以決定語句能執(zhí)行得有多快。Oracle中稱之為優(yōu)化器〔Optimizer〕的組件用來選擇這種它認(rèn)為最有效的執(zhí)行方案。1.2SQL語句緩存為了不重復(fù)解析一樣的SQL語句(因?yàn)榻馕霾僮鞅葦M費(fèi)資源，會導(dǎo)致性能下降)，在第一次解析之后，ORACLE將SQL語句及解析后得到的執(zhí)行方案存放在存中。這塊位于系統(tǒng)全局區(qū)域SGA(systemglobalarea)的共享池(sharedbufferpool)中的存可以被所有的數(shù)據(jù)庫用戶共享。因此，當(dāng)你執(zhí)行一個(gè)SQL語句時(shí)，如果該語句和之前的執(zhí)行過的*一語句完全一樣，并且之前執(zhí)行的該語句與其執(zhí)行方案仍然在存中存在，則ORACLE就不需要再進(jìn)展分析，直接得到該語句的執(zhí)行路徑。ORACLE的這個(gè)功能大提高了SQL的執(zhí)行性能并大大節(jié)省了存的使用。使用這個(gè)功能的關(guān)鍵是將執(zhí)行過的語句盡可能放到存中，所以這要求有大的共享池(通過設(shè)置sharedbufferpool參數(shù)值)和盡可能的使用綁定變量的方法執(zhí)行SQL語句。--查看sharedbufferpool參數(shù)的值：命令：showparametershared_pool_size--我這邊的運(yùn)行結(jié)果例如SQL>showparametershared_pool_sizeNAMETYPEVALUEshared_pool_sizebiginteger400M共享池過小，會導(dǎo)致數(shù)據(jù)庫性能降低，通過如下語句修改共享池的大小。--修改共享池大小altersystemsetshared_pool_size=400M1.3驅(qū)動表驅(qū)動表(DrivingTable)。這個(gè)概念用于嵌套與HASH連接中。如果該表返回較多的行數(shù)據(jù)，則對所有的后續(xù)操作有負(fù)面影響。一般說來，是應(yīng)用查詢的限制條件后，返回較少行的表作為驅(qū)動表，所以如果一個(gè)大表在WHERE條件有限制條件(如等值限制)，則該大表作為驅(qū)動表也是適宜的，所以并不是只有較小的表可以作為驅(qū)動表，正確說法應(yīng)該為應(yīng)用查詢的限制條件后，返回較少行的表作為驅(qū)動表。在執(zhí)行方案中，應(yīng)該為靠上的那個(gè)表。例如：表A:cu*_shipment_headers_all頭表表B:cu*_shp_deliveries_all行表情況一：表cu*_shipment_headers_all數(shù)據(jù)比擬少，作為驅(qū)動表。情況二：表cu*_shp_deliveries_all限定查詢條件，返回?cái)?shù)據(jù)少，作為驅(qū)動表。1.4組合索引由多個(gè)列構(gòu)成的索引，如createinde*id*_emponemp(col1,col2,col3,……)，則我們稱id*_emp索引為組合索引。在組合索引中有一個(gè)重要的概念：引導(dǎo)列(leadingcolumn)，在上面的例子中，col1列為引導(dǎo)列。當(dāng)我們進(jìn)展查詢時(shí)可以使用wherecol1="，也可以使用wherecol1="andcol2="，這樣的限制條件都會使用索引，但是wherecol2="查詢就不會使用該索引。所以限制條件中包含先導(dǎo)列時(shí)，該限制條件才會使用該組合索引。在Oracle中可以創(chuàng)立組合索引，即同時(shí)包含兩個(gè)或兩個(gè)以上列的索引。在組合索引的使用方面，Oracle有以下特點(diǎn)：當(dāng)使用基于規(guī)則的優(yōu)化器〔RBO〕時(shí)，只有當(dāng)組合索引的前導(dǎo)列出現(xiàn)在SQL語句的where子句中時(shí)，才會使用到該索引；在使用Oracle9i之前的基于本錢的優(yōu)化器〔CBO〕時(shí)，只有當(dāng)組合索引的前導(dǎo)列出現(xiàn)在SQL語句的where子句中時(shí)，才可能會使用到該索引，這取決于優(yōu)化器計(jì)算的使用索引的本錢和使用全表掃描的本錢，Oracle會自動選擇本錢低的訪問路徑；從Oracle9i起，Oracle引入了一種新的索引掃描方式——索引跳躍掃描〔inde*skipscan〕，這種掃描方式只有基于本錢的優(yōu)化器〔CBO〕才能使用。這樣，當(dāng)SQL語句的where子句中即使沒有組合索引的前導(dǎo)列，并且索引跳躍掃描的本錢低于其他掃描方式的本錢時(shí)，Oracle就會使用該方式掃描組合索引；Oracle優(yōu)化器有時(shí)會做出錯誤的選擇，因?yàn)樗佟奥斆鳕暎膊蝗缥覀僑QL語句編寫人員更清楚表中數(shù)據(jù)的分布，在這種情況下，通過使用提示〔hint〕，我們可以幫助Oracle優(yōu)化器作出更好的選擇。上面4個(gè)例子的測試，可以參考網(wǎng)絡(luò)教程：.blogs./rootq/archive/2008/10/19/1314669.html執(zhí)行方案2.1執(zhí)行方案概要為了執(zhí)行語句，Oracle可能必須實(shí)現(xiàn)許多步驟。這些步驟中的每一步可能是從數(shù)據(jù)庫中物理檢索數(shù)據(jù)行，或者用*種方法準(zhǔn)備數(shù)據(jù)行，供發(fā)出語句的用戶使用。Oracle用來執(zhí)行語句的這些步驟的組合被稱之為執(zhí)行方案。執(zhí)行方案是SQL優(yōu)化中最為復(fù)雜也是最為關(guān)鍵的局部，只有知道了ORACLE在部到底是如何執(zhí)行該SQL語句后，才能知道優(yōu)化器選擇的執(zhí)行方案是否為最優(yōu)的。2.2執(zhí)行方案案例一個(gè)SQL代碼：SELECTename,job,sal,dnameFROMscott.emp,scott.deptWHEREemp.deptno=dept.deptnoANDNOTE*ISTS(SELECT*FROMsalgradeWHEREemp.salBETWEENlosalANDhisal)執(zhí)行方案：執(zhí)行順序：執(zhí)行順序的原則是：由上至下，從右向左由上至下：在執(zhí)行方案中一般含有多個(gè)節(jié)點(diǎn)，一樣級別(或并列)的節(jié)點(diǎn)，靠上的優(yōu)先執(zhí)行，靠下的后執(zhí)行從右向左：在*個(gè)節(jié)點(diǎn)下還存在多個(gè)子節(jié)點(diǎn)，先從最靠右的子節(jié)點(diǎn)開場執(zhí)行。存取方法：1全表掃描〔FullTableScans,FTS或TABLEACESSFULL〕2通過ROWID的表存取〔TableAccessbyROWID或rowidlookup〕3索引掃描〔Inde*Scan或inde*lookup〕2.3執(zhí)行方案步驟執(zhí)行方案解析：第３步和第６步分別的從EMP表和SALGRADE表讀所有行。第５步在PK_DEPTNO索引中查找由步驟３返回的每個(gè)DEPTNO值。它找出與DEPT表中相關(guān)聯(lián)的那些行的ROWID。第４步從DEPT表中檢索出ROWID為第５步返回的那些行。由黑色字框指出的步驟在行源上操作，如做2表之間的關(guān)聯(lián)，排序，或過濾等操作。第２步實(shí)現(xiàn)嵌套的循環(huán)操作(相當(dāng)于C語句中的嵌套循環(huán))，接收從第３步和第４步來的行源，把來自第３步源的每一行與它第４步中相應(yīng)的行連接在一起，返回結(jié)果行到第１步。第１步完成一個(gè)過濾器操作。它接收來自第２步和第６步的行源，消除掉第２步中來的，在第６步有相應(yīng)行的那些行，并將來自第２步的剩下的行返回給發(fā)出語句的用戶或應(yīng)用。執(zhí)行方案步驟：首先，Oracle實(shí)現(xiàn)步驟３，并一行一行地將結(jié)果行返回給第２步。對第３步返回的每一行，Oracle實(shí)現(xiàn)這些步驟：Oracle實(shí)現(xiàn)步驟５，并將結(jié)果ROWID返回給第４步。Oracle實(shí)現(xiàn)步驟４，并將結(jié)果行返回給第２步。Oracle實(shí)現(xiàn)步驟２，將承受來自第３步的一行和來自第４步的一行，并返回給第１步一行。Oracle實(shí)現(xiàn)步驟６，如果有結(jié)果行的話，將它返回給第１步。Oracle實(shí)現(xiàn)步驟１，如果從步驟６返回行，Oracle將來自第２步的行返回給發(fā)出SQL語句的用戶。2.4全表掃描為實(shí)現(xiàn)全表掃描，Oracle讀取表中所有的行，并檢查每一行是否滿足語句的WHERE限制條件。Oracle順序讀取分配給表的每個(gè)數(shù)據(jù)塊，直到讀到表的最高水線處(highwatermark,HWM，標(biāo)識表的最后一個(gè)數(shù)據(jù)塊)。一個(gè)多塊讀操作可以使一次I/O能讀取多塊數(shù)據(jù)塊(db_block_multiblock_read_count參數(shù)設(shè)定)，而非只讀取一個(gè)數(shù)據(jù)塊，這極大的減少了I/O總次數(shù)，提高了系統(tǒng)的吞吐量，所以利用多塊讀的方法可以高效實(shí)現(xiàn)全表掃描，而且只有在全表掃描的情況下才能使用多塊讀操作。在這種訪問模式下，每個(gè)數(shù)據(jù)塊只被讀一次。由于HWM標(biāo)識最后一塊被讀入的數(shù)據(jù)，而delete操作不影響HWM值，所以一個(gè)表的所有數(shù)據(jù)被delete后，其全表掃描的時(shí)間不會有改善，一般我們需要使用truncate命令來使HWM值歸為0。幸運(yùn)的是oracle10G后，可以人工收縮HWM的值。由FTS模式讀入的數(shù)據(jù)被放到高速緩存的LeastRecentlyUsed(LRU)列表的尾部，這樣可以使其快速交換出存，從而不使存重要的數(shù)據(jù)被交換出存。使用FTS的前提：在較大的表上不建議使用全表掃描，除非取出數(shù)據(jù)的比擬多，超過總量的5%--10%，或你想使用并行查詢功能時(shí)。2.5通過ROWID的表存取行的ROWID指出了該行所在的數(shù)據(jù)文件、數(shù)據(jù)塊以及行在該塊中的位置，所以通過ROWID來存取數(shù)據(jù)可以快速定位到目標(biāo)數(shù)據(jù)上，是Oracle存取單行數(shù)據(jù)的最快方法。為通過ROWID存取表，Oracle首先要獲取被選擇行的ROWID，或從語句的WHERE子句中得到，或者通過表的一個(gè)或多個(gè)索引的索引掃描得到。Oracle然后以得到的ROWID為依據(jù)定位每個(gè)被選擇的行。此存取方法不會用到多塊讀操作，一次I/O只能讀取一個(gè)數(shù)據(jù)塊。我們經(jīng)常在執(zhí)行方案中看到該存取方法，如通過索引查詢數(shù)據(jù)。2.6索引掃描通過inde*查找到數(shù)據(jù)對應(yīng)的rowid值(對于非唯一索引可能返回多個(gè)rowid值)，然后根據(jù)rowid直接從表中得到具體的數(shù)據(jù)。一個(gè)rowid唯一的表示一行數(shù)據(jù)，該行對應(yīng)的數(shù)據(jù)塊是通過一次i/o得到的，該次i/o只會讀取一個(gè)數(shù)據(jù)庫塊。在索引中，除了存儲每個(gè)索引的值外，索引還存儲具有此值的行對應(yīng)的ROWID值。索引掃由2步組成：(1)掃描索引得到對應(yīng)的rowid值。(2)通過找到的rowid從表中讀出具體的數(shù)據(jù)。每步都是單獨(dú)的一次I/O，但對于索引，由于經(jīng)常使用，絕大多數(shù)都已經(jīng)CACHE到存中，所以第1步的I/O經(jīng)常是邏輯I/O，即數(shù)據(jù)可以從存中得到。但是對于第2步來說，如果表比擬大，則其數(shù)據(jù)不可能全在存中，則其I/O很有可能是物理I/O，這是一個(gè)機(jī)械操作，相對邏輯I/O來說，極其費(fèi)時(shí)間。所以如果對大表進(jìn)展索引掃描，取出的數(shù)據(jù)如果大于總量的5%--10%，使用索引掃描會效率下降很多。2.74種類型的索引掃描索引唯一掃描(inde*uniquescan)索引圍掃描(inde*rangescan)索引全掃描(inde*fullscan)索引快速掃描(inde*fastfullscan)索引唯一掃描(inde*uniquescan)通過唯一索引查找一個(gè)數(shù)值經(jīng)常返回單個(gè)ROWID。如果該唯一索引有多個(gè)列組成(即組合索引)，則至少要有組合索引的引導(dǎo)列參與到該查詢中，如創(chuàng)立一個(gè)索引：createinde*id*_testonemp(ename,deptno,loc)。則selectenamefromempwhereename=‘JACK’anddeptno=‘DEV’語句可以使用該索引。如果該語句只返回一行，則存取方法稱為索引唯一掃描。而selectenamefromempwheredeptno=‘DEV’語句則不會使用該索引，因?yàn)閣here子句種沒有引導(dǎo)列。如果存在UNIQUE或PRIMARYKEY約束〔它保證了語句只存取單行〕，Oracle經(jīng)常實(shí)現(xiàn)唯一性掃描。參考上面的執(zhí)行方案截圖。索引圍掃描(inde*rangescan)使用索引存取多行數(shù)據(jù)，如果索引是組合索引，如索引唯一掃描所示，且selectenamefromempwhereename=‘JACK’anddeptno=‘DEV’語句返回多行數(shù)據(jù)，雖然該語句還是使用該組合索引進(jìn)展查詢，可此時(shí)的存取方法稱為索引圍掃描。在唯一索引上使用索引圍掃描的典型情況下是在謂詞(where限制條件)中使用了圍操作符(如>、<、<>、>=、<=、between)。索引全掃描(inde*fullscan)與全表掃描對應(yīng)，也有相應(yīng)的全索引掃描。在*些情況下，可能進(jìn)展全索引掃描而不是圍掃描，需要注意的是全索引掃描只在CBO模式(基于本錢的優(yōu)化方式)下才有效。CBO根據(jù)統(tǒng)計(jì)數(shù)值得知進(jìn)展全索引掃描比進(jìn)展全表掃描更有效時(shí)，才進(jìn)展全索引掃描，而且此時(shí)查詢出的數(shù)據(jù)都必須從索引中可以直接得到。索引快速掃描(inde*fastfullscan)掃描索引中的所有的數(shù)據(jù)塊，與inde*fullscan很類似，但是一個(gè)顯著的區(qū)別就是它不對查詢出的數(shù)據(jù)進(jìn)展排序，即數(shù)據(jù)不是以排序順序被返回。在這種存取方法中，可以使用多塊讀功能，也可以使用并行讀入，以便獲得最大吞吐量與縮短執(zhí)行時(shí)間。3.未完待續(xù)OpenandClosedIssuesfort