Oracle_性能查询

Oracle数据库性能问题脚本Zhaoyuanjie2019年7月31日星期三Oracle10g新技术2/10TopqueriesbyresourceusageDimensions•Resource(CPUtime,elapsedtime,buffergets,physicalreads,rowsreturned)•Execution(Acrossallexecutions,perexecution)•SQL(AllSQL,varioustypesofquerygeneratorqueries,inserts,updates,LIKE,…)2019年7月31日星期三Oracle10g新技术3/10按照使用资源列出前100个--Getthetop100queriesbytotalnumberofdisk_reads_deltaSELECT*FROM(SELECTSQL_ID,SUM(disk_reads_delta)ASDiskReadsFROMDBA_HIST_SQLSTATSWHERESNAP_ID209ANDSNAP_ID=210ANDS.MODULE='ClaimCenter'GROUPBYSQL_IDHAVINGSUM(disk_reads_delta)=0ORDERBY2DESC)DERIVED_TABLEWHEREROWNUM=100;2019年7月31日星期三Oracle10g新技术4/10按照使用资源列出100个--Getthetop100queriesbytotalnumberofdisk_reads_deltabyexecutionSELECT*FROM(SELECTSQL_ID,CASESUM(EXECUTIONS_DELTA)WHEN0THEN0ELSESUM(disk_reads_delta)/SUM(EXECUTIONS_DELTA)ENDASDiskReads/ExecutionFROMDBA_HIST_SQLSTATSWHERESNAP_ID209ANDSNAP_ID=210ANDS.MODULE='ClaimCenter'GROUPBYSQL_IDHAVINGCASESUM(EXECUTIONS_DELTA)WHEN0THEN0ELSESUM(disk_reads_delta)/SUM(EXECUTIONS_DELTA)END=0ORDERBY2DESC)DERIVED_TABLEWHEREROWNUM=100;2019年7月31日星期三Oracle10g新技术5/10访问或连接方法Dimensions•Resource(CPUtime,elapsedtime,buffergets,physicalreads,rowsreturned)•Execution(Acrossallexecutions,perexecution)•Accessorjoinmethod(Indexfastfullscan,indexskipscan,hashjoin,mergejoin,…)2019年7月31日星期三Oracle10g新技术6/10Examplesoftopqueriesbyaccessorjoinmethodacrossallexecutions--Getthetop100queries(mergeJoinQueries)bytotalnumberofdisk_reads_deltaSELECT*FROM(SELECTSQL_ID,SUM(disk_reads_delta)ASDiskReadsFROMDBA_HIST_SQLSTATSWHERESNAP_ID209ANDSNAP_ID=210ANDS.MODULE='ClaimCenter'ANDEXISTS(SELECT*FROMDBA_HIST_SQL_PLANPWHERES.SQL_ID=P.SQL_IDANDS.PLAN_HASH_VALUE=P.PLAN_HASH_VALUEANDP.OPERATION='MERGEJOIN')GROUPBYSQL_IDHAVINGSUM(disk_reads_delta)=0ORDERBY2DESC)DERIVED_TABLEWHEREROWNUM=100;2019年7月31日星期三Oracle10g新技术7/10Examplesoftopqueriesbyaccessorjoinmethodperexecution--Getthetop100queries(mergeJoinQueries)bytotalnumberofdisk_reads_deltabyexecutionSELECT*FROM(SELECTSQL_ID,CASESUM(EXECUTIONS_DELTA)WHEN0THEN0ELSESUM(disk_reads_delta)/SUM(EXECUTIONS_DELTA)ENDASBufferGets/ExecutionFROMDBA_HIST_SQLSTATSWHERESNAP_ID209ANDSNAP_ID=210ANDS.MODULE='ClaimCenter'ANDEXISTS(SELECT*FROMDBA_HIST_SQL_PLANPWHERES.SQL_ID=P.SQL_IDANDS.PLAN_HASH_VALUE=P.PLAN_HASH_VALUEANDP.OPERATION='MERGEJOIN')GROUPBYSQL_IDHAVINGCASESUM(EXECUTIONS_DELTA)WHEN0THEN0ELSESUM(disk_reads_delta)/SUM(EXECUTIONS_DELTA)END=0ORDERBY2DESC)DERIVED_TABLEWHEREROWNUM=100;2019年7月31日星期三Oracle10g新技术8/10Queriesthatreturntoofewrowsinatestenvironment•Isthetestvalidorisitskippinganexpensivecodepathduetomissingdataortestproblems?Example:--Getthequeriesthatreturn0rowsacrossallexecutionsSELECT*FROM(SELECTSQL_ID,SUM(rows_processed_delta)ASRowsProcessed,SUM(executions_delta)FROMDBA_HIST_SQLSTATSWHERESNAP_ID209ANDSNAP_ID=210ANDS.MODULE='ClaimCenter'GROUPBYSQL_IDHAVINGSUM(rows_processed_delta)=0ORDERBY3DESC)DERIVED_TABLEWHEREROWNUM=1000000;2019年7月31日星期三Oracle10g新技术9/10HotobjectsDimensions:•I/Otype(Logicalreads,physicalreads,physicalwrites)•Object((Object),(table,tablespace),(table))Missinginfo:•Whatmakesanobjecthot(accordingtoeachi/otype)2019年7月31日星期三Oracle10g新技术10/10Examplesoftopobjects--logical_reads,physical_reads,physical_writesbysegment(tablespace,table,object_type).selectn.owner,n.tablespace_name,casewhenn.object_type='TABLE'thenn.object_namewhenn.object_type='INDEX'then(SELECTtable_namefromdba_indexesWHEREindex_name=n.object_nameANDowner=n.owner)whenn.object_type='LOB'then(SELECTtable_namefromdba_lobsWHEREsegment_name=n.object_nameANDowner=n.owner)else'N/A'endasTable_name,n.object_name,n.object_type,r.logical_reads,round(r.logical_reads_ratio*100,2)logical_reads_ratio,r.physical_reads,round(r.physical_reads_ratio*100,2)physical_reads_ratio,r.physical_writes,round(r.physical_writes_ratio*100,2)physical_writes_ratiofromdba_hist_seg_stat_objn,(select*from(selecte.dataobj#,e.obj#,e.dbid,sum(e.logical_reads_delta)logical_reads,ratio_to_report(sum(e.logical_reads_delta))over()logical_reads_ratio,sum(e.physical_reads_delta)physical_reads,ratio_to_report(sum(e.physical_reads_delta))over()physical_reads_ratio,sum(e.physical_writes_delta)physical_writes,ratio_to_report(sum(e.physical_writes_delta))over()physical_writes_ratiofromdba_hist_seg_statewheree.snap_id209ande.snap_id=210groupbye.dataobj#,e.obj#,e.dbidhavingsum(e.logical_reads_delta)0orsum(e.physical_reads_delta)0orsum(e.physical_writes_delta)0orderbylogical_readsdesc)d)rwheren.dataobj#=r.dataobj#andn.obj#=r.obj#andn.dbid=r.dbidandn.owner='MKTG'orderbylogical_readsdesc;2019年7月31日星期三Oracle10g新技术11/10Examplesoftop(table,tablespace)s--logical_reads,physical_reads,physical_writesbytablespaceandtable.SELECTowner,tablespace_name,table_name,sum(logical_reads)asLOGICALREADS,sum(logical_reads_ratio)aslogical_reads_ratio,sum(physical_reads)asPHYSICALREADS,sum(physical_reads_ratio)asphysical_reads_ratio,sum(physical_writes)asPHYSICALWRITES,sum(physical_writes_ratio)asphysical_writes_ratioFROM(selectn.owner,n.tablespace_name,casewhenn.object_type='TABLE'