本篇内容介绍了“AWR TOP SQL实现方法是什么”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

1按解析次数排序

selecta.*,to_char(substr(b.sql_text,1,4000))from(selectdhs.sql_id,sum(parse_calls_delta)parse,sum(executions_delta)exec_nums,dhs.MODULEfromdba_hist_sqlstatdhswheresnap_id>22438andsnap_id<=22440groupbydhs.sql_id,MODULE)a,dba_hist_sqltextbwherea.sql_id=b.sql_idorderbya.parsedesc;

2按执行时间排序

selecta.*,to_char(substr(b.sql_text,1,4000))from(selectdhs.sql_id,round(sum(elapsed_time_delta)/1000/1000,2)"elapsed_time(s)",sum(executions_delta)execs,round(sum(elapsed_time_delta)/1000/1000/sum(executions_delta),2)elapsed_time_per,dhs.MODULEfromdba_hist_sqlstatdhswheresnap_id>22438andsnap_id<=22440groupbydhs.sql_id,MODULE)a,dba_hist_sqltextbwherea.sql_id=b.sql_idorderbya."elapsed_time(s)"desc;

3按CPU时间排序

selecta.*,to_char(substr(b.sql_text,1,4000))from(selectdhs.sql_id,round(sum(cpu_time_delta)/1000/1000,2)"cpu_time",sum(executions_delta)execs,round(sum(cpu_time_delta)/1000/1000/sum(executions_delta),2)cpu_time_per,round(sum(elapsed_time_delta)/1000/1000,2)"elapsed_time(s)",dhs.MODULEfromdba_hist_sqlstatdhswheresnap_id>22438andsnap_id<=22440groupbydhs.sql_id,MODULE)a,dba_hist_sqltextbwherea.sql_id=b.sql_idorderbya."cpu_time"desc;

4按User I/O wait排序

selecta.*,to_char(substr(b.sql_text,1,4000))from(selectdhs.sql_id,round(sum(iowait_delta)/1000/1000,2)"iowait_time(s)",sum(executions_delta)execs,round(sum(iowait_delta)/1000/1000/sum(executions_delta),2)iowait_time_per,round(sum(elapsed_time_delta)/1000/1000,2)"elapsed_time(s)",dhs.MODULEfromdba_hist_sqlstatdhswheresnap_id>22438andsnap_id<=22440groupbydhs.sql_id,MODULE)a,dba_hist_sqltextbwherea.sql_id=b.sql_idorderbya."iowait_time(s)"desc;

5按逻辑读(gets)排序

selecta.*,to_char(substr(b.sql_text,1,4000))from(selectdhs.sql_id,round(sum(buffer_gets_delta),2)"buffer_ges",sum(executions_delta)execs,round(sum(buffer_gets_delta)/sum(executions_delta),2)iowait_time_per,round(sum(elapsed_time_delta)/1000/1000,2)"elapsed_time(s)",dhs.MODULEfromdba_hist_sqlstatdhswheresnap_id>22438andsnap_id<=22440groupbydhs.sql_id,MODULE)a,dba_hist_sqltextbwherea.sql_id=b.sql_idorderbya."buffer_ges"desc;

7按物理读(physical read)排序

selecta.*,to_char(substr(b.sql_text,1,4000))from(selectdhs.sql_id,round(sum(DISK_READS_DELTA),2)"physical_read",sum(executions_delta)execs,round(sum(DISK_READS_DELTA)/sum(executions_delta),2)iowait_time_per,round(sum(elapsed_time_delta)/1000/1000,2)"elapsed_time(s)",dhs.MODULEfromdba_hist_sqlstatdhswheresnap_id>22438andsnap_id<=22440groupbydhs.sql_id,MODULE)a,dba_hist_sqltextbwherea.sql_id=b.sql_idorderbya."physical_read"desc;

8 按执行次数排序

selecta.*,to_char(substr(b.sql_text,1,4000))from(selectdhs.sql_id,round(sum(executions_delta),2)"exec_num",sum(ROWS_PROCESSED_DELTA)row_process,round(sum(ROWS_PROCESSED_DELTA)/sum(executions_delta),2)rows_per_exec,round(sum(elapsed_time_delta)/1000/1000,2)"elapsed_time(s)",dhs.MODULEfromdba_hist_sqlstatdhswheresnap_id>22438andsnap_id<=22440groupbydhs.sql_id,MODULE)a,dba_hist_sqltextbwherea.sql_id=b.sql_idorderbya."exec_num"desc;

“AWR TOP SQL实现方法是什么”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！