sql_ash_exec_hist_v1.sql

Add W3Buddy as a preferred source on Google

हि En

🔊 Listen

-------------------------------------------------------------------------------
-- Script:     sql_ash_exec_hist_v1.sql
-- Purpose:    Analyze execution-level session history for a specific SQL ID
--             using live and historical ASH views in Oracle.
--
-- Description:
--             This script aggregates ASH samples from GV$ACTIVE_SESSION_HISTORY
--             and DBA_HIST_ACTIVE_SESS_HISTORY, reporting execution duration,
--             user sessions, plan hash values, and SQL execution IDs.
--
-- Usage:
--             Run in SQL*Plus or SQL Developer.
--             Pass the SQL ID and number of past days to scan:
--               @sql_ash_exec_hist_v1.sql <sql_id> <days>
--
-- Output:
--             - Instance number
--             - Session ID and Serial#
--             - Username
--             - Force Matching Signature
--             - SQL ID
--             - SQL Execution ID
--             - SQL Execution Start Time
--             - SQL Plan Hash Value
--             - Total ASH Seconds (aggregated samples)
--             - Duration (HH:MM:SS)
--             - First and Last Sample Time
--
-- Example:
--             @sql_ash_exec_hist_v1.sql fbz3c1q4xgmnv 20
--
-- Author:     W3Buddy
-- Version:    v1.0
-- Date:       2025-06-18
-------------------------------------------------------------------------------

UNDEFINE salid;
UNDEFINE days;
DEFINE salid = &1;
DEFINE days  = &2;
COL min_time    FOR A40
COL max_time    FOR A40
COL duration    FOR A20
SELECT
    ash.instance_number,
    ash.session_id,
    ash.session_serial#,
    ash.username,
    ash.force_matching_signature,
    ash.sql_id,
    ash.sql_exec_id,
    ash.sql_exec_start,
    ash.sql_plan_hash_value,
    SUM(ash.ash_secs) AS ash_secs,
    SUBSTR(NUMTODSINTERVAL(SUM(ash.ash_secs), 'SECOND'), 11, 12) AS duration,
    MIN(ash.min_time) AS min_time,
    MAX(ash.max_time) AS max_time
FROM (
    -- Live ASH data
    WITH cut AS (
        SELECT /*+ MATERIALIZE */
               inst_id,
               MIN(sample_time) AS cut_time
        FROM gv$active_session_history
        GROUP BY inst_id
    )
    SELECT
        c.inst_id AS instance_number,
        c.session_id,
        c.session_serial#,
        b.username,
        c.force_matching_signature,
        c.sql_id,
        c.sql_exec_id,
        c.sql_exec_start,
        c.sql_plan_hash_value,
        COUNT(*) AS ash_secs,
        MIN(c.sample_time) AS min_time,
        MAX(c.sample_time) AS max_time
    FROM gv$active_session_history c
    JOIN dba_users b ON c.user_id = b.user_id
    JOIN cut ON c.inst_id = cut.inst_id
    WHERE c.sql_id = '&salid'
      AND c.sample_time > cut.cut_time
      AND c.sample_time > SYSDATE - &days
      AND c.in_sql_execution = 'Y'
      AND c.in_parse = 'N'
      AND c.in_hard_parse = 'N'
    GROUP BY
        c.inst_id, c.session_id, c.session_serial#,
        b.username, c.force_matching_signature,
        c.sql_id, c.sql_exec_id,
        c.sql_exec_start, c.sql_plan_hash_value
    UNION ALL
    -- Historical ASH data
    SELECT
        c.instance_number,
        c.session_id,
        c.session_serial#,
        b.username,
        c.force_matching_signature,
        c.sql_id,
        c.sql_exec_id,
        c.sql_exec_start,
        c.sql_plan_hash_value,
        COUNT(*) AS ash_secs,
        MIN(c.sample_time) AS min_time,
        MAX(c.sample_time) AS max_time
    FROM dba_hist_active_sess_history c
    JOIN dba_users b ON c.user_id = b.user_id
    JOIN cut ON c.instance_number = cut.inst_id
    WHERE c.sql_id = '&salid'
      AND c.sample_time > cut.cut_time
      AND c.sample_time > SYSDATE - &days
      AND c.in_sql_execution = 'Y'
      AND c.in_parse = 'N'
      AND c.in_hard_parse = 'N'
    GROUP BY
        c.instance_number, c.session_id, c.session_serial#,
        b.username, c.force_matching_signature,
        c.sql_id, c.sql_exec_id,
        c.sql_exec_start, c.sql_plan_hash_value
) ash
GROUP BY
    ash.instance_number,
    ash.session_id,
    ash.session_serial#,
    ash.username,
    ash.force_matching_signature,
    ash.sql_id,
    ash.sql_exec_id,
    ash.sql_exec_start,
    ash.sql_plan_hash_value
ORDER BY min_time
/

📄 Sample Output