As an Oracle DBA, one of our daily tasks is to optimize bad SQL statements that are affecting the system and causing performance degradation. First we identify the culprit SQL, then we extract the execution plan, after that, we start the cycle of SQL tuning and optimization as appropriate.
Distinguishing Actual Oracle Execution Plans from Suggested Ones
There are many methods to extract the execution plan for a specific SQL statement; however, not all these methods will provide the actual or real plan that the optimizer follows. Instead, some provide a suggestion or expectation of the execution plan, which is not always accurate and can be misleading.
What is an "Execution Plan"?
An execution plan is a set of steps that the optimizer performs when executing a SQL statement and performing an operation. There are many ways to extract the execution plan for a specific SQL, including:
- Enterprise Manager
- SQL*Plus AUTOTRACE
- EXPLAIN PLAN command
- SQL Trace (event 10046) with tkprof
- DBMS_XPLAN package (viewing plans from AWR, V$SQL_PLAN, SQL Tuning Sets, or Plan tables)
Some of these methods provide the “actual” execution plan, while others provide a “suggestion”—the steps that Oracle expects the optimizer to follow, which may not always be true.
The Test Case: Skewed Data and Bind Variables
In this test case, I will create a table with two columns and insert skewed data into one column, then build an index and histogram on that column. I will then query that column using bind variables.
Any method providing a suggested execution plan is essentially a synonym for the EXPLAIN PLAN command. Because this command does not use Bind Variable Peeking, it will not generate the optimal plan for each different value in this test case—a crucial detail for our results.
Environment Preparation for Scenario Testing
[oracle@testhost ~]$ sqlplus / as sysdba SQL> alter system flush shared_pool; SQL> alter system flush buffer_cache; SQL> drop user xp_test cascade; SQL> grant dba to xp_test identified by xp_test; SQL> conn xp_test/xp_test SQL> create table xp_table (emp_id varchar2(10), team varchar2(10)) pctfree 99; SQL> insert into xp_table select * from (SELECT 'EMP' || level , 'TEAM1' from dual connect by Level = 1000); SQL> insert into xp_table select * from (SELECT 'EMP' || (level + 1000) , 'TEAM2' from dual connect by Level = 10); SQL> commit; SQL> create index index_test on xp_table (team); SQL> exec dbms_stats.gather_table_stats('XP_TEST','XP_TABLE',method_opt='FOR ALL COLUMNS SIZE AUTO FOR COLUMNS SIZE 254 TEAM',cascade=TRUE); SQL> select TABLE_NAME,COLUMN_NAME,HISTOGRAM from dba_tab_columns where table_name = 'XP_TABLE'; TABLE_NAME COLUMN_NAME HISTOGRAM -------------------- ------------------------------ --------------- XP_TABLE TEAM FREQUENCY XP_TABLE EMP_ID NONE SQL> select count(*),team from xp_table group by team order by 2 asc; COUNT(*) TEAM ---------- ---------- 1000 TEAM1 10 TEAM2
Evaluating Execution Plan Extraction Methods
With our environment ready, let’s test each method to see which reveals the actual path taken by the optimizer.
DBMS_XPLAN.DISPLAY_CURSOR (Actual Plan)
Using this method, we extract the execution plan directly from the shared pool, which will always be the real execution plan:
-- Define a bind variable: SQL> variable x varchar2(10); SQL> exec :x:='TEAM2'; SQL> select count(emp_id) from xp_table where team = :x; COUNT(EMP_ID) ------------- 10 SQL> select * from table(dbms_xplan.display_cursor()); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------- SQL_ID 31gbpz4ncsvp3, child number 0 ------------------------------------- select count(emp_id) from xp_table where team = :x Plan hash value: 726202289 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 4 (100)| | | 1 | SORT AGGREGATE | | 1 | 13 | | | | 2 | TABLE ACCESS BY INDEX ROWID | XP_TABLE | 10 | 130 | 4 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | INDEX_TEST | 10 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("TEAM"=:X)
The optimizer accessed the table using an INDEX RANGE SCAN. We are now 100% sure this is the real plan.
SQL*Plus AUTOTRACE (Suggested Plan)
Autotrace is a useful way to get SQL statistics, but will it provide the real plan?
SQL> set autotrace on explain; SQL> select count(emp_id) from xp_table where team = :x; COUNT(EMP_ID) ------------- 10 Execution Plan ---------------------------------------------------------- Plan hash value: 3545047802 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 102 (0)| 00:00:02 | | 1 | SORT AGGREGATE | | 1 | 13 | | | |* 2 | TABLE ACCESS FULL| XP_TABLE | 505 | 6565 | 102 (0)| 00:00:02 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("TEAM"=:X)
AUTOTRACE suggests a Full Table Scan, which contradicts our real plan. This confirms AUTOTRACE provides only a suggestion.
EXPLAIN PLAN Command (Suggested Plan)
The EXPLAIN PLAN command provides the plan without even running the SQL:
SQL> explain plan for select count(emp_id) from xp_table where team = :x; SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------- Plan hash value: 3545047802 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 102 (0)| 00:00:02 | | 1 | SORT AGGREGATE | | 1 | 13 | | | |* 2 | TABLE ACCESS FULL| XP_TABLE | 505 | 6565 | 102 (0)| 00:00:02 | -------------------------------------------------------------------------------
Confirmed: this method also incorrectly suggests a full table scan.
SQL Trace (Event 10046) with tkprof
SQL Trace with tkprof always provides the real execution plan. Optionally, we can use the explain keyword with tkprof to include the suggested plan as well:
SQL> alter session set events '10046 trace name context forever, level 12'; SQL> select count(emp_id) from xp_table where team = :x; SQL> alter session set events '10046 trace name context off'; -- Run tkprof on the resulting trace file
The tkprof output will show both the Row Source Operation (Actual) and the Execution Plan (Suggested). In our case, the Row Source Operation correctly showed the INDEX RANGE SCAN.
The Influence of SQL Plan Baselines
Let's see how creating a SQL Plan Baseline impacts the suggestions provided by AUTOTRACE and EXPLAIN PLAN.
-- Capture the current plan in a baseline SQL> alter session set optimizer_capture_sql_plan_baselines = TRUE; SQL> select count(emp_id) from xp_table where team = :x; SQL> alter session set optimizer_capture_sql_plan_baselines = FALSE;
Repeating the Test with Baselines
When we run AUTOTRACE or EXPLAIN PLAN now, they become baseline-aware:
SQL> set autotrace on explain; SQL> select count(emp_id) from xp_table where team = :x; -- Output now shows: |* 3 | INDEX RANGE SCAN | INDEX_TEST | ------------------------------------------------------- Note ----- - SQL plan baseline "SQL_PLAN_6br1qfn2vw604bc04bcd8" used for this statement
Because the tools are now pulling from the baseline, the "suggested" plan aligns with the actual plan.
Summary and Conclusion
There are many methods to extract the execution plan. While AUTOTRACE and EXPLAIN PLAN are convenient, they often provide only a suggestion that the optimizer may not follow in real time—particularly when bind variables and skewed data are involved.
For the actual execution plan, always rely on DBMS_XPLAN.DISPLAY_CURSOR or SQL Trace. However, as shown, even the suggestion-based tools will correctly reflect the plan once an SQL Plan Baseline is in place.
Oracle Database Consulting Services
Ready to optimize your Oracle Database for the future?
.svg){kind=small}
.svg){kind=small}