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

AWR/ASH Queries

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

--            This revised script focuses on CPU-intensive activities, with improved

--            precision for time windows, better error handling, and corrected RDS procedures.

--

-- Key Modifications and Suggestions:

-- 1. **Procedure Calls**: Replaced undocumented 'rds_run_*' with official 'rdsadmin.rdsadmin_diagnostic_util.awr_report' and '.ash_report'.

--    These are procedures (no return value), so executed via BEGIN-END. Filenames are constructed predictably and read using 'rds_file_util.read_text_file'.

--    Added optional reading of report content (uncomment if needed; large reports may overwhelm output—use SPOOL instead).

-- 2. **Snapshot Selection**: Improved logic to select snaps that bracket the desired time more accurately (covering the period).

-- 3. **Query Enhancements**:

--    - Emphasized CPU focus: Added/strengthened filters for 'ON CPU' in relevant queries (e.g., top sessions/SQL/users).

--    - Fixed Joins: Corrected dba_hist_sqltext join (use sql_id only; dbid is for multi-DB, but in RDS it's single). For objects, removed invalid dbid=owner_id.

--    - Connections History: Changed to show delta logons (new connections in interval) using LAG for precision on spikes.

--    - Added Percentages: Ensured consistent % calculations relative to total CPU samples where applicable.

--    - Error Handling: Added basic checks (e.g., if no data, output message).

--    - Performance: Used ANSI joins, FETCH FIRST for TOP N, and avoided unnecessary subqueries.

-- 4. **Best Practices**: 

--    - Consistent bind variables and formatting for readability.

--    - Comments on each section for troubleshooting guidance.

--    - Suggest narrower ASH windows (e.g., 5-15 min) for pinpointing issues; wider for trends.

--    - If no data: Check AWR retention (DBA_HIST_WR_CONTROL) and ensure Diagnostics Pack is licensed.

--    - For RDS: Reports go to 'BDUMP' by default; download from AWS Console if reading fails.

-- 5. **Pinpointing Issues**: Queries now prioritize CPU consumers. Correlate with AWR/ASH reports for full picture (e.g., Top SQL by CPU in AWR).

--

-- 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 for pinpointing)

VAR ash_begin_time TIMESTAMP;

EXEC :ash_begin_time := :desired_timestamp - INTERVAL '5' MINUTE; -- ADJUST ASH WINDOW if needed (narrow for precision)

VAR ash_end_time TIMESTAMP;

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

-- Define the dump directory for reports (default 'BDUMP'; create custom if needed via rdsadmin.rdsadmin_util.create_directory)

VAR dump_directory VARCHAR2(30);

EXEC :dump_directory := 'BDUMP';

-- 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 suitable snap IDs)

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

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

PROMPT -- Select snaps that cover the period (begin_snap: earliest covering start; end_snap: latest covering end) --

SET HEADING ON

SET PAGESIZE 100

SELECT snap_id, begin_interval_time, end_interval_time

FROM dba_hist_snapshot

WHERE begin_interval_time <= :ash_end_time

  AND end_interval_time >= :ash_begin_time  -- Bracket the ASH window for relevance

ORDER BY begin_interval_time;

SET HEADING OFF

SET PAGESIZE 0

PROMPT -- Adjust variables below based on the output above, then re-run the script. --

-- Define the AWR snapshot IDs for AWR report generation (from above query)

VAR awr_begin_snap_id NUMBER;

EXEC :awr_begin_snap_id := 12345; -- ADJUST ME!

VAR awr_end_snap_id NUMBER;

EXEC :awr_end_snap_id := 12346; -- ADJUST ME!

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

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

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

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

DECLARE

    v_filename VARCHAR2(256);

BEGIN

    -- Construct predictable filename

    v_filename := 'awrrpt_' || :awr_begin_snap_id || '_' || :awr_end_snap_id || '.html';

    -- Generate report (procedure; no return value)

    rdsadmin.rdsadmin_diagnostic_util.awr_report(

        begin_snap => :awr_begin_snap_id,

        end_snap => :awr_end_snap_id,

        report_type => 'html',

        dump_directory => :dump_directory

    );

    DBMS_OUTPUT.PUT_LINE('AWR Report generated: ' || v_filename);

    DBMS_OUTPUT.PUT_LINE('Download from AWS RDS Console (Logs & events > Logs tab) or read below.');

    -- Optional: Read and output content (uncomment if needed; for large reports, use SPOOL and download)

    /*

    FOR rec IN (SELECT text FROM TABLE(rdsadmin.rds_file_util.read_text_file(:dump_directory, v_filename))) LOOP

        DBMS_OUTPUT.PUT_LINE(rec.text);

    END LOOP;

    */

EXCEPTION

    WHEN OTHERS THEN

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

END;

/

PROMPT -- End of AWR Report Section. --

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

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

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

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

DECLARE

    v_filename VARCHAR2(256);

BEGIN

    -- Construct predictable filename (RDS pattern: ashrpt_YYYYMMDDHH24MISS_YYYYMMDDHH24MISS.html)

    v_filename := 'ashrpt_' || TO_CHAR(:ash_begin_time, 'YYYYMMDDHH24MISS') || '_' || TO_CHAR(:ash_end_time, 'YYYYMMDDHH24MISS') || '.html';

    -- Generate report (procedure; no return value)

    rdsadmin.rdsadmin_diagnostic_util.ash_report(

        begin_time => :ash_begin_time,

        end_time => :ash_end_time,

        report_type => 'html',

        dump_directory => :dump_directory

    );

    DBMS_OUTPUT.PUT_LINE('ASH Report generated: ' || v_filename);

    DBMS_OUTPUT.PUT_LINE('Download from AWS RDS Console (Logs & events > Logs tab) or read below.');

    -- Optional: Read and output content (uncomment if needed; for large reports, use SPOOL and download)

    /*

    FOR rec IN (SELECT text FROM TABLE(rdsadmin.rds_file_util.read_text_file(:dump_directory, v_filename))) LOOP

        DBMS_OUTPUT.PUT_LINE(rec.text);

    END LOOP;

    */

EXCEPTION

    WHEN OTHERS THEN

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

END;

/

PROMPT -- End of ASH Report Section. --

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

-- 5. DB Load Profile (Minute-by-Minute Active Sessions, with CPU Focus)

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

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

SET HEADING ON

SELECT

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

    COUNT(*) AS total_active_sessions,

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

    SUM(CASE WHEN session_state = 'WAITING' THEN 1 ELSE 0 END) AS waiting_sessions,

    ROUND(SUM(CASE WHEN session_state = 'ON CPU' THEN 1 ELSE 0 END) * 100 / GREATEST(COUNT(*), 1), 2) AS pct_on_cpu

FROM dba_hist_active_sess_history

WHERE sample_time BETWEEN :ash_begin_time AND :ash_end_time

GROUP BY TRUNC(sample_time, 'MI')

ORDER BY minute_bucket;

SET HEADING OFF

PROMPT -- If on_cpu_sessions high, check CPU capacity in AWS CloudWatch. --

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

-- 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 / GREATEST(SUM(COUNT(*)) OVER(), 1), 2) AS cpu_samples_pct

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

PROMPT -- High samples indicate heavy CPU sessions; kill or tune if needed. --

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

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

    COUNT(*) AS cpu_samples,

    ROUND(COUNT(*) * 100 / GREATEST((SELECT COUNT(*) FROM dba_hist_active_sess_history WHERE sample_time BETWEEN :ash_begin_time AND :ash_end_time AND session_state = 'ON CPU'), 1), 2) AS cpu_samples_pct_of_total,

    (SELECT DBMS_LOB.SUBSTR(sql_text, 4000, 1) FROM dba_hist_sqltext WHERE sql_id = h.sql_id AND ROWNUM = 1) AS sql_text  -- Truncated for output

FROM dba_hist_active_sess_history h

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

ORDER BY cpu_samples DESC

FETCH FIRST :top_n_count ROWS ONLY;

SET HEADING OFF

PROMPT -- Tune high-CPU SQL: Add indexes, rewrite, or gather stats. Get full text via V$SQL if current. --

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

-- 8. Top N Wait Events (Focusing on Non-Idle, to Complement CPU Analysis)

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

PROMPT -- Top :top_n_count Wait Events around :desired_timestamp (If CPU not the only issue) --

SET HEADING ON

SELECT

    h.event,

    h.wait_class,

    COUNT(*) AS wait_samples,

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

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

PROMPT -- If waits high (e.g., I/O), correlate with CPU overload. --

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

-- 9. Top N Users by CPU Samples (Heavy Hitters)

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

PROMPT -- Top :top_n_count Users by CPU Samples around :desired_timestamp --

SET HEADING ON

SELECT

    u.username,

    COUNT(*) AS cpu_samples,

    ROUND(COUNT(*) * 100 / GREATEST((SELECT COUNT(*) FROM dba_hist_active_sess_history WHERE sample_time BETWEEN :ash_begin_time AND :ash_end_time AND session_state = 'ON CPU'), 1), 2) AS cpu_samples_pct

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 u.username

ORDER BY cpu_samples DESC

FETCH FIRST :top_n_count ROWS ONLY;

SET HEADING OFF

PROMPT -- Focus on top users for application tuning or quotas. --

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

-- 10. Top N Programs / Modules by CPU Samples

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

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

SET HEADING ON

SELECT

    h.program,

    h.module,

    COUNT(*) AS cpu_samples,

    ROUND(COUNT(*) * 100 / GREATEST((SELECT COUNT(*) FROM dba_hist_active_sess_history WHERE sample_time BETWEEN :ash_begin_time AND :ash_end_time AND session_state = 'ON CPU'), 1), 2) AS cpu_samples_pct

FROM dba_hist_active_sess_history h

WHERE h.sample_time BETWEEN :ash_begin_time AND :ash_end_time

  AND h.session_state = 'ON CPU'

GROUP BY h.program, h.module

ORDER BY cpu_samples DESC

FETCH FIRST :top_n_count ROWS ONLY;

SET HEADING OFF

PROMPT -- Identifies application components driving CPU. --

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

-- 11. Top N Accessed Objects by Active Samples (Potential Hotspots)

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

PROMPT -- Top :top_n_count Accessed Objects by Active 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

WHERE h.sample_time BETWEEN :ash_begin_time AND :ash_end_time

  AND h.current_obj# > 0  -- Exclude invalid/undo

  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

PROMPT -- High access may indicate contention; check indexes/stats. --

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

-- 12. New Connections (Delta Logons) History from AWR

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

PROMPT -- New Connections (Delta Logons) History around :desired_timestamp --

PROMPT -- Shows connection spikes per snapshot interval --

SET HEADING ON

WITH logons AS (

    SELECT

        s.snap_id,

        s.begin_interval_time,

        s.end_interval_time,

        stat.value AS cumulative_logons,

        LAG(stat.value) OVER (ORDER BY s.snap_id) AS prev_cumulative_logons

    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

)

SELECT

    snap_id,

    begin_interval_time,

    end_interval_time,

    GREATEST(cumulative_logons - NVL(prev_cumulative_logons, 0), 0) AS new_logons_in_interval,

    ROUND(GREATEST(cumulative_logons - NVL(prev_cumulative_logons, 0), 0) / EXTRACT(SECOND FROM (end_interval_time - begin_interval_time)), 2) AS new_logons_per_second

FROM logons

WHERE prev_cumulative_logons IS NOT NULL

ORDER BY begin_interval_time;

SET HEADING OFF

PROMPT -- High spikes may indicate connection storms; check app pooling. --

PROMPT -- Investigation script execution complete. --

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

PROMPT -- If no data in queries, verify time window and AWR retention. --

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

-- SET PAGESIZE 14

-- SET FEEDBACK ON

-- SET HEADING ON

-- SET TRIMSPOOL OFF

-- SET LONG 80

Automated AWR/ASH Report Generation

SET SERVEROUTPUT ON SIZE UNLIMITED; -- Required to see DBMS_OUTPUT

DECLARE

    -- === Input Parameters (Adjust these as needed) ===

    p_begin_time        DATE := TO_DATE('2025-07-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS'); -- Selected start time (adjust to spike start)

    p_end_time          DATE := TO_DATE('2025-07-10 06:00:00', 'YYYY-MM-DD HH24:MI:SS');   -- Selected end time (adjust to spike end)

    p_report_type_param VARCHAR2(3) := 'AWR'; -- 'AWR' or 'ASH'

    p_report_format_param VARCHAR2(4) := 'HTML'; -- 'HTML' or 'TEXT' (case-insensitive for RDS procs)

    p_dump_directory    VARCHAR2(30) := 'BDUMP'; -- Default; can change to custom directory if created

    -- Variables for snap IDs (for AWR)

    v_begin_snap_id     NUMBER;

    v_end_snap_id       NUMBER;

    -- Variable to hold the constructed filename

    v_generated_filename VARCHAR2(256);

    -- Cursor for report content retrieval

    TYPE report_line_cur_type IS REF CURSOR;

    report_line_cur report_line_cur_type;

    v_report_line   VARCHAR2(32767); -- To hold each line of the report file

    -- Formatting

    v_separator VARCHAR2(80) := RPAD('-', 80, '-');

BEGIN

    DBMS_OUTPUT.PUT_LINE(v_separator);

    DBMS_OUTPUT.PUT_LINE('-- Oracle RDS AWR/ASH Report Generator --');

    DBMS_OUTPUT.PUT_LINE('-- Analysis Period: ' || TO_CHAR(p_begin_time, 'YYYY-MM-DD HH24:MI:SS') || ' to ' || TO_CHAR(p_end_time, 'YYYY-MM-DD HH24:MI:SS'));

    DBMS_OUTPUT.PUT_LINE('-- Report Type: ' || p_report_type_param || ', Format: ' || p_report_format_param || ', Directory: ' || p_dump_directory);

    DBMS_OUTPUT.PUT_LINE(v_separator);

    DBMS_OUTPUT.PUT_LINE(' ');

    IF UPPER(p_report_type_param) = 'AWR' THEN

        -- Improved snap ID logic: Bracket the time range accurately

        BEGIN

            SELECT MIN(snap_id)

            INTO v_begin_snap_id

            FROM dba_hist_snapshot

            WHERE end_interval_time > p_begin_time;

        EXCEPTION

            WHEN NO_DATA_FOUND THEN

                v_begin_snap_id := NULL;

        END;

        BEGIN

            SELECT MAX(snap_id)

            INTO v_end_snap_id

            FROM dba_hist_snapshot

            WHERE begin_interval_time < p_end_time;

        EXCEPTION

            WHEN NO_DATA_FOUND THEN

                v_end_snap_id := NULL;

        END;

        IF v_begin_snap_id IS NULL OR v_end_snap_id IS NULL OR v_begin_snap_id > v_end_snap_id THEN

            RAISE_APPLICATION_ERROR(-20001, 'Error: Could not find AWR snapshots covering the specified time interval. ' ||

                                            'Ensure AWR retention covers the period and check time boundaries. ' ||

                                            'Begin Snap ID found: ' || NVL(TO_CHAR(v_begin_snap_id), 'N/A') ||

                                            ', End Snap ID found: ' || NVL(TO_CHAR(v_end_snap_id), 'N/A'));

        END IF;

        DBMS_OUTPUT.PUT_LINE('Generating AWR report for Snap IDs: ' || v_begin_snap_id || ' to ' || v_end_snap_id || '...');

        -- Generate AWR report (procedure call, no return value)

        EXECUTE IMMEDIATE 'BEGIN rdsadmin.rdsadmin_diagnostic_util.awr_report(' ||

                          v_begin_snap_id || ', ' ||

                          v_end_snap_id || ', ''' ||

                          UPPER(p_report_format_param) || ''', ''' ||

                          p_dump_directory || '''); END;';

        -- Construct filename (predictable pattern)

        v_generated_filename := 'awrrpt_' || v_begin_snap_id || '_' || v_end_snap_id || '.' || LOWER(p_report_format_param);

    ELSIF UPPER(p_report_type_param) = 'ASH' THEN

        -- ASH report directly uses timestamps

        DBMS_OUTPUT.PUT_LINE('Generating ASH report for time range: ' || TO_CHAR(p_begin_time, 'YYYY-MM-DD HH24:MI:SS') || ' to ' || TO_CHAR(p_end_time, 'YYYY-MM-DD HH24:MI:SS') || '...');

        -- Generate ASH report (procedure call, no return value)

        EXECUTE IMMEDIATE 'BEGIN rdsadmin.rdsadmin_diagnostic_util.ash_report(' ||

                          'TO_DATE(''' || TO_CHAR(p_begin_time, 'YYYY-MM-DD HH24:MI:SS') || ''', ''YYYY-MM-DD HH24:MI:SS''), ' ||

                          'TO_DATE(''' || TO_CHAR(p_end_time, 'YYYY-MM-DD HH24:MI:SS') || ''', ''YYYY-MM-DD HH24:MI:SS''), ''' ||

                          UPPER(p_report_format_param) || ''', ''' ||

                          p_dump_directory || '''); END;';

        -- Construct filename (predictable pattern; adjust if RDS uses different)

        v_generated_filename := 'ashrpt_' || TO_CHAR(p_begin_time, 'YYYYMMDDHH24MISS') || '_' || TO_CHAR(p_end_time, 'YYYYMMDDHH24MISS') || '.' || LOWER(p_report_format_param);

    ELSE

        RAISE_APPLICATION_ERROR(-20002, 'Invalid p_report_type_param. Use ''AWR'' or ''ASH''.');

    END IF;

    DBMS_OUTPUT.PUT_LINE('Report generated successfully on RDS file system.');

    DBMS_OUTPUT.PUT_LINE('File Name: ' || v_generated_filename);

    DBMS_OUTPUT.PUT_LINE(' ');

    DBMS_OUTPUT.PUT_LINE(v_separator);

    DBMS_OUTPUT.PUT_LINE('-- Report Content (' || UPPER(p_report_format_param) || ' starts here) --');

    DBMS_OUTPUT.PUT_LINE(v_separator);

    -- --- Retrieve and Output Report Content ---

    -- Using rdsadmin.rds_file_util.read_text_file to get the content line by line

    OPEN report_line_cur FOR

        SELECT text

        FROM TABLE(rdsadmin.rds_file_util.read_text_file(p_dump_directory, v_generated_filename));

    LOOP

        FETCH report_line_cur INTO v_report_line;

        EXIT WHEN report_line_cur%NOTFOUND;

        DBMS_OUTPUT.PUT_LINE(v_report_line);

    END LOOP;

    CLOSE report_line_cur;

    DBMS_OUTPUT.PUT_LINE('--- Report Content Ends ---');

    DBMS_OUTPUT.PUT_LINE(' ');

    DBMS_OUTPUT.PUT_LINE('NOTE: For HTML reports, download the file ' || v_generated_filename || ' from RDS Console (Logs & events -> Logs tab) and open in a web browser for proper formatting.');

EXCEPTION

    WHEN OTHERS THEN

        DBMS_OUTPUT.PUT_LINE(v_separator);

        DBMS_OUTPUT.PUT_LINE('!!! An ERROR occurred during report generation !!!');

        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);

        -- Clean up: Ensure cursor is closed if error occurs during fetch

        IF report_line_cur%ISOPEN THEN

            CLOSE report_line_cur;

        END IF;

        DBMS_OUTPUT.PUT_LINE(v_separator);

        RAISE; -- Re-raise the exception to stop execution and indicate failure

END;

/

AWR/ASH - Initial Draft

 -- Oracle Performance Troubleshooting Queries for AWS RDS

-- Run these as a privileged user (e.g., master user) in SQL*Plus, SQL Developer, or similar.

-- Adjust dates, snapshot IDs, and other parameters as needed for your environment.

-- Dates are set for July 9-10, 2025 outage example.

-- Section 1: Identify Snapshot IDs for AWR

SELECT snap_id, begin_interval_time, end_interval_time

FROM dba_hist_snapshot

WHERE begin_interval_time >= TO_DATE('2025-07-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS')

AND end_interval_time <= TO_DATE('2025-07-10 06:00:00', 'YYYY-MM-DD HH24:MI:SS')

ORDER BY snap_id;

-- Section 1.1: Generate AWR Report (Replace &begin_snap and &end_snap)

SELECT output FROM TABLE(rdsadmin.rdsadmin_diagnostic_util.awr_report(

    dbid => (SELECT dbid FROM v$database),

    inst_num => (SELECT instance_number FROM v$instance),

    begin_snap => &begin_snap,  -- e.g., 1234

    end_snap => &end_snap,      -- e.g., 1235

    report_type => 'html'       -- Or 'text'

));

-- Manually Create Snapshot if Needed

EXEC rdsadmin.rdsadmin_util.create_snapshot;

-- Section 1.2: Generate ASH Report

SELECT output FROM TABLE(rdsadmin.rdsadmin_diagnostic_util.ash_report(

    begin_time => TO_TIMESTAMP_TZ('2025-07-09 23:50:00', 'YYYY-MM-DD HH24:MI:SS'),  -- Adjust start time

    end_time => TO_TIMESTAMP_TZ('2025-07-10 00:10:00', 'YYYY-MM-DD HH24:MI:SS'),    -- Adjust end time

    report_type => 'html'  -- Or 'text'

));

-- Section 2.1: Top 10 Sessions at a Particular Period (Historical from ASH)

SELECT 

    session_id, 

    session_serial#, 

    user_id, 

    program, 

    COUNT(*) AS samples,

    ROUND(COUNT(*) * 100 / SUM(COUNT(*)) OVER(), 2) AS pct_load

FROM dba_hist_active_sess_history

WHERE sample_time BETWEEN TO_TIMESTAMP('2025-07-09 23:50:00', 'YYYY-MM-DD HH24:MI:SS') 

    AND TO_TIMESTAMP('2025-07-10 00:10:00', 'YYYY-MM-DD HH24:MI:SS')

GROUP BY session_id, session_serial#, user_id, program

ORDER BY samples DESC

FETCH FIRST 10 ROWS ONLY;

-- Real-time Top 10 Active Sessions

SELECT sid, serial#, username, program, status

FROM v$session

WHERE status = 'ACTIVE'

ORDER BY last_call_et DESC

FETCH FIRST 10 ROWS ONLY;

-- Section 2.2: Top 10 CPU-Heavy Queries (Historical from ASH)

SELECT 

    sql_id, 

    COUNT(*) AS cpu_samples,

    ROUND(COUNT(*) * 100 / SUM(COUNT(*)) OVER(), 2) AS pct_cpu

FROM dba_hist_active_sess_history

WHERE sample_time BETWEEN TO_TIMESTAMP('2025-07-09 23:50:00', 'YYYY-MM-DD HH24:MI:SS') 

    AND TO_TIMESTAMP('2025-07-10 00:10:00', 'YYYY-MM-DD HH24:MI:SS')

AND session_state = 'ON CPU'

GROUP BY sql_id

ORDER BY cpu_samples DESC

FETCH FIRST 10 ROWS ONLY;

-- Get SQL Text for a Specific sql_id (Replace &sql_id)

SELECT sql_fulltext FROM v$sql WHERE sql_id = '&sql_id';

-- Real-time Top 10 CPU-Heavy Queries

SELECT sql_id, cpu_time, executions, cpu_time/executions AS avg_cpu

FROM v$sql

ORDER BY cpu_time DESC

FETCH FIRST 10 ROWS ONLY;

-- Section 2.3: Top Wait Events (Historical from AWR, Replace &begin_snap and &end_snap)

SELECT 

    event, 

    total_waits, 

    time_waited_micro / 1000000 AS time_waited_sec,

    ROUND(time_waited_micro * 100 / SUM(time_waited_micro) OVER(), 2) AS pct_time

FROM dba_hist_system_event

WHERE snap_id BETWEEN &begin_snap AND &end_snap

AND wait_class <> 'Idle'

ORDER BY time_waited_micro DESC

FETCH FIRST 10 ROWS ONLY;

-- Real-time Top Wait Events

SELECT event, total_waits, time_waited

FROM v$system_event

WHERE wait_class <> 'Idle'

ORDER BY time_waited DESC

FETCH FIRST 10 ROWS ONLY;

-- Section 2.4: Top Users by Session Count (Real-time)

SELECT username, COUNT(*) AS session_count

FROM v$session

WHERE username IS NOT NULL

GROUP BY username

ORDER BY session_count DESC

FETCH FIRST 10 ROWS ONLY;

-- Historical Top Users by Unique Sessions

SELECT username, COUNT(DISTINCT session_id) AS unique_sessions

FROM dba_hist_active_sess_history

WHERE sample_time BETWEEN TO_TIMESTAMP('2025-07-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS') 

    AND TO_TIMESTAMP('2025-07-10 06:00:00', 'YYYY-MM-DD HH24:MI:SS')

GROUP BY username

ORDER BY unique_sessions DESC

FETCH FIRST 10 ROWS ONLY;

-- Section 2.5: Top Programs/Modules (Real-time)

SELECT program, module, COUNT(*) AS count

FROM v$session

GROUP BY program, module

ORDER BY count DESC

FETCH FIRST 10 ROWS ONLY;

-- Historical Top Programs/Modules

SELECT program, module, COUNT(*) AS samples

FROM dba_hist_active_sess_history

WHERE sample_time BETWEEN TO_TIMESTAMP('2025-07-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS') 

    AND TO_TIMESTAMP('2025-07-10 06:00:00', 'YYYY-MM-DD HH24:MI:SS')

GROUP BY program, module

ORDER BY samples DESC

FETCH FIRST 10 ROWS ONLY;

-- Section 2.6: DB Load (AAS Historical)

SELECT 

    begin_time, 

    ROUND(SUM(active_sessions) / COUNT(*), 2) AS aas

FROM (

    SELECT 

        begin_interval_time AS begin_time,

        COUNT(*) AS active_sessions

    FROM dba_hist_active_sess_history h

    JOIN dba_hist_snapshot s ON h.snap_id = s.snap_id

    WHERE s.begin_interval_time BETWEEN TO_DATE('2025-07-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS') 

        AND TO_DATE('2025-07-10 06:00:00', 'YYYY-MM-DD HH24:MI:SS')

    GROUP BY sample_id, begin_interval_time

)

GROUP BY begin_time

ORDER BY begin_time;

-- Real-time AAS (Last 5 Minutes)

SELECT ROUND(COUNT(*) / 300, 2) AS aas  -- 300 samples in 5 min (1/sec)

FROM v$active_session_history

WHERE sample_time > SYSTIMESTAMP - INTERVAL '5' MINUTE;

-- Additional: Top SQL by Elapsed Time/IO

SELECT sql_id, elapsed_time, disk_reads

FROM v$sql

ORDER BY elapsed_time DESC

FETCH FIRST 10 ROWS ONLY;

-- Buffer Cache Hit Ratio

SELECT ROUND((1 - (physical_reads / (consistent_gets + db_block_gets))) * 100, 2) AS hit_ratio

FROM v$buffer_pool_statistics;

-- Lock Contention

SELECT sid, type, id1, id2, lmode, request, block

FROM v$lock

WHERE request > 0;