Oracle_性能查询学习资料.ppt

《Oracle_性能查询学习资料.ppt》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、Oracle_性能查询按照使用资源列出前100个--Getthetop100queriesbytotalnumberofdisk_reads_deltaSELECT*FROM(SELECTSQL_ID,SUM(disk_reads_delta)AS"DiskReads"FROMDBA_HIST_SQLSTATSWHERESNAP_ID>209ANDSNAP_ID<=210ANDS.MODULE='ClaimCenter'GROUPBYSQL_IDHAVINGSUM(disk_reads_delta)>=0ORDERBY2D

2、ESC)DERIVED_TABLEWHEREROWNUM<=100;2021/9/202/10Oracle10g新技术按照使用资源列出100个--Getthetop100queriesbytotalnumberofdisk_reads_deltabyexecutionSELECT*FROM(SELECTSQL_ID,CASESUM(EXECUTIONS_DELTA)WHEN0THEN0ELSESUM(disk_reads_delta)/SUM(EXECUTIONS_DELTA)ENDAS"DiskReads/Executi

3、on"FROMDBA_HIST_SQLSTATSWHERESNAP_ID>209ANDSNAP_ID<=210ANDS.MODULE='ClaimCenter'GROUPBYSQL_IDHAVINGCASESUM(EXECUTIONS_DELTA)WHEN0THEN0ELSESUM(disk_reads_delta)/SUM(EXECUTIONS_DELTA)END>=0ORDERBY2DESC)DERIVED_TABLEWHEREROWNUM<=100;2021/9/203/10Oracle10g新技术访问或连接方法Di

4、mensionsResource(CPUtime,elapsedtime,buffergets,physicalreads,rowsreturned)Execution(Acrossallexecutions,perexecution)Accessorjoinmethod(Indexfastfullscan,indexskipscan,hashjoin,mergejoin,…)2021/9/204/10Oracle10g新技术Examplesoftopqueriesbyaccessorjoinmethodacrossall

5、executions--Getthetop100queries(mergeJoinQueries)bytotalnumberofdisk_reads_deltaSELECT*FROM(SELECTSQL_ID,SUM(disk_reads_delta)AS"DiskReads"FROMDBA_HIST_SQLSTATSWHERESNAP_ID>209ANDSNAP_ID<=210ANDS.MODULE='ClaimCenter'ANDEXISTS(SELECT*FROMDBA_HIST_SQL_PLANPWHERES.SQ

6、L_ID=P.SQL_IDANDS.PLAN_HASH_VALUE=P.PLAN_HASH_VALUEANDP.OPERATION='MERGEJOIN')GROUPBYSQL_IDHAVINGSUM(disk_reads_delta)>=0ORDERBY2DESC)DERIVED_TABLEWHEREROWNUM<=100;2021/9/205/10Oracle10g新技术Examplesoftopqueriesbyaccessorjoinmethodperexecution--Getthetop100queries(m

7、ergeJoinQueries)bytotalnumberofdisk_reads_deltabyexecutionSELECT*FROM(SELECTSQL_ID,CASESUM(EXECUTIONS_DELTA)WHEN0THEN0ELSESUM(disk_reads_delta)/SUM(EXECUTIONS_DELTA)ENDAS"BufferGets/Execution"FROMDBA_HIST_SQLSTATSWHERESNAP_ID>209ANDSNAP_ID<=210ANDS.MODULE='ClaimCe

8、nter'ANDEXISTS(SELECT*FROMDBA_HIST_SQL_PLANPWHERES.SQL_ID=P.SQL_IDANDS.PLAN_HASH_VALUE=P.PLAN_HASH_VALUEANDP.OPERATION='MERGEJOIN')GROUPBYSQL_IDHAVINGCA