大型企業(yè)級(jí)應(yīng)用環(huán)境SQL優(yōu)化探秘課件

大型企業(yè)級(jí)應(yīng)用環(huán)境SQL優(yōu)化探秘個(gè)人介紹楊廷琨(yangtingkun)OracleACEDirectorITPUB數(shù)據(jù)庫(kù)管理區(qū)版主ACOUG核心會(huì)員參與編寫《Oracle數(shù)據(jù)庫(kù)性能優(yōu)化》、《OracleDBA手記》和《OracleDBA手記3》十四年的一線DBA經(jīng)驗(yàn)個(gè)人BLOG中積累了近3000篇原創(chuàng)技術(shù)文章云和恩墨技術(shù)總監(jiān)優(yōu)化的思路BEGINLOOPV:=F_FOUND_BOTTLENECK;P_OPTIMIZE(V);IF(F_PERFORMANCE)THEN EXIT;ENDIF;ENDLOOP;END;/一條SQL語句引發(fā)的血案一條SQL語句引發(fā)的血案新上線SQL？執(zhí)行計(jì)劃改變？一條SQL語句引發(fā)的血案SELECTUNICARD_NOFROMTF_R_UNICARDWHEREPRESENT_TAG='0'ANDLIMIT_DATE+0>SYSDATE+90ANDUNICARD_STATE||NULL='0'ANDUNICARD_VALCODE||NULL=:B3ANDROWNUM<=:B2ANDRESERVED1=:B1AND(RESERVED2<>'99'ORRESERVED2ISNULL)FORUPDATEFORUPDATE鎖表？LOCAL索引訪問效率低？ROWNUM綁定變量的值改變？COUNTSTOPKEY沒有生效？一條SQL語句引發(fā)的血案SQL>selectcolumn_name,NUM_DISTINCT,num_nulls,LAST_ANALYZED,HISTOGRAMfromdba_tab_columnswheretable_name='TF_R_UNICARD';COLUMN_NAMENUM_DISTINCTNUM_NULLSLAST_ANALYZEDHISTOGRAM-----------------------------------------------------------------------UNICARD_NO208636559001-4月-14HEIGHTBALANCEDUNICARD_BATCHNO548001-4月-14HEIGHTBALANCEDUNICARD_TYPE1001-4月-14NONEUNICARD_VALCODE9001-4月-14FREQUENCYUNICARD_STATE5001-4月-14FREQUENCYLIMIT_DATE6001-4月-14FREQUENCYBALANCE10001-4月-14NONEPRESENT_TAG1001-4月-14FREQUENCYRESERVED11386001-4月-14HEIGHTBALANCEDRESERVED217852423501-4月-14FREQUENCY一條SQL語句引發(fā)的血案SQL>SELECTCHILD_NUMBER,NAME,POSITION,DATATYPE_STRING,VALUE_STRINGFROMV$SQL_BIND_CAPTUREWHERESQL_ID='6zqqgm5k6nyt6'ANDCHILD_NUMBER=0;CHILD_NUMBERNAMEPOSITIONDATATYPE_STRINGVALUE_STRING----------------------------------------------------0:B31CHAR(32)040:B22NUMBER10:B13VARCHAR2(32)0422SQL>SELECTUNICARD_NO2FROMUCR_CARD_01.TF_R_UNICARD3WHEREPRESENT_TAG='0'4ANDLIMIT_DATE+0>SYSDATE+905ANDUNICARD_STATE||NULL='0'6ANDUNICARD_VALCODE||NULL='04'7ANDROWNUM<=18ANDRESERVED1='0422'9AND(RESERVED2<>'99'ORRESERVED2ISNULL);UNICARD_NO----------------------------------------XXXXXXXXXXXXXX一條SQL語句引發(fā)的血案Elapsed:00:00:02.71Statistics----------------------------------------------------------0recursivecalls0dbblockgets

14079consistentgets0physicalreads0redosize530bytessentviaSQL*Nettoclient492bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)1rowsprocessed一條SQL語句引發(fā)的血案SQL>SELECTUNICARD_NO2FROMUCR_CARD_01.TF_R_UNICARDPARTITION(P1)A3WHEREPRESENT_TAG='0'4ANDLIMIT_DATE+0>SYSDATE+905ANDUNICARD_STATE||NULL='0'6ANDUNICARD_VALCODE||NULL='04'7ANDROWNUM<=18ANDRESERVED1='0422'9AND(RESERVED2<>'99'ORRESERVED2ISNULL);norowsselectedExecutionPlan-----------------------------------------------------------------------------------|Id|Operation|Name|Rows|Pstart|Pstop|-----------------------------------------------------------------------------------|0|SELECTSTATEMENT||1||||*1|COUNTSTOPKEY||||||2|PARTITIONHASHSINGLE||1|1|1||*3|TABLEACCESSBYLOCALINDEXROWID|TF_R_UNICARD|1|1|1||*4|INDEXRANGESCAN|IDX_TF_R_UNICARD_4|11|1|1|-----------------------------------------------------------------------------------Statistics--------------------------------------------------1recursivecalls0dbblockgets

2662consistentgets0physicalreads0redosize327bytessentviaSQL*Nettoclient481bytesreceivedviaSQL*Netfromclient1SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)0rowsprocessed一條SQL語句引發(fā)的血案SQL>SELECT/*+GATHER_PLAN_STATISTICS*/UNICARD_NO2FROMUCR_CARD_01.TF_R_UNICARD3WHEREPRESENT_TAG='0'4ANDLIMIT_DATE+0>SYSDATE+905ANDUNICARD_STATE||NULL='0'6ANDUNICARD_VALCODE||NULL='04'7ANDROWNUM<=18ANDRESERVED1='0422'9AND(RESERVED2<>'99'ORRESERVED2ISNULL);UNICARD_NO----------------------------------------XXXXXXXXXXXXXX一條SQL語句引發(fā)的血案SQL>SELECT*FROMTABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'IOSTATS'));PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------------------SQL_IDgcm5adh9hr10a,childnumber0-------------------------------------SELECT/*+GATHER_PLAN_STATISTICS*/UNICARD_NOFROMUCR_CARD_01.TF_R_UNICARDWHEREPRESENT_TAG='0'ANDLIMIT_DATE+0>SYSDATE+90ANDUNICARD_STATE||NULL='0'ANDUNICARD_VALCODE||NULL='04'ANDROWNUM<=1ANDRESERVED1='0422'AND(RESERVED2<>'99'ORRESERVED2ISNULL)Planhashvalue:490980256----------------------------------------------------------------------------------------------|Id|Operation|Name|Starts|E-Rows|A-Rows|Buffers|----------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1||1|14079||*1|COUNTSTOPKEY||1||1|14079||2|PARTITIONHASHALL||1|1|1|14079||*3|TABLEACCESSBYLOCALINDEXROWID|TF_R_UNICARD|5|1|1|14079||*4|INDEXRANGESCAN|IDX_TF_R_UNICARD_4|5|1484|19366|1023|----------------------------------------------------------------------------------------------一條SQL語句引發(fā)的血案SELECTUNICARD_NOFROMTF_R_UNICARDWHEREPRESENT_TAG='0'ANDLIMIT_DATE+0>SYSDATE+90ANDUNICARD_STATE||NULL='0'ANDUNICARD_VALCODE||NULL=:B3ANDROWNUM<=:B2ANDRESERVED1=:B1AND(RESERVED2<>'99'ORRESERVED2ISNULL)FORUPDATESQL>ALTERSESSIONFORCEPARALLELDDL;Sessionaltered.SQL>CREATEINDEXIND_UNICARD_RES_VALCODE_DATEONTF_R_UNICARD2(RESERVED1,UNICARD_VALCODE||NULL,UNICARD_STATE||NULL,LIMIT_DATE+0)PARALLEL8ONLINE;Indexcreated.SQL>ALTERINDEXIND_UNICARD_RES_VALCODE_DATENOPARALLEL;Indexaltered.一條SQL語句引發(fā)的血案一條SQL語句引發(fā)的血案針對(duì)業(yè)務(wù)特點(diǎn)的表結(jié)構(gòu)模型合理的分區(qū)方案根據(jù)訪問方式設(shè)計(jì)索引N條SQL語句引發(fā)的血案SQL>select7.8+1.9+1.5+1.3+1.2fromdual;7.8+1.9+1.5+1.3+1.2-------------------

13.7SQL>select26393.9*60-124175-30408-24024-20109-18402fromdual;26393.9*60-124175-30408-24024-20109-18402-----------------------------------------1366516時(shí)間都去哪了？N條SQL語句引發(fā)的血案N條SQL語句引發(fā)的血案N條SQL語句引發(fā)的血案stattimecpu_totalmem_utildisk_utilsnet_utilDISK_FS_IO_RATENET_PACKET_RATE09:00:02,81.7,86.1,8.1,48.0,4.92,12.7,62162.509:05:02,87.3,86.3,8.9,48.0,5.57,12.7,74992.709:10:02,90.6,86.4,16.2,48.0,5.06,12.6,74005.409:15:02,94.2,86.5,19.7,48.0,5.11,12.6,72529.209:20:02,97.0,86.6,15.1,48.0,5.08,