Awr Trends!!!!!!

Oracle Awr Trends 

define hours=12
column f_hours new_value v_hours
select &hours f_hours from dual;
column f_secs new_value v_secs
column f_samples new_value samples
select 3600 f_secs from dual;
select &v_secs f_samples from dual;
--select &seconds f_secs from dual;
column f_bars new_value v_bars
select 5 f_bars from dual;
column aas format 999.99
column f_graph new_value v_graph
select 30 f_graph from dual;
column graph format a30
column total format 99999
column npts format 99999
col waits for 999999999
col cpu for 999999999


set pagesize 400 linesize 500
/*
      dba_hist_active_sess_history
*/
select
        to_char(to_date(tday||' '||tmod*&v_secs,'YYMMDD SSSSS'),'DD-MON  HH24:MI:SS') tm,
        samples npts,
        total/&samples aas,
        substr(
        substr(substr(rpad('+',round((cpu*&v_bars)/&samples),'+') ||
        rpad('-',round((waits*&v_bars)/&samples),'-')  ||
        rpad(' ',p.value * &v_bars,' '),0,(p.value * &v_bars)) ||
        p.value  ||
        substr(rpad('+',round((cpu*&v_bars)/&samples),'+') ||
        rpad('-',round((waits*&v_bars)/&samples),'-')  ||
        rpad(' ',p.value * &v_bars,' '),(p.value * &v_bars),10) ,0,30)
        ,0,&v_graph)
        graph,
        -- total,
        cpu,
        waits
from (
   select
       to_char(sample_time,'YYMMDD')                   tday
     , trunc(to_char(sample_time,'SSSSS')/&v_secs) tmod
     , sum(decode(session_state,'ON CPU',1,decode(session_type,'BACKGROUND',0,1)))  total
     , (max(sample_id) - min(sample_id) + 1 )      samples
     , sum(decode(session_state,'ON CPU' ,1,0))    cpu
     , sum(decode(session_type,'BACKGROUND',0,decode(session_state,'WAITING',1,0))) waits
       /* for waits I want to subtract out the BACKGROUND
          but for CPU I want to count everyon */
   from
      gv$active_session_history
   where sample_time > sysdate - &v_hours/24
   group by  trunc(to_char(sample_time,'SSSSS')/&v_secs),
             to_char(sample_time,'YYMMDD')
union all
   select
       to_char(sample_time,'YYMMDD')                   tday
     , trunc(to_char(sample_time,'SSSSS')/&v_secs) tmod
     , sum(decode(session_state,'ON CPU',10,decode(session_type,'BACKGROUND',0,10)))  total
     , (max(sample_id) - min(sample_id) + 1 )      samples
     , sum(decode(session_state,'ON CPU' ,10,0))    cpu
     , sum(decode(session_type,'BACKGROUND',0,decode(session_state,'WAITING',10,0))) waits
       /* for waits I want to subtract out the BACKGROUND
          but for CPU I want to count everyon */
   from
      dba_hist_active_sess_history
   where sample_time > sysdate - &v_hours/24
   and sample_time < (select min(sample_time) from v$active_session_history)
   group by  trunc(to_char(sample_time,'SSSSS')/&v_secs),
             to_char(sample_time,'YYMMDD')
) ash,
  v$parameter p
where p.name='cpu_count'
order by to_date(tday||' '||tmod*&v_secs,'YYMMDD SSSSS')
;




TM                          NPTS     AAS GRAPH                                 CPU      WAITS
------------------------- ------ ------- ------------------------------ ---------- ----------
10-MAY  23:00:00             531    2.84 ++++----------                       2980       7240
11-MAY  00:00:00            3581   11.46 +++++++++++++++++++++++++-----      18320      22940
11-MAY  01:00:00            3581    3.77 ++++++++-----------                  5540       8030
11-MAY  02:00:00            3581    2.87 +++++++++------                      6140       4180
11-MAY  03:00:00            3591    3.51 +++++++++---------                   6290       6360
11-MAY  04:00:00            3581   20.04 ++++++++++++++++++++++++++++++      35660      36480
11-MAY  05:00:00            3581  303.96 ++++++++++++++++++++++++++++++      21310    1072960
11-MAY  06:00:00            3571  479.73 ++++++++++++++++++++++++++++--      20480    1706550
11-MAY  07:00:00             671   66.68 +++++-------------------------       3290     236750
11-MAY  07:00:00            2913  284.21 ++++++++++++++++--------------      11813    1011355
11-MAY  08:00:00            3593    4.17 +++++++++++++--------                9032       5981
11-MAY  09:00:00            3594    4.24 +++++++++++++--------                9178       6077
11-MAY  10:00:00            3593    3.59 ++++++++++++------                   8531       4396
11-MAY  11:00:00            3056    3.44 ++++++++++-------                    7152       5242

14 rows selected.

awr_event_trends.sql

from 
https://github.com/abdulirfan3/Oracle_SQL_Scripts/blob/master/awr_event_trends.sql

-- ttitle off
clear breaks computes


break on wait_class on report
prompt
prompt Some useful database wait-events upon which to search:
col wait_class format a20 heading "Wait Class"
col name format a60 heading "Name"
select  chr(9)||wait_class, name name
from    v$event_name
order by wait_class, name;



define V_NBR_DAYS=1
define V_EVENTNAME='direct path write'

set echo off feedback off timing off pagesize 500 linesize 160
set trimout on trimspool on verify off
col sort0 noprint
col day format a6 heading "Day"
col hr format a6 heading "Hour"
col event_name format a30 heading "Event Name"
col total_waits format 999,990 heading "Total|Waits (m)"
col time_waited format 999,990.00 heading "Secs|Waited"
col tot_wts format 990.00 heading "% Total|Waits"
col tot_pct format 990.00 heading "% Secs|Waited"
col avg_wait format 990.00 heading "Avg|hSecs|Per|Wait"
col avg_pct format 990.00 heading "% Avg|hSecs|Per|Wait"
col wt_graph format a18 heading "Graphical view|of % total|waits overall"
col tot_graph format a18 heading "Graphical view|of % total|secs waited overall"
col avg_graph format a18 heading "Graphical view|of % avg hSecs|per wait overall"


set linesize 500 pagesize 400
col EVENT_NAME for a27 
col WT_GRAPH for a20
col TOT_GRAPH for a20
col AVG_GRAPH for a20


col spoolname new_value V_SPOOLNAME noprint
col instance_name new_value V_INST_NAME noprint
col instance_number new_value V_INST_NBR noprint
col dbid new_value V_DBID noprint
select  replace(replace(replace(lower('&&V_EVENTNAME'),' ','_'),'(',''),')','') spoolname,
        i.instance_name,
	i.instance_number,
	d.dbid
from    v$instance i,
	v$database d;

--spool awr_evtrends_&&V_INST_NAME._&&V_SPOOLNAME

clear breaks computes
ttitle center 'Trends for waits on "&&V_EVENTNAME" over the past &&V_NBR_DAYS days' skip line
col total_waits format 999,990.00 heading "Waits (m)"
prompt
select  event_name,
	total_waits/1000000 total_waits,
        (ratio_to_report(total_waits) over ()*100) tot_wts,
        rpad('*', round((ratio_to_report(total_waits) over ()*100)/6, 0), '*') wt_graph,
        time_waited,
        (ratio_to_report(time_waited) over ()*100) tot_pct,
        rpad('*', round((ratio_to_report(time_waited) over ()*100)/6, 0), '*') tot_graph,
        avg_wait*100 avg_wait,
        (ratio_to_report(avg_wait) over ()*100) avg_pct,
        rpad('*', round((ratio_to_report(avg_wait) over ()*100)/6, 0), '*') avg_graph
from    (select event_name,
		sum(total_waits) total_waits,
                sum(time_waited)/1000000 time_waited,
                decode(sum(total_waits),0,0,(sum(time_waited)/sum(total_waits))/1000000) avg_wait
         from   (select s.event_name,
			s.snap_id,
                        nvl(decode(greatest(s.time_waited_micro,
                                            lag(s.time_waited_micro,1,0)
                                                    over (partition by  s.dbid,
                                                                        s.instance_number
                                                          order by s.snap_id)),
                                   s.time_waited_micro,
                                   s.time_waited_micro - lag(s.time_waited_micro)
                                                             over (partition by s.dbid,
                                                                                s.instance_number
                                                                   order by s.snap_id),
                                          s.time_waited_micro), 0) time_waited,
                        nvl(decode(greatest(s.total_waits,
                                            lag(s.total_waits,1,0)
                                                    over (partition by  s.dbid,
                                                                        s.instance_number
                                                          order by s.snap_id)),
                                   s.total_waits,
                                   s.total_waits - lag(s.total_waits)
                                                             over (partition by s.dbid,
                                                                                s.instance_number
                                                                   order by s.snap_id),
                                          s.total_waits), 0) total_waits
                 from   dba_hist_system_event                   s,
                        dba_hist_snapshot                       ss
                 where  s.event_name like '%'||'&&V_EVENTNAME'||'%'
		 and	s.instance_number = &&V_INST_NBR
		-- and	s.dbid = &&V_DBID
                 and    ss.snap_id = s.snap_id
                 and    ss.dbid = s.dbid
                 and    ss.instance_number = s.instance_number
		 and	ss.begin_interval_time >= trunc(sysdate) - &&V_NBR_DAYS)
         group by event_name)
order by time_waited desc;

   



clear breaks computes
break on report
compute avg of total_waits on report
compute avg of time_waited on report
compute avg of avg_wait on report
ttitle center 'Daily trends for waits on "&&V_EVENTNAME" over the past &&V_NBR_DAYS days' skip line
col total_waits format 999,990.00 heading "Waits (m)"
prompt
select  sort_day || trim(to_char(999999999999999-time_waited,'000000000000000')) sort0,
        day,
	event_name,
        total_waits/1000000 total_waits,
        (ratio_to_report(total_waits) over ()*100) tot_wts,
        rpad('*', round((ratio_to_report(total_waits) over ()*100)/6, 0), '*') wt_graph,
        time_waited,
        (ratio_to_report(time_waited) over ()*100) tot_pct,
        rpad('*', round((ratio_to_report(time_waited) over ()*100)/6, 0), '*') tot_graph,
        avg_wait*100 avg_wait,
        (ratio_to_report(avg_wait) over ()*100) avg_pct,
        rpad('*', round((ratio_to_report(avg_wait) over ()*100)/6, 0), '*') avg_graph
from    (select sort_day,
                day,
		event_name,
                sum(total_waits) total_waits,
                sum(time_waited)/1000000 time_waited,
                decode(sum(total_waits),0,0,(sum(time_waited)/sum(total_waits))/1000000) avg_wait
         from   (select to_char(ss.begin_interval_time, 'YYYYMMDD') sort_day,
                        to_char(ss.begin_interval_time, 'DD-MON') day,
			s.event_name,
                        s.snap_id,
                        nvl(decode(greatest(s.time_waited_micro,
                                            lag(s.time_waited_micro,1,0)
                                                    over (partition by  s.dbid,
                                                                        s.instance_number
                                                          order by s.snap_id)),
                                   s.time_waited_micro,
                                   s.time_waited_micro - lag(s.time_waited_micro)
                                                             over (partition by s.dbid,
                                                                                s.instance_number
                                                                   order by s.snap_id),
                                          s.time_waited_micro), 0) time_waited,
                        nvl(decode(greatest(s.total_waits,
                                            lag(s.total_waits,1,0)
                                                    over (partition by  s.dbid,
                                                                        s.instance_number
                                                          order by s.snap_id)),
                                   s.total_waits,
                                   s.total_waits - lag(s.total_waits)
                                                             over (partition by s.dbid,
                                                                                s.instance_number
                                                                   order by s.snap_id),
                                          s.total_waits), 0) total_waits
                 from   dba_hist_system_event                   s,
                        dba_hist_snapshot                       ss
                 where  s.event_name like '%'||'&&V_EVENTNAME'||'%'
		 and	s.instance_number = &&V_INST_NBR
		-- and	s.dbid = &&V_DBID
                 and    ss.snap_id = s.snap_id
                 and    ss.dbid = s.dbid
                 and    ss.instance_number = s.instance_number
		 and	ss.begin_interval_time >= trunc(sysdate) - &&V_NBR_DAYS)
         group by sort_day,
                  day,
		  event_name)
order by sort0;






clear breaks computes
ttitle center 'Hourly trends for waits on "&&V_EVENTNAME" over the past &&V_NBR_DAYS days' skip line
col total_waits format 9,990.00 heading "Waits (m)"
break on day skip 1 on hr on report
compute avg of total_waits on report
compute avg of time_waited on report
compute avg of avg_wait on report
prompt
select  sort_hr || trim(to_char(999999999999999-time_waited,'000000000000000')) sort0,
        day,
        hr,
	event_name,
        total_waits/1000000 total_waits,
        (ratio_to_report(total_waits) over (partition by day)*100) tot_wts,
        rpad('*', round((ratio_to_report(total_waits) over (partition by day)*100)/4, 0), '*') wt_graph,
        time_waited,
        (ratio_to_report(time_waited) over (partition by day)*100) tot_pct,
        rpad('*', round((ratio_to_report(time_waited) over (partition by day)*100)/4, 0), '*') tot_graph,
        avg_wait*100 avg_wait,
        (ratio_to_report(avg_wait) over (partition by day)*100) avg_pct,
        rpad('*', round((ratio_to_report(avg_wait) over (partition by day)*100)/4, 0), '*') avg_graph
from    (select sort_hr,
                day,
                hr,
		event_name,
                sum(total_waits) total_waits,
                sum(time_waited)/1000000 time_waited,
                decode(sum(total_waits),0,0,(sum(time_waited)/sum(total_waits))/1000000) avg_wait
         from   (select to_char(ss.begin_interval_time, 'YYYYMMDDHH24') sort_hr,
                        to_char(ss.begin_interval_time, 'DD-MON') day,
                        to_char(ss.begin_interval_time, 'HH24')||':00' hr,
			s.event_name,
                        s.snap_id,
                        nvl(decode(greatest(s.time_waited_micro,
                                   lag(s.time_waited_micro,1,0)
                                           over (partition by   s.dbid,
                                                                s.instance_number
                                                 order by s.snap_id)),
                                   s.time_waited_micro,
                                   s.time_waited_micro - lag(s.time_waited_micro)
                                                             over (partition by s.dbid,
                                                                                s.instance_number
                                                                   order by s.snap_id),
                                          s.time_waited_micro), 0) time_waited,
                        nvl(decode(greatest(s.total_waits,
                                   lag(s.total_waits,1,0)
                                           over (partition by   s.dbid,
                                                                s.instance_number
                                                 order by s.snap_id)),
                                   s.total_waits,
                                   s.total_waits - lag(s.total_waits)
                                                             over (partition by s.dbid,
                                                                                s.instance_number
                                                                   order by s.snap_id),
                                          s.total_waits), 0) total_waits
                 from   dba_hist_system_event                   s,
                        dba_hist_snapshot                       ss
                 where  s.event_name like '%'||'&&V_EVENTNAME'||'%'
		 and	s.instance_number = &&V_INST_NBR
		-- and	s.dbid = &&V_DBID
                 and    ss.snap_id = s.snap_id
                 and    ss.dbid = s.dbid
                 and    ss.instance_number = s.instance_number
		 and	ss.begin_interval_time >= trunc(sysdate) - &&V_NBR_DAYS)
         group by sort_hr,
                  day,
                  hr,
		  event_name)
order by sort0;




ttitle off
col avg_snap_frequency new_value V_AVG_SNAP_FREQUENCY noprint
select	decode(greatest(count(*), &&V_NBR_DAYS * 4), &&V_NBR_DAYS * 4, 'HOURLY', 'MULTIPLE TIMES/HOUR') avg_snap_frequency
from	(select count(*) cnt
	 from   dba_hist_snapshot
	 where  begin_interval_time >= trunc(sysdate) - &&V_NBR_DAYS
	 and    dbid = &&V_DBID
	 and    instance_number = &&V_INST_NBR
	 group by trunc(begin_interval_time, 'HH24')
	 having count(*) > 1);





clear breaks computes
ttitle center 'Snapshot-by-snapshot trends for waits on "&&V_EVENTNAME" over the past &&V_NBR_DAYS days' skip line
col total_waits format 9,990.00 heading "Waits (m)"
REM break on day skip 1 on hr on report
REM compute avg of total_waits on report
REM compute avg of time_waited on report
REM compute avg of avg_wait on report
select  sort_snap || trim(to_char(999999999999999-time_waited,'000000000000000')) sort0,
        day,
        tm,
	event_name,
        total_waits/1000000 total_waits,
        (ratio_to_report(total_waits) over (partition by day)*100) tot_wts,
        rpad('*', round((ratio_to_report(total_waits) over (partition by day)*100)/4, 0), '*') wt_graph,
        time_waited,
        (ratio_to_report(time_waited) over (partition by day)*100) tot_pct,
        rpad('*', round((ratio_to_report(time_waited) over (partition by day)*100)/4, 0), '*') tot_graph,
        avg_wait*100 avg_wait,
        (ratio_to_report(avg_wait) over (partition by day)*100) avg_pct,
        rpad('*', round((ratio_to_report(avg_wait) over (partition by day)*100)/4, 0), '*') avg_graph
from    (select sort_snap,
                day,
                tm,
		event_name,
                total_waits total_waits,
                time_waited/1000000 time_waited,
                decode(total_waits,0,0,((time_waited/total_waits)/1000000)) avg_wait
         from   (select to_char(ss.begin_interval_time, 'YYYYMMDDHH24MI') sort_snap,
                        to_char(ss.begin_interval_time, 'DD-MON') day,
                        to_char(ss.begin_interval_time, 'HH24:MI') tm,
			s.event_name,
                        s.snap_id,
                        nvl(decode(greatest(s.time_waited_micro,
                                   lag(s.time_waited_micro,1,0)
                                           over (partition by   s.dbid,
                                                                s.instance_number
                                                 order by s.snap_id)),
                                   s.time_waited_micro,
                                   s.time_waited_micro - lag(s.time_waited_micro)
                                                             over (partition by s.dbid,
                                                                                s.instance_number
                                                                   order by s.snap_id),
                                          s.time_waited_micro), 0) time_waited,
                        nvl(decode(greatest(s.total_waits,
                                   lag(s.total_waits,1,0)
                                           over (partition by   s.dbid,
                                                                s.instance_number
                                                 order by s.snap_id)),
                                   s.total_waits,
                                   s.total_waits - lag(s.total_waits)
                                                             over (partition by s.dbid,
                                                                                s.instance_number
                                                                   order by s.snap_id),
                                          s.total_waits), 0) total_waits
                 from   dba_hist_system_event                   s,
                        dba_hist_snapshot                       ss
                 where  '&&V_AVG_SNAP_FREQUENCY' <> 'HOURLY'
		 and	s.event_name like '%'||'&&V_EVENTNAME'||'%'
		 and	s.instance_number = &&V_INST_NBR
		 and	s.dbid = &&V_DBID
                 and    ss.snap_id = s.snap_id
                 and    ss.dbid = s.dbid
                 and    ss.instance_number = s.instance_number
		 and	ss.begin_interval_time >= trunc(sysdate) - &&V_NBR_DAYS))
order by sort0;

clear breaks computes

set lines 120
set verify off
undefine SQL_ID
accept SQL_ID char prompt 'Enter SQL ID: '
column "Cost" format 999,999,999
column "Module" format a15
column "Schema" format a15
column "Buffer Gets" format 999,999,999
column "Elapsed Time" format 999,999,999
column "Snapshot Time" format a30

SELECT   optimizer_cost "Cost",
         module "Module",
         parsing_schema_name "Schema",
         buffer_gets_total "Buffer Gets",
         elapsed_time_delta "Elapsed Time",
         end_interval_time "Snapshot Time"
FROM     dba_hist_sqlstat st, dba_hist_snapshot ss
WHERE    st.SQL_ID = '&SQL_ID' AND ss.snap_id = st.snap_id
ORDER BY "Snapshot Time" DESC
/

dbms_xplan.display_awr

dbms_xplan.display_awr..

from !!

SQL> desc dbms_xplan

FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_ID                         VARCHAR2                IN
 PLAN_HASH_VALUE                NUMBER(38)              IN     DEFAULT
 DB_ID                          NUMBER(38)              IN     DEFAULT
 FORMAT                         VARCHAR2                IN     DEFAULT
 CON_ID                         NUMBER(38)              IN     DEFAULT
 AWR_LOCATION                   VARCHAR2                IN     DEFAULT




 
select distinct sql_id,plan_hash_value from dba_hist_sql_plan 

-- where 1=1

;


SQL_ID        PLAN_HASH_VALUE
------------- ---------------
afcswub17n34t      1774461949
62yyzw3309d6a      2667441017
6y55dxn24t86q      2904063496
4m5tr50dg6xmc      2772656747



set linesize 120
 col PLAN_TABLE_OUTPUT for a120
select * from table(dbms_xplan.display_awr(SQL_ID=>'afcswub17n34t',format=>'ALLSTATS LAST +cost +bytes'));
 
 

set linesize 120
col PLAN_TABLE_OUTPUT for a120
select * from table(dbms_xplan.display_awr(SQL_ID=>'afcswub17n34t',PLAN_HASH_VALUE=>1774461949,format=>'ALLSTATS LAST +cost +bytes'));
 
 

set linesize 120
col PLAN_TABLE_OUTPUT for a120
select * from table(dbms_xplan.display_awr(SQL_ID=>'afcswub17n34t',PLAN_HASH_VALUE=>1774461949,format=>'ALLSTATS LAST +outline'));
 

 set linesize 120
 col PLAN_TABLE_OUTPUT for a120
select * from table(dbms_xplan.display_awr(SQL_ID=>'afcswub17n34t',PLAN_HASH_VALUE=>1774461949,format=>'ALLSTATS LAST +cost +bytes +PARALLEL +PARTITION +IOSTATS +outline +PEEKED_BINDS'));
 
 
 
 
 

Load Profile in AWR reports

Load Profile in AWR reports

=====

col short_name  format a20              heading 'Load Profile'
col per_sec     format 999,999,999.9    heading 'Per Second'
col per_tx      format 999,999,999.9    heading 'Per Transaction'
set colsep '   '
 
select lpad(short_name, 20, ' ') short_name
     , per_sec
     , per_tx from
    (select short_name
          , max(decode(typ, 1, value)) per_sec
          , max(decode(typ, 2, value)) per_tx
          , max(m_rank) m_rank 
       from
        (select /*+ use_hash(s) */
                m.short_name
              , s.value * coeff value
              , typ
              , m_rank
           from v$sysmetric s,
               (select 'Database Time Per Sec'                      metric_name, 'DB Time' short_name, .01 coeff, 1 typ, 1 m_rank from dual union all
                select 'CPU Usage Per Sec'                          metric_name, 'DB CPU' short_name, .01 coeff, 1 typ, 2 m_rank from dual union all
                select 'Redo Generated Per Sec'                     metric_name, 'Redo size' short_name, 1 coeff, 1 typ, 3 m_rank from dual union all
                select 'Logical Reads Per Sec'                      metric_name, 'Logical reads' short_name, 1 coeff, 1 typ, 4 m_rank from dual union all
                select 'DB Block Changes Per Sec'                   metric_name, 'Block changes' short_name, 1 coeff, 1 typ, 5 m_rank from dual union all
                select 'Physical Reads Per Sec'                     metric_name, 'Physical reads' short_name, 1 coeff, 1 typ, 6 m_rank from dual union all
                select 'Physical Writes Per Sec'                    metric_name, 'Physical writes' short_name, 1 coeff, 1 typ, 7 m_rank from dual union all
                select 'User Calls Per Sec'                         metric_name, 'User calls' short_name, 1 coeff, 1 typ, 8 m_rank from dual union all
                select 'Total Parse Count Per Sec'                  metric_name, 'Parses' short_name, 1 coeff, 1 typ, 9 m_rank from dual union all
                select 'Hard Parse Count Per Sec'                   metric_name, 'Hard Parses' short_name, 1 coeff, 1 typ, 10 m_rank from dual union all
                select 'Logons Per Sec'                             metric_name, 'Logons' short_name, 1 coeff, 1 typ, 11 m_rank from dual union all
                select 'Executions Per Sec'                         metric_name, 'Executes' short_name, 1 coeff, 1 typ, 12 m_rank from dual union all
                select 'User Rollbacks Per Sec'                     metric_name, 'Rollbacks' short_name, 1 coeff, 1 typ, 13 m_rank from dual union all
                select 'User Transaction Per Sec'                   metric_name, 'Transactions' short_name, 1 coeff, 1 typ, 14 m_rank from dual union all
                select 'User Rollback UndoRec Applied Per Sec'      metric_name, 'Applied urec' short_name, 1 coeff, 1 typ, 15 m_rank from dual union all
                select 'Redo Generated Per Txn'                     metric_name, 'Redo size' short_name, 1 coeff, 2 typ, 3 m_rank from dual union all
                select 'Logical Reads Per Txn'                      metric_name, 'Logical reads' short_name, 1 coeff, 2 typ, 4 m_rank from dual union all
                select 'DB Block Changes Per Txn'                   metric_name, 'Block changes' short_name, 1 coeff, 2 typ, 5 m_rank from dual union all
                select 'Physical Reads Per Txn'                     metric_name, 'Physical reads' short_name, 1 coeff, 2 typ, 6 m_rank from dual union all
                select 'Physical Writes Per Txn'                    metric_name, 'Physical writes' short_name, 1 coeff, 2 typ, 7 m_rank from dual union all
                select 'User Calls Per Txn'                         metric_name, 'User calls' short_name, 1 coeff, 2 typ, 8 m_rank from dual union all
                select 'Total Parse Count Per Txn'                  metric_name, 'Parses' short_name, 1 coeff, 2 typ, 9 m_rank from dual union all
                select 'Hard Parse Count Per Txn'                   metric_name, 'Hard Parses' short_name, 1 coeff, 2 typ, 10 m_rank from dual union all
                select 'Logons Per Txn'                             metric_name, 'Logons' short_name, 1 coeff, 2 typ, 11 m_rank from dual union all
                select 'Executions Per Txn'                         metric_name, 'Executes' short_name, 1 coeff, 2 typ, 12 m_rank from dual union all
                select 'User Rollbacks Per Txn'                     metric_name, 'Rollbacks' short_name, 1 coeff, 2 typ, 13 m_rank from dual union all
                select 'User Transaction Per Txn'                   metric_name, 'Transactions' short_name, 1 coeff, 2 typ, 14 m_rank from dual union all
                select 'User Rollback Undo Records Applied Per Txn' metric_name, 'Applied urec' short_name, 1 coeff, 2 typ, 15 m_rank from dual) m
          where m.metric_name = s.metric_name
            and s.intsize_csec > 5000
            and s.intsize_csec < 7000
			--sysdate - interval '5' minute
			and END_TIME > sysdate - interval '5' minute
			)
      group by short_name)
 order by m_rank;


   DB Time              4.6
              DB CPU              2.0
           Redo size        255,839.5           7,734.4
       Logical reads        596,220.2          18,024.6
       Block changes          1,221.1              36.9
      Physical reads          2,155.1              65.2
     Physical writes             23.4                .7
          User calls            754.5              22.8
              Parses            874.5              26.4
         Hard Parses              6.7                .2
              Logons             20.8                .6
            Executes          1,003.1              30.3
           Rollbacks               .0
        Transactions             33.1
        Applied urec               .0                .0

  
  
  ---  ===== 
	   
	   
	   
	   
 
set head off  pages 0  lines 300 echo off  feedback off
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
VARIABLE x VARCHAR2(30)
exec select max(snap_id) -1 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
exec select DBID into :DID from v$database;
exec select INSTANCE_NUMBER into :INST_NUMBER from v$instance ;




WITH snaps
     AS (SELECT :DID db_id,
                :INST_NUMBER instance_number,
                :EndSnap e_snap_id,
                :BgnSnap b_snap_id
           FROM DUAL),      e_u_val
     AS (SELECT SUM (VALUE) end_val
           FROM dba_hist_sysstat e, snaps sn
          WHERE     1 = 1
                AND e.snap_id = sn.e_snap_id
                AND e.dbid = sn.db_id
                AND e.instance_number = sn.instance_number
                AND e.stat_name IN ('user rollbacks', 'user commits')),
     b_u_val
     AS (SELECT SUM (VALUE) bgn_val
           FROM dba_hist_sysstat b, snaps sn
          WHERE     1 = 1
                AND b.snap_id = sn.b_snap_id
                AND b.dbid = sn.db_id
                AND b.instance_number = sn.instance_number
                AND b.stat_name IN ('user rollbacks', 'user commits')),
     d_u_val
     AS (SELECT end_val - bgn_val usr_val
           FROM e_u_val, b_u_val
          WHERE 1 = 1),
     db_tme
     AS (SELECT     EXTRACT (
                       DAY FROM e.end_interval_time - b.end_interval_time)
                  * 86400
                +   EXTRACT (
                       HOUR FROM e.end_interval_time - b.end_interval_time)
                  * 3600
                +   EXTRACT (
                       MINUTE FROM e.end_interval_time - b.end_interval_time)
                  * 60
                + EXTRACT (
                     SECOND FROM e.end_interval_time - b.end_interval_time)
                   d_db_tme
           FROM dba_hist_snapshot b, dba_hist_snapshot e, snaps sn
          WHERE     e.snap_id = sn.e_snap_id
                AND b.snap_id = sn.b_snap_id
                AND b.dbid = sn.db_id
                AND b.instance_number = sn.instance_number
                AND e.dbid = sn.db_id
                AND e.instance_number = sn.instance_number),
     trn_val
     AS (SELECT 'Transactions:' st_name,
                ROUND (usr_val / d_db_tme, 2) per_sec,
                NULL per_txn,
                12 m_rank
           FROM db_tme, d_u_val
          WHERE 1 = 1),
     bgn_val
     AS (SELECT /*+ use_hash(s) */
               m.st_name, b.VALUE VALUE, m_rank
           FROM dba_hist_sysstat b,
                (SELECT 'redo size' stat_name, 'Redo size:' st_name, 1 m_rank
                   FROM DUAL
                 UNION ALL
                 SELECT 'session logical reads' stat_name,
                        'Logical reads:' st_name,
                        2 m_rank
                   FROM DUAL
                 UNION ALL
                 SELECT 'db block changes' metric_name,
                        'Block changes:' st_name,
                        3 m_rank
                   FROM DUAL
                 UNION ALL
                 SELECT 'physical reads' metric_name,
                        'Physical reads' st_name,
                        4 m_rank
                   FROM DUAL
                 UNION ALL
                 SELECT 'physical writes' metric_name,
                        'Physical writes:' st_name,
                        5 m_rank
                   FROM DUAL
                 UNION ALL
                 SELECT 'user calls' metric_name,
                        'User calls:' st_name,
                        6 m_rank
                   FROM DUAL
                 UNION ALL
                 SELECT 'parse count (total)' metric_name,
                        'Parses:' st_name,
                        7 m_rank
                   FROM DUAL
                 UNION ALL
                 SELECT 'parse count (hard)' metric_name,
                        'Hard Parses:' st_name,
                        8 m_rank
                   FROM DUAL
                 UNION ALL
                 SELECT 'logons cumulative' metric_name,
                        'Logons:' st_name,
                        9 m_rank
                   FROM DUAL
                 UNION ALL
                 SELECT 'execute count' metric_name,
                        'Executes:' st_name,
                        10 m_rank
                   FROM DUAL
                 UNION ALL
                 SELECT 'user rollbacks' metric_name,
                        'Rollbacks:' st_name,
                        11 m_rank
                   FROM DUAL) m,
                snaps sn
          WHERE     1 = 1
                AND m.stat_name = b.stat_name
                AND b.snap_id = sn.b_snap_id
                AND b.dbid = sn.db_id
                AND b.instance_number = sn.instance_number),
     end_val
     AS (SELECT /*+ use_hash(s) */
               m.st_name, b.VALUE VALUE, m_rank
           FROM dba_hist_sysstat b,
                (SELECT 'redo size' stat_name, 'Redo size:' st_name, 1 m_rank
                   FROM DUAL
                 UNION ALL
                 SELECT 'session logical reads' stat_name,
                        'Logical reads:' st_name,
                        2 m_rank
                   FROM DUAL
                 UNION ALL
                 SELECT 'db block changes' metric_name,
                        'Block changes:' st_name,
                        3 m_rank
                   FROM DUAL
                 UNION ALL
                 SELECT 'physical reads' metric_name,
                        'Physical reads' st_name,
                        4 m_rank
                   FROM DUAL
                 UNION ALL
                 SELECT 'physical writes' metric_name,
                        'Physical writes:' st_name,
                        5 m_rank
                   FROM DUAL
                 UNION ALL
                 SELECT 'user calls' metric_name,
                        'User calls:' st_name,
                        6 m_rank
                   FROM DUAL
                 UNION ALL
                 SELECT 'parse count (total)' metric_name,
                        'Parses:' st_name,
                        7 m_rank
                   FROM DUAL
                 UNION ALL
                 SELECT 'parse count (hard)' metric_name,
                        'Hard Parses:' st_name,
                        8 m_rank
                   FROM DUAL
                 UNION ALL
                 SELECT 'logons cumulative' metric_name,
                        'Logons:' st_name,
                        9 m_rank
                   FROM DUAL
                 UNION ALL
                 SELECT 'execute count' metric_name,
                        'Executes:' st_name,
                        10 m_rank
                   FROM DUAL
                 UNION ALL
                 SELECT 'user rollbacks' metric_name,
                        'Rollbacks:' st_name,
                        11 m_rank
                   FROM DUAL) m,
                snaps sn
          WHERE     1 = 1
                AND m.stat_name = b.stat_name
                AND b.snap_id = sn.e_snap_id
                AND b.dbid = sn.db_id
                AND b.instance_number = sn.instance_number)
  SELECT st_name, per_sec, per_txn
    FROM (SELECT e.st_name,
                 ROUND ( (e.VALUE - b.VALUE) / (SELECT d_db_tme FROM db_tme),
                        1)
                    per_sec,
                 ROUND ( (e.VALUE - b.VALUE) / (SELECT usr_val FROM d_u_val), 1)
                    per_txn,
                 e.m_rank
            FROM end_val e, bgn_val b
           WHERE e.st_name = b.st_name AND e.m_rank = b.m_rank
          UNION ALL
          SELECT st_name, round(per_sec,1), per_txn, m_rank FROM trn_val)
ORDER BY m_rank
/


Redo size:            5,382,951.2      79709.2
Logical reads:          640,660.8       9486.7
Block changes:           23,180.7        343.3
Physical reads           10,727.6        158.9
Physical writes:            586.8          8.7
User calls:                 796.8         11.8
Parses:                     884.7         13.1
Hard Parses:                  6.4           .1
Logons:                      21.0           .3
Executes:                 1,150.6           17
Rollbacks:                     .0            0
Transactions:                67.5

RMAN Restoring the Spfile

 Rman Restoring the spfile form backup.. 


export NLS_DATE_FORMAT='dd/mm/yyyy hh24:mi:ss';

rman TARGET /

RMAN> STARTUP FORCE NOMOUNT;

Restore the server parameter file. If restoring to the default location, then run:

RMAN> RESTORE SPFILE FROM AUTOBACKUP; 

If restoring to a nondefault location, 

RMAN> RESTORE SPFILE TO '/tmp/spfileTEMP.ora' FROM AUTOBACKUP;

=====

RMAN> list backup ;


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
2409    Full    15.34M     DISK        00:00:01     22/04/2023 02:00:37
        BP Key: 2409   Status: AVAILABLE  Compressed: NO  Tag: TAG20230422T020036
        Piece Name: /u01/app/oracle/VIHAAN8/cf_c-3962735431-20230422-01 
        
        
rman >

restore spfile to '/tmp/spvihaan8.ora' from '/u01/app/oracle/VIHAAN8/cf_c-3962735431-20230422-01';



Starting restore at 22/04/2023 10:12:05
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1348 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/VIHAAN8/cf_c-3962735431-20230422-01
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 22/04/2023 10:12:07




RMAN>
 ls -ltr /tmp/*.ora
-rw-r----- 1 oracle oinstall 4608 Apr 22 10:12 /tmp/spvihaan8.ora



CREATE PFILE='/tmp/spvihaan8.txt' FROM SPFILE = '/tmp/spvihaan8.ora';

CREATE PFILE='/tmp/spvihaan8.txt' FROM SPFILE = '/tmp/spvihaan8.ora';primary:sys@IBRAC-ibrac2 sqlplus>

File created.



sqlplus> !ls -ltr /tmp/*.txt
-rw-r--r-- 1 oracle oinstall 1503 Apr 22 10:15 /tmp/spvihaan8.txt

===

restore spfile from autobackup db_recovery_file_dest='XXXXXXXX' db_name='vihaan08';

=========

RMAN> list backup of spfile;

using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
2405    Full    15.34M     DISK        00:00:00     21-04-2023 02:00:37
        BP Key: 2405   Status: AVAILABLE  Compressed: NO  Tag: TAG20230421T020037
        Piece Name: /u01/app/oracle/VIHAAN8/cf_c-3962735431-20230421-01
  SPFILE Included: Modification time: 20-04-2023 14:11:36
  SPFILE db_unique_name: VIHCDBD8

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
2407    Full    15.34M     DISK        00:00:01     22-04-2023 02:00:30
        BP Key: 2407   Status: AVAILABLE  Compressed: NO  Tag: TAG20230422T020029
        Piece Name: /u01/app/oracle/VIHAAN8/cf_c-3962735431-20230422-00
  SPFILE Included: Modification time: 20-04-2023 14:11:36
  SPFILE db_unique_name: VIHCDBD8

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
2409    Full    15.34M     DISK        00:00:01     22-04-2023 02:00:37
        BP Key: 2409   Status: AVAILABLE  Compressed: NO  Tag: TAG20230422T020036
        Piece Name: /u01/app/oracle/VIHAAN8/cf_c-3962735431-20230422-01
  SPFILE Included: Modification time: 20-04-2023 14:11:36
  SPFILE db_unique_name: VIHCDBD8

Oracle flashback Hourly info

Oracle flashback Hourly info http://anuj-singh.blogspot.com/2011/08/oracle-flashback-info.html

set term off term on
SET head on FEED off ECHO OFF LINES 1000 TRIMSPOOL ON TRIM on PAGES 1000
COLUMN separator HEADING "!|!|!"                 FORMAT A1
COLUMN "Date"  HEADING "Date"      FORMAT A9
COLUMN "Total" HEADING "Total|(#)" FORMAT 9999
COLUMN "Day"   HEADING "Day"       FORMAT A3
COLUMN h0      HEADING "h0|(#)"    FORMAT 999
COLUMN h1      HEADING "h1|(#)"    FORMAT 999
COLUMN h2      HEADING "h2|(#)"    FORMAT 999
COLUMN h3      HEADING "h3|(#)"    FORMAT 999
COLUMN h4      HEADING "h4|(#)"    FORMAT 999
COLUMN h5      HEADING "h5|(#)"    FORMAT 999
COLUMN h6      HEADING "h6|(#)"    FORMAT 999
COLUMN h7      HEADING "h7|(#)"    FORMAT 999
COLUMN h8      HEADING "h8|(#)"    FORMAT 999
COLUMN h9      HEADING "h9|(#)"    FORMAT 999
COLUMN h10     HEADING "h10|(#)"   FORMAT 999
COLUMN h11     HEADING "h11|(#)"   FORMAT 999
COLUMN h12     HEADING "h12|(#)"   FORMAT 999
COLUMN h13     HEADING "h13|(#)"   FORMAT 999
COLUMN h14     HEADING "h14|(#)"   FORMAT 999
COLUMN h15     HEADING "h15|(#)"   FORMAT 999
COLUMN h16     HEADING "h16|(#)"   FORMAT 999
COLUMN h17     HEADING "h17|(#)"   FORMAT 999
COLUMN h18     HEADING "h18|(#)"   FORMAT 999
COLUMN h19     HEADING "h19|(#)"   FORMAT 999
COLUMN h20     HEADING "h20|(#)"   FORMAT 999
COLUMN h21     HEADING "h21|(#)"   FORMAT 999
COLUMN h22     HEADING "h22|(#)"   FORMAT 999
COLUMN h23     HEADING "h23|(#)"   FORMAT 999
PROMPT
PROMPT *******************************************************************************************************************************************
PROMPT *   flashback   S U M M A R Y (By Frequency)
PROMPT *   (Hourly and Daily figures in number of flashback)
PROMPT *******************************************************************************************************************************************
PROMPT
PROMPT 
SELECT  to_char(trunc(first_time),'DD-Mon-YY') "Date",
        to_char(first_time, 'Dy') "Day",
         '|'                                               separator,
        count(1) Total,
         '|'                                               separator,
        SUM(decode(to_char(first_time, 'hh24'),'00',1,0)) "h0",
        SUM(decode(to_char(first_time, 'hh24'),'01',1,0)) "h1",
        SUM(decode(to_char(first_time, 'hh24'),'02',1,0)) "h2",
        SUM(decode(to_char(first_time, 'hh24'),'03',1,0)) "h3",
        SUM(decode(to_char(first_time, 'hh24'),'04',1,0)) "h4",
        SUM(decode(to_char(first_time, 'hh24'),'05',1,0)) "h5",
        SUM(decode(to_char(first_time, 'hh24'),'06',1,0)) "h6",
        SUM(decode(to_char(first_time, 'hh24'),'07',1,0)) "h7",
        SUM(decode(to_char(first_time, 'hh24'),'08',1,0)) "h8",
        SUM(decode(to_char(first_time, 'hh24'),'09',1,0)) "h9",
        SUM(decode(to_char(first_time, 'hh24'),'10',1,0)) "h10",
        SUM(decode(to_char(first_time, 'hh24'),'11',1,0)) "h11",
        SUM(decode(to_char(first_time, 'hh24'),'12',1,0)) "h12",
        SUM(decode(to_char(first_time, 'hh24'),'13',1,0)) "h13",
        SUM(decode(to_char(first_time, 'hh24'),'14',1,0)) "h14",
        SUM(decode(to_char(first_time, 'hh24'),'15',1,0)) "h15",
        SUM(decode(to_char(first_time, 'hh24'),'16',1,0)) "h16",
        SUM(decode(to_char(first_time, 'hh24'),'17',1,0)) "h17",
        SUM(decode(to_char(first_time, 'hh24'),'18',1,0)) "h18",
        SUM(decode(to_char(first_time, 'hh24'),'19',1,0)) "h19",
        SUM(decode(to_char(first_time, 'hh24'),'20',1,0)) "h20",
        SUM(decode(to_char(first_time, 'hh24'),'21',1,0)) "h21",
        SUM(decode(to_char(first_time, 'hh24'),'22',1,0)) "h22",
        SUM(decode(to_char(first_time, 'hh24'),'23',1,0)) "h23"
 from v$flashback_database_logfile 
where 1=1
group by trunc(first_time), to_char(first_time, 'Dy')
order by trunc(first_time)
/


              !       !
              ! Total !   h0   h1   h2   h3   h4   h5   h6   h7   h8   h9  h10  h11  h12  h13  h14  h15  h16  h17  h18  h19  h20  h21  h22  h23
Date      Day !   (#) !  (#)  (#)  (#)  (#)  (#)  (#)  (#)  (#)  (#)  (#)  (#)  (#)  (#)  (#)  (#)  (#)  (#)  (#)  (#)  (#)  (#)  (#)  (#)  (#)
--------- --- - ----- - ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
09-Apr-23 Sun |    67 |    0    0    0    0    0    0    0    2    3    3    4    5    4    5    4    5    4    4    4    5    5    3    4    3
10-Apr-23 Mon |    23 |    4    2    3    3    2    2    4    3    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0

Oracle  Please ensure that this directory is writable and has atleast 60 MB of disk space. Installation cannot continue. ..

Oracle – Remove / Detach Home from Oracle Inventory

====

[oracle@rac01:/u01/app/oracle/product/18.3.0/oui/bin] $

./runInstaller -silent -detachHome -invPtrLoc /etc/oraInst.loc ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1"

Error in writing to directory /tmp/OraInstall2023-04-04_07-04-16AM. Please ensure that this directory is writable and has atleast 60 MB of disk space. Installation cannot continue.

Starting Oracle Universal Installer...


if not enough space in /tmp 

Do following !!

mkdir $ORACLE_BASE/tmp
export TMP=$ORACLE_BASE/tmp
export TMPDIR=$ORACLE_BASE/tmp
export TEMP=$ORACLE_BASE/tmp


then run again 


./runInstaller -silent -detachHome -invPtrLoc /etc/oraInst.loc ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1"
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 16256 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
find: ‘/u01/app/oraInventory/logs/logs/VIS_delete’: Permission denied
find: ‘/u01/app/oraInventory/logs/logs/VIS_delete’: Permission denied
find: ‘/u01/app/oraInventory/logs/logs/VIS_delete’: Permission denied
'DetachHome' was successful.

=============

grep "HOME NAME" /u01/app/oraInventory/ContentsXML/inventory.xml|grep db


grep "HOME NAME" /u01/app/oraInventory/ContentsXML/inventory.xml|grep db
<HOME NAME="OraDB12Home1" LOC="/u01/app/oracle/product/12.1.0/dbhome_1" TYPE="O" IDX="2">
<HOME NAME="OraDB12Home2" LOC="/u01/app/oracle/product/12.2.0/dbhome_1" TYPE="O" IDX="4"/>
<HOME NAME="OraDb11g_home2" LOC="/u01/app/oracle/product/11.2.0/dbhome_2" TYPE="O" IDX="18"/>
<HOME NAME="OraDb11g_home1" LOC="/u01/app/oracle/product/11.2.0/dbhome_1" TYPE="O" IDX="5" REMOVED="T"/>




$ORACLE_HOME/oui/bin/runInstaller -silent -detachHome ORACLE_HOME="/u01/app/oracle/product/11.2.0/dbhome_1" ORACLE_HOME_NAME="OraDb11g_home1"

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)