| layout | default |
|---|---|
| title | DBA AWR SQL Execution Plan History | Oracle EBS SQL Report |
| description | Execution plan history for a particular SQL id from the automatic workload repository |
| keywords | Oracle EBS, Oracle E-Business Suite, SQL report, Blitz Report, Diagnostic Pack, Enginatics, DBA, AWR, SQL, Execution, dba_hist_sql_plan, v$parameter, dba_segments |
| permalink | /DBA%20AWR%20SQL%20Execution%20Plan%20History/ |
Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.
Execution plan history for a particular SQL id from the automatic workload repository
SQL Id, Plan Hash Value, Using Index, Object Name, Options, Objects larger than x GB, Show Object Size, Diagnostic Pack enabled, Container Data
dba_hist_sql_plan, v$parameter, dba_segments
DBA AWR SQL Performance Summary, DBA SGA SQL Execution Plan History, DBA AWR Active Session History, DBA SGA+PGA Memory Configuration, DBA AWR Blocking Session Summary, DBA AWR Tablespace Usage, DBA AWR System Metrics Summary, DBA Blocking Sessions, Blitz Report Security
Some Oracle EBS SQL reports in this library require functions from the utility package xxen_util. Install it before running the SQL directly against your Oracle EBS database.
| Resource | Link |
|---|---|
| Excel Example Output | DBA AWR SQL Execution Plan History 22-Dec-2025 083522.xlsx |
| Blitz Report™ XML Import | DBA_AWR_SQL_Execution_Plan_History.xml |
| Full SQL on Enginatics | www.enginatics.com/reports/dba-awr-sql-execution-plan-history/ |
The DBA AWR SQL Execution Plan History report is a critical tool for diagnosing "Plan Flipping" or "Plan Regression". It tracks how the execution plan for a specific SQL statement has changed over time. In Oracle, the Optimizer decides the best way to execute a query (which index to use, join order, etc.). Sometimes, this plan changes for the worse, causing a query that used to take 1 second to suddenly take 1 hour.
- Performance Regression: "This report was fast yesterday. Today it's slow. Nothing changed in the code."
- Upgrade Analysis: "Did the database upgrade cause the Optimizer to pick a bad plan for our critical payroll query?"
- Index Impact: "Did dropping that index cause the query to switch to a full table scan?"
This report lists all execution plans captured in AWR for a given SQL ID.
Key Features:
- Plan Hash Value: A unique identifier for the plan structure. A change in this value confirms the plan changed.
- Cost: The Optimizer's estimated cost for the plan.
- Timestamp: When the plan was first and last seen.
The report queries DBA_HIST_SQL_PLAN.
Key Tables:
DBA_HIST_SQL_PLAN: Stores the steps of the execution plan.DBA_HIST_SQLSTAT: Links the plan to performance metrics.
- Stability: Allows DBAs to identify unstable queries and lock down their plans (using SQL Profiles or Baselines).
- Root Cause Analysis: Definitively proves whether a performance drop was caused by a plan change.
- Recovery: Provides the "good" plan hash value needed to restore performance.
- Blitz Report™ – World’s Fastest Oracle EBS Reporting Tool
- Oracle Discoverer Replacement – Import Worksheets into Blitz Report™
- Oracle EBS Reporting Toolkits by Blitz Report™
- Blitz Report™ FAQ & Community Q&A
- Supply Chain Hub by Blitz Report™
- Blitz Report™ Customer Case Studies
- Oracle EBS Reporting Blog
- Oracle EBS Reporting Resource Centre
© 2026 Enginatics