How to Find Old Execution Plans for Any SQL_ID

 

Oracle's Time Machine: How to Find Old Execution Plans for Any SQL_ID

By a Performance Tuning Enthusiast in Hyderabad | August 7, 2025

Here in Hyderabad, as the evening draws in, there's a familiar story playing out in data centers across the city. A critical report that flew through the system yesterday is crawling today. Users are complaining, tickets are being raised, and all eyes are on the DBA. What went wrong? The prime suspect is almost always the same: a change in the query's execution plan.

But how can you prove it? To solve this mystery, you need a time machine. You need to see not only the plan the query is using now but also the plan it was using before the trouble started. Luckily, Oracle Database 19c provides the tools to do just that. All you need is the query’s unique identifier: the SQL_ID.

Let's dive into how you can become a database detective and uncover a query's past.

First, The Key Concepts: SQL_ID and PLAN_HASH_VALUE

Think of it like this:

• SQL_ID: This is like the license plate for your query text. No matter how many times the query runs, if the text is identical, it will have the same SQL_ID.

• PLAN_HASH_VALUE: This is the unique identifier for a specific execution plan, or the route the query takes to get the data. A single SQL_ID can have multiple PLAN_HASH_VALUEs over time if the optimizer decides on a different route.

Our mission is to find the different routes (plans) a single car (SQL_ID) has taken.

---

Method 1: The Crime Scene Investigator 🔍 (Finding Recent Plans)

Scenario: The performance issue is happening right now, or happened very recently. The evidence is still fresh and likely sitting in the database's memory (the "shared pool").

This is the quickest way to get an answer.

Step 1: Get the SQL_ID

If you don't have it already, you can grab it from V$SQL using a snippet of the query text.

SQL

-- Find the license plate for your query
SELECT sql_id, child_number, sql_text
FROM v$sql
WHERE sql_text LIKE '%some_unique_part_of_your_query%';

Step 2: Display the Cached Plan

Now, use the magical DBMS_XPLAN.DISPLAY_CURSOR function. It reads the plan directly from the cache.

SQL

-- Show me the current route!
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('your_sql_id', NULL, 'TYPICAL'));

• Just replace 'your_sql_id' with the one you found.

• Providing NULL for the second parameter tells Oracle to show you all the plans for that SQL_ID currently in memory.

This will give you a formatted execution plan, showing you exactly how Oracle is running the query right now.

---

Method 2: The Historian ⏳ (Digging into the Past with AWR)

Scenario: The problem started yesterday, or last week. The evidence is long gone from the live memory cache. We need to go to the archives.

For this, we turn to the Automatic Workload Repository (AWR). This is Oracle's built-in performance data warehouse.

Important Note: Using AWR features requires an Oracle Diagnostic Pack license. Make sure your organization is licensed before using these queries in production.

Step 1: Find the Historical SQL_ID

Similar to before, but this time we search the AWR history.

SQL

SELECT sql_id, sql_text
FROM dba_hist_sqltext
WHERE sql_text LIKE '%some_unique_part_of_your_query%';

Step 2: Uncover All Historical Plans

We'll use a different function, DBMS_XPLAN.DISPLAY_AWR, which is specifically designed to pull plans from the AWR.

SQL

-- Open the archives for this SQL_ID
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('your_sql_id', NULL, NULL, 'TYPICAL'));

This command will display every distinct execution plan (PLAN_HASH_VALUE) that AWR has ever captured for your SQL_ID. You might see the "good" plan from last week and the "bad" plan from today side-by-side!

Step 3: Pinpoint When the Plan Changed

This is the masterstroke. How do you know which plan was active and when? By joining the AWR's SQL statistics with its snapshot information, you can build a timeline.

SQL

-- Show me a timeline of every plan used by this query
SELECT
    s.snap_id,
    s.end_interval_time AS last_seen,
    st.plan_hash_value,
    st.executions_delta AS executions_in_snapshot
FROM
    dba_hist_sqlstat st
JOIN
    dba_hist_snapshot s ON st.snap_id = s.snap_id
WHERE
    st.sql_id = 'your_sql_id'
ORDER BY
    s.end_interval_time DESC;

The result of this query is gold. The LAST_SEEN column tells you the timestamp of the last time a specific PLAN_HASH_VALUE was observed. By looking at the top rows, you can see the most recent plan, and by scrolling down, you can see what the plan was before and pinpoint the exact time the plan flipped.

Your Performance Tuning Workflow

1. Problem Now? Use DBMS_XPLAN.DISPLAY_CURSOR for an instant look.

2. Problem in the Past? Use DBMS_XPLAN.DISPLAY_AWR to see all historical plans.

3. Need to Know When? Run the final AWR history query to build a timeline of plan changes.

Armed with this knowledge, you are no longer just guessing. You can definitively say, "This query started performing poorly at 10:00 AM yesterday because the plan changed from 3541068894 to 1048557912." That's not just debugging; that's database forensics.

Happy tuning!