Search This Blog

Total Pageviews

Sunday, 3 September 2017

Rman backup status from sqlplus

Rman backup status from sqlplus> ..


Rman backup status 
Rman backup output via sql  
v$rman_output to review  backups

for status ... 
http://anuj-singh.blogspot.com/2017/05/rman-sql-to-find-backup-details.html?m=1



https://anuj-singh.blogspot.com/search?q=gv%24backup_sync_io

-- v$rman_output goes away with a restart

set linesize 200  pagesize 200
select output
from v$rman_output where session_recid = (select max(session_recid) from v$rman_status)
order by recid ;

OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
connected to target database: GRAINUAT (DBID=2803380037, not open)
using target database control file instead of recovery catalog

old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored

allocated channel: c1
channel c1: SID=134 device type=DISK


or 

set linesize 200  pagesize 200
select output
from v$rman_output where session_recid in (select session_recid from v$rman_status WHERE operation = 'BACKUP' AND status != 'COMPLETED' AND status != 'RUNNING')
order by recid ;


====

set linesize 200  pagesize 200
select output
from v$rman_output where session_recid in (select session_recid from v$rman_status
where start_time > sysdate -7)
order by recid ;

set linesize 200  pagesize 200
select output
from v$rman_output where session_recid in (select session_recid from v$rman_status
where start_time > sysdate -7)
order by recid ;


OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
connected to target database: GRAINUAT (DBID=2803380037, not open)
using target database control file instead of recovery catalog

old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored

allocated channel: c1
channel c1: SID=134 device type=DISK

allocated channel: c2
channel c2: SID=167 device type=DISK
alter session set nls_date_format='DD-MON-YYYY hh24:mi:ss';

set  linesize 200
select object_type,mbytes_processed, start_time, end_time,status
from v$rman_status
where session_recid = (select max(session_recid) from v$rman_status) and operation !='RMAN'
order by recid

OBJECT_TYPE     MBYTES_PROCESSED START_TIME           END_TIME             STATUS
--------------- ---------------- -------------------- -------------------- -----------------------
ARCHIVELOG                    15 03-SEP-2017 14:54:43 03-SEP-2017 14:54:48 COMPLETED
DB INCR               199589.594 03-SEP-2017 14:54:48 03-SEP-2017 18:17:23 RUNNING

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


set linesize 300 pagesize 300
-- TTITLE LEFT '% Completed. Aggregate is the overall progress:'
SET LINE 300
col SKILL for a15
SELECT 
''''||sid ||','|| serial#||',@'||inst_id ||'''' Skill,opname, round(sofar/totalwork*100) "% Complete"   FROM gv$session_longops
WHERE opname LIKE 'RMAN%'
AND totalwork != 0
AND sofar <> totalwork
 ORDER BY 1;



set linesize 300 pagesize 300
col info for a120
select  'Session info  '||skill||info Rman_Session_info  from (SELECT ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' Skill,'Throughput: '||  ROUND(SUM(v.value/1024/1024),1) || ' Meg so far @ ' || ROUND(SUM(v.value /1024/1024)/NVL((SELECT MIN(elapsed_seconds) info
                FROM gv$session_longops
                WHERE opname  LIKE 'RMAN: aggregate input'
                AND sofar != TOTALWORK
                AND elapsed_seconds IS NOT NULL
           ),SUM(v.value /1024/1024)),2) || ' Meg/sec' info
     FROM gv$sesstat v, v$statname n, gv$session s
  WHERE v.statistic# = n.statistic#
  AND n.name       = 'physical write total bytes'
  AND v.sid        = s.sid
  AND v.inst_id    = s.inst_id
  AND s.program LIKE 'rman@%'
  GROUP BY ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''', n.name)
;





-- TTITLE LEFT 'Channels waiting...'
COL client_info FORMAT A15 TRUNC
COL event FORMAT A30 
COL state FORMAT A30
COL wait FORMAT 999.90 HEAD "Min waiting"
col SKILL for a15
SELECT ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' Skill, p.spid, s.client_info, status, event, state, seconds_in_wait/60 wait   FROM gv$process p, gv$session s
WHERE p.addr = s.paddr
AND client_info LIKE 'rman%';



-- TTITLE LEFT 'Files currently being written to...'
set numf 9999999999999
COL filename FORMAT a80
SELECT filename, bytes, io_count FROM gv$backup_async_io
 WHERE status='IN PROGRESS'
/

=====


define _start prompt='sysdate-1'
define _errors_only='y'



col command_id  for a19
col start_time  for a10
col end_time    for a10


COL RECID           NEW_VAL P_RECID       NOPRINT
COL COMMAND_ID      NEW_VAL P_COMMAND_ID  NOPRINT
COL START_TIME      NEW_VAL P_START_TIME  NOPRINT
COL END_TIME        NEW_VAL P_END_TIME    NOPRINT
COL OPERATION       NEW_VAL P_OPERATION   NOPRINT
COL STATUS          NEW_VAL P_STATUS      NOPRINT

COL LINE#       NOPRINT;

break on recid on command_id on start_time on end_time on operation on status skip page;
set pause off;

ttitle left -
     '###############################################################################################################' skip 1-
     '# RECID      : ' P_RECID       skip 1-
     '# COMMAND_ID : ' P_COMMAND_ID  skip 1-
     '# START_TIME : ' P_START_TIME  skip 1-
     '# END_TIME   : ' P_END_TIME    skip 1-
     '# OPERATION  : ' P_OPERATION   skip 1-
     '# STATUS     : ' P_STATUS      skip 2;


with 
 st as (
   select/*+ materialize */
     --s.sid,
     --s.parent_recid,
     s.recid,command_id
    ,to_char(start_time,'hh24:mi:ss') start_time
    ,to_char(end_time  ,'hh24:mi:ss') end_time
    ,operation||' '||object_type      operation
    ,status                      status
   from v$rman_status s 
   where start_time >= case 
                            when '&_start' is null then trunc(sysdate-3) 
                            when regexp_like('&_start','\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d') then to_date('&_start','yyyy-mm-dd hh24:mi:ss')
                            when regexp_like('&_start','\d\d\d\d-\d\d-\d\d') then to_date('&_start','yyyy-mm-dd')
                            when regexp_like('&_start','^\d+$') then sysdate-to_number('&_start')
                            else trunc(sysdate)
                        end
   and s.status not in ('RUNNING','COMPLETED') 
 )
   select --distinct command_id||' '||operation||' '||object_type||' '||status  errors 
     st.*
    --,o.recid as line#
    ,o.output
   from st
       ,v$rman_output o
   where 
     o.RMAN_STATUS_RECID=st.recid
     and (  o.output not like 'Backup Set%'
        and o.output not like '  Backup Piece%'
        and o.output not like 'input%'
        and o.output not like 'channel%'
        and o.output not like 'piece%'
     )
     and (
           lower('&_errors_only')!='y' 
            or 
          (o.output like 'RMAN-%' 
            and substr(o.output,1,10) not in (
                                        'RMAN-06210', 
                                        'RMAN-06211', 
                                        'RMAN-06212', 
                                        'RMAN-06213', 
                                        'RMAN-06214')
         ))
   order by --command_id,start_time,
     st.recid,o.recid;

undef _start

clear break


====




define _start=''

select recid, parent_recid, row_type, command_id, operation, object_type, status
from v$rman_status 
where start_time >= case 
                        when '&_start' is null then trunc(sysdate-3) 
                        when regexp_like('&_start','\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d') then to_date('&_start','yyyy-mm-dd hh24:mi:ss')
                        when regexp_like('&_start','\d\d\d\d-\d\d-\d\d') then to_date('&_start','yyyy-mm-dd')
                        when regexp_like('&_start','^\d+$') then sysdate-to_number('&_start')
                        else trunc(sysdate)
                    end
order by start_time desc;

undef _start


###############################################################################
# RECID      :      48091
# COMMAND_ID : 2022-04-11T21:02:29
# START_TIME : 21:19:28
# END_TIME   : 21:21:19
# OPERATION  : RMAN
# STATUS     : COMPLETED

PARENT_RECID ROW_TYPE            OBJECT_TYPE
------------ ------------------- -------------
             SESSION



===

define _start=''
col command_id  for a19
col start_time  for a10
col end_time    for a10


COL RECID           NEW_VAL P_RECID       NOPRINT
COL COMMAND_ID      NEW_VAL P_COMMAND_ID  NOPRINT
COL START_TIME      NEW_VAL P_START_TIME  NOPRINT
COL END_TIME        NEW_VAL P_END_TIME    NOPRINT
COL OPERATION       NEW_VAL P_OPERATION   NOPRINT
COL STATUS          NEW_VAL P_STATUS      NOPRINT

COL LINE#       NOPRINT

break on recid on command_id on start_time on end_time on operation on status skip page;
set pause on;

ttitle left -
     '###############################################################################################################' skip 1-
     '# RECID      : ' P_RECID       skip 1-
     '# COMMAND_ID : ' P_COMMAND_ID  skip 1-
     '# START_TIME : ' P_START_TIME  skip 1-
     '# END_TIME   : ' P_END_TIME    skip 1-
     '# OPERATION  : ' P_OPERATION   skip 1-
     '# STATUS     : ' P_STATUS      skip 2;


with 
 st as (
   select/*+ materialize */
     --s.sid,
     --s.parent_recid,
     s.recid,command_id
    ,to_char(start_time,'hh24:mi:ss') start_time
    ,to_char(end_time  ,'hh24:mi:ss') end_time
    ,operation||' '||object_type      operation
    ,status                      status
   from v$rman_status s 
   where start_time >= case 
                            when '&_start' is null then trunc(sysdate-1) 
                            when regexp_like('&_start','\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d') then to_date('&_start','yyyy-mm-dd hh24:mi:ss')
                            when regexp_like('&_start','\d\d\d\d-\d\d-\d\d') then to_date('&_start','yyyy-mm-dd')
                            when regexp_like('&_start','^\d+$') then sysdate-to_number('&_start')
                            else trunc(sysdate)
                        end
   and s.status not in ('RUNNING','COMPLETED') 
 )
   select --distinct command_id||' '||operation||' '||object_type||' '||status  errors 
     st.*
    --,o.recid as line#
    ,o.output
   from st
       ,v$rman_output o
   where 
     o.RMAN_STATUS_RECID=st.recid
     and (  o.output not like 'Backup Set%'
        and o.output not like '  Backup Piece%'
        and o.output not like 'input%'
        and o.output not like 'channel%'
        and o.output not like 'piece%'
     )
   order by --command_id,start_time,
     st.recid,o.recid;

undef _start




################################################################################
# RECID      :      48105
# COMMAND_ID : 2022-04-13T21:17:28
# START_TIME : 21:17:28
# END_TIME   : 21:17:32
# OPERATION  : RMAN
# STATUS     : COMPLETED WITH ERRORS

OUTPUT
--------------------------------------------------------------------------------
 unavailable;



==========


define NUMBER_OF_DAYS=15
set lines 300 pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 999,999,999,999,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 999999999 heading "OUT|INST"
col START_TIME  for a24
col END_TIME for a24
col Sday for a14
col eday for a14
select
j.session_recid, j.session_stamp,
 (j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
to_char(j.start_time, 'dd-mm-yyyy hh24:mi:ss') start_time,
to_char(j.end_time, 'dd-mm-yyyy hh24:mi:ss') end_time
,to_char(j.start_time,'DAY','nls_date_language=ENGLISH') Sday
,to_char(j.end_time,'DAY','nls_date_language=ENGLISH') Eday
 --, j.elapsed_seconds
 , j.time_taken_display,
  x.cf, x.df, x.i0, x.i1, x.l,
  ro.inst_id output_instance
from V$RMAN_BACKUP_JOB_DETAILS j
  left outer join (select
                     d.session_recid, d.session_stamp,
                     sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
                     sum(case when d.controlfile_included = 'NO'
                               and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
                     sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
                     sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
                     sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
                   from
                     V$BACKUP_SET_DETAILS d
                     join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
                   where s.input_file_scan_only = 'NO'
                   group by d.session_recid, d.session_stamp) x
    on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
  left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
                   from GV$RMAN_OUTPUT o
                   group by o.session_recid, o.session_stamp)
    ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
--and j.input_type='DB FULL'  ------------<<<<<<<<<<<<<<<<<
--and j.output_bytes/1024/1024 > 19000000   --- for Big backup only
-- and j.output_bytes/1024/1024 > 566579
order by j.start_time;



======


alter session set nls_date_format='dd-mm-yyyy hh24:mi';

set linesize 500 pages 300
col OUTPUT_BYTES_DISPLAY for a12
col elapsed_seconds for 99999999.99
col COMPRESSION_RATIO for 9999.99

col TIME_TAKEN_DISPLAY for a12
col OUTPUT_BYTES_DISPLAY1 for a22
select * from (
SELECT 
--jd.session_recid,
 --        jd.session_stamp,
jd.output_bytes_display output_bytes_display1,
  jd.time_taken_display,
     --    jd.session_key,
         jd.start_time,
         jd.end_time,
         jd.elapsed_seconds,
      -- jd.time_taken_display,
         jd.output_device_type,
         jd.status,
         jd.input_type,
         jd.compression_ratio,
         jd.output_bytes_display,
         x.cf,
         x.df,
         x.i0,
         x.i1,
         x.l,
         ro.inst_id
    FROM V$RMAN_BACKUP_JOB_DETAILS jd
         LEFT OUTER JOIN
         (  SELECT d.session_recid,
                   d.session_stamp,
                   SUM (
                      CASE
                         WHEN d.controlfile_included = 'YES' THEN d.pieces
                         ELSE 0
                      END)
                      CF,
                   SUM (
                      CASE
                         WHEN d.backup_type || d.incremental_level = 'D'
                         THEN
                            d.pieces
                         ELSE
                            0
                      END)
                      DF,
                   SUM (
                      CASE
                         WHEN    (d.backup_type || d.incremental_level = 'D0')
                              OR (d.backup_type || d.incremental_level = 'I0')
                         THEN
                            d.pieces
                         ELSE
                            0
                      END)
                      I0,
                   SUM (
                      CASE
                         WHEN d.backup_type || d.incremental_level = 'I1'
                         THEN
                            d.pieces
                         ELSE
                            0
                      END)
                      I1,
                   SUM (CASE WHEN d.backup_type = 'L' THEN d.pieces ELSE 0 END) L
              FROM V$BACKUP_SET_DETAILS d
                   JOIN V$BACKUP_SET s
                      ON s.set_stamp = d.set_stamp AND s.set_count = d.set_count
             WHERE s.input_file_scan_only = 'NO'
          GROUP BY d.session_recid, d.session_stamp) x
            ON     x.session_recid = jd.session_recid
               AND x.session_stamp = jd.session_stamp
         LEFT OUTER JOIN
         (  SELECT o.session_recid, o.session_stamp, MIN (inst_id) inst_id
              FROM GV$RMAN_OUTPUT o
          GROUP BY o.session_recid, o.session_stamp) ro
            ON     ro.session_recid = jd.session_recid
               AND ro.session_stamp = jd.session_stamp
   WHERE jd.start_time > TRUNC (SYSDATE) - 20
  and output_bytes_display like '%T%'  ------
  --and output_bytes_display1
ORDER BY jd.start_time
)
;




====

set lines 1000 pages 1000
set numformat 99999999999999999999
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS' ;
col output_size1 for a12 heading "output_size"
col STATUS for a24
select distinct r.object_type,r.SESSION_RECID,r.START_TIME,r.END_TIME,(r.END_TIME-r.START_TIME)*24 "ET(Hr)",
upper(dbms_xplan.format_size2(r.output_bytes )) output_size1 ,
--r.OUTPUT_BYTES/1048576/1024 "SIZE (GB)",
r.status,r.OUTPUT_DEVICE_TYPE,r.input_bytes,
INPUT_BYTES_PER_SEC/1048576 "read (MB/sec)",
r.output_bytes,
OUTPUT_BYTES_PER_SEC/1048576 "output (MB/sec)"
from v$rman_status r, v$rman_backup_job_details d
where r.OPERATION like '%BACKUP%'
and d.SESSION_RECID=r.SESSION_RECID
and r.END_TIME > sysdate -7
;



define NUMBER_OF_DAYS=4
set lines 300 pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 999,999,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 999999999 heading "OUT|INST"
col START_TIME  for a24
col END_TIME for a24
col output_size1 for a9
col "ET(Hr)" for 9999.99
col timeT for a15
select
  --j.session_recid, j.session_stamp,
  to_char(j.start_time, 'dd-mm-yyyy hh24:mi:ss') start_time,
  to_char(j.end_time, 'dd-mm-yyyy hh24:mi:ss') end_time,
 -- REGEXP_SUBSTR(NUMTODSINTERVAL(NVL(j.END_TIME,j.START_TIME) - j.START_TIME,'day') ,'\d{2} \d{2}:\d{2}:\d{2}\.\d{3}' ) timeT,
  (j.END_TIME-j.START_TIME)*24 "ET(Hr)",
--  (j.output_bytes/1024/1024) output_mbytes,
upper(dbms_xplan.format_size2(j.output_bytes )) output_size1,
  j.status, j.input_type
  /* ,decode(to_char(j.start_time, 'd'), 1, 'Monday', 2, 'Tuesday',
                                     3, 'Wednesday', 4, 'Thursday',
                                     5, 'Friday', 6, 'Saturday',
                                     7, 'Sunday') dow */
 --, j.elapsed_seconds
 , j.time_taken_display,
  x.cf, x.df, x.i0, x.i1, x.l,
  ro.inst_id output_instance
from V$RMAN_BACKUP_JOB_DETAILS j
  left outer join (select
                     d.session_recid, d.session_stamp,
                     sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
                     sum(case when d.controlfile_included = 'NO'
                               and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
                     sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
                     sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
                     sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
                   from
                     V$BACKUP_SET_DETAILS d
                     join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
                   where s.input_file_scan_only = 'NO'
                   group by d.session_recid, d.session_stamp) x
    on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
  left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
                   from GV$RMAN_OUTPUT o
                   group by o.session_recid, o.session_stamp)
    ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
order by j.start_time;





col output_size for a10
col output_size1 for a12 heading "output_size"
col TAG for a20
col STATUS for a10
col DB_unique_name  for a15
select distinct  SYS_CONTEXT('USERENV','DB_UNIQUE_NAME') DB_unique_name, p.tag, s.incremental_level, 
upper(dbms_xplan.format_size2(r.output_bytes )) output_size1 ,
(r.END_TIME-r.START_TIME)*24 "ET(Hr)",
r. status, r.object_type,r.SESSION_RECID,r.START_TIME,r.END_TIME,
r.OUTPUT_BYTES/1048576/1024 "SIZE (GB)",r.status,r.OUTPUT_DEVICE_TYPE,r.input_bytes/1048576 "input (MB)",
INPUT_BYTES_PER_SEC/1048576 "read (MB/sec)",
--r.output_bytes/1048576 "output (MB)",
--r.output_bytes
  upper(dbms_xplan.format_size2(r.output_bytes )) output_size ,
OUTPUT_BYTES_PER_SEC/1048576 "output (MB/sec)"
from v$backup_piece p,v$rman_status r, v$rman_backup_job_details d, v$backup_set s
where p.RMAN_STATUS_RECID=r.RECID 
and p.RMAN_STATUS_STAMP=r.STAMP
and r.OPERATION like '%BACKUP%'
and d.SESSION_RECID=r.SESSION_RECID
and s.set_stamp=p.set_stamp 
and s.set_count=p.set_count
and s.incremental_level=0     --- <<< level 0
and r.END_TIME > sysdate -7
;


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


database size !!


set linesize 300
col "Database Size"  for a20
col "Free space"        for a20
col "Used space"      for a20
col hostname    for a37
col Instance    for a15
col DB_unique_name  for a15
col Tdate               heading 'Date'
col "Database SizeH" for a15
alter session set nls_date_format='dd-mm-yyyy hh24:mi' ;
select sysdate Tdate,SYS_CONTEXT('USERENV','HOST') hostname,sys_context('USERENV', 'INSTANCE_NAME') Instance ,
SYS_CONTEXT('USERENV','DB_UNIQUE_NAME') DB_unique_name,
round(sum(used.bytes)/1024/1024/1024 ) || ' GB' "Database Size" ,
dbms_xplan.format_number(sum(used.bytes)) "Database SizeH"
,round(sum(used.bytes)/1024/1024/1024 ) - round(free.SS/1024/1024/1024) || ' GB' "Used space"
,round(free.SS /1024/1024/1024) || ' GB' "Free space",LOG_MODE
from (select bytes from v$datafile
union all
select bytes from v$tempfile
union all
select bytes from v$log) used , (select sum(bytes) SS from dba_free_space) free,(select LOG_MODE from v$database)
group by free.SS,LOG_MODE
/


define NUMBER_OF_DAYS=20
set lines 300 pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 999,999,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 999999999 heading "OUT|INST"
col START_TIME  for a24
col END_TIME for a24
col output_size1 for a9
col "ET(Hr)" for 9999.99
col timeT for a15

select * from (
select
  --j.session_recid, j.session_stamp,
  to_char(j.start_time, 'dd-mm-yyyy hh24:mi:ss') start_time,
  to_char(j.end_time, 'dd-mm-yyyy hh24:mi:ss') end_time,
 -- REGEXP_SUBSTR(NUMTODSINTERVAL(NVL(j.END_TIME,j.START_TIME) - j.START_TIME,'day') ,'\d{2} \d{2}:\d{2}:\d{2}\.\d{3}' ) timeT,
  (j.END_TIME-j.START_TIME)*24 "ET(Hr)",
--  (j.output_bytes/1024/1024) output_mbytes,
upper(dbms_xplan.format_size2(j.output_bytes )) output_size1,
  j.status, j.input_type
   ,decode(to_char(j.start_time, 'd'), 1, 'Monday', 2, 'Tuesday',
                                     3, 'Wednesday', 4, 'Thursday',
                                     5, 'Friday', 6, 'Saturday',
                                     7, 'Sunday') dow 
 --, j.elapsed_seconds
 , j.time_taken_display,
  x.cf, x.df, x.i0, x.i1, x.l,
  ro.inst_id output_instance
from V$RMAN_BACKUP_JOB_DETAILS j
  left outer join (select
                     d.session_recid, d.session_stamp,
                     sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
                     sum(case when d.controlfile_included = 'NO'
                               and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
                     sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
                     sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
                     sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
                   from
                     V$BACKUP_SET_DETAILS d
                     join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
                   where s.input_file_scan_only = 'NO'
                   group by d.session_recid, d.session_stamp) x
    on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
  left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
                   from GV$RMAN_OUTPUT o
                   group by o.session_recid, o.session_stamp)
    ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
order by j.start_time
)
where 1=1
--and output_size1 like '%T%'
and status ='FAILED'
;



SET LINESIZE 250
SET PAGESIZE 100
SET TRIMSPOOL ON

COLUMN start_time           FORMAT A19           HEADING "START_TIME"
COLUMN end_time             FORMAT A19           HEADING "END_TIME"
COLUMN duration_hr_min      FORMAT A15           HEADING "DURATION (H:M)"
COLUMN status               FORMAT A20           HEADING "STATUS"
COLUMN input_type           FORMAT A12           HEADING "INPUT_TYPE"
COLUMN output_device_type   FORMAT A10           HEADING "OUTPUT DEV"
COLUMN input_size           FORMAT A12           HEADING "INPUT SIZE"
COLUMN input_rate           FORMAT A12           HEADING "INPUT_RATE"
COLUMN output_size          FORMAT A12           HEADING "OUTPUT SIZE"
COLUMN output_rate          FORMAT A12           HEADING "OUTPUT_RAT"

SELECT 
    TO_CHAR(v.start_time, 'DD-MM-YYYY HH24:MI:SS') AS start_time,
    TO_CHAR(v.end_time, 'DD-MM-YYYY HH24:MI:SS')   AS end_time,
    TRUNC(v.elapsed_seconds/3600) || 'h ' || 
    LPAD(TRUNC(MOD(v.elapsed_seconds, 3600)/60), 2, '0') || 'm' AS duration_hr_min,
    v.status,
    v.input_type,
    v.output_device_type,
    v.input_bytes_display                        AS input_size,
    v.input_bytes_per_sec_display                AS input_rate,
    v.output_bytes_display                       AS output_size,
    v.output_bytes_per_sec_display               AS output_rate
FROM 
    v$rman_backup_job_details v
WHERE 1=1
  AND v.input_type = 'DB INCR'
ORDER BY 
    v.start_time DESC;



https://anuj-singh.blogspot.com/search?q=gv%24backup_sync_io


Posted by at 5 comments:

Friday, 25 August 2017

Oracle active session report ( ASH report ) ..

Oracle active session report ( ASH report )  .. 


 
1 Hr Interval report 
SELECT TO_CHAR(SYSDATE-1/24, 'DD-MM-YYYY HH24:MI') bdate , TO_CHAR(SYSDATE , 'DD-MM-YYYY HH24:MI') edate FROM dual;

BDATE EDATE
---------------- ----------------
25-08-2017 09:34 25-08-2017 10:34



vi ash.sql 
---========
VAR dbid NUMBER
VAR inst_id NUMBER

COL bdate NEW_VALUE def_bdate
COL edate NEW_VALUE def_edate

SET TERMOUT OFF

SELECT TO_CHAR(SYSDATE-1/24, 'DD-MM-YYYY HH24:MI') bdate , TO_CHAR(SYSDATE , 'DD-MM-YYYY HH24:MI') edate FROM dual
/

SET TERMOUT ON

ACCEPT bdate DATE FORMAT 'DD-MM-YYYY HH24:MI' DEFAULT '&def_bdate' PROMPT "Enter begin time [&def_bdate]: " 
ACCEPT edate DATE FORMAT 'DD-MM-YYYY HH24:MI' DEFAULT '&def_edate' PROMPT "Enter end time [&def_edate]: " 


BEGIN
SELECT inst_id, dbid INTO :inst_id, :dbid FROM gv$database WHERE inst_id = SYS_CONTEXT('USERENV', 'INSTANCE');
END;
/


SPOOL ash_report.txt
SET TERMOUT OFF PAGESIZE 0 HEADING OFF LINESIZE 1000 TRIMSPOOL ON TRIMOUT ON TAB OFF

--SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_HTML(:dbid, :inst_id, TO_DATE('&bdate', 'DD-MM-YYYY HH24:MI'), TO_DATE('&edate', 'DD-MM-YYYY HH24:MI'), null, null, null, null ));

SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT(:dbid, :inst_id, TO_DATE('&bdate', 'DD-MM-YYYY HH24:MI'), TO_DATE('&edate', 'DD-MM-YYYY HH24:MI'), null, null, null, null ));

SPOOL OFF
SET TERMOUT ON PAGESIZE 1000 HEADING ON
PROMPT Done.


----========= End

SYS@rac1> @ash

Enter begin time [25-08-2017 09:28]:
Enter end time [25-08-2017 10:28]:

PL/SQL procedure successfully completed.


SYS@rac1> SYS@icrac1> !ls -ltr
total 284
-rw-r--r-- 1 oracle oinstall 15323 Aug 25 10:28 ash_report.txt


!cat ash_report.txt
cat: !cat: No such file or directory
old 1: SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT(:dbid, :inst_id, TO_DATE('&bdate', 'DD-MM-YYYY HH24:MI'), TO_DATE('&edate', 'DD-MM-YYYY HH24:MI'), null, null, null, null ))
new 1: SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT(:dbid, :inst_id, TO_DATE('25-08-2017 09:28', 'DD-MM-YYYY HH24:MI'), TO_DATE('25-08-2017 10:28', 'DD-MM-YYYY HH24:MI'), null, null, null, null ))
ASH Report For 

==========
define report_type = 'text'
define begin_time = '-5'
define duration = ''
define report_name = 'ashrpt.text'


set echo off heading on underline on;
column inst_num  heading "Inst Num"  new_value inst_num  format 99999
column inst_name heading "Instance"  new_value inst_name format a12
column db_name   heading "DB Name"   new_value db_name   format a12
column dbid      heading "DB Id"     new_value dbid      format 9999999999 just c


prompt
prompt Current Instance
prompt ~~~~~~~~~~~~~~~~


select d.dbid            dbid
     , d.name            db_name
     , i.instance_number inst_num
     , i.instance_name   inst_name
  from v$database d,  v$instance i
  ;

@?/rdbms/admin/ashrpt

undefine  report_type
undefine  begin_time 
undefine  duration 
undefine  report_name 

====================================================================================
VAR dbid NUMBER
VAR inst_id NUMBER

BEGIN
SELECT inst_id, dbid INTO :inst_id, :dbid FROM gv$database WHERE inst_id = SYS_CONTEXT('USERENV', 'INSTANCE');
END;
/

define bdate='28:01:2022 19:00'
define edate='28:01:2022 20:10'

SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT(:dbid, :inst_id, TO_DATE('&bdate', 'DD-MM-YYYY HH24:MI'), TO_DATE('&edate', 'DD-MM-YYYY HH24:MI'), null, null, null, null ));




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

--- rac

define slot_width = ''
define target_session_id = ''
define target_sql_id = ''
define target_wait_class = ''
define target_service_hash = ''
define target_module_name = ''
define target_action_name = ''
define target_client_id = ''
define target_plsql_entry = ''



define report_type = 'text'
define begin_time = '-5'
define duration = ''
define report_name = 'ashrpt.text'


set echo off heading on underline on;
column inst_num  heading "Inst Num"  new_value inst_num  format 99999
column inst_name heading "Instance"  new_value inst_name format a12
column db_name   heading "DB Name"   new_value db_name   format a12
column dbid      heading "DB Id"     new_value dbid      format 9999999999 just c


prompt
prompt Current Instance
prompt ~~~~~~~~~~~~~~~~


select d.dbid            dbid
     , d.name            db_name
     , i.instance_number inst_num
     , i.instance_name   inst_name
  from v$database d,  v$instance i
  ;

@?/rdbms/admin/ashrpti

undefine  report_type
undefine  begin_time 
undefine  duration 
undefine  report_name 


====

VAR dbid NUMBER
VAR inst_id NUMBER
BEGIN
SELECT inst_id, dbid INTO :inst_id, :dbid FROM gv$database WHERE inst_id = SYS_CONTEXT('USERENV', 'INSTANCE');
END;
/

define FROM_TIMESTAMP='2023-06-01 14:00:00'
define TO_TIMESTAMP  ='2023-06-02 14:00:00'
define sql_id='gqkr2um43ga39'
SELECT * from table(dbms_workload_repository.ash_global_report_text
                       (  l_dbid         => :dbid
                        , l_inst_num     => :inst_id
                        , l_btime        => TO_DATE('&&FROM_TIMESTAMP','YYYY-MM-DD HH24:MI:SS')
                        , l_etime        => TO_DATE('&&TO_TIMESTAMP','YYYY-MM-DD HH24:MI:SS')
                        , l_sid          => NULL
                        , l_sql_id       => '&sql_id'
                        , l_wait_class   => NULL
                        , l_service_hash => NULL
                        , l_module       => NULL
                        , l_action       => NULL
                        , l_client_id    => NULL
                       )
                   );








VAR dbid NUMBER
VAR inst_id NUMBER

BEGIN
SELECT inst_id, dbid INTO :inst_id, :dbid FROM gv$database WHERE inst_id = SYS_CONTEXT('USERENV', 'INSTANCE');
END;
/

define bdate='15:07:2023 10:00'
define edate='15:07:2023 11:10'

SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT(:dbid, :inst_id, TO_DATE('&bdate', 'DD-MM-YYYY HH24:MI'), TO_DATE('&edate', 'DD-MM-YYYY HH24:MI'), null, null, null, null ));







VAR dbid NUMBER
VAR inst_id NUMBER
BEGIN
SELECT inst_id, dbid INTO :inst_id, :dbid FROM gv$database WHERE inst_id = SYS_CONTEXT('USERENV', 'INSTANCE');
END;
/

define FROM_TIMESTAMP='2023-07-15 10:00:00'
define TO_TIMESTAMP  ='2023-07-15 11:00:00'
define sql_id='dnm7fqpf08p8w'
SELECT * from table(dbms_workload_repository.ash_report_text
                       (  l_dbid         => :dbid
                        , l_inst_num     => :inst_id
                        , l_btime        => TO_DATE('&&FROM_TIMESTAMP','YYYY-MM-DD HH24:MI:SS')
                        , l_etime        => TO_DATE('&&TO_TIMESTAMP','YYYY-MM-DD HH24:MI:SS')
                        , l_sid          => NULL
                        , l_sql_id       => '&sql_id'
                        , l_wait_class   => NULL
                        , l_service_hash => NULL
                        , l_module       => NULL
                        , l_action       => NULL
                        , l_client_id    => NULL
                       )
                   );





with out sql !!!

VAR dbid NUMBER
VAR inst_id NUMBER
BEGIN
SELECT inst_id, dbid INTO :inst_id, :dbid FROM gv$database WHERE inst_id = SYS_CONTEXT('USERENV', 'INSTANCE');
END;
/

define FROM_TIMESTAMP='2023-07-15 10:00:00'
define TO_TIMESTAMP  ='2023-07-15 11:00:00'
define sql_id='dnm7fqpf08p8w' ----<<<<<<
SELECT * from table(dbms_workload_repository.ash_report_text
                       (  l_dbid         => :dbid
                        , l_inst_num     => :inst_id
                        , l_btime        => TO_DATE('&&FROM_TIMESTAMP','YYYY-MM-DD HH24:MI:SS')
                        , l_etime        => TO_DATE('&&TO_TIMESTAMP','YYYY-MM-DD HH24:MI:SS')
                        , l_sid          => NULL
                        , l_sql_id       => NULL
                        , l_wait_class   => NULL
                        , l_service_hash => NULL
                        , l_module       => NULL
                        , l_action       => NULL
                        , l_client_id    => NULL
                       )
                   );


======







================
Awr report 
 set linesize 300 pagesize 300
 col command for a150
SELECT
    'spool awr_XYZ_inst_1_'
    || t.si
    || '_'
    || t.se
    || '.text '
    || CHR(10)
    || 'SELECT * FROM TABLE(dbms_workload_repository.awr_report_text('
    || t.dbid
    || ','
    || t.instance_number
    || ','
    || t.si
    || ','
    || t.se
    || '));'
    || CHR(10)
    || ' spool off;' command 
FROM
    (SELECT
            dbid,
            snap_id si,
            snap_id + 1 se,
            instance_number
     FROM   dba_hist_snapshot
     WHERE 1=1
    --        begin_interval_time >   TO_DATE('27102019 18:00:00', 'ddmmyyyy hh24:mi:ss')
   --   AND end_interval_time   <=  TO_DATE('27102019 19:30:00', 'ddmmyyyy hh24:mi:ss')
	  and begin_interval_time > sysdate -1
      AND  instance_number = 1
      ) t;

COMMAND
------------------------------------------------------------------------------------------------------------------------------------------------------
spool awr_XYZ_inst_1_33103_33104.text
SELECT * FROM TABLE(dbms_workload_repository.awr_report_text(1222414252,1,33103,33104));
 spool off;

spool awr_XYZ_inst_1_33104_33105.text
SELECT * FROM TABLE(dbms_workload_repository.awr_report_text(1222414252,1,33104,33105));
 spool off;

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



--- global Report
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 ;
SELECT output FROM TABLE (dbms_workload_repository.awr_global_report_text (:DID,'',:BgnSnap,:EndSnap,0 ) );


=====


define sql_id='8cnh50qfgwg73'

set linesize 300 pagesize 300
select * from table(dbms_workload_repository.awr_sql_report_text((select dbid from v$database), (select instance_number from v$instance), :BgnSnap,:EndSnap, '&sql_id'));



=====================
set linesize 300 pagesize 300
select * from table(dbms_workload_repository.awr_sql_report_text((select dbid from v$database), (select instance_number from gv$instance where instance_number=1), :BgnSnap,:EndSnap, '&sql_id'));


set linesize 300 pagesize 300
select * from table(dbms_workload_repository.awr_sql_report_text((select dbid from v$database), (select instance_number from gv$instance where instance_number=2), :BgnSnap,:EndSnap, '&sql_id'));


===

Global report !!!

set linesize 300

VARIABLE DID NUMBER
exec select DBID into :DID from v$database;

SELECT * FROM TABLE(dbms_workload_repository.ash_global_report_text(:DID, 1, SYSDATE-1/24, SYSDATE, l_wait_class=>'Other'));

SELECT * FROM TABLE(dbms_workload_repository.ash_global_report_text(:DID, 1, SYSDATE-1/24, SYSDATE));


SELECT * FROM TABLE(dbms_workload_repository.ash_global_report_text(:DID, '', SYSDATE-1/24, SYSDATE, l_wait_class=>'Other'));


set serveroutput on 
declare
  i_dbid number;
  i_inst_id NUMBER;
begin

  DBMS_OUTPUT.ENABLE (buffer_size => NULL); 

  select dbid into i_dbid from v$database;
  select instance_number into i_inst_id from v$instance;

  for cursor1 in 
  (
  select * from table( DBMS_WORKLOAD_REPOSITORY.ASH_GLOBAL_REPORT_text( l_dbid => i_dbid, l_inst_num => i_inst_id,   l_btime => SYSDATE-1/24,  l_etime =>  SYSDATE  ))
  )
    LOOP
    DBMS_OUTPUT.PUT_LINE( cursor1.output);
    END LOOP;


end;
/

====


DBMS_WORKLOAD_REPOSITORY.ASH_GLOBAL_REPORT_TEXT(
   l_dbid          IN VARCHAR2(1023),
   l_inst_num      IN NUMBER,
   l_btime         IN DATE,
   l_etime         IN DATE,
   l_options       IN NUMBER    DEFAULT 0,      -- Not currently used by Oracle
   l_slot_width    IN NUMBER    DEFAULT 0,      -- Not currently used by Oracle 
   l_sid           IN NUMBER    DEFAULT NULL,   -- v$session.sid
   l_sql_id        IN VARCHAR2  DEFAULT NULL,   -- V$SQL.SQL_ID                (Wildcard allowed)
   l_wait_class    IN VARCHAR2  DEFAULT NULL,   -- v$event_name.wait_class     (Wildcard allowed)
   l_service_hash  IN NUMBER    DEFAULT NULL,   -- v$active_services.name_hash
   l_module        IN VARCHAR2  DEFAULT NULL,   -- v$session.module            (Wildcard allowed)
   l_action        IN VARCHAR2  DEFAULT NULL,   -- v$session.action            (Wildcard allowed)
   l_client_id     IN VARCHAR2  DEFAULT NULL,   -- v$session.client_identifier (Wildcard allowed)
   l_plsql_entry   IN VARCHAR2  DEFAULT NULL,
   l_data_src      IN NUMBER    DEFAULT 0)
 RETURN awrrpt_text_type_table PIPELINED;



define FROM_TIMESTAMP='2020-04-28 15:00:00'
define TO_TIMESTAMP='2020-04-28 15:10:00'

SELECT * from table(dbms_workload_repository.ash_global_report_text
                       (  l_dbid         => (select dbid from v$database)
                        , l_inst_num     => DECODE(upper('&INSTANCE_LIST'),'%',NULL,'&INSTANCE_LIST')
                        , l_btime        => TO_DATE('&&FROM_TIMESTAMP','YYYY-MM-DD HH24:MI:SS')
                        , l_etime        => TO_DATE('&&TO_TIMESTAMP','YYYY-MM-DD HH24:MI:SS')
                        , l_sid          => NULL
                        , l_sql_id       => NULL
                        , l_wait_class   => NULL
                        , l_service_hash => NULL
                        , l_module       => NULL
                        , l_action       => NULL
                        , l_client_id    => NULL
                       )
                   );


/*
sysdate - interval '3' hour
sysdate - interval '5' MINUTE
*/


 SELECT * from table(dbms_workload_repository.ash_global_report_text
                       (  l_dbid         => (select dbid from v$database)
                        , l_inst_num     => ''
                        , l_btime        => (SYSDATE - interval '3' hour)
                        , l_etime        => (sysdate - interval '5' MINUTE)
                        , l_sid          => NULL
                        , l_sql_id       => NULL
                        , l_wait_class   => NULL
                        , l_service_hash => NULL
                        , l_module       => NULL
                        , l_action       => NULL
                        , l_client_id    => NULL
                       )
                   );

===
for sql !!


 SELECT * from table(dbms_workload_repository.ash_global_report_text
                       (  l_dbid         => (select dbid from v$database)
                        , l_inst_num     => ''
                        , l_btime        => (SYSDATE - interval '3' hour)
                        , l_etime        => (sysdate - interval '5' MINUTE)
                        , l_sid          => NULL
                        , l_sql_id       => '34cd4y8mbqvsk'
                        , l_wait_class   => NULL
                        , l_service_hash => NULL
                        , l_module       => NULL
                        , l_action       => NULL
                        , l_client_id    => NULL
                       )
                   );

====

Ash Top



set linesize 500 pagesize 300
COL "%This" FOR A7
--COL p1     FOR 99999999999999
--COL p2     FOR 99999999999999
--COL p3     FOR 99999999999999
COL p1text FOR A30 word_wrap
COL p2text FOR A30 word_wrap
COL p3text FOR A30 word_wrap
COL p1hex  FOR A17
COL p2hex  FOR A17
COL p3hex  FOR A17
COL AAS    FOR 9999.9
COL totalseconds HEAD "Total|Seconds" FOR 99999999
COL event  FOR A40 WORD_WRAP

SELECT * FROM (
    SELECT /*+ LEADING(a) USE_HASH(u) */
        COUNT(*)                                                     totalseconds
      , ROUND(COUNT(*) / ((CAST(sysdate AS DATE) - CAST(sysdate-1/24 AS DATE)) * 86400), 1) AAS
      , LPAD(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100)||'%',5,' ') "%This"
      , username,sql_id
    , SUM(CASE WHEN wait_class IS NULL           THEN 1 ELSE 0 END) "CPU"
    , SUM(CASE WHEN wait_class ='User I/O'       THEN 1 ELSE 0 END) "User I/O"
    , SUM(CASE WHEN wait_class ='Application'    THEN 1 ELSE 0 END) "Application"
    , SUM(CASE WHEN wait_class ='Concurrency'    THEN 1 ELSE 0 END) "Concurrency"
    , SUM(CASE WHEN wait_class ='Commit'         THEN 1 ELSE 0 END) "Commit"
    , SUM(CASE WHEN wait_class ='Configuration'  THEN 1 ELSE 0 END) "Configuration"
    , SUM(CASE WHEN wait_class ='Idle'           THEN 1 ELSE 0 END) "Idle"
    , SUM(CASE WHEN wait_class ='Network'        THEN 1 ELSE 0 END) "Network"
    , SUM(CASE WHEN wait_class ='System I/O'     THEN 1 ELSE 0 END) "System I/O"
    , SUM(CASE WHEN wait_class ='Scheduler'      THEN 1 ELSE 0 END) "Scheduler"
    , SUM(CASE WHEN wait_class ='Administrative' THEN 1 ELSE 0 END) "Administrative"
    , SUM(CASE WHEN wait_class ='Cluster'        THEN 1 ELSE 0 END) "Cluster"
    , SUM(CASE WHEN wait_class ='Queueing'       THEN 1 ELSE 0 END) "Queueing"
    , SUM(CASE WHEN wait_class ='Other'          THEN 1 ELSE 0 END) "Other"
--      , TO_CHAR(MIN(sample_time), 'YYYY-MM-DD HH24:MI:SS') first_seen
--      , TO_CHAR(MAX(sample_time), 'YYYY-MM-DD HH24:MI:SS') last_seen
--      , MAX(sql_exec_id) - MIN(sql_exec_id)
--      , COUNT(DISTINCT sql_exec_start||':'||sql_exec_id)
    FROM
        (SELECT
             a.*
           , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p1 ELSE null END, '0XXXXXXXXXXXXXXX') p1hex
           , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p2 ELSE null END, '0XXXXXXXXXXXXXXX') p2hex
           , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p3 ELSE null END, '0XXXXXXXXXXXXXXX') p3hex
        FROM gv$active_session_history a) a
      , dba_users u
    WHERE
        a.user_id = u.user_id (+)
    AND session_type='FOREGROUND'
    AND sample_time BETWEEN sysdate-1/24 AND sysdate
    GROUP BY  username,sql_id
    ORDER BY  TotalSeconds DESC   , username
)
WHERE  ROWNUM <= 20
/




set linesize 300 pagesize 300

COL "%This" FOR A7
--COL p1     FOR 99999999999999
--COL p2     FOR 99999999999999
--COL p3     FOR 99999999999999
COL p1text      FOR A30 word_wrap
COL p2text      FOR A30 word_wrap
COL p3text      FOR A30 word_wrap
COL p1hex       FOR A17
COL p2hex       FOR A17
COL p3hex       FOR A17
COL AAS         FOR 9999.9
COL totalseconds HEAD "Total|Seconds" FOR 99999999
COL dist_sqlexec_seen HEAD "Distinct|Execs Seen"
COL event       FOR A42 WORD_WRAP
COL event2      FOR A46 WORD_WRAP
COL time_model_name FOR A50 WORD_WRAP
COL program2    FOR A40 TRUNCATE
COL username    FOR A20 wrap
COL obj         FOR A30
COL objt        FOR A50
COL sql_opname  FOR A20
COL top_level_call_name FOR A30
COL wait_class  FOR A15

SELECT * FROM (
    WITH bclass AS (SELECT class, ROWNUM r from v$waitstat)
    SELECT /*+ LEADING(a) USE_HASH(u) */
        10 * COUNT(*)                                                      "TotalSeconds"
      , ROUND(10 * COUNT(*) / ((CAST(sysdate AS DATE) - CAST(sysdate-2/24 AS DATE)) * 86400), 1) AAS
      , LPAD(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100)||'%',5,' ') "%This"
      , username,sql_id
--      , 10 * SUM(CASE WHEN wait_class IS NULL           THEN 1 ELSE 0 END) "CPU"
--      , 10 * SUM(CASE WHEN wait_class ='User I/O'       THEN 1 ELSE 0 END) "User I/O"
--      , 10 * SUM(CASE WHEN wait_class ='Application'    THEN 1 ELSE 0 END) "Application"
--      , 10 * SUM(CASE WHEN wait_class ='Concurrency'    THEN 1 ELSE 0 END) "Concurrency"
--      , 10 * SUM(CASE WHEN wait_class ='Commit'         THEN 1 ELSE 0 END) "Commit"
--      , 10 * SUM(CASE WHEN wait_class ='Configuration'  THEN 1 ELSE 0 END) "Configuration"
--      , 10 * SUM(CASE WHEN wait_class ='Cluster'        THEN 1 ELSE 0 END) "Cluster"
--      , 10 * SUM(CASE WHEN wait_class ='Idle'           THEN 1 ELSE 0 END) "Idle"
--      , 10 * SUM(CASE WHEN wait_class ='Network'        THEN 1 ELSE 0 END) "Network"
--      , 10 * SUM(CASE WHEN wait_class ='System I/O'     THEN 1 ELSE 0 END) "System I/O"
--      , 10 * SUM(CASE WHEN wait_class ='Scheduler'      THEN 1 ELSE 0 END) "Scheduler"
--      , 10 * SUM(CASE WHEN wait_class ='Administrative' THEN 1 ELSE 0 END) "Administrative"
--      , 10 * SUM(CASE WHEN wait_class ='Queueing'       THEN 1 ELSE 0 END) "Queueing"
--      , 10 * SUM(CASE WHEN wait_class ='Other'          THEN 1 ELSE 0 END) "Other"
      , TO_CHAR(MIN(sample_time), 'YYYY-MM-DD HH24:MI:SS') first_seen
      , TO_CHAR(MAX(sample_time), 'YYYY-MM-DD HH24:MI:SS') last_seen
    FROM
        (SELECT
             a.*
           , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p1 ELSE null END, '0XXXXXXXXXXXXXXX') p1hex
           , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p2 ELSE null END, '0XXXXXXXXXXXXXXX') p2hex
           , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p3 ELSE null END, '0XXXXXXXXXXXXXXX') p3hex
           , NVL(event, session_state)||
                CASE 
                    WHEN event like 'enq%' AND session_state = 'WAITING'
                    THEN ' [mode='||BITAND(p1, POWER(2,14)-1)||']'
                    WHEN a.event IN (SELECT name FROM v$event_name WHERE parameter3 = 'class#') 
                    THEN ' ['||CASE WHEN a.p3 <= (SELECT MAX(r) FROM bclass) 
                               THEN (SELECT class FROM bclass WHERE r = a.p3)
                               ELSE (SELECT DECODE(MOD(BITAND(a.p3,TO_NUMBER('FFFF','XXXX')) - 17,2),0,'undo header',1,'undo data', 'error') FROM dual)
                               END  ||']' 
                    ELSE null 
                END event2 -- event is NULL in ASH if the session is not waiting (session_state = ON CPU)
           , CASE WHEN a.session_type = 'BACKGROUND' OR REGEXP_LIKE(a.program, '.*\([PJ]\d+\)') THEN
                REGEXP_REPLACE(SUBSTR(a.program,INSTR(a.program,'(')), '\d', 'n')
             ELSE
                '('||REGEXP_REPLACE(REGEXP_REPLACE(a.program, '(.*)@(.*)(\(.*\))', '\1'), '\d', 'n')||')'
             END || ' ' program2
           , CASE WHEN BITAND(time_model, POWER(2, 01)) = POWER(2, 01) THEN 'DBTIME '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 02)) = POWER(2, 02) THEN 'BACKGROUND '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 03)) = POWER(2, 03) THEN 'CONNECTION_MGMT '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 04)) = POWER(2, 04) THEN 'PARSE '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 05)) = POWER(2, 05) THEN 'FAILED_PARSE '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 06)) = POWER(2, 06) THEN 'NOMEM_PARSE '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 07)) = POWER(2, 07) THEN 'HARD_PARSE '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 08)) = POWER(2, 08) THEN 'NO_SHARERS_PARSE '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 09)) = POWER(2, 09) THEN 'BIND_MISMATCH_PARSE '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 10)) = POWER(2, 10) THEN 'SQL_EXECUTION '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 11)) = POWER(2, 11) THEN 'PLSQL_EXECUTION '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 12)) = POWER(2, 12) THEN 'PLSQL_RPC '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 13)) = POWER(2, 13) THEN 'PLSQL_COMPILATION '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 14)) = POWER(2, 14) THEN 'JAVA_EXECUTION '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 15)) = POWER(2, 15) THEN 'BIND '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 16)) = POWER(2, 16) THEN 'CURSOR_CLOSE '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 17)) = POWER(2, 17) THEN 'SEQUENCE_LOAD '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 18)) = POWER(2, 18) THEN 'INMEMORY_QUERY '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 19)) = POWER(2, 19) THEN 'INMEMORY_POPULATE '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 20)) = POWER(2, 20) THEN 'INMEMORY_PREPOPULATE '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 21)) = POWER(2, 21) THEN 'INMEMORY_REPOPULATE '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 22)) = POWER(2, 22) THEN 'INMEMORY_TREPOPULATE '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 23)) = POWER(2, 23) THEN 'TABLESPACE_ENCRYPTION ' END time_model_name
        FROM dba_hist_active_sess_history a) a
      , dba_users u
      , (SELECT
             object_id,data_object_id,owner,object_name,subobject_name,object_type
           , owner||'.'||object_name obj
           , owner||'.'||object_name||' ['||object_type||']' objt
         FROM dba_objects) o
    WHERE
        a.user_id = u.user_id (+)
    AND a.current_obj# = o.object_id(+)
    AND session_type='FOREGROUND'
    AND a.sample_time BETWEEN sysdate-2/24 AND sysdate
    AND a.dbid = (SELECT d.dbid FROM v$database d) -- for partition pruning
    --AND a.snap_id IN (SELECT sn.snap_id FROM dba_hist_snapshot sn WHERE sn.begin_interval_time >= sysdate-2/24 AND sn.end_interval_time <= sysdate) -- for partition pruning
    AND snap_id IN (SELECT snap_id FROM dba_hist_snapshot WHERE sample_time BETWEEN sysdate-2/24 AND sysdate) -- for2partition pruning
    AND (a.dbid, a.snap_id) IN (SELECT d.dbid, sn.snap_id FROM v$database d, dba_hist_snapshot sn 
WHERE d.dbid = sn.dbid AND sn.begin_interval_time >= sysdate-2/24 AND sn.end_interval_time <= sysdate) -- for patition pruning
    GROUP BY
        username,sql_id
    ORDER BY
        "TotalSeconds" DESC
       , username,sql_id
)
WHERE
    ROWNUM <= 20
/





with time !!!!

define FROM_TIMESTAMP='2022-04-28 15:00:00'
define TO_TIMESTAMP='2022-04-28 15:10:00'

set linesize 300 pagesize 300

COL "%This" FOR A7
--COL p1     FOR 99999999999999
--COL p2     FOR 99999999999999
--COL p3     FOR 99999999999999
COL p1text      FOR A30 word_wrap
COL p2text      FOR A30 word_wrap
COL p3text      FOR A30 word_wrap
COL p1hex       FOR A17
COL p2hex       FOR A17
COL p3hex       FOR A17
COL AAS         FOR 9999.9
COL totalseconds HEAD "Total|Seconds" FOR 99999999
COL dist_sqlexec_seen HEAD "Distinct|Execs Seen"
COL event       FOR A42 WORD_WRAP
COL event2      FOR A46 WORD_WRAP
COL time_model_name FOR A50 WORD_WRAP
COL program2    FOR A40 TRUNCATE
COL username    FOR A20 wrap
COL obj         FOR A30
COL objt        FOR A50
COL sql_opname  FOR A20
COL top_level_call_name FOR A30
COL wait_class  FOR A15

SELECT * FROM (
    WITH bclass AS (SELECT class, ROWNUM r from v$waitstat)
    SELECT /*+ LEADING(a) USE_HASH(u) */
        10 * COUNT(*)                                                      "TotalSeconds"
      , ROUND(10 * COUNT(*) / ((CAST(sysdate AS DATE) - CAST(sysdate-2/24 AS DATE)) * 86400), 1) AAS
      , LPAD(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100)||'%',5,' ') "%This"
      , username,sql_id
--      , 10 * SUM(CASE WHEN wait_class IS NULL           THEN 1 ELSE 0 END) "CPU"
--      , 10 * SUM(CASE WHEN wait_class ='User I/O'       THEN 1 ELSE 0 END) "User I/O"
--      , 10 * SUM(CASE WHEN wait_class ='Application'    THEN 1 ELSE 0 END) "Application"
--      , 10 * SUM(CASE WHEN wait_class ='Concurrency'    THEN 1 ELSE 0 END) "Concurrency"
--      , 10 * SUM(CASE WHEN wait_class ='Commit'         THEN 1 ELSE 0 END) "Commit"
--      , 10 * SUM(CASE WHEN wait_class ='Configuration'  THEN 1 ELSE 0 END) "Configuration"
--      , 10 * SUM(CASE WHEN wait_class ='Cluster'        THEN 1 ELSE 0 END) "Cluster"
--      , 10 * SUM(CASE WHEN wait_class ='Idle'           THEN 1 ELSE 0 END) "Idle"
--      , 10 * SUM(CASE WHEN wait_class ='Network'        THEN 1 ELSE 0 END) "Network"
--      , 10 * SUM(CASE WHEN wait_class ='System I/O'     THEN 1 ELSE 0 END) "System I/O"
--      , 10 * SUM(CASE WHEN wait_class ='Scheduler'      THEN 1 ELSE 0 END) "Scheduler"
--      , 10 * SUM(CASE WHEN wait_class ='Administrative' THEN 1 ELSE 0 END) "Administrative"
--      , 10 * SUM(CASE WHEN wait_class ='Queueing'       THEN 1 ELSE 0 END) "Queueing"
--      , 10 * SUM(CASE WHEN wait_class ='Other'          THEN 1 ELSE 0 END) "Other"
      , TO_CHAR(MIN(sample_time), 'YYYY-MM-DD HH24:MI:SS') first_seen
      , TO_CHAR(MAX(sample_time), 'YYYY-MM-DD HH24:MI:SS') last_seen
    FROM
        (SELECT
             a.*
           , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p1 ELSE null END, '0XXXXXXXXXXXXXXX') p1hex
           , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p2 ELSE null END, '0XXXXXXXXXXXXXXX') p2hex
           , TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p3 ELSE null END, '0XXXXXXXXXXXXXXX') p3hex
           , NVL(event, session_state)||
                CASE 
                    WHEN event like 'enq%' AND session_state = 'WAITING'
                    THEN ' [mode='||BITAND(p1, POWER(2,14)-1)||']'
                    WHEN a.event IN (SELECT name FROM v$event_name WHERE parameter3 = 'class#') 
                    THEN ' ['||CASE WHEN a.p3 <= (SELECT MAX(r) FROM bclass) 
                               THEN (SELECT class FROM bclass WHERE r = a.p3)
                               ELSE (SELECT DECODE(MOD(BITAND(a.p3,TO_NUMBER('FFFF','XXXX')) - 17,2),0,'undo header',1,'undo data', 'error') FROM dual)
                               END  ||']' 
                    ELSE null 
                END event2 -- event is NULL in ASH if the session is not waiting (session_state = ON CPU)
           , CASE WHEN a.session_type = 'BACKGROUND' OR REGEXP_LIKE(a.program, '.*\([PJ]\d+\)') THEN
                REGEXP_REPLACE(SUBSTR(a.program,INSTR(a.program,'(')), '\d', 'n')
             ELSE
                '('||REGEXP_REPLACE(REGEXP_REPLACE(a.program, '(.*)@(.*)(\(.*\))', '\1'), '\d', 'n')||')'
             END || ' ' program2
           , CASE WHEN BITAND(time_model, POWER(2, 01)) = POWER(2, 01) THEN 'DBTIME '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 02)) = POWER(2, 02) THEN 'BACKGROUND '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 03)) = POWER(2, 03) THEN 'CONNECTION_MGMT '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 04)) = POWER(2, 04) THEN 'PARSE '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 05)) = POWER(2, 05) THEN 'FAILED_PARSE '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 06)) = POWER(2, 06) THEN 'NOMEM_PARSE '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 07)) = POWER(2, 07) THEN 'HARD_PARSE '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 08)) = POWER(2, 08) THEN 'NO_SHARERS_PARSE '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 09)) = POWER(2, 09) THEN 'BIND_MISMATCH_PARSE '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 10)) = POWER(2, 10) THEN 'SQL_EXECUTION '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 11)) = POWER(2, 11) THEN 'PLSQL_EXECUTION '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 12)) = POWER(2, 12) THEN 'PLSQL_RPC '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 13)) = POWER(2, 13) THEN 'PLSQL_COMPILATION '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 14)) = POWER(2, 14) THEN 'JAVA_EXECUTION '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 15)) = POWER(2, 15) THEN 'BIND '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 16)) = POWER(2, 16) THEN 'CURSOR_CLOSE '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 17)) = POWER(2, 17) THEN 'SEQUENCE_LOAD '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 18)) = POWER(2, 18) THEN 'INMEMORY_QUERY '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 19)) = POWER(2, 19) THEN 'INMEMORY_POPULATE '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 20)) = POWER(2, 20) THEN 'INMEMORY_PREPOPULATE '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 21)) = POWER(2, 21) THEN 'INMEMORY_REPOPULATE '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 22)) = POWER(2, 22) THEN 'INMEMORY_TREPOPULATE '  END
           ||CASE WHEN BITAND(time_model, POWER(2, 23)) = POWER(2, 23) THEN 'TABLESPACE_ENCRYPTION ' END time_model_name
        FROM dba_hist_active_sess_history a) a
      , dba_users u
      , (SELECT
             object_id,data_object_id,owner,object_name,subobject_name,object_type
           , owner||'.'||object_name obj
           , owner||'.'||object_name||' ['||object_type||']' objt
         FROM dba_objects) o
    WHERE
        a.user_id = u.user_id (+)
    AND a.current_obj# = o.object_id(+)
    AND session_type='FOREGROUND'
    AND a.sample_time BETWEEN sysdate-2/24 AND sysdate
    AND a.dbid = (SELECT d.dbid FROM v$database d) -- for partition pruning
    --AND a.snap_id IN (SELECT sn.snap_id FROM dba_hist_snapshot sn WHERE sn.begin_interval_time >= sysdate-2/24 AND sn.end_interval_time <= sysdate) -- for partition pruning
    AND snap_id IN (SELECT snap_id FROM dba_hist_snapshot WHERE sample_time BETWEEN sysdate-2/24 AND sysdate) -- for2partition pruning
    AND (a.dbid, a.snap_id) IN (SELECT d.dbid, sn.snap_id FROM v$database d, dba_hist_snapshot sn 
WHERE d.dbid = sn.dbid 
    -- AND sn.begin_interval_time >= sysdate-2/24 AND sn.end_interval_time <= sysdate) -- for patition pruning
   -- AND sn.begin_interval_time >=TO_DATE('&&FROM_TIMESTAMP','YYYY-MM-DD HH24:MI:SS')  AND sn.end_interval_time <=TO_DATE('&&TO_TIMESTAMP','YYYY-MM-DD HH24:MI:SS')
)
    GROUP BY
        username,sql_id
    ORDER BY
        "TotalSeconds" DESC
       , username,sql_id
)
WHERE
    ROWNUM <= 20
/




prompt ************************************
prompt **** ASH OVERALL WAIT PROFILE
prompt ************************************
prompt RR columns are ratio to report




define how_many_min_back='10'  -----------!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!Min

alter session set nls_date_format='dd-mm-yyyy hh24:mi'; 


col MIN_ASH_AVAILABLE   for a28                                                        
col MAX_ASH_AVAILABLE    for a28    
SELECT MIN(sample_time) min_ash_available,max(sample_time) max_ash_available,sysdate-MIN(sample_time) available_duration FROM v$active_session_history;


set linesize 300 pagesize 300
col EVENT for a40
select * from (
select NVL(event,'CPU') event,count(*),round((ratio_to_report(sum(1)) over ()*100),1) rr from gv$active_session_history
WHERE user_id<>0
AND sample_time<trunc(SYSDATE+1) AND sample_time>trunc(sysdate-1)
group by event
order by 2 desc
) where rownum<10;



 
prompt ************************************
prompt **** ASH I/O by SQL_ID
prompt ************************************
prompt RR columns are ratio to report
 
set linesize 300 pagesize 300
col EVENT for a30 
COLUMN force_matching_signature FOR 999999999999999999999999999
select * from (
select
sql_id ,sql_plan_hash_value,force_matching_signature,NVL(event,'CPU') Event,count(*),round((ratio_to_report(sum(1)) over ()*100),1) rr from gv$active_session_history
where 1=1
AND wait_class LIKE '%I/O'
--AND event IS null
and user_id<>0
AND sql_id IS NOT NULL
group by sql_id,sql_plan_hash_value,event,force_matching_signature
order by 5 desc
) where rownum<30;



prompt ************************************
prompt **** ASH OVERALL WAIT PROFILE
prompt ************************************
prompt RR columns are ratio to report



SELECT MIN(sample_time) min_ash_available,sysdate-MIN(sample_time) available_duration FROM v$active_session_history;


select * from (
select NVL(event,'CPU') event,count(*),round((ratio_to_report(sum(1)) over ()*100),1) rr from gv$active_session_history
WHERE user_id<>0
AND SAMPLE_TIME > sysdate - ('&how_many_min_back'/(24*60))
group by event
order by 2 desc
) where rownum<10;
 


prompt ************************************
prompt **** ASH I/O by SQL_ID, Top 10
prompt ************************************
prompt RR columns are ratio to report
 
COLUMN force_matching_signature FOR 999999999999999999999999999
select * from (
select
sql_id ,sql_plan_hash_value,force_matching_signature,NVL(event,'CPU') Event,count(*),round((ratio_to_report(sum(1)) over ()*100),1) rr from gv$active_session_history
where 1=1
AND SAMPLE_TIME > sysdate - ('&how_many_min_back'/(24*60))
AND wait_class LIKE '%I/O'
--AND event IS null
and user_id<>0
AND sql_id IS NOT NULL
group by sql_id,sql_plan_hash_value,event,force_matching_signature
order by 5 desc
) where rownum<11;




prompt ************************************
prompt **** ASH DB_TIME by SQL_ID, Top 10
prompt ************************************

select * from (
select sql_id
, count(*) DBTime
, round(count(*)*100/sum(count(*))
over (), 2) pctload
from gv$active_session_history
where sample_time > sysdate - ('&how_many_min_back'/(24*60))
and session_type <> 'BACKGROUND'
group by sql_id
order by count(*) desc
) where rownum < 11;



====
SQL REPORT ...
@?/rdbms/admin/awrsqrpt.sql



====


if running slow !!!!!



alter session set "_push_join_predicate" = FALSE ;
SELECT * from table(dbms_workload_repository.ash_global_report_text
                       (  l_dbid         => (select dbid from v$database)
                        , l_inst_num     => ''
                        , l_btime        => (SYSDATE - interval '1' hour)
                        , l_etime        => (sysdate - interval '5' MINUTE)
                        , l_sid          => NULL
                        , l_sql_id       => NULL
                        , l_wait_class   => NULL
                        , l_service_hash => NULL
                        , l_module       => NULL
                        , l_action       => NULL
                        , l_client_id    => NULL
                       )
                   );




define sql_id='36psp2j8kf741'
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;

set linesize 300 pagesize 300
select * from table(dbms_workload_repository.awr_sql_report_text((SELECT DBID FROM V$DATABASE), (SELECT INSTANCE_NUMBER FROM V$INSTANCE), :BgnSnap,:EndSnap, '&sql_id'));


alter session set "_push_join_predicate" = FALSE ;

define sql_id='ga5m3ggy97233'
SELECT * from table(dbms_workload_repository.ash_global_report_text
                       (  l_dbid         => (select dbid from v$database)
                        , l_inst_num     => ''
                        , l_btime        => (SYSDATE - interval '1' hour)
                        , l_etime        => (sysdate - interval '5' MINUTE)
                        , l_sid          => NULL
                        , l_sql_id       => '&sql_id'  
                        , l_wait_class   => NULL
                        , l_service_hash => NULL
                        , l_module       => NULL
                        , l_action       => NULL
                        , l_client_id    => NULL
                       )
                   );



alter session set "_push_join_predicate" = FALSE ;

set serveroutput on linesize 400
declare
  i_dbid number;
  i_inst_id NUMBER;
begin

  DBMS_OUTPUT.ENABLE (buffer_size => NULL); 

  select dbid into i_dbid from v$database;
  select instance_number into i_inst_id from v$instance;

  for cursor1 in 
  (
  select * from table( DBMS_WORKLOAD_REPOSITORY.ASH_GLOBAL_REPORT_text( l_dbid => i_dbid, l_inst_num => i_inst_id,   l_btime => SYSDATE - 10/24/60,  l_etime =>  SYSDATE  ))
  )
    LOOP
    DBMS_OUTPUT.PUT_LINE( cursor1.output);
    END LOOP;


end;
/





SET LINESIZE 200
SET PAGESIZE 0
SET TRIMSPOOL ON
SET LONG 1000000

col OUTPUT for a150
SELECT * 
FROM TABLE(
  DBMS_WORKLOAD_REPOSITORY.ASH_GLOBAL_REPORT_TEXT(
    l_dbid     => (SELECT dbid FROM v$database),
    l_inst_num => (SELECT instance_number FROM v$instance),
    l_btime    => SYSDATE - INTERVAL '5' MINUTE,
    l_etime    => SYSDATE
  )
);












alter session set nls_date_format = 'dd-mm-yyyy hh24:mi:ss';

DEF   3="TIMESTAMP'2026-02-09 05:00:00'"
DEF   4="TIMESTAMP'2026-02-09 10:00:00'"

define d_date_format='dd-mm-yy hh:mi';


set linesize 300 trimspool on pagesize 60

col event format a40
col p1text format a20
col p1 format a25
col p2text format a20
col begin_interval format a19
col instance_number format 9999 head 'INST|NUM'
col sql_id format a13
col event_rank format 99999 head 'EVENT|RANK'

--spool awr-top-sqlid-event.log

break on begin_interval skip 1

with snaps as (
	select 
		min(snap_id) min_snap_id
		, max(snap_id) max_snap_id
	from dba_hist_snapshot
	where 1=1
       -- begin_interval_time >= to_date(:v_begin_date,'d_date_format')
	--and end_interval_time <= to_date(:v_end_date,'d_date_format')
         AND begin_interval_time BETWEEN &3 AND &4
),
data as (
	select 
		h.snap_id
		, h.dbid
		, h.instance_number
		, case h.session_state
			when 'ON CPU' then 'CPU'
			else h.event
		end event
		, h.p1text
		, CASE
			WHEN event like 'enq%' THEN
				'0x'||trim(to_char(h.p1, 'XXXXXXXXXXXXXXXX'))||': '||
				chr(bitand(h.p1, 4278190080)/16777216)||
				chr(bitand(h.p1,16711680)/65536)||
				' mode '||bitand(h.p1, power(2,14)-1)
			ELSE NULL 
		END AS p1
		, h.sql_id	
	from dba_hist_active_sess_history h
	where h.sql_id is not null
),
agg_data as (
select  distinct
	to_char(hs.begin_interval_time,'&d_date_format') begin_interval
	, d.instance_number
	, d.sql_id
	, d.event
	, d.p1text
	, d.p1
	, count(d.event) over (partition by d.snap_id, d.sql_id, d.event, d.instance_number) event_count
from data d
join dba_hist_snapshot hs on hs.snap_id = d.snap_id
	and hs.instance_number = d.instance_number
	and hs.dbid = d.dbid
	and hs.snap_id between (select min_snap_id from snaps)
		and (select max_snap_id from snaps)
--where rownum <= 200
order by begin_interval, event_count desc, instance_number
), 
rank_output as (
	select
		begin_interval
		, instance_number
		, sql_id
		, event
		, p1text
		, p1
		, event_count
		, row_number() over (partition by begin_interval, instance_number order by event_count desc)	 event_rank
	from agg_data
)
select 
		begin_interval
		, instance_number
		, sql_id
		, event
		, p1text
		, p1
		, event_count
		, event_rank
from rank_output
where event_rank <=10
order by begin_interval, event_count desc
/










set linesize 300 pagesize 300

DEF   3="TIMESTAMP'2026-02-09 05:00:00'"
DEF   4="TIMESTAMP'2026-02-09 10:00:00'"

define d_date_format='dd-mm-yy hh:mi';


-- awr-top-events.sql
-- get top 5 events per AWR snapshot, per instance


break on begin_interval skip 1

with snaps as (
	select 
		min(snap_id) min_snap_id
		, max(snap_id) max_snap_id
	from dba_hist_snapshot
	where 1=1
--	begin_interval_time >= to_date(:v_begin_date,'d_date_format')
	-- and end_interval_time <= to_date(:v_end_date,'d_date_format')
	 AND begin_interval_time BETWEEN &3 AND &4
	),
data as (
	select 
		h.snap_id
		, h.dbid
		, h.instance_number
		, case h.session_state
			when 'ON CPU' then 'CPU'
			else h.event
		end event
	from dba_hist_active_sess_history h
	where h.event != 'ges generic event' -- see MOS 2638401.1
),
agg_data as (
select  distinct
	to_char(hs.begin_interval_time,'&d_date_format') begin_interval
	, d.instance_number
	, d.event
	, count(d.event) over (partition by d.snap_id, d.event, d.instance_number) event_count
from data d
join dba_hist_snapshot hs on hs.snap_id = d.snap_id
	and hs.instance_number = d.instance_number
	and hs.dbid = d.dbid
	and hs.snap_id between (select min_snap_id from snaps)
		and (select max_snap_id from snaps)
--where rownum <= 200
order by begin_interval, event_count desc, instance_number
), 
rank_output as (
	select
		begin_interval
		, instance_number
		, event
		, event_count
		, row_number() over (partition by begin_interval, instance_number order by event_count desc)	 event_rank
	from agg_data
)
select 
		begin_interval
		, instance_number
		, event
		, event_count
from rank_output
where event_rank <= 5
order by begin_interval, event_count desc
/







set linesize 300 pagesize 300

DEF   3="TIMESTAMP'2026-02-09 05:00:00'"
DEF   4="TIMESTAMP'2026-02-09 10:00:00'"

define d_date_format='dd-mm-yy hh:mi';


-- awr-top-events.sql
-- get top 5 events per AWR snapshot, per instance


break on begin_interval skip 1

with snaps as (
	select 
		min(snap_id) min_snap_id
		, max(snap_id) max_snap_id
	from dba_hist_snapshot
	where 1=1
--	begin_interval_time >= to_date(:v_begin_date,'d_date_format')
	-- and end_interval_time <= to_date(:v_end_date,'d_date_format')
	 AND begin_interval_time BETWEEN &3 AND &4
	),
data as (
	select 
		h.snap_id
		, h.dbid
		, h.instance_number
		, case h.session_state
			when 'ON CPU' then 'CPU'
			else h.event
		end event
	from dba_hist_active_sess_history h
	where h.event != 'ges generic event' -- see MOS 2638401.1
),
agg_data as (
select  distinct
	to_char(hs.begin_interval_time,'&d_date_format') begin_interval
	, d.instance_number
	, d.event
	, count(d.event) over (partition by d.snap_id, d.event, d.instance_number) event_count
from data d
join dba_hist_snapshot hs on hs.snap_id = d.snap_id
	and hs.instance_number = d.instance_number
	and hs.dbid = d.dbid
	and hs.snap_id between (select min_snap_id from snaps)
		and (select max_snap_id from snaps)
--where rownum <= 200
order by begin_interval, event_count desc, instance_number
), 
rank_output as (
	select
		begin_interval
		, instance_number
		, event
		, event_count
		, row_number() over (partition by begin_interval, instance_number order by event_count desc)	 event_rank
	from agg_data
)
select 
		begin_interval
		, instance_number
		, event
		, event_count
from rank_output
where event_rank <= 5
order by begin_interval, event_count desc
/








Posted by at 6 comments:
Subscribe to: Posts (Atom)

Oracle DBA

My photo
Essex, United Kingdom

anuj blog Archive