Explain plan Command : Why we should not rely for exact runtime plan.

For getting SQL plan we have lot of method in oracle, one of them being EXPLAIN PLAN FOR Command.We use EXPLAIN PLAN Command to get estimated Plan without running the SQL. Oracle perform parsing on SQL and store estimated plan in plan table.

What else we want, we are getting plan with out running it. its Kinda Coool…!
Hence most of us would tends to use same on verifying and validate run time behaviour of a SQL.

But Only “EXPLAIN PLAN” should not be use to validate SQL runtime behaviour (plan).
I will try to walk you through different cases, where explain plan won’t simulated same plan as runtime and having cardinality estimation mismatch

All test cases were run on 12.1.0.1

Case 1 : Histogram columns.!

SQL> Create table tab_test1 as
2 Select 'Data' || rownum col1 , decode(mod(rownum,10000),0,'X','Y') col2 from dual connect by rownum < 1000000;
Table created.
SQL> select col2,count(*) from tab_test1 group by col2;


Col2 COUNT(*)
- ----------
Y 999900
X 99

We created table tab_test1 with column col2 as SKEW.
SQL> create index idx_col2 on tab_test1(col2);
Index created.
SQL> EXEC dbms_stats.Gather_table_stats(user, 'TAB_TEST1',method_opt => 'FOR COLUMNS SIZE 254 col2');

As Data were Skew we created histogram to switch between FTS(Full) and Index Access.
Let examine plan using EXPLAIN PLAN.

SQL> variable n1 varchar2(1);
SQL> exec :n1 := 'X';

PL/SQL procedure successfully completed.
SQL> explain plan for
2 select count(*) from tab_test1 where col2 = :n1;

Explained.
After Setting bind variable value to ‘X’ , what should be cardinality estimations for above SQL?
Let see.. πŸ™‚

SQL> select * from table(dbms_xplan.display);


PLAN_TABLE_OUTPUT
----------------------------------------
Plan hash value: 2111116345
----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 498 (2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
|* 2 | INDEX FAST FULL SCAN| IDX_COL2 | 500K| 976K| 498 (2)| 00:00:01 |
------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
2 - filter("COL2"=:N1)
14 rows selected.

As Highlighted , Cardinality Estimations is no were near to actual rows though we have histogram.
For cardinality estimations histogram stats were completely ignored.

Estimated cardinality was calculated, as oracle do for normal columns.

For condition := Col2 = :n1
NDV --- no.of distinct values := 2
Total Rows := 999999
cardinality estimations calculated is 999999/2 := 500k approx.

When we use EXPLAIN PLAN, it didn’t take into considerations histogram stats defined on columns.
Instead it treat column as normal non skew and accordingly get the plan.
When we are using Explain plan for checking plan No bind peeks takes place (for testing _optim_peek_user_binds is not set to false).

For hard coded values, its work fine.
`
SQL> Explain plan for select count(*) from tab_test1 where col2 = 'X';
Explained.
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------
Plan hash value: 2759238038
------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
|* 2 | INDEX RANGE SCAN| IDX_COL2 | 99 | 198 | 3 (0)| 00:00:01 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COL2"='X')

As highlighted, for hard coded values histogram stats was taken into considerations.Plan publish is what is expected “INDEX RANGE SCAN”.

For SQL comprise of bind variable based condition for histogram indexed column, runtime plan will get influence with histogram and bind peeking.But Explain plan command for such scenario wont guarantee same plan as runtime.

Ideally For histogram columns, Explain plan command should be avoided.
For ease, on single table access we can test using hard coded variables as explain above.

Case 2 : Implicit Conversion!

Will try to showcase a scenario in which “EXPLAIN_PLAN FOR” Command failed to showcase implicit conversion!.

SQL> create table tab_test2 as
2 select to_char(object_id) obj_id , object_name , object_type from dba_objects ;
Table created.

SQL> desc tab_test2;
Name Type
------------ -------------
OBJ_ID VARCHAR2(40)
OBJECT_NAME VARCHAR2(128)
OBJECT_TYPE VARCHAR2(23)

SQL> create index idx_objid on tab_test2(obj_id);
Index created.

SQL> variable n number; -- column OBJ_ID is varchar but we are passing NUMBER.
SQL> exec :n := 100001;
PL/SQL procedure successfully completed.

SQL> explain plan for
2 select * from tab_test2 where obj_id = :n;
Explained.

As we are passing NUMBER Datatype and Column “obj_id” is Varchar, Oracle will perform implicit conversion a runtime.

Let see what Explain plan command is publishing!

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------
Plan hash value: 606915548
-----------------------------------------------------------------
| Id | Operation | Name | Rows |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB_TEST2 | 1 |
|* 2 | INDEX RANGE SCAN | IDX_OBJID | 1 |
-----------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------
2 - access("OBJ_ID"=:N)

Check at Predicate information though we are passing bind variables with data type as NUMBER, oracle will not perform any conversion as per EXPLAIN PLAN FOR command.

Problem is , internally Explain plan command will treat complete SQL as Varchar.
Hence though bind variable was defined as NUMBER
variable n number;

Explain plan command will treat it as Varchar and since Varchar(:n) is compared with Varchar(OBJ_ID), no conversion was publish by command.

As a developer\tuner we are expecting same plan and might miss conversion with approving such SQL for productions runs.

Now lets run the sql.

SQL> select count(*) from tab_test2 where obj_id = :n1;
COUNT(*)
----------
1


SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------
SQL_ID dr5xyw1z711g2, child number 0
-------------------------------------
select count(*) from tab_test2 where obj_id = :n1

Plan hash value: 3730970499
------------------------------------------------
| Id | Operation | Name | Rows |
------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | TABLE ACCESS FULL| TAB_TEST2 | 1 |
------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_NUMBER("OBJ_ID")=:N1)

Prod run will be a surprise , as it wont be as expected though Explain plan command have validated same.
So clearly, implicit was a need and was not captured through Explain plan command.

So next time , whenever you are checking plan through Explain plan command have above behaviours in mind.

Best approach should be to run the sql and check plan using dbms_xplan utility package after run or for long running sql using sql monitors features introduced in 11g.

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Oracle Internal and tagged , , , , . Bookmark the permalink.

5 Responses to Explain plan Command : Why we should not rely for exact runtime plan.

  1. prem says:

    Very good explanation Deepak . We want more valuable info from you πŸ™‚

    Like

  2. Harsh says:

    Good Work Deepak for sharing all these valulable details about explain Plan
    Its really helpful for all reader of the blog.

    Like

  3. Ashitosh says:

    Quite Informative Deepak.
    Thanks for sharing

    Like

  4. tushar says:

    Excellent work Deepak sir!! Very helpful.

    Like

  5. siddharth says:

    Very informative post.Thanks for sharing Deepak sir.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s