A Query Tuning

Introduction

When a query slows down, you’re not powerless—and you’re not the first person to encounter a slow query. Most SQL performance problems are fixable, and this guide gives you a way to start. This isn’t about blame. It's about control. If you start with the premise that it is something you did that slowed down the query, then you follow up with the premise there is something you can do to speed it up. And by doing so, you’ll gain something even more valuable: the ability to diagnose and avoid problems earlier next time. Even if this is your first slow query, it won’t be your last. The process you follow here—understanding how SQL executes, capturing a plan, identifying where things go off track—will make you faster and better at writing efficient queries in the future. You’ll stop performance issues before they start. Taking ownership doesn’t mean going it alone—it means being in control. It means asking better questions, testing smarter ideas, and sometimes solving the issue before it becomes a roadblock.

This guide helps you do that. It gives you the tools, mindset, and examples you need to take control of performance and build that skill for good.

How SQL Executes - A Conceptual Overview

Most performance problems begin with a SQL statement. Understanding how SQL runs in a relational database gives you the foundation to troubleshoot when things go wrong.

This overview helps you:

• Grasp the core moving parts behind SQL performance.
• Understand where problems originate.
• Decide what to check first.

Relational Databases: What You Need to Know

Relational databases like Oracle are declarative, set-based engines. You tell them what data you want (using SQL), and the database figures out how to retrieve it. The execution is driven by a component called the optimizer, which chooses the most efficient plan it can—based on statistics, indexes, and data volume.

Every SQL statement is turned into a plan composed of a small number of operations:

• Table access (full scan or index)
• Join (nested loop, hash, merge)
• Filter
• Aggregate
• Sort

The plan is what determines performance.

A Mental Model You Can Use

Here’s a simplified way to think about SQL execution, especially if you come from an algorithm or systems background:

• SQL is a request.
• The optimizer is a planner.
• The execution plan is a strategy.
• Execution is a parallel, resource-aware run of that strategy.

When performance suffers, one or more of these steps failed to line up with reality.

Where Problems Show Up

Problem Area	What’s Happening Under the Hood
Query runs forever	Bad access path (for example: full table scan)
Query never returns	Execution stuck on lock, contention, or temp space
Query used to be fast	Execution plan changed (due to stats, bind peeking)
Query runs fast once	Plan got cached or parallelism kicked in by chance
System is overloaded	Too many concurrent queries, bad query patterns

Start Here: Performance Triage

Use this decision tree to get oriented quickly:

Is the problem with one query or the whole system?

• One query is slow
  • Capture the SQL_ID
  • View the execution plan
  • Look for bad access paths, joins, temp usage
  • See: Diagnosing a Slow Query
• Multiple queries are slow
  • Check system resource usage (CPU, I/O, concurrency)
  • Identify heavy queries in AWR or SQL Monitor
  • See: Diagnosing System-Wide Performance Issues
• Query performance regressed recently
  • Check plan history (DBA_HIST_SQL_PLAN)
  • See if optimizer stats changed or binds differ
  • See: Understanding Plan Instability

Next Step

Pick the path that matches your situation and go straight there. You don’t have to understand everything about Oracle SQL internals—but the more you understand, the better you’ll be able to reason about performance.

To start that process, you need a baseline.

Baselining Your Query

Before you ask “Is my query slow?” you need to ask a different question:

How does this query normally behave?

A baseline gives you a point of comparison. It’s the only way to tell whether performance is degrading, improving, or holding steady. Without one, everything is just a hunch.

Why Baselining Matters

Most performance issues don’t come out of nowhere. They evolve—slowly, then suddenly. Baselining helps you:

• Detect regressions early
• Justify changes or rollback decisions
• Compare across environments (for example, dev vs. prod)
• Communicate clearly with support

Baseline Tuning

A functional query is not necessarily a fast query. You run your first baseline once you have formulated a functional query: a query that returns the expected result. You run your baseline against representative data volumes on a mostly quiet system. Your baseline is the fastest you can ever expect your query to run. Higher system loading will only slow your query down. In any case, your first baseline may be your first indication that you have a problem. Specifically, your baseline fails to meet performance expectations. So your first baseline collection may evolve into a tuning effort.

What to Capture

For any business-critical or frequently executed query, record the following:

Metric	How to Get It (GV$SQL)
SQL_ID	SELECT sql_id FROM gv$sql WHERE sql_text LIKE '%...%'
Elapsed time (avg)	elapsed_time / executions
Rows processed	rows_processed / executions
Buffer gets	Logical I/O (work done)
Disk reads	Physical I/O (slower)
Plan hash value	Unique ID for execution plan

If the query is already running, use DBMS_SQL_MONITOR or DBMS_XPLAN.DISPLAY_CURSOR to capture real-time stats.

A SQL ID is a unique identifier for a SQL statement in Oracle. It is constructed by hashing the text of the SQL statement.

The SQL ID is generated using a hash function that takes the SQL statement text as input. The resulting hash value is then converted to a 13-character string, which is the SQL ID.

The hashing algorithm used to generate the SQL ID is not publicly documented, but it is designed to produce a unique identifier for each distinct SQL statement.

Here are some key points to note about SQL IDs:

1. Case sensitivity: SQL IDs are case-sensitive, so the same SQL statement with different casing will produce different SQL IDs.
2. Whitespace and formatting: SQL IDs are sensitive to whitespace and formatting, so the same SQL statement with different formatting will produce different SQL IDs.
3. Literal values: SQL IDs are sensitive to literal values, so the same SQL statement with different literal values will produce different SQL IDs.
4. Bind variables: If a SQL statement uses bind variables, the SQL ID will be the same regardless of the values bound to the variables.

When to Baseline

• Before go-live
• After a major schema or stats change
• After tuning
• Periodically for high-impact queries

Storing baselines in a spreadsheet or performance table gives you history that support or DevOps can reference later.

Baseline Template (Example)

SQL_ID	Date	Avg Elapsed (s)	Avg Rows	Avg Buff Gets	Avg Disk Reads	Plan Hash
abcd1234	2025-05-02	2.8	14,200	28,000	64	872349876

Tip:

Automate this for key queries with a scheduled job.

If you care about a query, baseline it. You’ll thank yourself later when something changes—and you need to prove it.

Baseline SQL Script (for Recent Performance Snapshot)

Here’s a sample SQL script to baseline a query by SQL_ID, returning key performance metrics that you can copy into Excel, store in a logging table, or track over time:

SELECT
    sql_id,
    plan_hash_value,
    TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS baseline_date,
    ROUND(elapsed_time/1000000, 2) AS total_elapsed_sec,
    executions,
    ROUND((elapsed_time/1000000) / NULLIF(executions, 0), 2) AS avg_elapsed_sec,
    rows_processed,
    ROUND(rows_processed / NULLIF(executions, 0)) AS avg_rows,
    buffer_gets,
    ROUND(buffer_gets / NULLIF(executions, 0)) AS avg_buffer_gets,
    disk_reads,
    ROUND(disk_reads / NULLIF(executions, 0)) AS avg_disk_reads
FROM
    gv$sql
WHERE
    sql_id = '<your SQL_ID>'
ORDER BY
    last_active_time DESC
FETCH FIRST 1 ROWS ONLY;

Output Columns

Column	Description
sql_id	Identifier of the query
plan_hash_value	Unique ID for the current execution plan
baseline_date	Date when the baseline was captured
total_elapsed_sec	Total time across all executions
executions	Number of times the query ran
avg_elapsed_sec	Average execution time per run
avg_rows	Average rows returned
avg_buffer_gets	Logical I/O (how much work Oracle did)
avg_disk_reads	Physical I/O (slowest part of query access)

Baselining Procedure

Here’s how to automate nightly baseline logging for important SQL statements by maintaining a list of SQL_IDs and looping through them in a job.

Step 1: Create a Tracking Table

CREATE TABLE sql_baseline_log (
    log_id           NUMBER GENERATED ALWAYS AS IDENTITY,
    log_date         DATE DEFAULT SYSDATE,
    sql_id           VARCHAR2(13),
    plan_hash_value  NUMBER,
    executions       NUMBER,
    total_elapsed_s  NUMBER(10,2),
    avg_elapsed_s    NUMBER(10,2),
    rows_processed   NUMBER,
    avg_rows         NUMBER,
    buffer_gets      NUMBER,
    avg_buffer_gets  NUMBER,
    disk_reads       NUMBER,
    avg_disk_reads   NUMBER,
    PRIMARY KEY (log_id)
);

Step 2: Create the Baselining Procedure

CREATE OR REPLACE PROCEDURE log_sql_baseline(p_sql_id IN VARCHAR2) AS
BEGIN
    INSERT INTO sql_baseline_log (
        sql_id, plan_hash_value, executions,
        total_elapsed_s, avg_elapsed_s,
        rows_processed, avg_rows,
        buffer_gets, avg_buffer_gets,
        disk_reads, avg_disk_reads
    )
    SELECT
        sql_id,
        plan_hash_value,
        executions,
        ROUND(elapsed_time / 1e6, 2),
        ROUND(elapsed_time / 1e6 / NULLIF(executions, 0), 2),
        rows_processed,
        ROUND(rows_processed / NULLIF(executions, 0)),
        buffer_gets,
        ROUND(buffer_gets / NULLIF(executions, 0)),
        disk_reads,
        ROUND(disk_reads / NULLIF(executions, 0))
    FROM
        gv$sql
    WHERE
        sql_id = p_sql_id
    ORDER BY
        last_active_time DESC
    FETCH FIRST 1 ROWS ONLY;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Baseline logging failed: ' || SQLERRM);
END;

Step 3: Example Call

BEGIN
    log_sql_baseline('abcdef1234567');  -- Replace with your SQL_ID
END;

Optional: Automate It Nightly for Key Queries

You can maintain a list of important SQL_IDs in a table and loop through them in a scheduled job. Here’s how to automate nightly baseline logging for important SQL statements by maintaining a list of SQL_IDs and looping through them in a job.

Step 1: Create a Tracking Table

CREATE TABLE tracked_sql_ids (
    sql_id         VARCHAR2(13) PRIMARY KEY,
    description    VARCHAR2(200)
);

Insert the SQL_IDs of the queries you care about:

INSERT INTO tracked_sql_ids (sql_id, description)
VALUES ('abcdef1234567', 'Critical dashboard query');

COMMIT;

Step 2: Create a Batch Baselining Procedure

CREATE OR REPLACE PROCEDURE baseline_all_tracked_sql IS
BEGIN
    FOR r IN (SELECT sql_id FROM tracked_sql_ids) LOOP
        BEGIN
            log_sql_baseline(r.sql_id);
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('Error logging ' || r.sql_id || ': ' || SQLERRM);
        END;
    END LOOP;
END;

This wraps your log_sql_baseline procedure and runs it for each tracked SQL_ID.

Step 3: Optional: Schedule the Job (DBMS_SCHEDULER)

BEGIN
    DBMS_SCHEDULER.create_job (
        job_name        => 'baseline_tracked_sql',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN baseline_all_tracked_sql; END;',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=DAILY;BYHOUR=2',  -- runs at 2 AM
        enabled         => TRUE
    );
END;

You now have:

• A log of query performance over time.
• A foundation for spotting regressions.
• A process that runs nightly with minimal overhead.

Is My Query Slow?

Before jumping into diagnosis, it’s worth asking: is the query actually slow—or just slower than expected? The goal of this section is to help you assess whether a query meets your requirements or needs tuning.

Start with your Requirements

Not all queries need to be fast. Some run in the background or during batch windows. Others support REST services, dashboards, or interactive user flows—and those do have time limits. If you’re running in a REST service (including Oracle REST Data Services, APEX APIs, or external integrations), Oracle enforces a 300-second (5 minute) timeout. That includes SQL or PL/SQL blocks.

Rule of thumb:

If your query supports a REST service or user interaction, it must complete in under 300 seconds. Otherwise, it will fail.

How to Think About "Fast Enough"

Ask yourself:

• Is this query blocking a user?
  • Yes - Aim for sub-second to low-second response times.
• Is this query supporting a dashboard or interactive page?
  • Yes - Sub-5 seconds preferred; sub-30 seconds may be acceptable with feedback (for example, spinner).
• Is it a background report or batch process?
  • Yes - Performance is relative. It may be fine as-is unless it’s consuming too many resources.

Measuring Execution Time

Even if the user reports that “it’s slow,” confirm it:

SELECT elapsed_time/1000000 AS seconds, executions
FROM gv$sql
WHERE sql_id = '<your-sql-id>';

This shows average time per execution. One run taking 90 seconds may not be a problem—unless it’s being called hundreds of times per hour or runs in a timeout-sensitive context.

Don't Tune Without a Target

Tuning without knowing your goal leads to wasted time. Instead, start with:

• Functional context: Who/what depends on this query?
• Frequency: How often does it run?
• Failure mode: Has it ever hit a timeout or caused a system issue?

If the answer to any of those is yes—or if the user experience is impacted—then move on to Diagnosing a Slow Query.

Diagnosing a Slow Query

When a query is slow, the key is to work from facts—not guesses. You don’t need to be an expert in Oracle internals, but you do need to capture what the database is doing and compare that to what you expected. This section walks you through a simple and repeatable process.

Step 1: Get the SQL_ID

Before you do anything else, identify the query using its SQL_ID. You can get it:

• From the application or APEX session logs
• Using a query on recent activity:

  SELECT sql_id, sql_text
  FROM gv$sql
  WHERE sql_text LIKE '%target_table%'
  AND last_active_time > SYSDATE - 1/24
  ORDER BY last_active_time DESC;

Step 2: Get the Execution Plan

Once you have the SQL_ID, pull the most recent execution plan. This tells you how Oracle is trying to run the query.

SELECT * 
FROM table(DBMS_XPLAN.DISPLAY_CURSOR('<your-sql-id>', NULL, 'ALLSTATS LAST'));

This gives you the final plan from the most recent run, including row estimates vs. actual rows, I/O, and temp usage.

Step 3: Ask These Questions

Review the execution plan and look for red flags:

Question	What to Look For
Are there full table scans?	Look for TABLE ACCESS FULL on large tables
Are joins using hash or nested loops?	Hash joins are usually good for large inputs. Nested loops on big tables may be slow.
Are estimated rows way off from actual rows?	That means bad stats or data skew.
Is there temp usage?	Indicates a sort, hash join, or aggregation that didn’t fit in memory.
Is the plan parallel?	Look for PX steps. If it’s not using parallel and it should, the query may be underutilizing the ADW architecture.

Step 4: Check for Waits

If the plan looks okay, check what the query is waiting on:

Current Waits

SELECT event, wait_class, time_waited, sid
FROM gv$session
WHERE sql_id = '<your-sql-id>';

Recent Waits

SELECT event, wait_class, time_waited, session_id
FROM gv$active_session_history
WHERE sql_id = '<your-sql-id>';

Look for:

• db file sequential read: Index access (can be okay)
• db file scattered read: Full table scan
• direct path read temp: Temp usage, probably from a spill
• enq: TX: Locking issues

What's a Spill

A spill occurs when operations like sorts, joins, or aggregations require more memory than is available, causing intermediate data to be written to temporary disk storage instead. This typically happens in the temporary tablespace and can significantly slow down performance due to increased disk I/O. Spills indicate that a query exceeded its in-memory processing capacity and may benefit from SQL tuning.

Step 5: Cross Check Stats

Bad estimates can lead to bad plans. Check the table and column stats:

SELECT table_name, last_analyzed, num_rows, owner
FROM dba_tables
WHERE table_name = '<target-table>';

Also check for histograms:

SELECT column_name, histogram, num_distinct, owner
FROM dba_tab_col_statistics
WHERE table_name = '<target-table>';

If LAST_ANALYZED is old or there are no histograms on skewed columns, you may want to gather fresh stats.

What's a Skewed Column

A skewed column is a column where some values appear much more frequently than others—creating an uneven distribution of data. In a well-distributed column, values are fairly balanced. In a skewed column, a few values dominate the data.

Why Skew Matters

The Oracle optimizer relies on statistics to estimate how many rows a query will return. If it thinks all values are equally likely (uniform distribution), it may pick a bad plan:

• Use a full table scan instead of an index.

• Choose the wrong join method.

• Under- or over-allocate memory.

How Oracle Handles Skew

When Oracle detects skew, it can collect a histogram to model the actual frequency of column values. This helps the optimizer make better decisions, especially in bind-sensitive or filter-heavy queries.

You can check for skewed columns using:

SELECT column_name, histogram, num_distinct
FROM dba_tab_col_statistics
WHERE table_name = '<target-table>' AND owner = '<my-owner>';

Look for FREQUENCY or TOP-FREQUENCY histograms—that means Oracle found skew and is tracking it.

Step 6: Consider Plan History

Plans can change. To see what changed and when:

SELECT *
FROM dba_hist_sql_plan
WHERE sql_id = '<your-sql-id>'
ORDER BY plan_hash_value, timestamp;

Look for:

• Plan changes over time
• Elapsed time or row differences across plans

Summary: What to do Next

If you found…

• Bad access paths: Add indexes or rewrite joins.
  • You won't be able to add indexes to replicated objects
• Mismatched estimates: Check stats and consider histograms.
• Temp spills: Filter earlier.
• Wrong join type: Add hints or use a different query shape.
• Stale plans: Consider SQL plan baselines or profiles.

Diagnosing System Wide Performance Issues

When your entire application or environment feels sluggish—slower pages, delayed jobs, or timeouts—you need to look beyond individual queries and assess system-wide health. In the Oracle Retail Data Store (RDS) environment, you have limited infrastructure visibility, but the DB Ops Console and AWR reports give you powerful tools to diagnose problems effectively.

This section explains where to look and what to interpret using the capabilities provided in the RDS DB Ops Console.

Step 1: Use AWR Reports to Understand System Load

The Automated Workload Repository (AWR) report is your most powerful tool for understanding what the system was doing during a specific period. In the RDS DB Ops Console, you can:

• View automatically generated AWR reports (hourly snapshots)
• Filter and search for reports by date/time
• View full reports directly in the UI or download them for further analysis

To access:

Retail Home -> Application Navigator -> DB Ops Console -> AWR Reports

What AWR Tells You

An AWR report answers the following key questions:

• What was the database doing most during the time window?
• What were the top resource consumers?
• Was the database waiting on something?
• Did any SQL dominate CPU, I/O, or elapsed time?

Focus Areas in the AWR Report

Top 10 Foreground Events by Total Wait Time

• Start here. It tells you what the database spent time waiting on.
• Events to watch:
  • db file sequential read or scattered read: I/O-bound
  • direct path read/write temp: spills to disk due to memory limits
  • enq: TX - row lock contention: blocking transactions
  • log file sync: commit contention
  • resmgr:cpu quantum: Resource Manager throttling (ADW)

Look at both total time and waits per second to distinguish chronic from bursty issues.

Load Profile

• Gives a high-level view of system intensity.
• Important metrics:
• DB Time(s) – Total time spent on queries and waits
• DB CPU(s) – Portion of DB time spent on CPU
• Executes (SQL) – Query activity volume
• Redo size (bytes) – Change rate and workload size

If DB Time is much higher than CPU, most time was spent waiting, not computing.

SQL Statistics

Sections include:

• Elapsed Time – Longest running queries
• CPU Time – CPU-bound queries
• Buffer Gets – Logical I/O volume (data movement)
• Executions – Most frequently run statements

Identify:

• One-off queries that were expensive
• Repeated queries that may be chatty or inefficient
• SQL with high cost but low rows returned: signs of inefficiency

Buffer Cache Hit Ratio

Hit ratio should be high. Low means excessive disk I/O

Deriving Buffer Cache Hit Ratio from AWR

The buffer cache hit ratio is not listed directly in the AWR report, but you can compute it using values from the Instance Activity Stats section:

Buffer Cache Hit Ratio = 1 - (physical reads / (db block gets + consistent gets))

Step-by-Step

1. Open your AWR report.
2. Find the “Instance Activity Stats” section.
3. Locate the following three metrics:
   • physical reads
   • db block gets
   • consistent gets
4. Plug the values into the formula above.
5. Multiply by 100 for a percentage.

A high ratio may look healthy but can mask inefficient access patterns. Use this metric as a supporting clue, not a primary diagnostic.

Parse to Execute Ratio

Deriving the Parse to Execution Ratio from AWR

• Parse to Execute Ratio – High = too many hard parses (bad reuse)
• Redo per Txn – Spikes can indicate heavy DML or poor batching

Parse to Execute Ratio = parse count/execute count

Step-by-Step

1. Open your AWR report.
2. Find the “Instance Activity Stats” section.
3. Locate the following two metrics:

• parse count (total)
• execute count

1. Plug the values into the formula above.
2. Multiply by 100 for a percentage.

Understanding Temporary Table Space Usage

If direct path read temp or direct path write temp are among the top wait events, it may indicate a problem. Check the total wait time for these events. If the wait time for these events is a significant portion of the total wait time, then sorts, hash joins, or group by operations may be spilling to disk.

You will find path read temp and direct path write temp in the Foreground Wait Events table. One or both may be missing from the table. If they are missing, temporary table space usage is unlikely to be a problem.

Blocking and Concurrency

When investigating blocking and concurrency, you are looking for wait events. If the locks and latches described below are among the top wait events, it may indicate a problem. Check the total wait time for these events. If the wait time for these events is a significant portion of the total wait time, you will need to investigate further.

Explicit Locks

Explicit locks are created with LOCK TABLE or SELECT ... FOR UPDATE statements. If a session is blocked because of a LOCK TABLE, you will see it associated with a enq: TM - contention wait event. If a session is blocked because of a SELECT ... FOR UPDATE statement, you will see it associated with a enq: TX - row lock contention wait event. You can find these statements in SQL text of running sessions.

Implicit Locks

INSERT, UPDATE, or DELETE implicitly acquire locks as well. You will see a blocked session associated with a enq: TX - row lock contention wait event, if it is blocked by an INSERT, UPDATE, or DELETE operation.

Latch Locks

Latch locks are lightweight synchronization mechanisms used by Oracle to protect shared data structures in memory. Here are some common latch locks and their causes:

1. cache buffers chains latch: This latch protects the buffer cache chains, which are used to manage the buffer cache. Contention on this latch can occur due to:
   • High buffer cache activity (for example, frequent reads and writes).
   • Poor buffer cache sizing or configuration.
   • High concurrency (for example, many sessions accessing the same data).
2. cache buffers lru chain latch: This latch protects the LRU (Least Recently Used) chain, which is used to manage the buffer cache. Contention on this latch can occur due to:
   • High buffer cache activity (for example, frequent reads and writes).
   • Poor buffer cache sizing or configuration.
3. redo allocation latch: This latch is used to allocate space in the redo log buffer. Contention on this latch can occur due to:
   • High redo log activity (for example, frequent commits or high DML activity).
   • Poor redo log buffer sizing or configuration.
4. redo copy latch: This latch is used to copy redo log data from the log buffer to the redo log files. Contention on this latch can occur due to:
   • High redo log activity (for example, frequent commits or high DML activity).
   • Poor redo log buffer sizing or configuration.
5. library cache latch: This latch protects the library cache, which is used to store parsed SQL statements and other metadata. Contention on this latch can occur due to:
   • High SQL parsing activity (for example, frequent execution of dynamic SQL).
   • Poor cursor management (for example, not using bind variables).
6. shared pool latch: This latch protects the shared pool, which is used to store shared SQL and PL/SQL objects. Contention on this latch can occur due to:
   • High shared pool activity (for example, frequent parsing or loading of SQL and PL/SQL objects).
   • Poor shared pool sizing or configuration.

Common Causes of Latch Contention

1. High concurrency: Many sessions accessing the same data or resources simultaneously.
2. Inefficient SQL: SQL statements that cause high parsing or execution overhead.
3. Lack of indexing or poor indexing: Insufficient or poorly designed indexing can lead to high buffer cache activity and latch contention.
4. Mismatch between index design and query goals.

Replicate Objects

Only replication will create locks on replicated objects. Read operations (for example, SELECT statements) on replicate objects will generally not be blocked by locks. In other words, locking on replicated objects should not be an issue for replicate objects. You are also unable to create indexes for replicated objects. As a result, you may find yourself in a position where you lack the indexing to support your query. You can list indexes for views using the DBA_INDEXES and DBA_IND_COLUMN tables if you are uncertain about which columns are indexed in each view.

What to Compare

If this is not your first AWR review, compare against:

• A previous report from the same time of day (for example, yesterday 2–3 PM)
• A known healthy period (for example, baseline from last successful run)

Look for:

• New top SQLs
• Increased wait time or database time
• Different execution patterns (for example, hash joins vs. nested loops)

Report Retention Notes

• AWR reports in the RDS DB Ops Console are retained for 30 days.

• Use the “Refresh Report Logs” button to get the latest list.

• Reports can be downloaded for local storage or comparison.

• Custom reports (if you have RDS_MANAGEMENT_OWNER role) can be generated for any snapshot range. A snapshot range longer than several hours is not recommended.

When to Escalate or Log a Baseline

If the AWR shows a shift in workload or a clear new bottleneck, capture and store:

• The report ID
• Top SQLs (SQL_ID and plan hash)
• Wait events
• Load profile

This gives you a complete snapshot to compare after changes or to include when engaging support.

AWR Review Checklist

Snapshot Range Reviewed (Begin Snapshot ID - End Snapshot ID): _____________________________

Time Window (Start Time – End Time): _________________________

1. SYSTEM LOAD
   • DB Time per Second:
     • Value: ________ — Is it high for this system?
   • CPU Usage per Second:
     • Value: ________ — Is DB Time >> CPU time? Suggests waits.
   • Executions per Second:
     • Value: ________ — Is there a spike or dip?
2. TOP WAIT EVENTS

   Wait Event	Time (s)	Notes [ e.g., temp spill, I/O, locking ]
   1.
   2.
   3.

3. TOP SQL STATEMENTS

   SQL_ID	Metric	Value	Comment (slow/overused/etc.)
   	Elapsed Time
   	Buffer Gets
   	Executions

4. TEMPORARY SPACE USAGE
   • Temp Waits (for example, direct path read/write temp):
     • Observed? Yes / No
     • Notes: ___________________________________________
   • Operations likely causing spills (sorts, joins, and so on): ___________________________________________
5. INSTANCE EFFICIENCY RATIOS
   • Buffer Cache Hit Ratio: _________ %
   • Parse to Execute Ratio: _________ (High = too many hard parses?)
6. NOTABLE CHANGES COMPARED TO BASELINE

   Area	What Changed	Details
   Workload	Increased / Decreased
   Waits	New / Increased
   Top SQL	Changed / Same

7. INITIAL CONCLUSION / NEXT STEP

   _____________________________________________________

   (for example, Investigate temp usage, tune top SQL, compare plans, escalate to support)

Step 2: Use Database Metrics (DB Ops Console)

Navigate to:

Retail Home -> Application Navigator -> DB Ops Console -> Database Metrics

Table A-1 Key Metrics to Watch

Metric	What It Indicates
CPU Utilization	High = heavy load; may align with performance drop
Storage Utilization	If near capacity, could be causing issues
Session Count	Spike in active sessions = concurrency issue
Execute Count	High = heavy workload; baseline expected volume
Running Statements	Indicates how many statements are using resources
Queued Statements	Indicates backlog due to contention
APEX Load Time	Perceived slowness for end users
APEX Page Events	Intensity of user interaction with APEX apps

Look at trends using 1-minute or 5-minute intervals. Spikes or sustained elevation may confirm a user-reported slowdown.

Step 3: Triage with "Top SQL"

Navigate to:

DB Ops Console -> Top SQL

• Identify SQLs that are frequently active or waiting.
• Filter by session state: ON CPU vs. WAITING.
• Click into a SQL ID to view execution details and full text.

This is your quickest path to confirm which queries are stressing the system now, even outside the AWR snapshot window.

Check DBMS Jobs (If Relevant)

Navigate to:

DB Ops Console -> DBMS Jobs

• View failed or long-running jobs.
• Check job history for trends or specific failures during slow periods.

Step 5: Session Management (Admin Role)

If you have admin rights, you can also:

• View active sessions (filter by username, SQL ID, and so on).
• Identify stuck or long-running sessions.
• Use the provided interface or API to terminate safe-to-kill sessions (based on naming patterns like _RDS_CUSTOM).

Table A-2 Interpretation Tips

Observation	Likely Cause / Next Step
High wait time on temp I/O	Queries spilling to disk → check joins/sorts
One query dominates Top SQL	Regressed plan or unindexed access → tune it
Lots of queued statements	Resource bottleneck (e.g., CPU) → throttle, tune
Sharp spike in APEX load time	Backend slowness or DB resource saturation
Session count far above normal	Application surge, leaking sessions, or blocking

Takeaway

Even without OS access or OEM, you can do meaningful diagnosis using:

• AWR reports for time-window analysis
• Database metrics for real-time and trending load
• Top SQL and jobs to find active bottlenecks

You don’t need to solve everything at once. Start by identifying what changed, what’s dominant, or what’s waiting, and then follow the evidence.

Understanding Plan Instability

You tune a query, it runs well, and then—suddenly—it doesn’t. This is often due to plan instability, where Oracle’s optimizer picks a different execution plan for the same SQL text. Plan changes are normal. But when the new plan performs worse, it becomes a problem.

You should not encounter a plan instability problem. The Oracle Autonomous Database should keep plan instability from becoming a problem. The steps below will help you make a case and submit a ticket if you think you have an instability problem.

What is Plan Instability?

Oracle’s optimizer generates a plan each time a query is parsed. If the inputs to the optimizer change—even slightly—it might produce a different plan. Most of the time, that’s fine. But sometimes:

• The new plan is much slower.
• The old plan would still be better.
• You didn’t change anything, but the plan changed anyway.

Why Plans Change

Cause	Description
Stats changed	Table, index, or column stats were refreshed. Cardinality estimates changed.
Bind peeking	The first bind value caused a plan that’s bad for later values.
No histograms	Optimizer assumes uniform data when actual data is skewed.
New index or object	The optimizer sees a new access path and tries it.
Adaptive optimization	Oracle switched plans mid-execution based on early row counts.
SQL text changed slightly	Even whitespace differences result in new SQL_IDs and plans.
Cursor aged out	The plan aged out of the shared pool and was regenerated.

How to Detect Plan Instability

Use AWR or SQL history views to spot plan changes:

SELECT sql_id, plan_hash_value, COUNT(*)
FROM dba_hist_sql_plan
WHERE sql_id = 'your_sql_id'
GROUP BY sql_id, plan_hash_value
ORDER BY COUNT(*) DESC;

• If there are multiple plan_hash_values for the same sql_id, you’ve had a plan change.
• Look at performance metrics for each plan: which one was faster? More consistent?

SELECT is_bind_sensitive, is_bind_aware, is_shareable
FROM v$sql
WHERE sql_id = 'your_sql_id';

If is_bind_sensitive = Y but is_bind_aware = N, you may be suffering from bind peek instability.

When Plan Changes are Bad

A plan change is only a problem when the new plan is worse. Symptoms include:

• Increased runtime
• Increased temp usage (due to spills)
• Higher CPU or I/O
• Lower row estimates than actuals
• Drastically different join orders

What To Do

If you believe you have encountered an unstable plan, submit your research in a ticket. Generally, you will not have access to the tools that will allow you to fix the problem yourself.