Oracle AWR Report Script top sqls' waits .. 


	
set numf 9999999999999999999999.99

VARIABLE SNAP_ID_MIN NUMBER
VARIABLE SNAP_ID_MAX NUMBER
VARIABLE DBID NUMBER
VARIABLE INSTANCE_NUMBER number

exec select max(snap_id) -1 into :SNAP_ID_MIN from dba_hist_snapshot ;
exec select max(snap_id) into :SNAP_ID_MAX  from dba_hist_snapshot ;
exec select DBID into :DBID from v$database;
exec select INSTANCE_NUMBER into :INSTANCE_NUMBER from v$instance ;


set linesize 300 pagesize 300	
	
	select dbid,
       db_name,
       instance_number,
       inst_name,
       begin_snap_id,
       end_snap_id,
       elapsed,
       (SELECT  sum(e.VALUE-b.value) as diff_value
           FROM DBA_HIST_SYSSTAT  B,
                DBA_HIST_SYSSTAT  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       in( 'physical reads') ) as physical_reads,
       (SELECT  sum(e.VALUE-b.value) as diff_value
           FROM DBA_HIST_SYSSTAT  B,
                DBA_HIST_SYSSTAT  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       in( 'session logical reads') ) as logical_reads,
       (SELECT SUM(E.TIME_WAITED_MICRO -      NVL(B.TIME_WAITED_MICRO, 0))
          FROM DBA_HIST_SYSTEM_EVENT B, 
               DBA_HIST_SYSTEM_EVENT E
         WHERE B.SNAP_ID(+) = base_info.begin_snap_id
           AND E.SNAP_ID = base_info.end_snap_id
           AND B.DBID(+) = E.DBID
                AND B.INSTANCE_NUMBER(+) = E.INSTANCE_NUMBER
           AND E.INSTANCE_NUMBER = base_info.instance_number
           AND B.EVENT_ID(+) = E.EVENT_ID
           AND E.WAIT_CLASS = 'User I/O') as UIOW_TIME,--STAT_UIOW_TIME
       (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;
	   
	   
                      DBID DB_NAME              INSTANCE_NUMBER INST_NAME                     BEGIN_SNAP_ID                END_SNAP_ID                    ELAPSED             PHYSICAL_READS              LOGICAL_READS                  UIOW_TIME                    DB_TIME          TRANSACTION_COUNT
-------------------------- --------- -------------------------- ---------------- -------------------------- -------------------------- -------------------------- -------------------------- -------------------------- -------------------------- -------------------------- --------------------------
             1222414252.00 XXRAC                           1.00 xxrac1                             12280.00                   12312.00                  115204.08                41865968.00               392896011.00             14553390318.00             39638901871.00                    5463.00


	

AWR SQL  ordered by Reads 
	


set linesize 600 pagesize 300

col "SQL Text" for a70 wrap
define phyr='41865968.00'	
	   
       select *
     from (select sqt.dskr as "Physical Reads",
                  sqt.exec as "Executions",
                  round(decode(sqt.exec, 0, to_number(null),(sqt.dskr / sqt.exec)),2) as "Reads per Exec",
                  round(decode(&phyr, 0, to_number(null),(100 * sqt.dskr)/&phyr),2) as  "%Total",
                  round(nvl((sqt.elap/1000000), to_number(null)),2) as "Elapsed Time (s)",
                  round(decode(sqt.elap, 0, to_number(null),(100 * (sqt.cput / sqt.elap))),2) as "%CPU",
                  round(decode(sqt.elap, 0, to_number(null), (100 * (sqt.uiot / sqt.elap))),2) 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,4000,1), to_clob('** SQL Text Not Available **')) as  "SQL Text"
           from (select sql_id, max(module) module,
                        sum(disk_reads_delta) dskr, sum(executions_delta) exec,
                        sum(cpu_time_delta) cput, sum(elapsed_time_delta) elap,
                        sum(iowait_delta) uiot
                  from dba_hist_sqlstat
                  where 1=1
                     and   instance_number = :INSTANCE_NUMBER
                    and :SNAP_ID_MIN < snap_id
                    and snap_id        <= :SNAP_ID_MAX
               group by sql_id) sqt,
               dba_hist_sqltext st
           where st.sql_id(+)             = sqt.sql_id
              and &phyr                    > 0
           order by nvl(sqt.dskr, -1) desc, sqt.sql_id)
    where rownum < 60 and  (rownum <= 10 or   "%Total" > 0.1)
	
	
	
            Physical Reads                 Executions             Reads per Exec                     %Total           Elapsed Time (s)                       %CPU                        %IO SQL Id        SQL Module                                                               SQL Text
-------------------------- -------------------------- -------------------------- -------------------------- -------------------------- -------------------------- -------------------------- ------------- ------------------------------------------------------------------------ ----------------------------------------------------------------------
                                                                                                                                                                                                                      bytes = :25,
                                                                                                                                                                                                                      other_tag = :26,
                                                                                                                                                                                                                      partition_start = :
                                                                                                                                                                                                                   27,                                                 partition_s
                                                                                                                                                                                                                   top = :28,                                                          par
                                                                                                                                                                                                                   tition_id = :29,
                                                                                                                                                                                                                other = :30,
                                                                                                                                                                                                                distribution = :31,
                                                                                                                                                                                                                cpu_cost = :32,
           





SQL statements  Top SQL

	
set linesize 500 pagesize 300
	
VARIABLE SNAP_ID_MIN NUMBER
VARIABLE SNAP_ID_MAX NUMBER
VARIABLE DBID NUMBER
VARIABLE INSTANCE_NUMBER number

exec select max(snap_id) -1 into :SNAP_ID_MIN from dba_hist_snapshot ;
exec select max(snap_id) into :SNAP_ID_MAX  from dba_hist_snapshot ;
exec select DBID into :DBID from v$database;
exec select INSTANCE_NUMBER into :INSTANCE_NUMBER from v$instance ;

	
Logical read TOP 10
	
	select *
     from (select sqt.dskr,
                  sqt.exec,
                  decode(sqt.exec, 0, to_number(null), (sqt.dskr / sqt.exec)),
                  (100 * sqt.dskr) /  (SELECT sum(e.VALUE) - sum(b.value)
                     FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
                    WHERE B.SNAP_ID = :SNAP_ID_MIN
                      AND E.SNAP_ID = :SNAP_ID_MAX
                      AND B.DBID = :DBID
                      AND E.DBID = :DBID
                      AND B.INSTANCE_NUMBER = :INSTANCE_NUMBER 
                      AND E.INSTANCE_NUMBER = :INSTANCE_NUMBER 
                      and e.STAT_NAME = 'physical reads'
                      and b.stat_name = 'physical reads') norm_val,
                  nvl((sqt.cput / 1000000), to_number(null)),
                  nvl((sqt.elap / 1000000), to_number(null)),
                  sqt.sql_id,
                  decode(sqt.module, null, null, 'Module: ' || sqt.module),
                  nvl(st.sql_text, to_clob('** SQL Text Not Available **'))
             from (select sql_id,
                          max(module) module,
                          sum(disk_reads_delta) dskr,
                          sum(executions_delta) exec,
                          sum(cpu_time_delta) cput,
                          sum(elapsed_time_delta) elap
                     from dba_hist_sqlstat
                    where dbid = :DBID
                      and instance_number = :INSTANCE_NUMBER 
                      and :SNAP_ID_MIN < snap_id
                      and snap_id <= :SNAP_ID_MAX
                    group by sql_id) sqt,
                  dba_hist_sqltext st
            where st.sql_id(+) = sqt.sql_id
              and st.dbid(+) = :DBID
              and (SELECT sum(e.VALUE) - sum(b.value)
                     FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
                    WHERE B.SNAP_ID = :SNAP_ID_MIN
                      AND E.SNAP_ID = :SNAP_ID_MAX
                      AND B.DBID = :DBID
                      AND E.DBID = :DBID
                      AND B.INSTANCE_NUMBER = :INSTANCE_NUMBER 
                      AND E.INSTANCE_NUMBER = :INSTANCE_NUMBER 
                      and e.STAT_NAME = 'physical reads'
                      and b.stat_name = 'physical reads') > 0
            order by nvl(sqt.dskr, -1) desc, sqt.sql_id)
    where rownum < 65
	and(rownum <= 10
    or norm_val > 1)

	
	

      DSKR       EXEC DECODE(SQT.EXEC,0,TO_NUMBER(NULL),(SQT.DSKR/SQT.EXEC))   NORM_VAL NVL((SQT.CPUT/1000000),TO_NUMBER(NULL)) NVL((SQT.ELAP/1000000),TO_NUMBER(NULL)) SQL_ID        DECODE(SQT.MODULE,NULL,NULL,'MODULE:'||SQT.MODULE)                       NVL(ST.SQL_TEXT,TO_CLOB('**SQLTEXTNOTAVAILABLE**'))
---------- ---------- ------------------------------------------------------ ---------- --------------------------------------- --------------------------------------- ------------- ------------------------------------------------------------------------ --------------------------------------------------------------------------------
   1303904        384                                             3395.58333 99.8915974                              256.457058                              715.109381 8cnh50qfgwg73 Module: DBMS_SCHEDULER          SELECT NVL(SUM(BYTES),0) FROM SYS.DBA_FREE_SPACE WHERE TABLESPACE_NAME = :B1
  



  
Physical reading TOP 10
	
select *
from (select sqt.dskr Physical_Reads,
sqt.exec Executions,
decode(sqt.exec, 0, to_number(null), (sqt.dskr / sqt.exec)) Reads_per_Exec ,
(100 * sqt.dskr) /
(SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = :SNAP_ID_MIN
AND E.SNAP_ID = :SNAP_ID_MAX
AND B.DBID = :DBID
AND E.DBID = :DBID
AND B.INSTANCE_NUMBER = 1
AND E.INSTANCE_NUMBER = 1
and e.STAT_NAME = 'physical reads'
and b.stat_name = 'physical reads') Total_rate,
nvl((sqt.cput / 1000000), to_number(null)) CPU_Time_s,
nvl((sqt.elap / 1000000), to_number(null)) Elapsed_Time_s,
sqt.sql_id,
decode(sqt.module, null, null, 'Module: ' || sqt.module) SQL_Module,
nvl(st.sql_text, to_clob('** SQL Text Not Available **')) SQL_Text
from (select sql_id,
max(module) module,
sum(disk_reads_delta) dskr,
sum(executions_delta) exec,
sum(cpu_time_delta) cput,
sum(elapsed_time_delta) elap
from dba_hist_sqlstat
where dbid = :DBID
and instance_number = 1
and :SNAP_ID_MIN < snap_id
and snap_id <= :SNAP_ID_MAX
group by sql_id) sqt,
dba_hist_sqltext st
where st.sql_id(+) = sqt.sql_id
and st.dbid(+) = :DBID
and (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = :SNAP_ID_MIN
AND E.SNAP_ID = :SNAP_ID_MAX
AND B.DBID = :DBID
AND E.DBID = :DBID
AND B.INSTANCE_NUMBER = 1
AND E.INSTANCE_NUMBER = 1
and e.STAT_NAME = 'physical reads'
and b.stat_name = 'physical reads') > 0
order by nvl(sqt.dskr, -1) desc, sqt.sql_id)
where rownum < 65 and(rownum <= 10
or Total_rate > 1);





Physical reading TOP 10

select *
from (select sqt.dskr Physical_Reads,
sqt.exec Executions,
decode(sqt.exec, 0, to_number(null), (sqt.dskr / sqt.exec)) Reads_per_Exec ,
(100 * sqt.dskr) /
(SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = :SNAP_ID_MIN
AND E.SNAP_ID = :SNAP_ID_MAX
AND B.DBID = :DBID
AND E.DBID = :DBID
AND B.INSTANCE_NUMBER = 1
AND E.INSTANCE_NUMBER = 1
and e.STAT_NAME = 'physical reads'
and b.stat_name = 'physical reads') Total_rate,
nvl((sqt.cput / 1000000), to_number(null)) CPU_Time_s,
nvl((sqt.elap / 1000000), to_number(null)) Elapsed_Time_s,
sqt.sql_id,
decode(sqt.module, null, null, 'Module: ' || sqt.module) SQL_Module,
nvl(st.sql_text, to_clob('** SQL Text Not Available **')) SQL_Text
from (select sql_id,
max(module) module,
sum(disk_reads_delta) dskr,
sum(executions_delta) exec,
sum(cpu_time_delta) cput,
sum(elapsed_time_delta) elap
from dba_hist_sqlstat
where dbid = :DBID
and instance_number = 1
and :SNAP_ID_MIN < snap_id
and snap_id <= :SNAP_ID_MAX
group by sql_id) sqt,
dba_hist_sqltext st
where st.sql_id(+) = sqt.sql_id
and st.dbid(+) = :DBID
and (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = :SNAP_ID_MIN
AND E.SNAP_ID = :SNAP_ID_MAX
AND B.DBID = :DBID
AND E.DBID = :DBID
AND B.INSTANCE_NUMBER = 1
AND E.INSTANCE_NUMBER = 1
and e.STAT_NAME = 'physical reads'
and b.stat_name = 'physical reads') > 0
order by nvl(sqt.dskr, -1) desc, sqt.sql_id)
where rownum < 65 and(rownum <= 10
or Total_rate > 1);




            PHYSICAL_READS                 EXECUTIONS             READS_PER_EXEC                 TOTAL_RATE                 CPU_TIME_S             ELAPSED_TIME_S SQL_ID        SQL_MODULE         SQL_TEXT
-------------------------- -------------------------- -------------------------- -------------------------- -------------------------- -------------------------- ------------- ------------------ ----------------------------------------------------------------------
                1303904.00                     384.00                    3395.58                      99.89                     256.46                     715.11 8cnh50qfgwg73 Module: DBMS_SCHED SELECT NVL(SUM(BYTES),0) FROM SYS.DBA_FREE_SPACE WHERE TABLESPACE_NAME
                                                                                                                                                                                ULER                = :B1






CPU consumption TOP 10


col SQL_MODULE  for a18
select *
from (select nvl((sqt.elap / 1000000), to_number(null)) Elapsed_Time_s,
nvl((sqt.cput / 1000000), to_number(null)) CPU_Time_s,
sqt.exec Executions,
decode(sqt.exec,
0,
to_number(null),
(sqt.elap / sqt.exec / 1000000)) Elap_per_Exec_s,
(100 *
(sqt.elap / (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = :SNAP_ID_MIN
AND E.SNAP_ID = :SNAP_ID_MAX
AND B.DBID = :DBID
AND E.DBID = :DBID
AND B.INSTANCE_NUMBER = 1
AND E.INSTANCE_NUMBER = 1
and e.STAT_NAME = 'DB time'
and b.stat_name = 'DB time')))/1000 Total_DB_Time_rate,
sqt.sql_id,
to_clob(decode(sqt.module,
null,
null,
'Module: ' || sqt.module)) SQL_Module,
nvl(st.sql_text, to_clob(' ** SQL Text Not Available ** ')) SQL_Text
from (select sql_id,
max(module) module,
sum(elapsed_time_delta) elap,
sum(cpu_time_delta) cput,
sum(executions_delta) exec
from dba_hist_sqlstat
where dbid = :DBID
and instance_number = 1
and :SNAP_ID_MIN < snap_id
and snap_id <= :SNAP_ID_MAX
group by sql_id) sqt,
dba_hist_sqltext st
where st.sql_id(+) = sqt.sql_id
and st.dbid(+) = :DBID
order by nvl(sqt.cput, -1) desc, sqt.sql_id)
where rownum < 65
and (rownum <= 10 or Total_DB_Time_rate > 1);


            ELAPSED_TIME_S                 CPU_TIME_S                 EXECUTIONS            ELAP_PER_EXEC_S         TOTAL_DB_TIME_RATE SQL_ID        SQL_MODULE         SQL_TEXT
-------------------------- -------------------------- -------------------------- -------------------------- -------------------------- ------------- ------------------ ----------------------------------------------------------------------
                    715.11                     256.46                     384.00                       1.86                     309.17 8cnh50qfgwg73 Module: DBMS_SCHED SELECT NVL(SUM(BYTES),0) FROM SYS.DBA_FREE_SPACE WHERE TABLESPACE_NAME
                                                                                                                                                     ULER                = :B1
 


Execution time TOP 10

select *
from (select nvl((sqt.elap / 1000000), to_number(null)) Elapsed_Time_s,
nvl((sqt.cput / 1000000), to_number(null)) CPU_Time_s,
sqt.exec Executions,
decode(sqt.exec,
0,
to_number(null),
(sqt.elap / sqt.exec / 1000000)) Elap_per_Exec_s,
(100 *
(sqt.elap / (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = :SNAP_ID_MIN
AND E.SNAP_ID = :SNAP_ID_MAX
AND B.DBID = :DBID
AND E.DBID = :DBID
AND B.INSTANCE_NUMBER = 1
AND E.INSTANCE_NUMBER = 1
and e.STAT_NAME = 'DB time'
and b.stat_name = 'DB time')))/1000 Total_DB_Time_rate,
sqt.sql_id,
to_clob(decode(sqt.module,
null,
null,
'Module: ' || sqt.module)) SQL_Module,
nvl(st.sql_text, to_clob(' ** SQL Text Not Available ** ')) SQL_Text
from (select sql_id,
max(module) module,
sum(elapsed_time_delta) elap,
sum(cpu_time_delta) cput,
sum(executions_delta) exec
from dba_hist_sqlstat
where dbid = :DBID
and instance_number = 1
and :SNAP_ID_MIN < snap_id
and snap_id <= :SNAP_ID_MAX
group by sql_id) sqt,
dba_hist_sqltext st
where st.sql_id(+) = sqt.sql_id
and st.dbid(+) = :DBID
order by nvl(sqt.elap, -1) desc, sqt.sql_id)
where rownum < 65
and (rownum <= 10 or Total_DB_Time_rate > 1);




          ELAPSED_TIME_S                 CPU_TIME_S                 EXECUTIONS            ELAP_PER_EXEC_S         TOTAL_DB_TIME_RATE SQL_ID        SQL_MODULE         SQL_TEXT
-------------------------- -------------------------- -------------------------- -------------------------- -------------------------- ------------- ------------------ ----------------------------------------------------------------------
                    715.11                     256.46                     384.00                       1.86                     309.17 8cnh50qfgwg73 Module: DBMS_SCHED SELECT NVL(SUM(BYTES),0) FROM SYS.DBA_FREE_SPACE WHERE TABLESPACE_NAME
                                                                                                                                                     ULER                = :B1





	 

select substr(sql_text,1,40), count(*)
from gv$sqlarea
group by substr(sql_text,1,40) having count(*) > 50;


col USERNAME for a20
col sql_text for a70 wrap
col BEGIN_INTERVAL_TIME for a29	
SELECT t.sql_id,
  dbms_lob.substr(q.SQL_TEXT,100,1) sql_text,
  t.PARSING_SCHEMA_NAME username,
  t.executions_delta exec_count,
  begin_interval_time,  -- 5
  ROUND(SUM(t.elapsed_time_delta/1000000)/SUM(t.executions_delta),4) time_exec -- 6
FROM dba_hist_sqlstat t,
  dba_hist_snapshot s,
  DBA_HIST_SQLTEXT q
WHERE t.snap_id           = s.snap_id
AND t.dbid                = s.dbid
AND q.sql_id              =t.sql_id
AND t.instance_number     = s.instance_number
AND t.executions_delta   IS NOT NULL
AND t.elapsed_time_delta IS NOT NULL
AND t.executions_delta    > 0
AND s.begin_interval_time BETWEEN TRUNC(sysdate)-1 AND TRUNC(sysdate)   ---- 1Day
AND t.PARSING_SCHEMA_NAME NOT IN ('SYS','SYSTEM','DBSNMP') -- yesterday's stats
GROUP BY t.sql_id,
  dbms_lob.substr(q.SQL_TEXT,100,1),
  PARSING_SCHEMA_NAME,
  t.executions_delta,
  s.begin_interval_time
ORDER BY 5,6 DESC;


SQL_ID        SQL_TEXT                                                               USERNAME                             EXEC_COUNT BEGIN_INTERVAL_TIME                            TIME_EXEC
------------- ---------------------------------------------------------------------- -------------------- -------------------------- ----------------------------- --------------------------
bcv9qynmu1nv9 select sys.dbms_standard.dictionary_obj_type from dual                 MDSYS                                    352.00 01-MAR-23 10.00.24.829 PM                            .00






Most important system statiscts/performance overview:



set linesize 400 pagesize 300

col MAXIMUM    for 999999999.999
col AVERAGE for 999999999.999


col METRIC_NAME for a30
SELECT begin_time,
  CASE METRIC_NAME
    WHEN 'SQL Service Response Time'
    THEN 'SQL Service Response
Time (secs)'
    WHEN 'Response Time Per Txn'
    THEN 'Response Time Per Txn
(secs)'
    ELSE METRIC_NAME
  END METRIC_NAME,
  CASE METRIC_NAME
    WHEN 'SQL Service Response Time'
    THEN ROUND((MINVAL / 100),2)
    WHEN 'Response Time Per Txn'
    THEN ROUND((MINVAL / 100),2)
    ELSE MINVAL
  END MININUM,
  CASE METRIC_NAME
    WHEN 'SQL Service Response Time'
    THEN ROUND((MAXVAL / 100),2)
    WHEN 'Response Time Per Txn'
    THEN ROUND((MAXVAL / 100),2)
    ELSE MAXVAL
  END MAXIMUM,
  CASE METRIC_NAME
    WHEN 'SQL Service Response Time'
    THEN ROUND((AVERAGE / 100),2)
    WHEN 'Response Time Per Txn'
    THEN ROUND((AVERAGE / 100),2)
    ELSE AVERAGE
  END AVERAGE
FROM SYS.DBA_HIST_SYSMETRIC_SUMMARY
WHERE METRIC_NAME IN ('CPU Usage Per Sec', 'CPU Usage Per Txn', 'Database CPU Time Ratio', 'Database Wait Time Ratio', 'Executions Per Sec', 'Executions Per Txn', 'Response Time Per Txn', 'SQL Service Response Time', 'User Transaction Per Sec','User Commits Per Sec')
--AND BEGIN_TIME BETWEEN sysdate -1 and sysdate
AND BEGIN_TIME > sysdate - interval '300' minute
ORDER BY 1;


BEGIN_TIM METRIC_NAME                       MININUM        MAXIMUM        AVERAGE
--------- ------------------------------ ---------- -------------- --------------
03-MAR-23 User Transaction Per Sec                0         34.106         17.592
03-MAR-23 Executions Per Txn                      0       1175.000        132.021
03-MAR-23 Response Time Per Txn                   0      51142.100       2929.060
          (secs)