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;

ASH/AWR Reports

You're looking for how to download the HTML-formatted AWR or ASH reports that you've generated in your Oracle RDS instance. Since you don't have direct operating system access on RDS, you can't just scp the files. AWS provides specific mechanisms for this.

There are two primary ways to download these reports from Oracle RDS:

1. Through the AWS RDS Console (Recommended and Easiest)

2. Using rdsadmin.rds_file_util.read_text_file from a SQL client

Let's detail each method.

---

Method 1: Downloading via the AWS RDS Console (Easiest)

This is the most common and user-friendly way. When you generate an AWR or ASH report using rdsadmin.rds_run_awr_report or rdsadmin.rds_run_ash_report (especially if you specify report_type => 'HTML'), RDS automatically makes these reports available for download in the console.

Steps:

1. Generate the Report in HTML Format:

   Make sure you specified 'HTML' as the report_type when calling the rdsadmin procedures.

   • For AWR:

     SQL

     SELECT rdsadmin.rds_run_awr_report(
                l_begin_snap => YOUR_BEGIN_SNAP_ID,
                l_end_snap   => YOUR_END_SNAP_ID,
                l_report_type => 'HTML' -- <<< IMPORTANT: Specify HTML
            ) AS AWR_REPORT_TEXT FROM DUAL;
     

   • For ASH:

     SQL

     BEGIN
         rdsadmin.rds_run_ash_report(
             begin_time => TO_TIMESTAMP('YYYY-MM-DD HH24:MI:SS', 'YYYY-MM-DD HH24:MI:SS'), -- Your start time
             end_time   => TO_TIMESTAMP('YYYY-MM-DD HH24:MI:SS', 'YYYY-MM-DD HH24:MI:SS'),   -- Your end time
             report_type => 'HTML' -- <<< IMPORTANT: Specify HTML
         );
     END;
     /
     

   After executing the above, the report will be generated and placed in a default directory managed by RDS (usually BDUMP or a specific diagnostic directory).

2. Navigate to the RDS Console:

   • Go to the AWS Management Console and navigate to RDS.

   • In the navigation pane, choose Databases.

   • Select your Oracle DB instance.

3. Go to the Logs & events Tab:

   • Click on the "Logs & events" tab.

4. Scroll Down to the "Logs" Section:

   • In the "Logs" section, you'll see a list of various log files.

5. Search for your Report File:

   • AWR reports generated in HTML will typically have names like awrrpt_BEGINSNAP_ENDSNAP.html (e.g., awrrpt_123_124.html).

   • ASH reports generated in HTML will typically have names like ashrpt_YYYYMMDDHH24MISS_YYYYMMDDHH24MISS.html (e.g., ashrpt_20250709000000_20250709010000.html).

   • You might need to use the search/filter box if you have many log files.

6. Download the Report:

   • Select the .html report file you want to download.

   • Click the "Download" button.

The file will download to your local machine, and you can then open it in any web browser.

---

Method 2: Downloading via SQL Client using rdsadmin.rds_file_util.read_text_file

This method is useful if you want to automate the retrieval, or if you prefer to stay within your SQL client, but it requires more steps and manual file creation on your local machine.

Steps:

1. Generate the Report (if not already done):

   Use the rdsadmin.rds_run_awr_report or rdsadmin.rds_run_ash_report as shown in Method 1, ensuring report_type => 'HTML'.

2. Identify the Report Filename:

   You'll need the exact filename (e.g., awrrpt_123_124.html) and the directory it's in (usually BDUMP by default for these reports). You can list files in the BDUMP directory:

   SQL

   SELECT filename, filesize, mtime
   FROM TABLE(rdsadmin.rds_file_util.listdir('BDUMP'))
   WHERE filename LIKE 'awrrpt_%.html' OR filename LIKE 'ashrpt_%.html'
   ORDER BY mtime DESC;
   

3. Read the HTML Content using SQL:

   The rdsadmin.rds_file_util.read_text_file function reads the content of the file.2 You'll need to spool this output to a local file.

   • For SQL*Plus (recommended for this method):

     SQL

     -- Set output formatting to prevent line breaks and headers in the HTML content
     SET HEADING OFF
     SET FEEDBACK OFF
     SET PAGESIZE 0
     SET LINESIZE 32767 -- Max line size to avoid wrapping HTML
     SET LONG 32767      -- Max long for CLOB output
     SET TRIMSPOOL ON    -- Trim trailing spaces
     
     -- Spool the output to a local HTML file
     SPOOL C:\Path\To\Your\Report\awrrpt_123_124.html -- <<< CHANGE THIS PATH AND FILENAME
     
     SELECT text
     FROM TABLE(rdsadmin.rds_file_util.read_text_file('BDUMP', 'awrrpt_123_124.html')); -- <<< CHANGE FILENAME
     
     SPOOL OFF
     SET HEADING ON
     SET FEEDBACK ON
     SET PAGESIZE 14
     SET LINESIZE 80 -- Reset your SQL*Plus settings
     

   • For SQL Developer/Toad:

     You might need to copy the CLOB output directly from the query result grid and paste it into a text editor, then save it as an .html file. This is more manual than SPOOL but works if SPOOL isn't an option or you prefer the GUI.

4. Open the HTML File:

   Once saved to your local machine, open the .html file with your preferred web browser.

You've provided a very good set of SQL queries targeting dba_hist_active_sess_history and dba_hist_snapshot, which are the core views for AWR and ASH data. These queries are excellent starting points for a detailed performance investigation.

However, since you're operating in an AWS RDS for Oracle environment, there are crucial considerations and necessary adjustments. Oracle RDS for Oracle restricts direct access to some DBMS_WORKLOAD_REPOSITORY functions and requires the use of rdsadmin specific packages for generating AWR/ASH reports and interacting with diagnostic files.

Here's my assessment and how to "tune" (adapt) them for your RDS environment and general best practices:

General Assessment:

• Good Starting Point: The logic in each query directly targets the relevant performance metrics.

• ASH-Focused: Most of the queries hit dba_hist_active_sess_history, which is excellent for detailed, granular analysis during spikes.

• Missing Bind Variables for FETCH FIRST: While FETCH FIRST N ROWS ONLY is good, a bind variable could make it more flexible.

• No Schema-Specific Filtering: For multi-tenant or multi-application databases, filtering by schema/user could be beneficial (though user_id is selected in one query).

• DBID and Instance Number for AWR: DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTMLtypically requires DBID and instance number. For RDS, the instance number is usually 1, and DBID can be retrieved from v$database.

---

Detailed Review and Tuning for Oracle RDS:

Let's go through each query, assess it, and provide the RDS-adapted version or tuning tips.

Crucial RDS Note: In RDS, you cannot directly call DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML as a SELECT statement directly in SQL*Plus/SQL Developer that outputs HTML. You must use rdsadmin.rds_run_awr_report for report generation, and then retrieve the report from the RDS Console or using rdsadmin.rds_file_util.read_text_file.

---

1. 01_awr_generate_html.sql - Generate AWR HTML Report

• Original Query:

  SQL

  SELECT output
  FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
      (SELECT dbid FROM v$database),
      1, -- Instance Number
      :begin_snap_id,
      :end_snap_id
  ));
  

• Assessment:

  • Problematic for RDS: Direct invocation of DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML in this TABLE() format is generally not supported for direct output in RDS.

  • Correct Parameters: The use of DBID, Instance Number, begin_snap_id, end_snap_id is correct for AWR.

• Tuning/RDS Adaptation:

  • You must use the rdsadmin.rds_run_awr_report procedure. This procedure places the generated report file in a diagnostic directory on the RDS instance, which you then download via the AWS Console or read via rdsadmin.rds_file_util.read_text_file.

  • Recommendation:

    SQL

    -- RDS-Adapted: Generate AWR HTML Report
    -- This procedure will place the awrrpt_...html file in a diagnostic directory on RDS.
    -- You will then download it from the AWS RDS Console (Logs & events -> Logs) or read it using rdsadmin.rds_file_util.read_text_file.
    BEGIN
        rdsadmin.rds_run_awr_report(
            l_begin_snap => :begin_snap_id, -- Bind variable for AWR begin snapshot ID
            l_end_snap   => :end_snap_id,   -- Bind variable for AWR end snapshot ID
            l_report_type => 'HTML'         -- Specify HTML output
        );
    END;
    /
    

  • To Retrieve: Use AWS RDS Console (Logs & events tab) or SELECT text FROM TABLE(rdsadmin.rds_file_util.read_text_file('BDUMP', 'awrrpt_YOUR_BEGIN_SNAP_ID_YOUR_END_SNAP_ID.html')); (using SPOOL for SQL*Plus).

---

2. 02_top_10_sessions_cpu.sql - Top 10 sessions consuming CPU

• Original Query:

  SQL

  SELECT session_id, session_serial#, COUNT(*) AS samples
  FROM dba_hist_active_sess_history
  WHERE sample_time BETWEEN :begin_time AND :end_time
    AND session_state = 'ON CPU'
  GROUP BY session_id, session_serial#
  ORDER BY samples DESC
  FETCH FIRST 10 ROWS ONLY;
  

• Assessment:

  • Excellent: Directly targets CPU-consuming sessions using session_state = 'ON CPU'. FETCH FIRST 10 ROWS ONLY is good.

• Tuning/RDS Adaptation:

  • Include more identifying columns: PROGRAM, MODULE, USERNAME, SQL_ID. This makes the output much more useful for debugging.

  • Join V$SESSION (if current) or DBA_USERS: To get the username from user_id.

  • Recommendation:

    SQL

    -- Top N sessions consuming CPU during a specific period
    SELECT
        h.session_id,
        h.session_serial#,
        u.username,
        h.program,
        h.module,
        h.sql_id,
        COUNT(*) AS cpu_samples -- Renamed alias for clarity
    FROM dba_hist_active_sess_history h
    JOIN dba_users u ON h.user_id = u.user_id
    WHERE h.sample_time BETWEEN :begin_time AND :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 10 ROWS ONLY; -- Use a bind variable here if you want: FETCH FIRST :num_rows ROWS ONLY
    

---

3. 03_top_10_sqls_cpu.sql - Top 10 SQLs by CPU

• Original Query:

  SQL

  SELECT sql_id, COUNT(*) AS samples
  FROM dba_hist_active_sess_history
  WHERE sample_time BETWEEN :begin_time AND :end_time
    AND session_state = 'ON CPU'
  GROUP BY sql_id
  ORDER BY samples DESC
  FETCH FIRST 10 ROWS ONLY;
  

• Assessment:

  • Good: Correctly identifies CPU-bound SQL.

• Tuning/RDS Adaptation:

  • Get the SQL text: This is essential for understanding the query. You can join DBA_HIST_SQLTEXT.

  • Consider total elapsed time: While CPU is a focus, sometimes a query is high CPU and high elapsed time due to other waits.

  • Recommendation:

    SQL

    -- Top N SQLs by CPU consumption during a specific period
    SELECT
        h.sql_id,
        s.sql_text, -- Get SQL text
        COUNT(*) AS cpu_samples
    FROM dba_hist_active_sess_history h
    JOIN dba_hist_sqltext s ON h.sql_id = s.sql_id AND h.dbid = s.dbid -- Join to get SQL text
    WHERE h.sample_time BETWEEN :begin_time AND :end_time
      AND h.session_state = 'ON CPU'
      AND h.sql_id IS NOT NULL -- Exclude background processes/non-SQL activity
    GROUP BY h.sql_id, s.sql_text
    ORDER BY cpu_samples DESC
    FETCH FIRST 10 ROWS ONLY; -- Use a bind variable here if you want: FETCH FIRST :num_rows ROWS ONLY
    

  • Note on SQL_TEXT: DBA_HIST_SQLTEXT.SQL_TEXT is a LONG datatype. In many SQL clients, you might need to set SET LONG XXX (e.g., SET LONG 20000) to retrieve the full text, or use DBMS_METADATA.GET_DDL for SQL statements if available and if you have the hash value/address. For ASH, the SQL_TEXT in DBA_HIST_SQLTEXT is often sufficient.

---

4. 04_top_wait_events.sql - Top wait events

• Original Query:

  SQL

  SELECT event, COUNT(*) AS waits
  FROM dba_hist_active_sess_history
  WHERE sample_time BETWEEN :begin_time AND :end_time
    AND session_state = 'WAITING'
  GROUP BY event
  ORDER BY waits DESC
  FETCH FIRST 10 ROWS ONLY;
  

• Assessment:

  • Good: Directly identifies top wait events.

• Tuning/RDS Adaptation:

  • Consider wait class: Grouping by wait_class can provide a higher-level view of the bottleneck (e.g., 'User I/O', 'Concurrency', 'Commit').

  • Recommendation:

    SQL

    -- Top N wait events during a specific period
    SELECT
        h.event,
        h.wait_class, -- Include wait class
        COUNT(*) AS wait_samples
    FROM dba_hist_active_sess_history h
    WHERE h.sample_time BETWEEN :begin_time AND :end_time
      AND h.session_state = 'WAITING'
      AND h.wait_class != 'Idle' -- Exclude idle waits
    GROUP BY h.event, h.wait_class
    ORDER BY wait_samples DESC
    FETCH FIRST 10 ROWS ONLY; -- Use a bind variable if desired
    

---

5. 05_top_users.sql - Top users by session count

• Original Query:

  SQL

  SELECT user_id, COUNT(*) AS active_sessions
  FROM dba_hist_active_sess_history
  WHERE sample_time BETWEEN :begin_time AND :end_time
  GROUP BY user_id
  ORDER BY active_sessions DESC;
  

• Assessment:

  • Good: Identifies active users.

• Tuning/RDS Adaptation:

  • Get username: The user_id is less readable than the username.

  • Recommendation:

    SQL

    -- Top N users by active session samples during a specific period
    SELECT
        u.username,
        COUNT(*) AS active_session_samples -- Renamed for clarity (it's active session samples, not raw count)
    FROM dba_hist_active_sess_history h
    JOIN dba_users u ON h.user_id = u.user_id
    WHERE h.sample_time BETWEEN :begin_time AND :end_time
    GROUP BY u.username
    ORDER BY active_session_samples DESC
    FETCH FIRST 10 ROWS ONLY; -- Use a bind variable if desired
    

---

6. 06_top_modules_programs.sql - Top programs and modules

• Original Query:

  SQL

  SELECT program, COUNT(*) AS samples
  FROM dba_hist_active_sess_history
  WHERE sample_time BETWEEN :begin_time AND :end_time
  GROUP BY program
  ORDER BY samples DESC;
  

• Assessment:

  • Good: Identifies programs.

• Tuning/RDS Adaptation:

  • Include module: Often, MODULE provides more granular detail than PROGRAM.

  • Recommendation:

    SQL

    -- Top N programs and modules by active session samples during a specific period
    SELECT
        h.program,
        h.module, -- Include module for more detail
        COUNT(*) AS active_session_samples
    FROM dba_hist_active_sess_history h
    WHERE h.sample_time BETWEEN :begin_time AND :end_time
    GROUP BY h.program, h.module
    ORDER BY active_session_samples DESC
    FETCH FIRST 10 ROWS ONLY; -- Use a bind variable if desired
    

---

7. 07_db_load.sql - DB Load profile

• Original Query:

  SQL

  SELECT TO_CHAR(sample_time, 'YYYY-MM-DD HH24:MI') AS minute,
         COUNT(*) AS active_sessions
  FROM dba_hist_active_sess_history
  WHERE sample_time BETWEEN :begin_time AND :end_time
  GROUP BY TO_CHAR(sample_time, 'YYYY-MM-DD HH24:MI')
  ORDER BY minute;
  

• Assessment:

  • Excellent: Provides a minute-by-minute (or second-by-second if you adapt TO_CHAR) view of active sessions, directly showing the load profile over time.

• Tuning/RDS Adaptation:

  • Recommendation: Keep as is. This query is fundamental for visualizing load spikes from ASH. You could get more granular with seconds if needed: TO_CHAR(sample_time, 'YYYY-MM-DD HH24:MI:SS').

---

8. 08_blocking_sessions.sql - Blocking sessions

• Original Query:

  SQL

  SELECT blocking_session, session_id, session_serial#, COUNT(*) AS samples
  FROM dba_hist_active_sess_history
  WHERE sample_time BETWEEN :begin_time AND :end_time
    AND blocking_session IS NOT NULL
  GROUP BY blocking_session, session_id, session_serial#
  ORDER BY samples DESC;
  

• Assessment:

  • Good: Identifies blocking chains from ASH.

• Tuning/RDS Adaptation:

  • Get more info on blocking/blocked sessions: Username, program, SQL ID.

  • Recommendation:

    SQL

    -- Top N blocking/blocked session pairs by active samples
    SELECT
        h.blocking_session,
        u_blocker.username AS blocking_username,
        h.session_id,
        h.session_serial#,
        u_blocked.username AS blocked_username,
        h.sql_id AS blocked_sql_id,
        h.event AS blocked_wait_event,
        COUNT(*) AS samples
    FROM dba_hist_active_sess_history h
    LEFT JOIN dba_users u_blocker ON h.blocking_session_id = u_blocker.user_id -- Note: blocking_session_id exists in ASH
    LEFT JOIN dba_users u_blocked ON h.user_id = u_blocked.user_id
    WHERE h.sample_time BETWEEN :begin_time AND :end_time
      AND h.blocking_session IS NOT NULL -- This implies it's a session being blocked
    GROUP BY
        h.blocking_session,
        u_blocker.username,
        h.session_id,
        h.session_serial#,
        u_blocked.username,
        h.sql_id,
        h.event
    ORDER BY samples DESC
    FETCH FIRST 10 ROWS ONLY; -- Use a bind variable if desired
    

  • Note: blocking_session_id is the correct column for the ID of the blocking session in dba_hist_active_sess_history.

---

9. 09_top_objects.sql - Top accessed objects

• Original Query:

  SQL

  SELECT o.object_name, o.object_type, COUNT(*) AS access_count
  FROM dba_hist_active_sess_history h
  JOIN dba_objects o ON h.current_obj# = o.object_id
  WHERE h.sample_time BETWEEN :begin_time AND :end_time
  GROUP BY o.object_name, o.object_type
  ORDER BY access_count DESC;
  

• Assessment:

  • Good: Identifies hot objects.

• Tuning/RDS Adaptation:

  • Include owner: Crucial for unique identification of objects across schemas.

  • Filter for relevant object types: You might only be interested in TABLE or INDEX.

  • Recommendation:

    SQL

    -- Top N accessed objects by active session samples
    SELECT
        o.owner AS object_owner, -- Include 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 -- Add dbid/owner_id to join for robustness
    WHERE h.sample_time BETWEEN :begin_time AND :end_time
      AND h.current_obj# IS NOT NULL
      AND o.owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'OUTLN', 'AUDSYS', 'RDSADMIN') -- Exclude internal Oracle objects
      AND o.object_type IN ('TABLE', 'INDEX', 'PARTITION', 'SUBPARTITION') -- Focus on common types
    GROUP BY o.owner, o.object_name, o.object_type
    ORDER BY access_samples DESC
    FETCH FIRST 10 ROWS ONLY; -- Use a bind variable if desired
    

---

By using these adapted queries, you'll be able to extract much richer and more contextual information from your Oracle RDS AWR/ASH data, enabling a more precise performance diagnosis. Remember to always provide the correct bind variables (:begin_time, :end_time, :begin_snap_id, :end_snap_id) when executing these queries.