Oracle AWR 报告脚本：SQL ordered by Elapsed Time

原创盖国强 2022-07-26
3478
以下是 Oracle AWR 报告中，SQL ordered by Elapsed Time 部分的计算语句：
/****************************************************************************************
SQL ordered by Elapsed Time
		Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. 
		% Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100 
		%Total - Elapsed Time as a percentage of Total DB time 
		%CPU - CPU Time as a percentage of Elapsed Time 
		%IO - User I/O Time as a percentage of Elapsed Time 
		Captured SQL account for 75.8% of Total DB Time (s): 214,223 
		Captured PL/SQL account for 0.5% of Total DB Time (s): 214,223 
****************************************************************************************/
select dbid,
       db_name,
       instance_number,
       inst_name,
       begin_snap_id,
       end_snap_id,
       elapsed,
       (SELECT  e.VALUE-b.value as diff_value
          FROM DBA_HIST_SYS_TIME_MODEL B,
               DBA_HIST_SYS_TIME_MODEL E
          WHERE e.dbid = b.dbid
            and e.instance_number = b.instance_number
            and e.STAT_ID = b.STAT_ID
            and B.DBID            = base_info.dbid
            AND B.INSTANCE_NUMBER = base_info.instance_number
            AND B.SNAP_ID         = base_info.begin_snap_id
            AND E.SNAP_ID         = base_info.end_snap_id
            AND B.STAT_NAME       = 'DB time'

         ) as db_time,
       (SELECT sum(E.VALUE)-sum(B.VALUE) as STAT_TXN
            FROM DBA_HIST_SYSSTAT B,
                 DBA_HIST_SYSSTAT E
           WHERE b.dbid          = e.dbid 
           and b.instance_number = e.instance_number 
           and b.STAT_ID         = e.STAT_ID
           AND E.DBID            = base_info.dbid
           and e.instance_number = base_info.instance_number
           and b.snap_id         = base_info.begin_snap_id
           and e.snap_id          = base_info.end_snap_id 
           AND e.STAT_NAME in ('user rollbacks','user commits')
         ) as transaction_count
  from (with db_info as (select d.dbid            dbid,
                                d.name            db_name,
                                i.instance_number instance_number,
                                i.instance_name   inst_name
                           from v$database d, v$instance i), 
         snap_info as (select c.*,
                              EXTRACT(DAY FROM c.max_end_interval_time - c.min_end_interval_time) * 86400
                               + EXTRACT(HOUR FROM c.max_end_interval_time - c.min_end_interval_time) * 3600
                               + EXTRACT(MINUTE FROM c.max_end_interval_time - c.min_end_interval_time) * 60
                               + EXTRACT(SECOND FROM c.max_end_interval_time - c.min_end_interval_time) ELAPSED
                         from (select min(snap_id) begin_snap_id,
                                      max(snap_id) end_snap_id,
                                      min(END_INTERVAL_TIME) as  min_end_interval_time,
                                      max(END_INTERVAL_TIME) as max_end_interval_time
                                 from dba_hist_snapshot sn
                                where sn.begin_interval_time >= trunc(sysdate) - 1
                                  and sn.begin_interval_time < sysdate) c
                        )        
       select * from db_info, snap_info) base_info;
       
select *
  from (select to_char(nvl((sqt.elap / 1000000), to_number(null)),'9,999,990') as "Elapsed Time (s)",
  						 to_char(nvl((sqt.cput / 1000000), to_number(null)),'9,999,990') as "CPU Time (s)",
  						 to_char(sqt.exec,'999,999,999') as "Executions",
  						 to_char(decode(sqt.exec,0, to_number(null),(sqt.elap / sqt.exec / 1000000)),'9999990.0') as "Elapsed Time per Exec (s)",--10g中用 Elap per Exec (s) 表示
               to_char((100 * (sqt.elap / &db_time )),9990.0)  as "% Total DB Time",--11g中的"%Total%"
               decode(sqt.elap, 0, to_number(null), (100 * (sqt.cput / sqt.elap))) as "%CPU",
               decode(sqt.elap, 0, to_number(null), (100 * (sqt.iowt / sqt.elap))) as "%IO",
               sqt.sql_id as "SQL Id",
               decode(sqt.module,null, null, 'Module: ' || sqt.module) as "SQL Module",
               nvl(dbms_lob.substr(st.sql_text,1400,1), to_clob('** SQL Text Not Available **')) as  "SQL Text" --norm_val 直接执行时可去掉dbms_lob.substr()
          from (select sql_id, 
	                     max(module) module,
	                     sum(elapsed_time_delta) elap,
	                     sum(cpu_time_delta)     cput,
	                     sum(executions_delta)   exec,
	                     sum(iowait_delta)       iowt
                  from dba_hist_sqlstat
                 where /*dbid = &dbid*/
                    instance_number = &instance_number
                   and &begin_snap_id < snap_id
                   and snap_id <= &end_snap_id
                 group by sql_id
                ) sqt,
               dba_hist_sqltext st
         where st.sql_id(+) = sqt.sql_id
           /*and st.dbid(+) = 3929621523*/
         order by nvl(sqt.elap, -1) desc, sqt.sql_id)
 where rownum < 65
   and (rownum <= 10 or "% Total DB Time" > 1.0);

复制