大型企业级应用环境SQL优化探秘课件

大型企业级应用环境SQL优化探秘个人介绍杨廷琨(yangtingkun)OracleACEDirectorITPUB数据库管理区版主ACOUG核心会员参与编写《Oracle数据库性能优化》、《OracleDBA手记》和《OracleDBA手记3》十四年的一线DBA经验个人BLOG中积累了近3000篇原创技术文章云和恩墨技术总监优化的思路BEGINLOOPV:=F_FOUND_BOTTLENECK;P_OPTIMIZE(V);IF(F_PERFORMANCE)THEN EXIT;ENDIF;ENDLOOP;END;/一条SQL语句引发的血案一条SQL语句引发的血案新上线SQL？执行计划改变？一条SQL语句引发的血案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语句引发的血案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语句引发的血案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语句引发的血案Elapsed:00:00:02.71Statistics----------------------------------------------------------0recursivecalls0dbblockgets

14079consistentgets0physicalreads0redosize530bytessentviaSQL*Nettoclient492bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)1rowsprocessed一条SQL语句引发的血案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语句引发的血案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语句引发的血案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语句引发的血案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语句引发的血案一条SQL语句引发的血案针对业务特点的表结构模型合理的分区方案根据访问方式设计索引N条SQL语句引发的血案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时间都去哪了？N条SQL语句引发的血案N条SQL语句引发的血案N条SQL语句引发的血案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,