{"id":4325,"date":"2025-06-19T00:38:24","date_gmt":"2025-06-19T00:38:24","guid":{"rendered":"https:\/\/w3buddy.com\/?post_type=cposts&#038;p=4325"},"modified":"2025-06-19T00:42:51","modified_gmt":"2025-06-19T00:42:51","slug":"sql_ash_exec_hist_v1-sql","status":"publish","type":"cposts","link":"https:\/\/w3buddy.com\/blog\/notes\/performance-tuning\/sql_ash_exec_hist_v1-sql\/","title":{"rendered":"sql_ash_exec_hist_v1.sql"},"content":{"rendered":"\n<pre class=\"wp-block-code\"><code>-------------------------------------------------------------------------------\n-- Script:     sql_ash_exec_hist_v1.sql\n-- Purpose:    Analyze execution-level session history for a specific SQL ID\n--             using live and historical ASH views in Oracle.\n--\n-- Description:\n--             This script aggregates ASH samples from GV$ACTIVE_SESSION_HISTORY\n--             and DBA_HIST_ACTIVE_SESS_HISTORY, reporting execution duration,\n--             user sessions, plan hash values, and SQL execution IDs.\n--\n-- Usage:\n--             Run in SQL*Plus or SQL Developer.\n--             Pass the SQL ID and number of past days to scan:\n--               @sql_ash_exec_hist_v1.sql &lt;sql_id> &lt;days>\n--\n-- Output:\n--             - Instance number\n--             - Session ID and Serial#\n--             - Username\n--             - Force Matching Signature\n--             - SQL ID\n--             - SQL Execution ID\n--             - SQL Execution Start Time\n--             - SQL Plan Hash Value\n--             - Total ASH Seconds (aggregated samples)\n--             - Duration (HH:MM:SS)\n--             - First and Last Sample Time\n--\n-- Example:\n--             @sql_ash_exec_hist_v1.sql fbz3c1q4xgmnv 20\n--\n-- Author:     W3Buddy\n-- Version:    v1.0\n-- Date:       2025-06-18\n-------------------------------------------------------------------------------\n\nUNDEFINE salid;\nUNDEFINE days;\nDEFINE salid = &amp;1;\nDEFINE days  = &amp;2;\nCOL min_time    FOR A40\nCOL max_time    FOR A40\nCOL duration    FOR A20\nSELECT\n    ash.instance_number,\n    ash.session_id,\n    ash.session_serial#,\n    ash.username,\n    ash.force_matching_signature,\n    ash.sql_id,\n    ash.sql_exec_id,\n    ash.sql_exec_start,\n    ash.sql_plan_hash_value,\n    SUM(ash.ash_secs) AS ash_secs,\n    SUBSTR(NUMTODSINTERVAL(SUM(ash.ash_secs), 'SECOND'), 11, 12) AS duration,\n    MIN(ash.min_time) AS min_time,\n    MAX(ash.max_time) AS max_time\nFROM (\n    -- Live ASH data\n    WITH cut AS (\n        SELECT \/*+ MATERIALIZE *\/\n               inst_id,\n               MIN(sample_time) AS cut_time\n        FROM gv$active_session_history\n        GROUP BY inst_id\n    )\n    SELECT\n        c.inst_id AS instance_number,\n        c.session_id,\n        c.session_serial#,\n        b.username,\n        c.force_matching_signature,\n        c.sql_id,\n        c.sql_exec_id,\n        c.sql_exec_start,\n        c.sql_plan_hash_value,\n        COUNT(*) AS ash_secs,\n        MIN(c.sample_time) AS min_time,\n        MAX(c.sample_time) AS max_time\n    FROM gv$active_session_history c\n    JOIN dba_users b ON c.user_id = b.user_id\n    JOIN cut ON c.inst_id = cut.inst_id\n    WHERE c.sql_id = '&amp;salid'\n      AND c.sample_time > cut.cut_time\n      AND c.sample_time > SYSDATE - &amp;days\n      AND c.in_sql_execution = 'Y'\n      AND c.in_parse = 'N'\n      AND c.in_hard_parse = 'N'\n    GROUP BY\n        c.inst_id, c.session_id, c.session_serial#,\n        b.username, c.force_matching_signature,\n        c.sql_id, c.sql_exec_id,\n        c.sql_exec_start, c.sql_plan_hash_value\n    UNION ALL\n    -- Historical ASH data\n    SELECT\n        c.instance_number,\n        c.session_id,\n        c.session_serial#,\n        b.username,\n        c.force_matching_signature,\n        c.sql_id,\n        c.sql_exec_id,\n        c.sql_exec_start,\n        c.sql_plan_hash_value,\n        COUNT(*) AS ash_secs,\n        MIN(c.sample_time) AS min_time,\n        MAX(c.sample_time) AS max_time\n    FROM dba_hist_active_sess_history c\n    JOIN dba_users b ON c.user_id = b.user_id\n    JOIN cut ON c.instance_number = cut.inst_id\n    WHERE c.sql_id = '&amp;salid'\n      AND c.sample_time > cut.cut_time\n      AND c.sample_time > SYSDATE - &amp;days\n      AND c.in_sql_execution = 'Y'\n      AND c.in_parse = 'N'\n      AND c.in_hard_parse = 'N'\n    GROUP BY\n        c.instance_number, c.session_id, c.session_serial#,\n        b.username, c.force_matching_signature,\n        c.sql_id, c.sql_exec_id,\n        c.sql_exec_start, c.sql_plan_hash_value\n) ash\nGROUP BY\n    ash.instance_number,\n    ash.session_id,\n    ash.session_serial#,\n    ash.username,\n    ash.force_matching_signature,\n    ash.sql_id,\n    ash.sql_exec_id,\n    ash.sql_exec_start,\n    ash.sql_plan_hash_value\nORDER BY min_time\n\/\n<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">\ud83d\udcc4 <strong>Sample Output<\/strong><\/h2>\n\n\n\n<figure style=\"font-size:10px\" class=\"wp-block-table\"><table><thead><tr><th>INSTANCE_NUMBER<\/th><th>SESSION_ID<\/th><th>SESSION_SERIAL#<\/th><th>USERNAME<\/th><th>FORCE_MATCHING_SIGNATURE<\/th><th>SQL_ID<\/th><th>SQL_EXEC_ID<\/th><th>SQL_EXEC_START<\/th><th>SQL_PLAN_HASH_VALUE<\/th><th>ASH_SECS<\/th><th>DURATION<\/th><th>MIN_TIME<\/th><th>MAX_TIME<\/th><\/tr><\/thead><tbody><tr><td>1<\/td><td>328<\/td><td>48912<\/td><td>HR<\/td><td>12345678901234567890<\/td><td>fbz3c1q4xgmnv<\/td><td>16777216<\/td><td>18-JUN-25 01:03:20<\/td><td>2891234567<\/td><td>18<\/td><td>00:00:18<\/td><td>18-JUN-25 01:03:20.000000<\/td><td>18-JUN-25 01:03:37.000000<\/td><\/tr><tr><td>1<\/td><td>328<\/td><td>48912<\/td><td>HR<\/td><td>12345678901234567890<\/td><td>fbz3c1q4xgmnv<\/td><td>16777217<\/td><td>18-JUN-25 01:06:45<\/td><td>2891234567<\/td><td>22<\/td><td>00:00:22<\/td><td>18-JUN-25 01:06:45.000000<\/td><td>18-JUN-25 01:07:07.000000<\/td><\/tr><tr><td>2<\/td><td>412<\/td><td>18871<\/td><td>SCOTT<\/td><td>67890123456789012345<\/td><td>fbz3c1q4xgmnv<\/td><td>16777218<\/td><td>18-JUN-25 02:11:10<\/td><td>2891234567<\/td><td>11<\/td><td>00:00:11<\/td><td>18-JUN-25 02:11:10.000000<\/td><td>18-JUN-25 02:11:21.000000<\/td><\/tr><\/tbody><\/table><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>\ud83d\udcc4 Sample Output INSTANCE_NUMBER SESSION_ID SESSION_SERIAL# USERNAME FORCE_MATCHING_SIGNATURE SQL_ID SQL_EXEC_ID SQL_EXEC_START SQL_PLAN_HASH_VALUE ASH_SECS DURATION MIN_TIME MAX_TIME 1 328 48912 HR 12345678901234567890 fbz3c1q4xgmnv 16777216 18-JUN-25 01:03:20 2891234567 18 00:00:18 18-JUN-25 01:03:20.000000 18-JUN-25 01:03:37.000000 1 328 48912 HR 12345678901234567890 fbz3c1q4xgmnv 16777217 18-JUN-25 01:06:45 2891234567 22 00:00:22 18-JUN-25 01:06:45.000000 18-JUN-25 01:07:07.000000 2 412 18871 SCOTT 67890123456789012345 fbz3c1q4xgmnv 16777218 [&hellip;]<\/p>\n","protected":false},"template":"","meta":{"googlesitekit_rrm_CAowu461DA:productID":""},"categories":[952,992],"class_list":["post-4325","cposts","type-cposts","status-publish","hentry","category-notes","category-performance-tuning"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts\/4325","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/cposts"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=4325"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=4325"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}