oracle電子-dba手記chapter網(wǎng)名Eygle

蓋國強（網(wǎng)蓋國強（網(wǎng)名OracleACE總監(jiān)，云和恩墨 超級版主，DBA服務(wù)的倡導(dǎo)者和實踐有《深入解析Oracle》、《循序漸進Oracle》、《深入淺出個人： 索引與數(shù)據(jù)索引是Oracle數(shù)據(jù)庫中最常見的一種數(shù)據(jù)方式，而且在多數(shù)情況下，能否有效地使用索引，也決定了一個系統(tǒng)性能的優(yōu)劣。本章通過一個簡單的索引優(yōu)化案例，來分析索引的使用對高DBTime使用之SQL發(fā)最初在客戶的系統(tǒng)中，通過AWR報告采樣CPU較高的SQL語句。在3小時的采樣Snap Snap SessionsBegin3065527-Mar-09End3065827-Mar-09DB179.41231.10CPU消耗分別占到總DBTime2.7%1.5%，分別27位，而且這兩個明顯完全相同的SQL文本，因為后者多出一個空格而成為了獨立的SQL：SQLorderedbyCPU DB/Inst:ERPDB/ERPDB1Snaps:30655-->ResourcesreportedforPL/SQLcodeincludestheresourcesusedbyallSQLstatementscalledbythecode.->%TotalDBTimeistheElapsedTimeoftheSQLstatementdividedintotheTotalDatabaseTimemultipliedby100CPUper%TimeTime(s)Exec(s)DB SQL 2.7a0s339yuwgngm ame,shortcodefromeeodewhereeeode>''orderbyeeode 1.50ucbx6n0c6dtf ame,shortcodefromeeodewhereeeode>''orderbyeeode由于執(zhí)行次數(shù)較高，前一個SQL在邏輯讀部分也被采樣出來，排在第7SQLorderedby DB/Inst:ERPDB/ERPDB1Snaps:30655-->ResourcesreportedforPL/SQLcodeincludestheresourcesusedbyallSQLstatementscalledbythecode.->TotalBuffer ->CapturedSQLaccount 30.6%of BufferGets per %TotalTime(s)Time SQL 374.17 ame,shortcodefromeeodewhereeeode>''orderbySQLAWR采樣獲取了這個SQL的執(zhí)行計劃，注意到這個SQL執(zhí)行了全表掃描：SQL>select*fromtable(dbms_xplan.display_awr('a0s339yuwgngm'));SQL_ID ame,shortcodefromeeodewhereeeode>''orderbyeeodePlanhashvalue:599125693|Id| |Name|Rows|Bytes|TempSpc|Cost(%CPU)| |0|SELECT|||||506|||1|SORTORDER||27290|1039K2792K506|00:00:07|2|TABLE|EEODE|272901039K|226| 在當前環(huán)境中通過autotraceSQL> ame,shortcodefromcnderp.eeodewhereeeode>''orderby 66513rowsselected.ExecutionPlan|Id| |Name|Rows|Bytes|TempSpc|Cost(%CPU)|0|SELECT |66513|2468K | (1)|1|SORTORDER |66513|2468K|6280K| (1)|2|TABLEACCESS |EEODE|66513|2468K |838(1)1recursive0dbblock consistent0physical0redo3324223bytessentviaSQL*Nettoclient49266bytesreceivedviaSQL*Netfromclient4436SQL*Netroundtripsto/from1sorts(memory)0sorts rowsprocessed從輸出的統(tǒng)計信息中注意到，這個查詢返回了66513行記錄，通過排序輸出，產(chǎn)生了3779執(zhí)行計劃獲取及索引優(yōu)cname、shortcode上創(chuàng)建一個復(fù)合索引，使得SQL的執(zhí)行計劃發(fā)生了徹底改變，SQL的邏輯讀從3779次降低到446次，Cost從1499次降低到761次，這個SQL的執(zhí)行性能得到了提升：SQL> ame,shortcodefromcnderp.eeodewhereeeode>''orderby 66566rowsselected.ExecutionPlan|Id| | |Rows|Bytes|TempSpc|Cost(%CPU)|0|SELECT |66567|2470K |761(2)|1|SORTORDER |66567|2470K|6280K|761(2)|2|INDEXFASTFULLSCAN |66567|2470K| |100(1) 1recursive0dbblock446consistent0physical0redo3326742bytessentviaSQL*Nettoclient49299bytesreceivedviaSQL*Netfromclient4439SQL*Netroundtripsto/from1sorts(memory)0sorts66566rowsprocessedSQL的單次執(zhí)行時間由原來的0.38秒降低到0.28秒，CPU時間消耗得以縮減，而在邏輯讀采樣列表中，這個SQL徹底了：SQLorderedbyCPU DB/Inst:ERPDB/ERPDB1Snaps:30626-->ResourcesreportedforPL/SQLcodeincludestheresourcesusedbyallSQLstatementscalledbythecode.->%TotalDBTimeistheElapsedTimeoftheSQLstatementdividedintotheTotalDatabaseTimemultipliedby100CPU%TimeTimeExecDBSQL 2.0a0s339yuwgngm ame,shortcodefromeeodewhereeeode>''orderbyeeodeINDEXFASTFULLSCAN與排創(chuàng)建索引后由于需要查詢的字段都可以通過索引獲取，所以不再需要具體的數(shù)據(jù)表，數(shù)據(jù)庫通過INDEXFASTFULLSCAN來完成查詢，由于查詢包含一個ORDERBY排序運算，執(zhí)行計劃中顯示了SORTORDERBY的過程，統(tǒng)計數(shù)據(jù)中顯示了查詢產(chǎn)生了一次內(nèi)存排序：SQL> ame,shortcodefromcnderp.eeodewhereeeode>''orderby |Id| | |Rows|Bytes|TempSpc|Cost(%CPU)|0|SELECT||66567|||1|SORTORDER||665672470K||||2|INDEXFAST|||2470K||446consistent1sorts(memory)0sorts66566rowsprocessed大家都知道索引是有序的數(shù)據(jù)，那么為什么INDEXFASTFULLSCAN需要排序呢？INDEXFASTFULLSCAN與常見的INDEXFULLSCAN又有何區(qū)別呢？13-1是最常用的B*Tree13-1B*Tree基于索引的有序，如果時從葉節(jié)點自左向右，出來的數(shù)據(jù)就將是有序的，如果掃描一定的范圍就是通常所說的INDEXRANGESCAN；如果順序所有的葉節(jié)點這就是INDEXFULLSCAN。由INDEXFULLSCAN而INDEXFASTFULLSCAN以Exent（如 Oracle10gR2版本SQL>select*fromv$version;OracleDatabase10gEnterpriseEditionRelease10.2.0.3.0-ProdPL/SQLRelease10.2.0.3.0-Production TNSforLinux:Version10.2.0.3.0-ProductionNLSRTLVersion10.2.0.3.0–Production首先基于DBA_OBJECTSSQL>createtableeygleasselect*fromdba_objectswhere1=0;Tablecreated.SQL>createindexidx_object_idoneygle(object_id);Indexcreated.SQL>insertintoeygleselect*fromdba_objectswhererownum10000rowscreated.SQL>commit;Commitcomplete.SQL>altertableeyglemodify(object_idnotnull);Tablealtered. yzetableeyglecomputestatisticsfortableforallindexesforallindexedcolumns;10INDEXFASTFULLSCAN，使用了7個邏輯讀SQL>setautotraceSQL>selectobject_idfromeyglewhererownum<11;10rowsExecutionPlanhashvalue:|Id| | |Rows|Bytes|Cost(%CPU)| |0|SELECT||||2|00:00:011|COUNT|||| |2|INDEXFASTFULL||||2|00:00:01PredicateInformation(identifiedbyoperation1-filter(ROWNUM<11)0recursive0dbblock7consistent0physical10rowsINDEXFULLSCAN，使用了3個邏輯讀，輸出的結(jié)果自低至高，SQL>select/*+index(eygleidx_object_id)*/object_idfromeyglewhererownum<11;2345678910rowsExecutionPlanhashvalue:|0|SELECT|||402|00:00:011|COUNT|||0|SELECT|||402|00:00:011|COUNT|||| |2|INDEXFULL|IDX_OBJECT_ID||2||PredicateInformation(identifiedbyoperationid):1-filter(ROWNUM<11)1recursive0dbblock3consistent0physical10rows如果增加ORDERBY子句，INDEXFASTFULLSCANSQL>select/*+index_ffs(eygleidx_object_id)*/2fromeyglewhererownum<11orderby10rowsExecutionPlanhashvalue:|Id| | |Rows|Bytes|TempSpc|Cost(%CPU)|0|SELECT||1040|||1|SORTORDER||1040||2|COUNT|||||||3|INDEXFASTFULL|IDX_OBJECT_ID|10000|40000|6|INDEXFULLSCAN則無須排序SQL>selectobject_idfromeyglewhererownum<11orderby10rowsselected.ExecutionPlanPlanhashvalue:|Id| | |Rows|Bytes|Cost(%CPU)| |0|SELECT||10402|00:00:011|COUNT|||| 2|INDEXFULL|||IDX_OBJECT_ID|||2降序INDEXFULLSCAN消除排序的優(yōu)INDEXFULLSCAN在正常情況下，通過升序自左向右進行數(shù)據(jù)，以有序的結(jié)果集返回查詢，所以往往可以用于消除排序，而使用降序索引，也可以在某些環(huán)境下實現(xiàn)SQL的優(yōu)化與改進。以下是曾經(jīng)遇到的一個相關(guān)案例，由于一個典型的翻頁查詢，了數(shù)據(jù)庫大量的排序，極度影響了數(shù)據(jù)的性能，這個分頁查詢代碼如下：SELECTFROM(SELECTt.*,ROWNUMFROM(SELECTu.numuserid,u.vc2username,u.numusertype,u.numrank,u.numgender,u.numage,u.numdistrict,u.vc2district,u.numlooking,u.numality,u.numability,u.numzodiac,u.numexperienceFROMhw_user4loveuWHEREu.numintention<>99ANDu.numgender=:1ORDERBYu.numusertypeDESC,u.numrankDESC,u.numuseridDESC)tWHEREROWNUM<=WHEREi>查看該SQL語句的執(zhí)行計劃，發(fā)現(xiàn)該SQL對三個底層表執(zhí)行了全表掃描，而分頁查詢實質(zhì)上是一個視圖（HW_USER4LOVE），整個查詢邏輯讀高達44760：ExecutionSELECTSTATEMENTOptimizer=CHOOSE(Cost=31692Card=400VIEW(Cost=31692Card=401COUNT VIEW(Cost=31692Card=582622 SORT(ORDERBYSTOPKEY)(Cost=31692Card=582622Bytes=30296344) HASHJOIN(Cost=7435Card=582622 HASHJOIN(Cost=4991Card=583296 TABLEACCESS(FULL)OF'HW_USERPROFILE'(Cost=1752Card=583296 TABLEACCESS(FULL)OF'HW_USER'(Cost=1799Card=1380038 TABLEACCESS(FULL)OF'HW_USERSCORE'(Cost=506Card=1332871255dbblock44760consistent70299physical1sorts20rows selecttextfromdba_viewswhereview_name=upper('HW_User4Love');TEXTselectu.numUserIdasnumUserId,u.vc2UserNameasvc2UserName,u.numUserTypeasnumUserType,u.vc2MobileNumberasvc2MobileNumber,<省略大量字段s.numAbilityasnumAbilityfromHW_Useru,HW_UserProfilep,HW_UserScorewhereu.numUserId=p.numUserIdandu.numUserId=s.numUserIdands.numExperience>100withreadonlySQL>selectcount(*)fromhw_user;SQL>selectcount(*)fromhw_userprofile;SQL>selectcount(*)fromhw_userscore;SQL>SQL>selectcount(*)fromhw_user4love;顯然每次通過視圖全表掃描三個底層表是產(chǎn)生性能問題的主要原因，同時大量數(shù)據(jù)掃描的結(jié)果是量記錄滿足條件被返回，顯然也不利于性能。這個SQL應(yīng)用是用于用戶積分顯示的，用戶經(jīng)常，但是可以不必提供實時計算，基于這個考慮，CREATEMATERIALIZEDVIEWBUILDIMMEDIATEREFRESHCOMPLETESTARTWITHNEXTtrunc(sysdate+1)+4/24ENABLEQUERYREWRITEselectu.numUserIdasnumUserId,u.vc2UserNameasvc2UserName,u.numUserTypeasnumUserType,u.vc2MobileNumberasvc2MobileNumber,<省略大量字段s.numalityasnums.numAbilityasnumAbilityfromHW_Useru,HW_UserProfilep,HW_UserScorewhereu.numUserId=p.numUserIdandu.numUserId=s.numUserIdands.numExperience> ExecutionSELECTSTATEMENTOptimizer=CHOOSE(Cost=2381Card=400VIEW(Cost=2381Card=401COUNT2VIEW(Cost=2381Card=102802 SORT(ORDERBYSTOPKEY)(Cost=2381Card=102802 TABLEACCESS(FULL)OF'HW_USER4LOVE'(Cost=337Card=1028023446consistent2048physical1sorts(memory)0sorts20rows SQLorderbyu.numUserTypedesc,u.numRankdesc,u.numUserId SQL>createindexidx_desconHW_USER4LOVE(numUserTypedesc,SQL>createindexidx_desconHW_USER4LOVE(numUserTypedesc,numRankdesc,numUserIddesc);Indexcreated.再次執(zhí)行原查詢語句，注意此時數(shù)據(jù)庫選擇了INDEXFULLSCAN，邏輯讀降低到88ExecutionSELECTSTATEMENTOptimizer=CHOOSE(Cost=826Card=400VIEW(Cost=826Card=401COUNT VIEW(Cost=826Card=102802 TABLEACCESS(BYINDEXROWID)OF'HW_USER4LOVE'(Cost=826Card=102802 INDEX(FULLSCAN)OF'IDX_DESC'(NON-UNIQUE)(Cost=2688consistent2physical0sorts(memory)20rowsINDEXFASTFULLSCAN的數(shù)對于INDEXFASTFULLSCAN（SQL>altersystemflushshared_pool;SQL>altersystemflushbuffer_cache;SQL>alterSQL>altersystemflushshared_pool;SQL>altersystemflushbuffer_cache;SQL>altersessionsetevents'10046tracenamecontextforever,level12';SQL>selectobject_idfromeyglewhererownum<11;SQL>altersessionsetevents'10046tracenamecontext在的10046文件末端可以獲得這個SQL執(zhí)行的詳細過程關(guān)鍵的數(shù)據(jù)是dbfilesequentialread文件4的第38435塊，然后通過dbfilescatteredread文件4的38436順序下來的5個數(shù)據(jù)塊：PARSINGINCURSOR#4len=44dep=0uid=31oct=3lid=31tim=1210453070162506hv=1134303234selectobject_idfromeyglewhererownum<11ENDOFSTMTPARSE#4:c=90000,e=81959,p=16,cr=62,cu=0,mis=1,r=0,dep=0,og=1,tim=1210453070162498BINDS#4:EXECWAIT#4:nam='SQL*Netmessagetoclient'ela=4driverid=1650815232#bytes=1p3=0obj#=10235WAIT#4:nam='dbfilesequentialread'ela=35file#=4block#=38435blocks=1obj#=19466tim=1210453070163104WAIT#4:nam='dbfilescatteredread'ela=146file#=4block#=38436blocks=5obj#=19466tim=1210453070163482FETCH#4:c=0,e=691,p=6,cr=6,cu=0,mis=0,r=1,dep=0,og=1,tim=1210453070163577FETCH#4:c=0,e=76,p=0,cr=1,cu=0,mis=0,r=9,dep=0,og=1,tim=1210453070164270STAT#4id=1cnt=10pid=0pos=1obj=0op='COUNTSTOPKEY(cr=7pr=6pw=0time=698STAT#4id=2cnt=10pid=1pos=1obj=19466op='INDEXFASTFULLSCANIDX_OBJECT_ID(cr=7pw=0time=677通過DBA_EXTENTSSQL>selectsegment_name,file_id,extent_id,block_id,blocks,bytesfrom2whereowner='EYGLE'andsegment_name='IDX_OBJECT_ID'orderby408418428438為了分析數(shù)據(jù)的順序，可以首先通過索引將段頭38433和38434兩個數(shù)據(jù)塊轉(zhuǎn)儲出來，這兩個塊SQL>altersystemdumpSQL>altersystemdumpdatafile4blockmin38433blockmax38434;Systemaltered.其內(nèi)容為1個1級位圖塊和1個2級位圖塊，在空間請求時，須位圖塊來找到空閑空間***2009-04-12Startdumpdatablockstsn:4file#:4minblk38433maxblk38434buffertsn:4rdba:0x01009621(4/38433)scn:0x0000.00721389seq:0x02:0x04tail:frmt:0x02chkval:0xca31type:0x20=LEVELBITMAPBLOCKHexdumpofblock:st=0,typ_found=1DumpofLevelBitmapnbits:2nranges:2 parentdba:0x01009622poffset:0unformatted:0 total:16 usefulblock:3 4:FULL5:FULL6:FULL7:FULL8:FULL9:FULL10:FULL12:FULL13:FULL14:FULLbuffertsn:4rdba:0x01009622scn:0x0000.007213ceseq:0x01:0x04tail:frmt:0x02chkval:0xcbf9type:0x21=SECONDLEVELBITMAPBLOCKHexdumpofblock:st=0,typ_found=1DumpofSecondLevelBitmapnumber: nfree: ffree: Inc#:0Objd:19466L1Ranges0x01009621Free:1Inst:0x01009681Free:3Inst:Enddumpdatablockstsn:4file#:4minblk38433maxblkSQL>altersystemSQL>altersystemdumpdatafile4block38435;Systemaltered.這個PAGETABLESEGMENTHEADER實際上也就是ASSM管理中的第一個三級位圖塊，其內(nèi)容Startdumpdatablockstsn:4file#:4minblk38435maxblk38435buffertsn:4rdba:0x01009623(4/38435)scn:0x0000.007213ceseq:0x01:0x04tail:frmt:0x02chkval:0x7056type:0x23=PAGETABLESEGMENTHEADERHexdumpofblock:st=0,typ_found=1Extent0x01009621length:0x01009641length:0x01009681length:0x0100aa31length:8AuxillaryMapExtent0:L1dba:0x01009621Datadba:0x01009624Extent1:L1dba:0x01009621Datadba:0x01009641Extent2:L1dba:0x01009681Datadba:0x01009682Extent3:L1dba:0x01009681Datadba:0x0100aa31SecondLevelBitmapblockDBA1:Enddumpdatablockstsn:4file#:4minblk38435maxblk所以INDEXFASTFULLSCAN實際上做的工作就是，首先段頭塊，然后向下找到索引的根節(jié)點，WAIT#4:WAIT#4:nam='dbfilesequentialread'ela=35file#=4block#=38435blocks=1obj#=19466tim=1210453070163104WAIT#4:nam='dbfilescatteredread'ela=146file#=4block#=38436blocks=5obj#=19466tim=1210453070163482如果進行一下輕微的限制，通過設(shè)置db_file_multiblock_read_count=1使多塊讀每次也僅一個數(shù)據(jù)：SQL>altersessionsetdb_file_multiblock_read_count=1;SQL>altersystemflushshared_pool;SQL>altersystemflushSQL>altersessionsetevents'10046tracenamecontextforever,level12';SQL>selectobject_idfromeyglewhererownum<11;SQL>altersessionsetevents'10046tracenamecontext看一下文件的信息，數(shù)據(jù)庫此時只要3個數(shù)據(jù)塊就完成了任務(wù)，第一個塊是段頭信息，第二PARSINGINCURSOR#3len=44dep=0uid=31oct=3lid=31tim=1210454174185664hv=1134303234selectobject_idfromeyglewhererownum<11ENDOFSTMTBINDS#3:EXECWAIT#3:nam='dbfilesequentialread'ela=33file#=4block#=38435blocks=1obj#=19466tim=1210454174186227WAIT#3:nam='dbfilesequentialread'ela=26file#=4block#=38436blocks=1obj#=19466tim=1210454174186391WAIT#3:nam='dbfilesequentialread'ela=27file#=4block#=38437blocks=1obj#=19466tim=1210454174186491FETCH#3:c=0,e=521,p=3,cr=6,cu=0,mis=0,r=1,dep=0,og=1,tim=1210454174186546FETCH#3:c=0,e=86,p=0,cr=1,cu=0,mis=0,r=9,dep=0,og=1,tim=1210454174187135STAT#3id=1cnt=10pid=0pos=1obj=0op='COUNTSTOPKEY(cr=7pr=3pw=0time=531STAT#3id=2cnt=10pid=1pos=1obj=19466op='INDEXFASTFULLSCANIDX_OBJECT_ID(cr=7pw=0time=513SQL>SQL>ALTERSESSIONSETEVENTS'immediatetracenameTREEDUMPlevel19466';Sessionaltered.索引的具體結(jié)構(gòu)如下所示，第一個branch節(jié)點也就是根節(jié)點，其地址為0x1009624，正是FILE4Block***SESSIONID:(138.53783)2009-04-12begintreebranch: (0:nrow:20,level:leaf:0x100962716815655(-1:nrow:539rrow:leaf:0x100962816815656(0:nrow:533rrow:leaf: (1:nrow:533rrow:leaf:0x100962616815654(2:nrow:533rrow:(3:nrow:533rrow:(4:nrow:533rrow:(5:nrow:533rrow:(6:nrow:533rrow:(7:nrow:533rrow:(8:nrow:533rrow:(9:nrow:533rrow:(10:nrow:272rrow:(11:nrow:533rrow:(12:nrow:533rrow:(13:nrow:533rrow:(14:nrow:533rrow:(15:nrow:533rrow:(16:nrow:533rrow:(17:nrow:270rrow:(18:nrow:391rrow:endtreeSQL>SQL>altersystemdumpdatafile4blockmin38436blockmax38437;Systemaltered.Startdumpdatablockstsn:4file#:4minblk38436maxblk38437buffertsn:4rdba:0x01009624(4/38436)Branchblockheaderaddresskdxcolev --kdxcolevLevelLevel1Level02KDXCOLEVFlags=--kdxcolok --指示索引上是否正在發(fā)生修改塊結(jié)構(gòu)的事kdxcoopc0x80:opcode=0:iotflags=isconverted=Y--操作代kdxconco kdxcosdc1 kdxconro19 --表示當前索引節(jié)點中索引條目的數(shù)量，但是注意，不包括kdxbrlmc指針kdxcofbo66=0x42--表示當前索引節(jié)點中可用空間的起始點相對當前塊的位移量kdxcofeo7885=0x1ecd--表示當前索引節(jié)點中可用空間的最尾端的相對當前塊的位移量kdxcoavs7819表示當前索引塊中的可用空間總量，用kdxcofeokdxcofbo得到kdxbrlmc16815655=0x1009627--left-mostchildblockkdxbrsno18kdxbrbksz8056kdxbr2urrcrow#0[8047]dba16815656=0x1009628下級節(jié)點的塊地址col0;len3;(3):c20647col1;row#1[8038]dba:col0;len3;(3):c20ccol1;row#2[8029]dba:col0;len3;(3):c211col1;row#3[8020]dba:colcol0;len3;(3):c216col1;row#4[8011]dba:col0;len3;(3):c21ccol1;endofbranchblockdump以下是第一個葉節(jié)點的信息，這也正是查詢的前10條記錄返回的數(shù)據(jù)塊buffertsn:4rdba:0x01009625scn:0x0000.007213d3seq:0x01:0x06tail:0x13d30601frmt:0x02chkval:0x2625type:0x06=transdataLeafblockheaderaddress231482980=0xdcc2664kdxcolev0KDXCOLEVFlags=--kdxcolokkdxcoopc0x80:opcode=0:iotflags=isconverted=Ykdxconco2kdxcosdckdxconro533kdxcofbo1102=0x44ekdxcofeo1108=0x454kdxcoavs6kdxlesplkdxlendekdxlenxt16815654=0x1009626--下一個葉節(jié)點的地址kdxleprv16815656=0x1009628--上一個葉節(jié)點的地址kdxledsz0kdxlebkszTyp=2Len=3:c2,c,14SQL>selectdump(1119,16)fromTyp=2Len=3:c2,c,14SQL>selectdump(1119,16)fromc20c14row#0[1108]flag:S-,lock:2,len=13col0;len3;(3):c20c14col1;len6;(6):0100962c00row#1[1121]flag:S-,lock:2,len=13col0;len3;(3):c20c15col1;len6;(6):0100962c00row#2[1134]flag:S-,lock:2,len=13col0;len3;(3):c20c16col1;len6;(6):0100962c00row#3[1147]flag:S-,lock:2,len=13col0;len3;(3):c20c17col1;len6;(6):0100962c00row#531[8006]flag:S-,lock:2,colcol0;len3;(3):c211col1;len6;(6):0100963400row#532[8019]flag:S-,lock:2,len=13col0;len3;(3):c21134col1;len6;(6):0100963400endofleafblockdumpEnddumpdatablockstsn:4file#:4minblk38436maxblkINDEXFULLSCAN的數(shù)SQL>altersystemflushshared_pool;SQL>altersystemflushbuffer_cache;SQL>altersessionsetSQL>altersystemflushshared_pool;SQL>altersystemflushbuffer_cache;SQL>altersessionsetevents'10046tracenamecontextforever,levelSQL>select/*+index(eygleidx_object_id)*/object_idfromeyglewhererownum<11;SQL>altersessionsetevents'10046tracenamecontextoff';PARSINGINCURSOR#1len=78dep=0uid=31oct=3lid=31tim=1210456280357654select/*+index(eygleidx_object_id)*/object_idfromeyglewhererownum<11ENDOFSTMTBINDS#1:EXECWAIT#1:nam='dbfilesequentialread'ela=8877file#=4block#=38436blocks=1obj#=19466tim=1210456280367040WAIT#1:nam='dbfilesequentialread'ela=21file#=4block#=38439blocks=1obj#=19466tim=1210456280367151FETCH#1:c=0,e=9179,p=2,cr=2,cu=0,mis=0,r=1,dep=0,og=1,tim=1210456280367212FETCH#1:c=0,e=86,p=0,cr=1,cu=0,mis=0,r=9,dep=0,og=1,tim=1210456280367802STAT#1id=1cnt=10pid=0pos=1obj=0op='COUNTSTOPKEY(cr=3pr=2pw=0time=9185STAT#1id=2cnt=10pid=1pos=1obj=19466op='INDEXFULLSCANIDX_OBJECT_ID(cr=3pr=2pw=0time=9165us)'這里的數(shù)據(jù)要從索引的最左端，而根據(jù)前面的數(shù)據(jù)結(jié)構(gòu)，索引最左端的葉節(jié)點為0x1009627，也正是INDEXFULLSCAN的第一個數(shù)據(jù)塊：***SESSIONID:(159.7366)2009-04-12Startdumpdatablockstsn:4file#:4minblk38439maxblk38439buffertsn:4rdba:0x01009627(4/38439)Leafblockheaderaddress231159396=0xdc73664kdxcolev0KDXCOLEVFlags=--kdxcolokkdxcoopc0x80:opcode=0:iotflags=isconverted=Ykdxconco2kdxcosdckdxconro539kdxcofbo1114=0x45akdxcofeo1128=0x468kdxcoavs14kdxlesplkdxlendekdxlenxt16815656=0x1009628kdxleprv0=0x0kdxledszkdxlebkszrow#0[1128]flag:,lock:0,len=12col0;len2;(2):c103col1;len6;(6):0100962000row#1[1140]flag:,lock:0,len=12col0;len2;(2):c104col1;len6;(6):0100962000row#2[1152]flag:,lock:0,len=12col0;len2;(2):c105col1;len6;(6):0100962000row#3[1164]flag:,lock:0,len=12col0;len2;(2):c106col1;len6;(6):0100962000row#4[1176]flag:,lock:0,len=12col0;len2;(2):c107col1;len6;(6):0100962000row#5[1188]flag:,lock:0,len=12col0;len2;(2):c108col1;len6;(6):0100962000row#6[1200]flag:,lock:0,len=12col0;len2;(2):c109col1;len6;(6):0100962000row#538[8019]flag:,lock:0,len=13col0;len3;(3):c20646col1;len6;(6):0100963000endofleafblockdump鍵值順序與索引的性使用索引通常能夠提高查詢的性能，索引是否足夠優(yōu)化，仍然可能成為影響性能的因素。在一個復(fù)合引中，鍵值的順序也可能成為影響索引性能的決定因素。在一次客戶系統(tǒng)優(yōu)化中，發(fā)現(xiàn)了如下SQL有兩萬多次的邏輯讀：SQL>SELECTFROMWHERE(bkverISNULLORbkver=AND (ver<=0AND(passedISNULLORpassed<AND((rwcode=ORrwcode='7620'ORrwcode='7621'ORrwcode=ORrwcode='7623'ORrwcode='7624'ORrwcode= 1087rowsExecution|Id | |Rows|Bytes|Cost(%CPU)|0|SELECT |21388|11M(1)|1| ||2|TABLEACCESSBYINDEXROWID| 83|45235(0)|3|INDEXRANGE |CATLISTASK_BKVER_VER_PASS_RW 83 (0)|4|TABLEACCESSBYINDEXROWID| |21305|11M(1)|5|INDEXRANGE (1)recursive0dbblockgets21446consistentgets87rowsSQLSQL>SELECTFROMWHERE(bkverISNULLORbkver=AND (ver<=0AND(passedISNULLORpassed<AND((rwcode='1701')ORrwcode=)716rowsselected.ExecutionPlan|Id| | |Bytes|Cost(%CPU)|0|SELECT |5347|2845K|18730(1)|1| |2|TABLEACCESSBYINDEXROWID| 21|11445 52(0)|3 INDEXRANGE |CATLISTASK_BKVER_VER_PASS_RW 21 37(0)|4|TABLEACCESSBYINDEXROWID| |5326|2834K|18678(1)|5 INDEXRANGE |13256(1)recursive0dbblockgets21302consistentgets0sorts0sorts16rows VER、PASSED、RWCODE、RWXLOCK前4個字段在查詢中都被到?，F(xiàn)在要的是，這個索引是否高效，能否通過更合適的索引創(chuàng)建來減少邏輯讀，提升SQL的性能。以下列表是幾個主要字段的數(shù)據(jù)行分布，其中BKVER的選擇性不高，只有5個取值，VER次之，有個取值，PASSED選擇性最好，有521-4732506-2748892531-1--1-101493231223910建議用戶按照RWCODE、PASSED、VER、BKVER、RWXLOCK的順序SQL的邏輯讀降低到334次，性能得到了極大的提升：SQL>SELECTFROMWHERE(bkverISNULLORbkver=AND (ver<=0AND(passedISNULLORpassed<AND((rwcode='1849')ORrwcode='7620'ORrwcode=ORrwcode='7622'ORrwcode='7623'ORrwcode=ORrwcode='7625'ORrwcode=) 10142rowsselected.ExecutionPlanPlanhashvalue:|Id |Rows|Bytes|Cost(%CPU)|0|SELECT||31978||1|| |||2|INLIST| |||3||||*4INDEXRANGE |||||5INLIST |||||6|TABLEACCESSBYINDEX |31806|16M|21964(1)|*7 INDEXRANGE |202(1)PredicateInformation(identifiedbyoperation4-access(("RWCODE"='1849'OR"RWCODE"='7620'…)AND"PASSED"ISNULLAND"VER"<=0)filter("VER"<=0AND("BKVER"ISNULLOR"BKVER"=0))-access(("RWCODE"='1849'OR"RWCODE"='7620'…)AND"PASSED"<64ANDfilter("VER"<=0AND("BKVER"ISNULLOR"BKVER"=0)ANDLNNVL("PASSED"ISrecursive0dbblock334consistent142rowsSQL的邏73次SQL>SELECTFROMWHERE(bkverISNULLORbkver=AND (ver<=0AND(passedISNULLORpassed<AND((rwcode='1701')ORrwcode=)730rowsExecutionPlanhashvalue:|Id |Rows|Bytes|Cost(%CPU)|0|SELECT |7994|4254K(1)|1| |||2|INLIST 3|TABLEACCESSBYINDEX 43|23435|(0)4INDEXRANGE|43|4(0)|5INLIST||||||6|7951||(1)7INDEXRANGE |7967|(0)PredicateInformation(identifiedbyoperation-access(("RWCODE"='1701'OR"RWCODE"='7640')AND"PASSED"ISNULLAND"VER"<=0)filter("VER"<=0AND("BKVER"ISNULLOR"BKVER"=0))-access(("RWCODE"='1701'OR"RWCODE"='7640')AND"PASSED"<64AND"VER"<=0)filter("VER"<=0AND("BKVER"ISNULLOR"BKVER"=0)ANDLNNVL("PASSED"ISNULL))1recursive0dbblock73consistentphysical30rows使用基于函數(shù)的索引提升查詢性SnapSnap SessionsCurs/SessBeginSnap:EndSnapSnap SessionsCurs/SessBeginSnap:End2433612-Aug-092435912-Aug-09180.72數(shù)據(jù)庫系統(tǒng)的Top5TimedEvents如下，在這個列表中，GlobalCacheCrRequestTop5Timed % Time(s)Elaglobalcachecrrequest1,018,511CPUdbfilesequentialdbfilescatteredbufferbusyglobalGlobalCache的請求意味著跨實例的，這可能和多節(jié)點大批量的數(shù)據(jù)有關(guān)，面對這部分問題重點仍然要去關(guān)注SQLSQL列表部分，從第一個SQLTopSQL每次執(zhí)行的邏輯讀大約是15511，執(zhí)行較為頻繁，值得關(guān)注：SQLorderedbyGetsforDB:RACInstance:RAC1Snaps:24336-->EndBufferGets ->NotethatresourcesreportedforPL/SQLincludestheresourcesusedbyallSQLstatementscalledwithinthePL/SQLcode.AsindividualSQLstatementsarealsoreported,itispossibleandvalidforthesummedtotal%toexceed100 Buffer ExecutionsGetsperExec%TotalTime(s)Time(s)Hash 250.61Module:SELECT"BQYS_YZ"."YZLX", "BQYS_YZ"."YZXH","BQYS_YZ"."ZYH" "BQYS_YZ"."BAH" S_YZ"."FYLB" "BQYS_YZ"."YSDM" "YSXM" "BQYS_YZ"."BQ" "BQYS_YZ"."KSRQ""BQYS_YZ"."ZXRQ" "BQYS_YZ"."FL"SQL的代碼和執(zhí)行計劃如下：p55a1$getplan.sh2507268569SELECT"BQYS_YZ"."YZLX","BQYS_YZ"."YZXH","BQYS_YZ"."ZYH""BQYS_YZ"."BAH","BQYS_YZ"."FYLB","BQYS_YZ"."YSDM"BQYS_YZ"."ZBFLAG",'chw_brxm'"chw_brxm"FROM"BQYS_YZ"WHERE("BQYS_YZ"."BQ"=:BQ)and("BQYS_YZ"."ZCFLAG"='1')and(y/mm/dd')=:rq1)and("BQYS_YZ"."YZLX"=:yzlx33rowsHASH_VALUEEXECUTIONS PER_GETSMODULE 3578.6glxt.exe| |PHV/Object |Rows|Bytes|Cost|SELECT | ||||TABLEACCESSBYINDEXROWID |87K||INDEXRANGE |||注意這個SQL使用了索引數(shù)據(jù)，圖13-2這個索引是基于BQ和YZLX兩個字段建立的13-2BQYS_YZ_BQ_YZLX_IDXselectbq,count(*)frombqys_yzgroupbybq;selectyzlx,count(*)frombqys_yzgroupby124SQL>SELECT"BQYS_YZ"."YZLX","BQYS_YZ"."YZXH","BQYS_YZ"."ZYH",2"BQYS_YZ"."FYLB","BQYS_YZ"."YSDM","BQYS_YZ"."YSXM",3"BQYS_YZ"."KSRQ","BQYS_YZ"."ZXRQ","BQYS_YZ"."FL",4"BQYS_YZ"."YPDM","BQYS_YZ"."YPGG",5"BQYS_YZ"."ZLFLAG","BQYS_YZ"."DW","BQYS_YZ"."RCS",6"BQYS_YZ"."RSL","BQYS_YZ"."CZYDM",7"BQYS_YZ"."FYSJ","BQYS_YZ"."TSDW","BQYS_YZ"."FYFF",8"BQYS_YZ"."YTFLAG","BQYS_YZ"."GZFLAG",9"BQYS_YZ"."ZHFLAG","BQYS_YZ"."ZXJL","BQYS_YZ"."ZXFLAG","BQYS_YZ"."YZBZ","BQYS_YZ"."BYLX","BQYS_YZ"."JE","BQYS_YZ"."LSL","BQYS_YZ"."SLGX","BQYS_YZ"."JL","BQYS_YZ"."FZXH","BQYS_YZ"."FZBZ","BQYS_YZ"."YZTJ","BQYS_YZ"."LJSJ","BQYS_YZ"."YRFLAG","BQYS_YZ"."QZBZ","BQYS_YZ"."CZY_HS","BQYS_YZ"."SFDLDM","BQYS_YZ"."TYZXH","BQYS_YZ"."FZXZ","BQYS_YZ"."KSDM","BQYS_YZ"."YZFL","BQYS_YZ"."SQD_XH","BQYS_YZ"."BZSM","BQYS_YZ"."ZCFLAG","BQYS_YZ"."ZBFLAG",'chw_brxm'FROMWHERE("BQYS_YZ"."BQ"=AND("BQYS_YZ"."ZCFLAG"=AND(TO_CHAR("BQYS_YZ"."CZSJ",'yyyy/mm/dd')=AND("BQYS_YZ"."YZLX"=;96rowsExecutionSELECTSTATEMENTOptimizer=CHOOSE(Cost=12283Card=3750TABLEACCESS(BYINDEXROWID)OF'BQYS_YZ'(Cost=12283Card=375 INDEX(RANGESCAN)OF'BQYS_YZ_BQ_YZLX_IDX'(NON-UNIQUE)(Cost=2240recursive0dbblockgets15277consistentgets4physical0redo17498bytessentviaSQL*Netto717bytesreceivedviaSQL*Netfrom8SQL*Netroundtripsto/from0sorts0sorts96rows發(fā)現(xiàn)通過索引的數(shù)據(jù)，邏輯讀的確高達15277次，而查詢條件中注意到，CZSJ是個時間字WHEREWHERE("BQYS_YZ"."BQ"=AND("BQYS_YZ"."ZCFLAG"=AND(TO_CHAR("BQYS_YZ"."CZSJ",'yyyy/mm/dd')=AND("BQYS_YZ"."YZLX"=Indexcreated.再來看剛才的SQLExecution 0SELECTSTATEMENTOptimizer=CHOOSE(Cost=670Card=37510TABLEACCESS(BYINDEXROWID)OF'BQYS_YZ'(Cost=670Card=37521BITMAPCONVERSION(TO32BITMAP43BITMAPCONVERSION(FROM54SORT(ORDER65INDEX(RANGESCAN)OF'BQYS_YZ_FBI'(NON-UNIQUE)(Cost=5073BITMAPCONVERSION(FROM87INDEX(RANGESCAN)OF'BQYS_YZ_BQ_YZLX_IDX'(NON-UNIQUE)(Cost=2