-------------------------------------------------------------------------------
-- 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
/
