Anuj Singh Oracle DBA: 12 Sept 2021

Sunday, 12 September 2021

ashtop.sql from command line

from https://github.com/tanelpoder/tpt-oracle/blob/master/ash/ashtopsum.sql

set linesize 700 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
/

   Total
  Seconds     AAS %This   USERNAME             SQL_ID        FIRST_SEEN          LAST_SEEN
--------- ------- ------- -------------------- ------------- ------------------- -------------------
       20      .0   22%   SYS                  9w52tbp4nqgtt 2022-11-18 02:33:56 2022-11-18 03:13:56
       10      .0   11%   SYS                  1xj8zsb8qxa1n 2022-11-18 03:03:56 2022-11-18 03:03:56
       10      .0   11%   SYS                  34cd4y8mbqvsk 2022-11-18 03:03:56 2022-11-18 03:03:56
       10      .0   11%   SYS                  5j3ram21g5s8r 2022-11-18 03:13:56 2022-11-18 03:13:56
       10      .0   11%   SYS                  8xh3c4x40pnrz 2022-11-18 03:11:46 2022-11-18 03:11:46
       10      .0   11%   SYS                  afwv4sqa4vyt1 2022-11-18 02:53:56 2022-11-18 02:53:56
       10      .0   11%   SYS                  b6usrg82hwsa3 2022-11-18 02:23:56 2022-11-18 02:23:56
       10      .0   11%   SYS                  cf1ur00hw58mh 2022-11-18 02:33:56 2022-11-18 02:33:56

8 rows selected.

=====

--- change the date based on your requirement 

set pagesize 300 linesize 500
DEFINE 1="username,sql_id"
DEFINE 2="session_type='FOREGROUND'"

VARIABLE bdate VARCHAR2(35)
VARIABLE edate VARCHAR2(35)
exec SELECT CAST('25-MAR-2023 07:25' AS DATE) into :Bdate FROM dual;
exec SELECT CAST('25-MAR-2023 08:00' AS DATE) into :edate FROM dual;


-- @ashtop username,sql_id session_type='FOREGROUND' sysdate-1/24 sysdate
--
-- Other:
--     This script uses only the in-memory V$ACTIVE_SESSION_HISTORY, use
--     @dashtop.sql for accessiong the DBA_HIST_ACTIVE_SESS_HISTORY archive
--              
--------------------------------------------------------------------------------
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
--set linesize 200
col USERNAME for a20
col min_sample_time for a27
col max_sample_time for a27

SELECT * FROM (
    SELECT /*+ LEADING(a) USE_HASH(u) */
        COUNT(*)                                                     totalseconds
        , abs(ROUND(COUNT(*) / ((CAST(:Bdate AS DATE) - CAST(:edate AS DATE)) * 86400), 1)) AAS
        , LPAD(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100)||'%',5,' ')||' |' "%This"
        , &1
        , 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 ='Cluster'        THEN 1 ELSE 0 END) "Cluster"
        , 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 ='Queueing'       THEN 1 ELSE 0 END) "Queueing"
        , SUM(CASE WHEN wait_class ='Other'          THEN 1 ELSE 0 END) "Other"
        , MIN(sample_time) min_sample_time
        , MAX(sample_time) max_sample_time
    --    , MAX(sql_exec_id) - MIN(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
		from DBA_HIST_ACTIVE_SESS_HISTORY a) a
     -- , dba_users u
	  , cdb_users u
    WHERE
        a.user_id = u.user_id (+)
    AND &2
  AND sample_time BETWEEN CAST(:Bdate AS DATE) AND CAST(:edate AS DATE)  --ww 
    GROUP BY
        &1
    ORDER BY
        TotalSeconds DESC
       , &1
)
WHERE
    ROWNUM <= 20
/



    Total
  Seconds     AAS %This   USERNAME             SQL_ID               CPU   User I/O Application Concurrency     Commit Configuration    Cluster            Idle    Network System I/O  Scheduler Administrative   Queueing      Other MIN_SAMPLE_TIME             MAX_SAMPLE_TIME
--------- ------- ------- -------------------- ------------- ---------- ---------- ----------- ----------- ---------- ------------- ---------- ---------- ---------- ---------- ---------- -------------- ---------- ---------- --------------------------- ---------------------------
     6128     2.9   52% | SYS                                        10          0           0           0       5852             0          0               0          0          0          0              0          0        266 25-MAR-23 07.25.09.649 AM   25-MAR-23 07.35.00.991 AM

=====

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 ;




--@dashtop username,sql_id session_type='FOREGROUND' sysdate-1/24 sysdate

set linesize 500
set pages 9999
COL "%This" FOR A6
COL "UserI/O"   FOR 999999
COL "SystemI/O" FOR 999999
col USERNAME for a20
DEFINE 1="username,sql_id"
DEFINE 2="session_type='FOREGROUND'"
SELECT * FROM (
    SELECT /*+ LEADING(a) USE_HASH(u) */
        LPAD(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100)||'%',5,' ') "%This"
      , &1
      , 10 * COUNT(*)                                                      "TotalSeconds"
      , 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) "UserI/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) "SystemI/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"
      , MIN(sample_time)
      , MAX(sample_time)
    FROM
        dba_hist_active_sess_history a
      , dba_users u
    WHERE
        a.user_id = u.user_id (+)
    AND &2
    AND snap_id BETWEEN :BgnSnap AND :EndSnap
    AND snap_id IN (SELECT snap_id FROM dba_hist_snapshot WHERE snap_id BETWEEN :BgnSnap AND :EndSnap) -- for partition pruning
    GROUP BY
        &1
    ORDER BY
        "TotalSeconds" DESC
       , &1
)
WHERE
    ROWNUM <= 20
/


%This  USERNAME             SQL_ID        TotalSeconds        CPU UserI/O Application Concurrency     Commit Configuration    Cluster       Idle    Network SystemI/O  Scheduler Administrative   Queueing  Other MIN(SAMPLE_TIME)                                                            MAX(SAMPLE_TIME)
------ -------------------- ------------- ------------ ---------- ------- ----------- ----------- ---------- ------------- ---------- ---------- ---------- --------- ---------- -------------- ---------- ---------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
  43%                                             9260        200      10           0           0          0            10          0          0       9040     0          0              0          0          0 24-APR-23 03.00.23.959 AM                                                   24-APR-23 05.00.35.449 AM
  14%                       7grvag6ayaxn1         3010       3010       0           0           0          0             0          0          0     0          0          0              0          0          0 24-APR-23 03.00.33.975 AM                                                   24-APR-23 05.00.15.417 AM
  11%                       9px0f4mv0tn0t         2340       2190     150           0           0          0             0          0          0     0          0          0              0          0          0 24-APR-23 03.00.23.959 AM                                                   24-APR-23 05.00.35.449 AM
  10%                       ga5m3ggy97233         2240       2240       0           0           0          0             0          0          0     0          0          0              0          0          0 24-APR-23 03.02.04.120 AM                                                   24-APR-23 05.00.35.449 AM
   6%                       1m8z8cvabtqyf         1200        300       0           0         880          0             0          0          0     0          0          0              0          0         20 24-APR-23 03.03.04.232 AM                                                   24-APR-23 04.57.35.163 AM
   2%                       5y8u6k6fkzsqk          540        540       0           0           0          0             0          0          0     0          0          0              0          0          0 24-APR-23 03.05.04.411 AM                                                   24-APR-23 04.40.43.542 AM
   2%  SYS                                         430        340       0           0           0         70            20          0          0     0          0          0              0          0          0 24-APR-23 03.01.14.043 AM                                                   24-APR-23 04.42.23.685 AM
   2%                       gtxhhbn2gw777          370         70       0           0           0          0             0          0          0   300          0          0              0          0          0 24-APR-23 03.00.33.975 AM                                                   24-APR-23 04.58.25.238

 -- select SQL_ID, CHILD_NUMBER from gv$sql where rownum<10;
 
 define sql_id='5j7xryzqzqcbk'
 define child_number=1


set linesize 500 pagesize 300
alter session set "_rowsource_execution_statistics" = TRUE;
col PLAN_TABLE_OUTPUT for a200
SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => '&sql_id', cursor_child_no => 0, FORMAT => 'ADVANCED +ROWS +BYTES +COST +PARALLEL +PARTITION +IOSTATS +MEMSTATS +ALIAS +PEEKED_BINDS +OUTLINE +PREDICATE -PROJECTION +REMOTE +NOTE'));



define 1='session_id,sql_id'
--define 2='event is null'
define 2='event is not null'
define 3='sysdate-10/24/60'
define 4='sysdate'
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
COL event2      FOR A40 WORD_WRAP
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) */
        COUNT(*)                                                     totalseconds
      , ROUND(COUNT(*) / ((CAST(&4 AS DATE) - CAST(&3 AS DATE)) * 86400), 1) AAS
      , LPAD(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100)||'%',5,' ')||' |' "%This"
      , &1
      , 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) dist_sqlexec_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 a.event IN ('buffer busy waits', 'gc buffer busy', 'gc buffer busy acquire', 'gc buffer busy release')
                --THEN ' ['||CASE WHEN (SELECT class FROM bclass WHERE r = a.p3) IS NULL THEN ||']' ELSE null END event2 -- event is NULL in ASH if the session is not waiting (session_state = ON CPU)
                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)
        FROM gv$active_session_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 &2
    AND sample_time BETWEEN &3 AND &4
    GROUP BY
        &1
    ORDER BY
        TotalSeconds DESC
       , &1
)
WHERE
    ROWNUM <= 20
/

===


alter session set container=''  ;

set linesize 700 pagesize 300
define 1='username,sql_id'
DEFINE 2="session_type='FOREGROUND'"


define 3="TIMESTAMP'2023-01-20 07:00:00'" 
define 4="TIMESTAMP'2023-01-20 08:00: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 dop                 FOR 99
COL AAS                 FOR 9999.9
COL totalseconds        HEAD "Total|Seconds" FOR 99999999
COL dist_sqlexec_seen   HEAD "Distinct|Execs Seen" FOR 999999
COL dist_timestamps     HEAD "Distinct|Tstamps" FOR 999999
COL event               FOR A42 WORD_WRAP
COL event2              FOR A42 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

COL rd_rq               FOR 999,999,999
COL wr_rq               FOR 999,999,999
COL rd_mb               FOR 999,999,999
COL wr_mb               FOR 999,999,999
COL pgamem_mb              FOR 9,999,999
COL tempspc_mb             FOR 99,999,999

PROMPT This is an experimental script as some documentation/explanation is needed.
PROMPT The ASH "delta" metrics are not tied to individual SQL_IDs or wait events,
PROMPT They are valid in the session scope (and related mostly static attributes,
PROMPT like PROGRAM, MACHINE, MODULE). Which SQL_ID/operation/event happens to be
PROMPT active when ASH samples its data is just matter of luck. 

SELECT
    * 
FROM (
    WITH bclass AS (SELECT /*+ INLINE */ class, ROWNUM r from v$waitstat)
    SELECT /*+ LEADING(a) USE_HASH(u) */
        COUNT(*)                                                     totalseconds
      , ROUND(COUNT(*) / ((CAST(&4 AS DATE) - CAST(&3 AS DATE)) * 86400), 1) AAS
      , LPAD(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100)||'%',5,' ')||' |' "%This"
      , SUM(delta_read_io_requests)  rd_rq
      , SUM(delta_write_io_requests) wr_rq
      , SUM(delta_read_io_bytes)/1048576     rd_mb   
      , SUM(delta_write_io_bytes)/1048576    wr_mb
      --, SUM(delta_interconnect_io_bytes)
      --, SUM(delta_read_mem_bytes)       
      , MAX(pga_allocated)/1048576           pgamem_mb              
      , MAX(temp_space_allocated)/1048576    tempspc_mb
      , &1
      , 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) dist_sqlexec_seen
      , COUNT(DISTINCT sample_time) dist_timestamps
    FROM
        (SELECT
             a.*
           , session_id sid
           , session_serial# serial
           , 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
           , TRUNC(px_flags / 2097152) dop
           , NVL(a.event, a.session_state)||
                CASE 
                    WHEN a.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 gv$active_session_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 &2
 --  AND sample_time BETWEEN &3 AND &4
 and sample_time > sysdate - interval '30' minute
    GROUP BY
        &1
    ORDER BY
        TotalSeconds DESC
       , &1
)
WHERE
    ROWNUM <= 20
/

********

set pagesize 300 linesize 500
DEFINE 1="username,sql_id"
DEFINE 2="session_type='FOREGROUND'"
define 3='sysdate-10/24/60'
define 4 ='sysdate'

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 dop                 FOR 99
COL AAS                 FOR 9999.9
COL totalseconds        HEAD "Total|Seconds" FOR 99999999
COL dist_sqlexec_seen   HEAD "Distinct|Execs Seen" FOR 999999
COL dist_timestamps     HEAD "Distinct|Tstamps" FOR 999999
COL event               FOR A42 WORD_WRAP
COL event2              FOR A42 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 /*+ INLINE */ class, ROWNUM r from v$waitstat)
    SELECT /*+ LEADING(a) USE_HASH(u) */
        COUNT(*)                                                     totalseconds
      , ROUND(COUNT(*) / ((CAST(&4 AS DATE) - CAST(&3 AS DATE)) * 86400), 1) AAS
      , LPAD(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100)||'%',5,' ')||' |' "%This"
      , &1
      , 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) dist_sqlexec_seen
      , COUNT(DISTINCT sample_time) dist_timestamps
    FROM
        (SELECT
             a.*
           , session_id sid
           , session_serial# serial
           , 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
           , TRUNC(px_flags / 2097152) dop
           , NVL(a.event, a.session_state)||
                CASE 
                    WHEN a.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 gv$active_session_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 &2
   -- AND sample_time BETWEEN &3 AND &4
    GROUP BY
        &1
    ORDER BY
        TotalSeconds DESC
       , &1
)
WHERE
    ROWNUM <= 15
/

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


set linesize 300
col TIME for a30

def sql_id="5j7xryzqzqcbk"
def days_history="10"
def interval_hours="1"
select hss.instance_number inst,
 to_char(trunc(sysdate-&days_history+1)+trunc((cast(hs.begin_interval_time as date)- (trunc(sysdate-&days_history+1)))*24/(&interval_hours))*(&interval_hours)/24,'dd.mm.yyyy hh24:mi:ss') time,
 plan_hash_value,
 hss.sql_id,
 sum(hss.executions_delta) executions,
 round(sum(hss.elapsed_time_delta)/1000000,3) elapsed_time_s,
 round(sum(hss.cpu_time_delta)/1000000,3) cpu_time_s,
 round(sum(hss.iowait_delta)/1000000,3) iowait_s,
 round(sum(hss.clwait_delta)/1000000,3) clwait_s,
 round(sum(hss.apwait_delta)/1000000,3) apwait_s,
 round(sum(hss.ccwait_delta)/1000000,3) ccwait_s,
 round(sum(hss.rows_processed_delta),3) rows_processed,
 round(sum(hss.buffer_gets_delta),3) buffer_gets,
 round(sum(hss.disk_reads_delta),3) disk_reads,
 round(sum(hss.direct_writes_delta),3) direct_writes
from dba_hist_sqlstat hss, dba_hist_snapshot hs
where hss.sql_id='&sql_id'
 and hss.snap_id=hs.snap_id
 and hs.begin_interval_time>=trunc(sysdate)-&days_history+1
group by hss.sql_id,hss.instance_number, trunc(sysdate-&days_history+1)+trunc((cast(hs.begin_interval_time as date)-(trunc(sysdate-&days_history+1)))*24/(&interval_hours))*(&interval_hours)/24,plan_hash_value
having sum(hss.executions_delta)>0
order by hss.instance_number, trunc(sysdate-&days_history+1)+trunc((cast(hs.begin_interval_time as date)-(trunc(sysdate-&days_history+1)))*24/(&interval_hours))*(&interval_hours)/24, 4 desc;


with con_id


https://github.com/LucaCanali/Oracle_DBA_scripts/blob/master/ash_top.sql

set linesize 500 pagesize 300


col event for a27
define sec=10

set verify off

break on sql_id

with b as (
 select count(distinct sample_id) samples, max(sample_time)-min(sample_time) deltaT_interval,
        extract( hour from (max(sample_time)-min(sample_time)) )*60*60+extract( minute from (max(sample_time)-min(sample_time)) )*60+extract( second from (max(sample_time)-min(sample_time)) ) DeltaT
 from gv$active_session_history
 where sample_time>sysdate - interval '&sec' second
 )
select con_id,sql_id,decode(session_state,'WAITING',event,'CPU') event,
       round(100*sum(TM_DELTA_DB_TIME/TM_DELTA_TIME)/b.samples) "DB_TIME%",
       round(100*sum(TM_DELTA_CPU_TIME/TM_DELTA_TIME)/b.samples) "CPU_TIME%",
       round(sum(DELTA_READ_IO_REQUESTS)/b.deltaT) R_IOPs,
       round(sum(DELTA_READ_IO_BYTES)/b.deltaT/1000000,1) R_MBPs,
       round(sum(DELTA_WRITE_IO_REQUESTS)/b.deltaT) W_IOPs,
       round(sum(DELTA_WRITE_IO_BYTES)/b.deltaT/1000000,1) W_MBPs,
       round(max(PGA_ALLOCATED)/1000000,1) PGA_MB, round(max(TEMP_SPACE_ALLOCATED)/1000000,1) TEMP_MB
from gv$active_session_history,b
where sample_time>sysdate - interval '&sec' second
group by con_id,sql_id,event,b.samples,b.deltaT,session_state
--having round(100*sum(TM_DELTA_DB_TIME/TM_DELTA_TIME)/b.samples) >=1
order by 1,2,3 desc nulls last;



clear breaks
break on username
col username for a20
with b as (
 select count(distinct sample_id) samples, max(sample_time)-min(sample_time) deltaT_interval,
        extract( hour from (max(sample_time)-min(sample_time)) )*60*60+extract( minute from (max(sample_time)-min(sample_time)) )*60+extract( second from (max(sample_time)-min(sample_time)) ) DeltaT
 from gv$active_session_history
 where sample_time>sysdate - interval '&sec' second
 )
select (select us.name from sys.user$ us where us.user#=user_id)||case when regexp_substr(program,' \(...') <> ' (TNS' then regexp_substr(program,' \(.+') end username,
      con_id,
       sql_id,
       round(100*sum(TM_DELTA_DB_TIME/TM_DELTA_TIME)/b.samples) "DB_TIME%",
       round(100*sum(TM_DELTA_CPU_TIME/TM_DELTA_TIME)/b.samples) "CPU_TIME%",
       round(sum(DELTA_READ_IO_REQUESTS)/b.deltaT) R_IOPs,
       round(sum(DELTA_READ_IO_BYTES)/b.deltaT/1000000,1) R_MBPs,
       round(sum(DELTA_WRITE_IO_REQUESTS)/b.deltaT) W_IOPs,
       round(sum(DELTA_WRITE_IO_BYTES)/b.deltaT/1000000,1) W_MBPs,
       round(max(PGA_ALLOCATED)/1000000,1) PGA_MB, round(max(TEMP_SPACE_ALLOCATED)/1000000,1) TEMP_MB
from gv$active_session_history,b
where sample_time> sysdate - interval '&sec' second
group by con_id,user_id,program,sql_id,b.samples,b.deltaT
having round(100*sum(TM_DELTA_DB_TIME/TM_DELTA_TIME)/b.samples) >=2
order by 3 desc nulls last;



clear breaks
break on session_id

with b as (
 select count(distinct sample_id) samples, max(sample_time)-min(sample_time) deltaT_interval,
        extract( hour from (max(sample_time)-min(sample_time)) )*60*60+extract( minute from (max(sample_time)-min(sample_time)) )*60+extract( second from (max(sample_time)-min(sample_time)) ) DeltaT
 from gv$active_session_history
 where sample_time>sysdate - interval '&sec' second
 )
select
 con_id,
session_id,
       sql_id,
       round(100*sum(TM_DELTA_DB_TIME/TM_DELTA_TIME)/b.samples) "DB_TIME%",
       round(100*sum(TM_DELTA_CPU_TIME/TM_DELTA_TIME)/b.samples) "CPU_TIME%",
       round(sum(DELTA_READ_IO_REQUESTS)/b.deltaT) R_IOPs,
       round(sum(DELTA_READ_IO_BYTES)/b.deltaT/1000000,1) R_MBPs,
       round(sum(DELTA_WRITE_IO_REQUESTS)/b.deltaT) W_IOPs,
       round(sum(DELTA_WRITE_IO_BYTES)/b.deltaT/1000000,1) W_MBPs,
       round(max(PGA_ALLOCATED)/1000000,1) PGA_MB, round(max(TEMP_SPACE_ALLOCATED)/1000000,1) TEMP_MB
from gv$active_session_history,b
where sample_time>sysdate - interval '&sec' second
group by con_id,session_id,sql_id,b.samples,b.deltaT
having round(100*sum(TM_DELTA_DB_TIME/TM_DELTA_TIME)/b.samples) >=2
order by 3 desc nulls last;



======

col inst_sid_ser# for a12
col username for a26
col sql_id for a13
col exec_plan_ln#_obj# for a45
col dT for 9999
col event for a30
col "DB%,CPU%" for a8
col "R,W_IOPS" for a8
col "R,W_MBPS" for a8
col "PGA,TEMP_MB" for a8
col kill for a17
break on sample_id skip 1

--select inst_id||'_'||session_id||' '||session_serial# inst_sid_ser#,
select ''''||SESSION_ID||','|| SESSION_SERIAL#||',@'||INST_ID ||''''  kill,
       (select us.username from dba_users us where us.user_id=a.user_id)||
       case when regexp_substr(program,' \(...') <> ' (TNS' then regexp_substr(program,' \(.+') end username,
       sql_id, round((sysdate-sql_exec_start)*24*3600,1) dT,
       sql_opname||'-'||sql_plan_operation||'-'||sql_plan_options||','||sql_plan_line_id||','||nullif(current_obj#,-1) exec_plan_ln#_obj#,
       decode(session_state,'WAITING',event,'CPU') event,
       --blocking_session, wait_time,time_waited
       round(100*TM_DELTA_DB_TIME/TM_DELTA_TIME,0)||', '||round(100*TM_DELTA_CPU_TIME/TM_DELTA_TIME,0) "DB%,CPU%", 
       round (1000000*delta_read_io_requests/delta_time,0)||', '||round(1000000*delta_write_io_requests/delta_time,0) "R,W_IOPS", 
       round(delta_read_io_bytes/delta_time,1)||', '||round(delta_write_io_bytes/delta_time,1) "R,W_MBPS",
       round(pga_allocated/1000000,1)||', '||round(temp_space_allocated/1000000,1) "PGA,TEMP_MB"
from (select * from gv$active_session_history where inst_id=1 and rownum<300) a
where sample_id=(select sample_id from gv$active_session_history where inst_id=1 and rownum=1)
and sample_time > sysdate - interval '5' second
UNION ALL
--select inst_id||'_'||session_id||' '||session_serial# sid_ser#,
select ''''||SESSION_ID||','|| SESSION_SERIAL#||',@'||INST_ID ||''''  kill,
       (select us.username from dba_users us where us.user_id=a.user_id)||
       case when regexp_substr(program,' \(...') <> ' (TNS' then regexp_substr(program,' \(.+') end username,
       sql_id, round((sysdate-sql_exec_start)*24*3600,1) dT,
       sql_opname||'-'||sql_plan_operation||'-'||sql_plan_options||','||sql_plan_line_id||','||nullif(current_obj#,-1) exec_plan_ln#_obj#,
       decode(session_state,'WAITING',event,'CPU') event,
       --blocking_session, wait_time,time_waited
       round(100*TM_DELTA_DB_TIME/TM_DELTA_TIME,0)||', '||round(100*TM_DELTA_CPU_TIME/TM_DELTA_TIME,0) "DB%,CPU%", 
       round (1000000*delta_read_io_requests/delta_time,0)||', '||round(1000000*delta_write_io_requests/delta_time,0) "R,W_IOPS", 
       round(delta_read_io_bytes/delta_time,1)||', '||round(delta_write_io_bytes/delta_time,1) "R,W_MBPS",
       round(pga_allocated/1000000,1)||', '||round(temp_space_allocated/1000000,1) "PGA,TEMP_MB"
from (select * from gv$active_session_history where inst_id=2 and rownum<300) a
where sample_id=(select sample_id from gv$active_session_history where inst_id=2 and rownum=1)
and sample_time > sysdate - interval '5' second
UNION ALL
--select inst_id||'_'||session_id||' '||session_serial# sid_ser#,
select ''''||SESSION_ID||','|| SESSION_SERIAL#||',@'||INST_ID ||''''  kill,
       (select us.username from dba_users us where us.user_id=a.user_id)||
       case when regexp_substr(program,' \(...') <> ' (TNS' then regexp_substr(program,' \(.+') end username,
       sql_id, round((sysdate-sql_exec_start)*24*3600,1) dT,
       sql_opname||'-'||sql_plan_operation||'-'||sql_plan_options||','||sql_plan_line_id||','||nullif(current_obj#,-1) exec_plan_ln#_obj#,
       decode(session_state,'WAITING',event,'CPU') event,
       --blocking_session, wait_time,time_waited
       round(100*TM_DELTA_DB_TIME/TM_DELTA_TIME,0)||', '||round(100*TM_DELTA_CPU_TIME/TM_DELTA_TIME,0) "DB%,CPU%", 
       round (1000000*delta_read_io_requests/delta_time,0)||', '||round(1000000*delta_write_io_requests/delta_time,0) "R,W_IOPS", 
       round(delta_read_io_bytes/delta_time,1)||', '||round(delta_write_io_bytes/delta_time,1) "R,W_MBPS",
       round(pga_allocated/1000000,1)||', '||round(temp_space_allocated/1000000,1) "PGA,TEMP_MB"
from (select * from gv$active_session_history where inst_id=3 and rownum<300) a
where sample_id=(select sample_id from gv$active_session_history where inst_id=3 and rownum=1)
and sample_time > sysdate - interval '5' second
/


-- 11G
set linesize 500 pagesize 500
col inst_sid_ser# for a12
col username for a26
col sql_id for a13
col exec_plan_ln#_obj# for a45
col dT for 9999
col event for a30
col "DB%,CPU%" for a8
col "R,W_IOPS" for a8
col "R,W_MBPS" for a8
col "PGA,TEMP_MB" for a8
col kill for a17
break on sample_id skip 1

--select inst_id||'_'||session_id||' '||session_serial# inst_sid_ser#,
select ''''||SESSION_ID||','|| SESSION_SERIAL#||',@'||INST_ID ||''''  kill,
       (select us.username from dba_users us where us.user_id=a.user_id)||
       case when regexp_substr(program,' \(...') <> ' (TNS' then regexp_substr(program,' \(.+') end username,
       sql_id, round((sysdate-sql_exec_start)*24*3600,1) dT,
       sql_opname||'-'||sql_plan_operation||'-'||sql_plan_options||','||sql_plan_line_id||','||nullif(current_obj#,-1) exec_plan_ln#_obj#,
       decode(session_state,'WAITING',event,'CPU') event,
       --blocking_session, wait_time,time_waited
       round(100*TM_DELTA_DB_TIME/TM_DELTA_TIME,0)||', '||round(100*TM_DELTA_CPU_TIME/TM_DELTA_TIME,0) "DB%,CPU%", 
       round (1000000*delta_read_io_requests/delta_time,0)||', '||round(1000000*delta_write_io_requests/delta_time,0) "R,W_IOPS", 
       round(delta_read_io_bytes/delta_time,1)||', '||round(delta_write_io_bytes/delta_time,1) "R,W_MBPS",
       round(pga_allocated/1000000,1)||', '||round(temp_space_allocated/1000000,1) "PGA,TEMP_MB"
from (select * from gv$active_session_history where inst_id=1 and rownum<300) a
where sample_id=(select sample_id from gv$active_session_history where inst_id=1 and rownum=1)
and sample_time > sysdate - interval '5' second
UNION ALL
--select inst_id||'_'||session_id||' '||session_serial# sid_ser#,
select ''''||SESSION_ID||','|| SESSION_SERIAL#||',@'||INST_ID ||''''  kill,
       (select us.username from dba_users us where us.user_id=a.user_id)||
       case when regexp_substr(program,' \(...') <> ' (TNS' then regexp_substr(program,' \(.+') end username,
       sql_id, round((sysdate-sql_exec_start)*24*3600,1) dT,
       sql_opname||'-'||sql_plan_operation||'-'||sql_plan_options||','||sql_plan_line_id||','||nullif(current_obj#,-1) exec_plan_ln#_obj#,
       decode(session_state,'WAITING',event,'CPU') event,
       --blocking_session, wait_time,time_waited
       round(100*TM_DELTA_DB_TIME/TM_DELTA_TIME,0)||', '||round(100*TM_DELTA_CPU_TIME/TM_DELTA_TIME,0) "DB%,CPU%", 
       round (1000000*delta_read_io_requests/delta_time,0)||', '||round(1000000*delta_write_io_requests/delta_time,0) "R,W_IOPS", 
       round(delta_read_io_bytes/delta_time,1)||', '||round(delta_write_io_bytes/delta_time,1) "R,W_MBPS",
       round(pga_allocated/1000000,1)||', '||round(temp_space_allocated/1000000,1) "PGA,TEMP_MB"
from (select * from gv$active_session_history where inst_id=2 and rownum<300) a
where sample_id=(select sample_id from gv$active_session_history where inst_id=2 and rownum=1)
and sample_time > sysdate - interval '5' second
UNION ALL
--select inst_id||'_'||session_id||' '||session_serial# sid_ser#,
select ''''||SESSION_ID||','|| SESSION_SERIAL#||',@'||INST_ID ||''''  kill,
       (select us.username from dba_users us where us.user_id=a.user_id)||
       case when regexp_substr(program,' \(...') <> ' (TNS' then regexp_substr(program,' \(.+') end username,
       sql_id, round((sysdate-sql_exec_start)*24*3600,1) dT,
       sql_opname||'-'||sql_plan_operation||'-'||sql_plan_options||','||sql_plan_line_id||','||nullif(current_obj#,-1) exec_plan_ln#_obj#,
       decode(session_state,'WAITING',event,'CPU') event,
       --blocking_session, wait_time,time_waited
       round(100*TM_DELTA_DB_TIME/TM_DELTA_TIME,0)||', '||round(100*TM_DELTA_CPU_TIME/TM_DELTA_TIME,0) "DB%,CPU%", 
       round (1000000*delta_read_io_requests/delta_time,0)||', '||round(1000000*delta_write_io_requests/delta_time,0) "R,W_IOPS", 
       round(delta_read_io_bytes/delta_time,1)||', '||round(delta_write_io_bytes/delta_time,1) "R,W_MBPS",
       round(pga_allocated/1000000,1)||', '||round(temp_space_allocated/1000000,1) "PGA,TEMP_MB"
from (select * from gv$active_session_history where inst_id=3 and rownum<300) a
where sample_id=(select sample_id from gv$active_session_history where inst_id=3 and rownum=1)
and sample_time > sysdate - interval '5' second
/



--12c

set linesize 500 pagesize 500

col inst_sid_ser# for a12
col username for a26
col sql_id for a13
col exec_plan_ln#_obj# for a45
col dT for 9999
col event for a30
col "DB%,CPU%" for a8
col "R,W_IOPS" for a8
col "R,W_MBPS" for a8
col "PGA,TEMP_MB" for a8
col kill for a17
break on sample_id skip 1

--select inst_id||'_'||session_id||' '||session_serial# inst_sid_ser#,
select ''''||SESSION_ID||','|| SESSION_SERIAL#||',@'||INST_ID ||''''  kill,con_id,
       (select us.username from dba_users us where us.user_id=a.user_id)||
       case when regexp_substr(program,' \(...') <> ' (TNS' then regexp_substr(program,' \(.+') end username,
       sql_id, round((sysdate-sql_exec_start)*24*3600,1) dT,
       sql_opname||'-'||sql_plan_operation||'-'||sql_plan_options||','||sql_plan_line_id||','||nullif(current_obj#,-1) exec_plan_ln#_obj#,
       decode(session_state,'WAITING',event,'CPU') event,
       --blocking_session, wait_time,time_waited
       round(100*TM_DELTA_DB_TIME/TM_DELTA_TIME,0)||', '||round(100*TM_DELTA_CPU_TIME/TM_DELTA_TIME,0) "DB%,CPU%", 
       round (1000000*delta_read_io_requests/delta_time,0)||', '||round(1000000*delta_write_io_requests/delta_time,0) "R,W_IOPS", 
       round(delta_read_io_bytes/delta_time,1)||', '||round(delta_write_io_bytes/delta_time,1) "R,W_MBPS",
       round(pga_allocated/1000000,1)||', '||round(temp_space_allocated/1000000,1) "PGA,TEMP_MB"
from (select * from gv$active_session_history where inst_id=1 and rownum<300) a
where sample_id=(select sample_id from gv$active_session_history where inst_id=1 and rownum=1)
and sample_time > sysdate - interval '5' second
UNION ALL
--select inst_id||'_'||session_id||' '||session_serial# sid_ser#,
select ''''||SESSION_ID||','|| SESSION_SERIAL#||',@'||INST_ID ||''''  kill,con_id,
       (select us.username from dba_users us where us.user_id=a.user_id)||
       case when regexp_substr(program,' \(...') <> ' (TNS' then regexp_substr(program,' \(.+') end username,
       sql_id, round((sysdate-sql_exec_start)*24*3600,1) dT,
       sql_opname||'-'||sql_plan_operation||'-'||sql_plan_options||','||sql_plan_line_id||','||nullif(current_obj#,-1) exec_plan_ln#_obj#,
       decode(session_state,'WAITING',event,'CPU') event,
       --blocking_session, wait_time,time_waited
       round(100*TM_DELTA_DB_TIME/TM_DELTA_TIME,0)||', '||round(100*TM_DELTA_CPU_TIME/TM_DELTA_TIME,0) "DB%,CPU%", 
       round (1000000*delta_read_io_requests/delta_time,0)||', '||round(1000000*delta_write_io_requests/delta_time,0) "R,W_IOPS", 
       round(delta_read_io_bytes/delta_time,1)||', '||round(delta_write_io_bytes/delta_time,1) "R,W_MBPS",
       round(pga_allocated/1000000,1)||', '||round(temp_space_allocated/1000000,1) "PGA,TEMP_MB"
from (select * from gv$active_session_history where inst_id=2 and rownum<300) a
where sample_id=(select sample_id from gv$active_session_history where inst_id=2 and rownum=1)
and sample_time > sysdate - interval '5' second
UNION ALL
--select inst_id||'_'||session_id||' '||session_serial# sid_ser#,
select ''''||SESSION_ID||','|| SESSION_SERIAL#||',@'||INST_ID ||''''  kill,con_id,
       (select us.username from dba_users us where us.user_id=a.user_id)||
       case when regexp_substr(program,' \(...') <> ' (TNS' then regexp_substr(program,' \(.+') end username,
       sql_id, round((sysdate-sql_exec_start)*24*3600,1) dT,
       sql_opname||'-'||sql_plan_operation||'-'||sql_plan_options||','||sql_plan_line_id||','||nullif(current_obj#,-1) exec_plan_ln#_obj#,
       decode(session_state,'WAITING',event,'CPU') event,
       --blocking_session, wait_time,time_waited
       round(100*TM_DELTA_DB_TIME/TM_DELTA_TIME,0)||', '||round(100*TM_DELTA_CPU_TIME/TM_DELTA_TIME,0) "DB%,CPU%", 
       round (1000000*delta_read_io_requests/delta_time,0)||', '||round(1000000*delta_write_io_requests/delta_time,0) "R,W_IOPS", 
       round(delta_read_io_bytes/delta_time,1)||', '||round(delta_write_io_bytes/delta_time,1) "R,W_MBPS",
       round(pga_allocated/1000000,1)||', '||round(temp_space_allocated/1000000,1) "PGA,TEMP_MB"
from (select * from gv$active_session_history where inst_id=3 and rownum<300) a
where sample_id=(select sample_id from gv$active_session_history where inst_id=3 and rownum=1)
and sample_time > sysdate - interval '5' second
/








set pagesize 300 linesize 500
DEFINE 1="wait_class,event"
-- DEFINE 1="inst_id,wait_class,event,username,sql_id"
  --   DEFINE 1="inst_id,session_id,username,program,sql_id,sql_plan_hash_value,plsql_entry_object_id,blocking_session,event"
 -- DEFINE 1="inst_id,session_id,username,program,sql_id,sql_plan_hash_value,plsql_entry_object_id,sql_plan_operation,sql_plan_options,sql_plan_line_id"
DEFINE 2="session_type='FOREGROUND'"




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



set linesize 300

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
 
VARIABLE bdate VARCHAR2(35)
VARIABLE edate VARCHAR2(35)
exec SELECT CAST('12-JUL-2023 04:00' AS DATE) into :Bdate FROM dual;
exec SELECT CAST('12-JUL-2023 05:37' AS DATE) into :edate FROM dual;



--username,sql_id session_type='FOREGROUND'


define 1='username,sql_id'
define 2="session_type='FOREGROUND'"

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
--set linesize 200
col USERNAME for a20
col min_sample_time for a27
col max_sample_time for a27

SELECT * FROM (
    SELECT /*+ LEADING(a) USE_HASH(u) +PARALLEL(8) */
        COUNT(*)                                                     totalseconds
        , abs(ROUND(COUNT(*) / ((CAST(:Bdate AS DATE) - CAST(:edate AS DATE)) * 86400), 1)) AAS
        , LPAD(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100)||'%',5,' ')||' |' "%This"
        , &1
        , 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 ='Cluster'        THEN 1 ELSE 0 END) "Cluster"
        , 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 ='Queueing'       THEN 1 ELSE 0 END) "Queueing"
        , SUM(CASE WHEN wait_class ='Other'          THEN 1 ELSE 0 END) "Other"
        , MIN(sample_time) min_sample_time
        , MAX(sample_time) max_sample_time
    --    , MAX(sql_exec_id) - MIN(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
		-- from DBA_HIST_ACTIVE_SESS_HISTORY a) a
     , dba_users u
	  --, cdb_users u
    WHERE
        a.user_id = u.user_id (+)
   -- AND &2
 --AND sample_time BETWEEN CAST(:Bdate AS DATE) AND CAST(:edate AS DATE)  --ww 
 AND sample_time > sysdate - interval '50' minute
    GROUP BY
        &1
    ORDER BY
        TotalSeconds DESC
       , &1
)
WHERE
    ROWNUM <= 20
/

Anuj Singh Oracle DBA

Search This Blog

Total Pageviews

Sunday, 12 September 2021

ashtop.sql from command line

Oracle DBA

anuj blog Archive