Tanel Poder ash_wait_chains.sql

 

@ash_wait_chains event2 sql_id='sssss' "timestamp '2013-11-04 00:00:00'"   "timestamp '2013-11-04 03:00:00'" 

@ash_wait_chain.sql event2|program2|sql_opname 1=1 ' "timestamp '2013-11-04 00:00:00'"   "timestamp '2013-11-04 03:00:00'" 

-- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com

-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.

--------------------------------------------------------------------------------

--

-- File name:   ash_wait_chains.sql (v0.6 BETA)

-- Purpose:     Display ASH wait chains (multi-session wait signature, a session

--              waiting for another session etc.)

--              

-- Author:      Tanel Poder

-- Copyright:   (c) http://blog.tanelpoder.com

--              

-- Usage:       

--     @ash_wait_chains <grouping_cols> <filters> <fromtime> <totime>

--

-- Example:

--     @ash_wait_chains username||':'||program2||event2 session_type='FOREGROUND' sysdate-1/24 sysdate

--

-- Other:

--     This script uses only the in-memory V$ACTIVE_SESSION_HISTORY, use

--     @dash_wait_chains.sql for accessiong the DBA_HIST_ACTIVE_SESS_HISTORY archive

--

--     Oracle 10g does not  have the BLOCKING_INST_ID column in ASH so you'll need

--     to comment out this column in this script. This may give you somewhat

--     incorrect results in RAC environment with global blockers.

--              

--------------------------------------------------------------------------------

COL wait_chain FOR A300 WORD_WRAP

COL distinct_sids FOR 9999 HEAD "#Blkrs"

COL "%This" FOR A6

PROMPT

PROMPT -- Display ASH Wait Chain Signatures script v0.6 BETA by Tanel Poder ( http://blog.tanelpoder.com )

WITH 

bclass AS (SELECT /*+ INLINE */ class, ROWNUM r from v$waitstat),

ash AS (SELECT /*+ INLINE QB_NAME(ash) LEADING(a) USE_HASH(u) SWAP_JOIN_INPUTS(u) */

            a.*

          , o.*

          , u.username

          , CASE WHEN a.session_type = 'BACKGROUND' AND a.program LIKE '%(DBW%)' THEN

              '(DBWn)'

            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

          , NVL(a.event||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 ' ['||NVL((SELECT class FROM bclass WHERE r = a.p3),'undo @bclass '||a.p3)||']' ELSE null END,'ON CPU') 

                       || ' ' event2

          , 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

          , 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

          , 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 sample_time BETWEEN &3 AND &4

    ),

ash_samples AS (SELECT /*+ INLINE */ DISTINCT sample_id FROM ash),

ash_data AS (SELECT /*+ INLINE */ * FROM ash),

chains AS (

    SELECT /*+ INLINE */ 

        sample_time ts

      , level lvl

      , session_id sid

      , REPLACE(SYS_CONNECT_BY_PATH(&1, '->'), '->', ' -> ')||CASE WHEN CONNECT_BY_ISLEAF = 1 AND d.blocking_session IS NOT NULL THEN ' -> [idle blocker '||d.blocking_inst_id||','||d.blocking_session||','||d.blocking_session_serial#||(SELECT ' ('||s.program||')' FROM gv$session s WHERE (s.inst_id, s.sid , s.serial#) = ((d.blocking_inst_id,d.blocking_session,d.blocking_session_serial#)))||']' ELSE NULL END path -- there's a reason why I'm doing this 

      --, SYS_CONNECT_BY_PATH(&1, ' -> ')||CASE WHEN CONNECT_BY_ISLEAF = 1 THEN '('||d.session_id||')' ELSE NULL END path

      -- , REPLACE(SYS_CONNECT_BY_PATH(&1, '->'), '->', ' -> ')||CASE WHEN CONNECT_BY_ISLEAF = 1 AND LEVEL > 1 THEN ' [sid='||session_id||' seq#='||TO_CHAR(seq#)||']' ELSE NULL END path -- there's a reason why I'm doing this 

      --, REPLACE(SYS_CONNECT_BY_PATH(&1, '->'), '->', ' -> ') path -- there's a reason why I'm doing this (ORA-30004 :)

      , CASE WHEN CONNECT_BY_ISLEAF = 1 THEN d.session_id ELSE NULL END sids

      , CONNECT_BY_ISLEAF isleaf

      , CONNECT_BY_ISCYCLE iscycle

      , d.*

    FROM

        ash_samples s

      , ash_data d

    WHERE

        s.sample_id = d.sample_id 

    AND d.sample_time BETWEEN &3 AND &4

    CONNECT BY NOCYCLE

        (    PRIOR d.blocking_session = d.session_id

         AND PRIOR d.blocking_inst_id = d.inst_id

         AND PRIOR s.sample_id = d.sample_id

        )

    START WITH &2

)

SELECT * FROM (

    SELECT

        LPAD(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100)||'%',5,' ') "%This"

      , COUNT(*) seconds

      , ROUND(COUNT(*) / ((CAST(&4 AS DATE) - CAST(&3 AS DATE)) * 86400), 1) AAS

      , COUNT(DISTINCT sids) distinct_sids

      , path wait_chain

      , 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

      -- , MIN(sids)

      -- , MAX(sids)

    FROM

        chains

    WHERE

        isleaf = 1

    GROUP BY

        &1

      , path

    ORDER BY

        COUNT(*) DESC

    )

WHERE

    ROWNUM <= 30

/