Gem

 -- filename: rds_ash_awr_investigation.sql

--

-- Objective: To investigate heavy CPU hitters, session spikes, and top consumers

--            at a particular point in time using Oracle RDS AWR/ASH data.

--

-- Prerequisites:

--   - Oracle Diagnostics Pack License (Enterprise Edition).

--   - Connected to your Oracle RDS instance with sufficient privileges (e.g., rdsadmin user).

--   - Ensure SQL*Plus (or similar client) settings:

--     SET SERVEROUTPUT ON SIZE UNLIMITED

--     SET LONG 20000000 -- For full SQL text and AWR/ASH report output

--     SET PAGESIZE 0   -- No pagination

--     SET FEEDBACK OFF -- No "X rows selected" messages

--     SET HEADING OFF  -- No column headers (for report output)

--     SET TRIMSPOOL ON -- Trim trailing spaces from spool output

--

-- How to Use:

-- 1. Replace placeholder values for bind variables (e.g., :desired_timestamp, :begin_snap_id, :end_snap_id).

-- 2. (Optional but recommended for HTML reports) SPOOL the output to a .html file before executing the AWR/ASH report generation.

--    Example: SPOOL C:\temp\my_report.html

-- 3. Run the script: @rds_ash_awr_investigation.sql

-- 4. SPOOL OFF after execution.

-- 5. Open the .html file in a web browser for formatted reports.

----------------------------------------------------------------------------------------------------------------------------------------------------- -- 1. Define Bind Variables (ADJUST THESE VALUES) -------------------------------------------------------------------------------- -- 

--Declare bind variables using VAR command (client-side) 

VAR desired_timestamp TIMESTAMP; VAR ash_begin_time TIMESTAMP; VAR ash_end_time TIMESTAMP; VAR awr_begin_snap_id NUMBER; VAR awr_end_snap_id NUMBER; VAR top_n_count NUMBER; VAR generated_awr_filename VARCHAR2(256); -- New VAR for filename VAR generated_ash_filename VARCHAR2(256); -- New VAR for filename -- Execute all bind variable assignments within a single PL/SQL blockBEGIN :desired_timestamp := TO_TIMESTAMP('2025-07-09 00:30:00', 'YYYY-MM-DD HH24:MI:SS'); -- ADJUST ME! :ash_begin_time := :desired_timestamp - INTERVAL '5' MINUTE; -- ADJUST ASH WINDOW if needed:ash_end_time := :desired_timestamp + INTERVAL '5' MINUTE;  -- ADJUST ASH WINDOW if needed -- Find these snap IDs first using the "Find AWR Snapshots" query below. :awr_begin_snap_id := 12345; -- ADJUST ME! (e.g., from DBA_HIST_SNAPSHOT) :awr_end_snap_id := 12346;  -- ADJUST ME! (e.g., from DBA_HIST_SNAPSHOT) -- Define the N for TOP N queries :top_n_count := 10; DBMS_OUTPUT.PUT_LINE('-- Bind variables initialized.'); END; / -- PL/SQL block terminator -------------------------------------------------------------------------------- -- 2. Find AWR Snapshots (Run this first to get :awr_begin_snap_id and :awr_end_snap_id) -------------------------------------------------------------------------------- PROMPT -- Finding AWR Snapshots around the desired timestamp -- PROMPT -- Use these snap IDs for the :awr_begin_snap_id and :awr_end_snap_id variables above -- SET HEADING ON SET PAGESIZE 100 SELECTsnap_id, begin_interval_time, end_interval_time FROM dba_hist_snapshot WHERE begin_interval_time BETWEEN:desired_timestamp - INTERVAL '1' HOUR AND :desired_timestamp + INTERVAL '1' HOUR -- Wider window to find snaps ORDER BY begin_interval_time; SET HEADING OFF SET PAGESIZE 0 PROMPT -- Adjust :awr_begin_snap_id and :awr_end_snap_id variables based on the output above -- PROMPT -- Then re-run the script from the top. -- --------------------------------------------------------------------------------

PROMPT -- Then re-run the script from the top. --

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

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

-- 3. Generate AWR HTML Report (RDS-Adapted)

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

PROMPT -- Generating AWR HTML Report... --

PROMPT -- (This will generate a file on the RDS server, retrieve via AWS Console Logs & Events tab) --

PROMPT -- Or you can try to read it directly below after generation (if it's not too large). --

BEGIN

    -- This procedure generates the report file on the RDS instance.

    -- You cannot SELECT from it directly for output like a normal function.

    -- The output of this call will be the filename generated by RDS.

    DBMS_OUTPUT.PUT_LINE('Executing rdsadmin.rds_run_awr_report for snaps ' || :awr_begin_snap_id || ' to ' || :awr_end_snap_id || '...');

    SELECT rdsadmin.rds_run_awr_report(

               l_begin_snap => :awr_begin_snap_id,

               l_end_snap   => :awr_end_snap_id,

               l_report_type => 'HTML'

           ) INTO :generated_awr_filename FROM DUAL; -- Captures filename into a bind variable

    DBMS_OUTPUT.PUT_LINE('AWR Report generated on RDS: ' || :generated_awr_filename);

    DBMS_OUTPUT.PUT_LINE('You can download this from the AWS RDS Console (Logs & events tab).');

EXCEPTION

    WHEN OTHERS THEN

        DBMS_OUTPUT.PUT_LINE('Error generating AWR report: ' || SQLERRM);

END;

/

PROMPT -- AWR Report Generation command executed. Check AWS Console for the file. --

PROMPT -- To view content directly in SQL*Plus (if not too large), uncomment and adjust below. --

--

-- VAR awr_filename VARCHAR2(256);

-- EXEC :awr_filename := :generated_awr_filename; -- Use the filename captured above

--

-- PROMPT -- Attempting to read AWR report content directly to SQL*Plus output... --

-- SELECT text FROM TABLE(rdsadmin.rds_file_util.read_text_file('BDUMP', :awr_filename));

-- PROMPT -- End of AWR Report Content. --

--

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

-- 4. Generate ASH HTML Report (RDS-Adapted)

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

PROMPT -- Generating ASH HTML Report for ASH window :ash_begin_time to :ash_end_time... --

PROMPT -- (This will generate a file on the RDS server, retrieve via AWS Console Logs & Events tab) --

BEGIN

    -- This procedure generates the report file on the RDS instance.

    DBMS_OUTPUT.PUT_LINE('Executing rdsadmin.rds_run_ash_report for time ' || TO_CHAR(:ash_begin_time, 'YYYY-MM-DD HH24:MI:SS') || ' to ' || TO_CHAR(:ash_end_time, 'YYYY-MM-DD HH24:MI:SS') || '...');

    SELECT rdsadmin.rds_run_ash_report(

               begin_time => :ash_begin_time,

               end_time   => :ash_end_time,

               report_type => 'HTML'

           ) INTO :generated_ash_filename FROM DUAL; -- Captures filename into a bind variable

    DBMS_OUTPUT.PUT_LINE('ASH Report generated on RDS: ' || :generated_ash_filename);

    DBMS_OUTPUT.PUT_LINE('You can download this from the AWS RDS Console (Logs & events tab).');

EXCEPTION

    WHEN OTHERS THEN

        DBMS_OUTPUT.PUT_LINE('Error generating ASH report: ' || SQLERRM);

END;

/

PROMPT -- ASH Report Generation command executed. Check AWS Console for the file. --

PROMPT -- To view content directly in SQL*Plus (if not too large), uncomment and adjust below. --

--

-- VAR ash_filename VARCHAR2(256);

-- EXEC :ash_filename := :generated_ash_filename; -- Use the filename captured above

--

-- PROMPT -- Attempting to read ASH report content directly to SQL*Plus output... --

-- SELECT text FROM TABLE(rdsadmin.rds_file_util.read_text_file('BDUMP', :ash_filename));

-- PROMPT -- End of ASH Report Content. --

--

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

-- 5. DB Load Profile (Minute-by-Minute Active Sessions)

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

PROMPT -- DB Load Profile (Minute-by-Minute Active Sessions) around :desired_timestamp --

SET HEADING ON

SELECT

    TO_CHAR(sample_time, 'YYYY-MM-DD HH24:MI') AS minute_bucket,

    COUNT(*) AS total_active_sessions,

    COUNT(CASE WHEN session_state = 'ON CPU' THEN 1 ELSE NULL END) AS on_cpu_sessions,

    COUNT(CASE WHEN session_state = 'WAITING' THEN 1 ELSE NULL END) AS waiting_sessions

FROM dba_hist_active_sess_history

WHERE sample_time BETWEEN :ash_begin_time AND :ash_end_time

GROUP BY TO_CHAR(sample_time, 'YYYY-MM-DD HH24:MI')

ORDER BY minute_bucket;

SET HEADING OFF

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

-- 6. Top N Sessions Consuming CPU

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

PROMPT -- Top :top_n_count Sessions Consuming CPU around :desired_timestamp --

SET HEADING ON

SELECT

    h.session_id,

    h.session_serial#,

    u.username,

    h.program,

    h.module,

    h.sql_id,

    COUNT(*) AS cpu_samples,

    ROUND(COUNT(*) * 100 / SUM(COUNT(*)) OVER(), 2) AS "CPU_Samples_%"

FROM dba_hist_active_sess_history h

JOIN dba_users u ON h.user_id = u.user_id

WHERE h.sample_time BETWEEN :ash_begin_time AND :ash_end_time

  AND h.session_state = 'ON CPU'

GROUP BY

    h.session_id,

    h.session_serial#,

    u.username,

    h.program,

    h.module,

    h.sql_id

ORDER BY cpu_samples DESC

FETCH FIRST :top_n_count ROWS ONLY;

SET HEADING OFF

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

-- 7. Top N SQL Statements by CPU Usage

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

PROMPT -- Top :top_n_count SQL Statements by CPU Usage around :desired_timestamp --

SET HEADING ON

SELECT

    h.sql_id,

    TRUNC(COUNT(*) * 100 / (SELECT COUNT(*) FROM dba_hist_active_sess_history WHERE sample_time BETWEEN :ash_begin_time AND :ash_end_time AND session_state = 'ON CPU')) AS "CPU_Samples_%_of_Total_CPU",

    s.sql_text -- Note: SQL_TEXT is LONG, ensure SET LONG is adequate

FROM dba_hist_active_sess_history h

JOIN dba_hist_sqltext s ON h.sql_id = s.sql_id AND h.dbid = s.dbid

WHERE h.sample_time BETWEEN :ash_begin_time AND :ash_end_time

  AND h.session_state = 'ON CPU'

  AND h.sql_id IS NOT NULL

GROUP BY h.sql_id, s.sql_text

ORDER BY COUNT(*) DESC

FETCH FIRST :top_n_count ROWS ONLY;

SET HEADING OFF

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

-- 8. Top N Wait Events

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

PROMPT -- Top :top_n_count Wait Events around :desired_timestamp --

SET HEADING ON

SELECT

    h.event,

    h.wait_class,

    COUNT(*) AS wait_samples,

    ROUND(COUNT(*) * 100 / (SELECT COUNT(*) FROM dba_hist_active_sess_history WHERE sample_time BETWEEN :ash_begin_time AND :ash_end_time AND session_state = 'WAITING'), 2) AS "Wait_Samples_%"

FROM dba_hist_active_sess_history h

WHERE h.sample_time BETWEEN :ash_begin_time AND :ash_end_time

  AND h.session_state = 'WAITING'

  AND h.wait_class != 'Idle'

GROUP BY h.event, h.wait_class

ORDER BY wait_samples DESC

FETCH FIRST :top_n_count ROWS ONLY;

SET HEADING OFF

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

-- 9. Top N Users by Active Session Count

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

PROMPT -- Top :top_n_count Users by Active Session Samples around :desired_timestamp --

SET HEADING ON

SELECT

    u.username,

    COUNT(*) AS active_session_samples,

    ROUND(COUNT(*) * 100 / (SELECT COUNT(*) FROM dba_hist_active_sess_history WHERE sample_time BETWEEN :ash_begin_time AND :ash_end_time), 2) AS "Active_Samples_%"

FROM dba_hist_active_sess_history h

JOIN dba_users u ON h.user_id = u.user_id

WHERE h.sample_time BETWEEN :ash_begin_time AND :ash_end_time

GROUP BY u.username

ORDER BY active_session_samples DESC

FETCH FIRST :top_n_count ROWS ONLY;

SET HEADING OFF

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

-- 10. Top N Programs / Modules

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

PROMPT -- Top :top_n_count Programs and Modules by Active Session Samples around :desired_timestamp --

SET HEADING ON

SELECT

    h.program,

    h.module,

    COUNT(*) AS active_session_samples,

    ROUND(COUNT(*) * 100 / (SELECT COUNT(*) FROM dba_hist_active_sess_history WHERE sample_time BETWEEN :ash_begin_time AND :ash_end_time), 2) AS "Active_Samples_%"

FROM dba_hist_active_sess_history h

WHERE h.sample_time BETWEEN :ash_begin_time AND :ash_end_time

GROUP BY h.program, h.module

ORDER BY active_session_samples DESC

FETCH FIRST :top_n_count ROWS ONLY;

SET HEADING OFF

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

-- 11. Top N Accessed Objects

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

PROMPT -- Top :top_n_count Accessed Objects by Active Session Samples around :desired_timestamp --

SET HEADING ON

SELECT

    o.owner AS object_owner,

    o.object_name,

    o.object_type,

    COUNT(*) AS access_samples

FROM dba_hist_active_sess_history h

JOIN dba_objects o ON h.current_obj# = o.object_id AND h.dbid = o.owner_id

WHERE h.sample_time BETWEEN :ash_begin_time AND :ash_end_time

  AND h.current_obj# IS NOT NULL

  AND o.owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'OUTLN', 'AUDSYS', 'RDSADMIN')

  AND o.object_type IN ('TABLE', 'INDEX', 'PARTITION', 'SUBPARTITION')

GROUP BY o.owner, o.object_name, o.object_type

ORDER BY access_samples DESC

FETCH FIRST :top_n_count ROWS ONLY;

SET HEADING OFF

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

-- 12. Total Connections History (from AWR DBA_HIST_SYSSTAT)

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

PROMPT -- Total Connections History (Logons) around :desired_timestamp --

PROMPT -- (This shows logons per AWR snapshot interval, not live connections) --

SET HEADING ON

SELECT

    s.begin_interval_time,

    s.end_interval_time,

    stat.value AS total_logons_in_interval,

    ROUND(stat.value / EXTRACT(SECOND FROM (s.end_interval_time - s.begin_interval_time)), 2) AS logons_per_second

FROM dba_hist_sysstat stat

-- filename: rds_ash_awr_investigation.sql

--

-- Objective: To investigate heavy CPU hitters, session spikes, and top consumers

--            at a particular point in time using Oracle RDS AWR/ASH data.

--

-- Prerequisites:

--   - Oracle Diagnostics Pack License (Enterprise Edition).

--   - Connected to your Oracle RDS instance with sufficient privileges (e.g., rdsadmin user).

--   - Ensure SQL*Plus (or similar client) settings:

--     SET SERVEROUTPUT ON SIZE UNLIMITED

--     SET LONG 20000000 -- For full SQL text and AWR/ASH report output

--     SET PAGESIZE 0   -- No pagination

--     SET FEEDBACK OFF -- No "X rows selected" messages

--     SET HEADING OFF  -- No column headers (for report output)

--     SET TRIMSPOOL ON -- Trim trailing spaces from spool output

--

-- How to Use:

-- 1. Replace placeholder values for bind variables (e.g., :desired_timestamp, :begin_snap_id, :end_snap_id).

-- 2. (Optional but recommended for HTML reports) SPOOL the output to a .html file before executing the AWR/ASH report generation.

--    Example: SPOOL C:\temp\my_report.html

-- 3. Run the script: @rds_ash_awr_investigation.sql

-- 4. SPOOL OFF after execution.

-- 5. Open the .html file in a web browser for formatted reports.

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

-- 1. Define Bind Variables (ADJUST THESE VALUES)

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

-- Define your investigation timestamp (e.g., for yesterday 12:30 AM EST)

VAR desired_timestamp TIMESTAMP;

EXEC :desired_timestamp := TO_TIMESTAMP('2025-07-09 00:30:00', 'YYYY-MM-DD HH24:MI:SS'); -- ADJUST ME!

-- Define the time window for ASH-based queries (e.g., +/- 5 minutes around desired_timestamp)

VAR ash_begin_time TIMESTAMP;

EXEC :ash_begin_time := :desired_timestamp - INTERVAL '5' MINUTE; -- ADJUST ASH WINDOW if needed

VAR ash_end_time TIMESTAMP;

EXEC :ash_end_time := :desired_timestamp + INTERVAL '5' MINUTE; -- ADJUST ASH WINDOW if needed

-- Define the AWR snapshot IDs for AWR report generation

-- You need to find these first using the "Find AWR Snapshots" query below.

VAR awr_begin_snap_id NUMBER;

EXEC :awr_begin_snap_id := 12345; -- ADJUST ME! (e.g., from DBA_HIST_SNAPSHOT)

VAR awr_end_snap_id NUMBER;

EXEC :awr_end_snap_id := 12346; -- ADJUST ME! (e.g., from DBA_HIST_SNAPSHOT)

-- Define the N for TOP N queries

VAR top_n_count NUMBER;

EXEC :top_n_count := 10;

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

-- 2. Find AWR Snapshots (Run this first to get :awr_begin_snap_id and :awr_end_snap_id)

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

PROMPT -- Finding AWR Snapshots around the desired timestamp --

PROMPT -- Use these snap IDs for the :awr_begin_snap_id and :awr_end_snap_id variables above --

SET HEADING ON

SET PAGESIZE 100

SELECT snap_id, begin_interval_time, end_interval_time

FROM dba_hist_snapshot

WHERE begin_interval_time BETWEEN :desired_timestamp - INTERVAL '1' HOUR AND :desired_timestamp + INTERVAL '1' HOUR -- Wider window to find snaps

ORDER BY begin_interval_time;

SET HEADING OFF

SET PAGESIZE 0

PROMPT -- Adjust :awr_begin_snap_id and :awr_end_snap_id variables based on the output above --

PROMPT -- Then re-run the script from the top. --

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

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

-- 3. Generate AWR HTML Report (RDS-Adapted)

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

PROMPT -- Generating AWR HTML Report... --

PROMPT -- (This will generate a file on the RDS server, retrieve via AWS Console Logs & Events tab) --

PROMPT -- Or you can try to read it directly below after generation (if it's not too large). --

BEGIN

    -- This procedure generates the report file on the RDS instance.

    -- You cannot SELECT from it directly for output like a normal function.

    -- The output of this call will be the filename generated by RDS.

    DBMS_OUTPUT.PUT_LINE('Executing rdsadmin.rds_run_awr_report for snaps ' || :awr_begin_snap_id || ' to ' || :awr_end_snap_id || '...');

    SELECT rdsadmin.rds_run_awr_report(

               l_begin_snap => :awr_begin_snap_id,

               l_end_snap   => :awr_end_snap_id,

               l_report_type => 'HTML'

           ) INTO :generated_awr_filename FROM DUAL; -- Captures filename into a bind variable

    DBMS_OUTPUT.PUT_LINE('AWR Report generated on RDS: ' || :generated_awr_filename);

    DBMS_OUTPUT.PUT_LINE('You can download this from the AWS RDS Console (Logs & events tab).');

EXCEPTION

    WHEN OTHERS THEN

        DBMS_OUTPUT.PUT_LINE('Error generating AWR report: ' || SQLERRM);

END;

/

PROMPT -- AWR Report Generation command executed. Check AWS Console for the file. --

PROMPT -- To view content directly in SQL*Plus (if not too large), uncomment and adjust below. --

--

-- VAR awr_filename VARCHAR2(256);

-- EXEC :awr_filename := :generated_awr_filename; -- Use the filename captured above

--

-- PROMPT -- Attempting to read AWR report content directly to SQL*Plus output... --

-- SELECT text FROM TABLE(rdsadmin.rds_file_util.read_text_file('BDUMP', :awr_filename));

-- PROMPT -- End of AWR Report Content. --

--

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

-- 4. Generate ASH HTML Report (RDS-Adapted)

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

PROMPT -- Generating ASH HTML Report for ASH window :ash_begin_time to :ash_end_time... --

PROMPT -- (This will generate a file on the RDS server, retrieve via AWS Console Logs & Events tab) --

BEGIN

    -- This procedure generates the report file on the RDS instance.

    DBMS_OUTPUT.PUT_LINE('Executing rdsadmin.rds_run_ash_report for time ' || TO_CHAR(:ash_begin_time, 'YYYY-MM-DD HH24:MI:SS') || ' to ' || TO_CHAR(:ash_end_time, 'YYYY-MM-DD HH24:MI:SS') || '...');

    SELECT rdsadmin.rds_run_ash_report(

               begin_time => :ash_begin_time,

               end_time   => :ash_end_time,

               report_type => 'HTML'

           ) INTO :generated_ash_filename FROM DUAL; -- Captures filename into a bind variable

    DBMS_OUTPUT.PUT_LINE('ASH Report generated on RDS: ' || :generated_ash_filename);

    DBMS_OUTPUT.PUT_LINE('You can download this from the AWS RDS Console (Logs & events tab).');

EXCEPTION

    WHEN OTHERS THEN

        DBMS_OUTPUT.PUT_LINE('Error generating ASH report: ' || SQLERRM);

END;

/

PROMPT -- ASH Report Generation command executed. Check AWS Console for the file. --

PROMPT -- To view content directly in SQL*Plus (if not too large), uncomment and adjust below. --

--

-- VAR ash_filename VARCHAR2(256);

-- EXEC :ash_filename := :generated_ash_filename; -- Use the filename captured above

--

-- PROMPT -- Attempting to read ASH report content directly to SQL*Plus output... --

-- SELECT text FROM TABLE(rdsadmin.rds_file_util.read_text_file('BDUMP', :ash_filename));

-- PROMPT -- End of ASH Report Content. --

--

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

-- 5. DB Load Profile (Minute-by-Minute Active Sessions)

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

PROMPT -- DB Load Profile (Minute-by-Minute Active Sessions) around :desired_timestamp --

SET HEADING ON

SELECT

    TO_CHAR(sample_time, 'YYYY-MM-DD HH24:MI') AS minute_bucket,

    COUNT(*) AS total_active_sessions,

    COUNT(CASE WHEN session_state = 'ON CPU' THEN 1 ELSE NULL END) AS on_cpu_sessions,

    COUNT(CASE WHEN session_state = 'WAITING' THEN 1 ELSE NULL END) AS waiting_sessions

FROM dba_hist_active_sess_history

WHERE sample_time BETWEEN :ash_begin_time AND :ash_end_time

GROUP BY TO_CHAR(sample_time, 'YYYY-MM-DD HH24:MI')

ORDER BY minute_bucket;

SET HEADING OFF

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

-- 6. Top N Sessions Consuming CPU

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

PROMPT -- Top :top_n_count Sessions Consuming CPU around :desired_timestamp --

SET HEADING ON

SELECT

    h.session_id,

    h.session_serial#,

    u.username,

    h.program,

    h.module,

    h.sql_id,

    COUNT(*) AS cpu_samples,

    ROUND(COUNT(*) * 100 / SUM(COUNT(*)) OVER(), 2) AS "CPU_Samples_%"

FROM dba_hist_active_sess_history h

JOIN dba_users u ON h.user_id = u.user_id

WHERE h.sample_time BETWEEN :ash_begin_time AND :ash_end_time

  AND h.session_state = 'ON CPU'

GROUP BY

    h.session_id,

    h.session_serial#,

    u.username,

    h.program,

    h.module,

    h.sql_id

ORDER BY cpu_samples DESC

FETCH FIRST :top_n_count ROWS ONLY;

SET HEADING OFF

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

-- 7. Top N SQL Statements by CPU Usage

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

PROMPT -- Top :top_n_count SQL Statements by CPU Usage around :desired_timestamp --

SET HEADING ON

SELECT

    h.sql_id,

    TRUNC(COUNT(*) * 100 / (SELECT COUNT(*) FROM dba_hist_active_sess_history WHERE sample_time BETWEEN :ash_begin_time AND :ash_end_time AND session_state = 'ON CPU')) AS "CPU_Samples_%_of_Total_CPU",

    s.sql_text -- Note: SQL_TEXT is LONG, ensure SET LONG is adequate

FROM dba_hist_active_sess_history h

JOIN dba_hist_sqltext s ON h.sql_id = s.sql_id AND h.dbid = s.dbid

WHERE h.sample_time BETWEEN :ash_begin_time AND :ash_end_time

  AND h.session_state = 'ON CPU'

  AND h.sql_id IS NOT NULL

GROUP BY h.sql_id, s.sql_text

ORDER BY COUNT(*) DESC

FETCH FIRST :top_n_count ROWS ONLY;

SET HEADING OFF

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

-- 8. Top N Wait Events

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

PROMPT -- Top :top_n_count Wait Events around :desired_timestamp --

SET HEADING ON

SELECT

    h.event,

    h.wait_class,

    COUNT(*) AS wait_samples,

    ROUND(COUNT(*) * 100 / (SELECT COUNT(*) FROM dba_hist_active_sess_history WHERE sample_time BETWEEN :ash_begin_time AND :ash_end_time AND session_state = 'WAITING'), 2) AS "Wait_Samples_%"

FROM dba_hist_active_sess_history h

WHERE h.sample_time BETWEEN :ash_begin_time AND :ash_end_time

  AND h.session_state = 'WAITING'

  AND h.wait_class != 'Idle'

GROUP BY h.event, h.wait_class

ORDER BY wait_samples DESC

FETCH FIRST :top_n_count ROWS ONLY;

SET HEADING OFF

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

-- 9. Top N Users by Active Session Count

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

PROMPT -- Top :top_n_count Users by Active Session Samples around :desired_timestamp --

SET HEADING ON

SELECT

    u.username,

    COUNT(*) AS active_session_samples,

    ROUND(COUNT(*) * 100 / (SELECT COUNT(*) FROM dba_hist_active_sess_history WHERE sample_time BETWEEN :ash_begin_time AND :ash_end_time), 2) AS "Active_Samples_%"

FROM dba_hist_active_sess_history h

JOIN dba_users u ON h.user_id = u.user_id

WHERE h.sample_time BETWEEN :ash_begin_time AND :ash_end_time

GROUP BY u.username

ORDER BY active_session_samples DESC

FETCH FIRST :top_n_count ROWS ONLY;

SET HEADING OFF

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

-- 10. Top N Programs / Modules

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

PROMPT -- Top :top_n_count Programs and Modules by Active Session Samples around :desired_timestamp --

SET HEADING ON

SELECT

    h.program,

    h.module,

    COUNT(*) AS active_session_samples,

    ROUND(COUNT(*) * 100 / (SELECT COUNT(*) FROM dba_hist_active_sess_history WHERE sample_time BETWEEN :ash_begin_time AND :ash_end_time), 2) AS "Active_Samples_%"

FROM dba_hist_active_sess_history h

WHERE h.sample_time BETWEEN :ash_begin_time AND :ash_end_time

GROUP BY h.program, h.module

ORDER BY active_session_samples DESC

FETCH FIRST :top_n_count ROWS ONLY;

SET HEADING OFF

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

-- 11. Top N Accessed Objects

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

PROMPT -- Top :top_n_count Accessed Objects by Active Session Samples around :desired_timestamp --

SET HEADING ON

SELECT

    o.owner AS object_owner,

    o.object_name,

    o.object_type,

    COUNT(*) AS access_samples

FROM dba_hist_active_sess_history h

JOIN dba_objects o ON h.current_obj# = o.object_id AND h.dbid = o.owner_id

WHERE h.sample_time BETWEEN :ash_begin_time AND :ash_end_time

  AND h.current_obj# IS NOT NULL

  AND o.owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'OUTLN', 'AUDSYS', 'RDSADMIN')

  AND o.object_type IN ('TABLE', 'INDEX', 'PARTITION', 'SUBPARTITION')

GROUP BY o.owner, o.object_name, o.object_type

ORDER BY access_samples DESC

FETCH FIRST :top_n_count ROWS ONLY;

SET HEADING OFF

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

-- 12. Total Connections History (from AWR DBA_HIST_SYSSTAT)

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

PROMPT -- Total Connections History (Logons) around :desired_timestamp --

PROMPT -- (This shows logons per AWR snapshot interval, not live connections) --

SET HEADING ON

SELECT

    s.begin_interval_time,

    s.end_interval_time,

    stat.value AS total_logons_in_interval,

    ROUND(stat.value / EXTRACT(SECOND FROM (s.end_interval_time - s.begin_interval_time)), 2) AS logons_per_second

FROM dba_hist_sysstat stat

JOIN dba_hist_snapshot s ON stat.snap_id = s.snap_id AND stat.dbid = s.dbid AND stat.instance_number = s.instance_number

WHERE stat.stat_name = 'logons cumulative'

  AND s.begin_interval_time BETWEEN :desired_timestamp - INTERVAL '1' HOUR AND :desired_timestamp + INTERVAL '1' HOUR -- Adjustable window

ORDER BY s.begin_interval_time;

SET HEADING OFF

PROMPT -- Investigation script execution complete. --

PROMPT -- Remember to review the generated AWR/ASH HTML reports from the AWS Console. --

-- Reset SQL*Plus settings (optional, good practice)

-- SET PAGESIZE 14

-- SET FEEDBACK ON

-- SET HEADING ON

-- SET TRIMSPOOL OFF

-- SET LONG 80JOIN dba_hist_snapshot s ON stat.snap_id = s.snap_id AND stat.dbid = s.dbid AND stat.instance_number = s.instance_number

WHERE stat.stat_name = 'logons cumulative'

  AND s.begin_interval_time BETWEEN :desired_timestamp - INTERVAL '1' HOUR AND :desired_timestamp + INTERVAL '1' HOUR -- Adjustable window

ORDER BY s.begin_interval_time;

SET HEADING OFF

PROMPT -- Investigation script execution complete. --

PROMPT -- Remember to review the generated AWR/ASH HTML reports from the AWS Console. --

-- Reset SQL*Plus settings (optional, good practice)

-- SET PAGESIZE 14

-- SET FEEDBACK ON

-- SET HEADING ON

-- SET TRIMSPOOL OFF

-- SET LONG 80