Death to the Explain plan! Long live the Explain plan!

Should we use the explain plan?  The classic oracle answer, it depends!  Make sure you understand what it does and doesn’t show and it can still be useful.

Back about a 1000 years ago (in Oracle time), the explain plan and the execution plan were the same thing.  There was no way back then for these two things to be different.  Back then we had RULE based optimization and no BINDS.  Every parse was a hard parse and it was very simple to see what the optimizer was going to do at run time since the explain plan was exactly the same as what it would do when it ran.

Then Oracle introduced the concept of BINDS and the soft parse.  This started us on a road where the explain plan and the execution plan could be different.  And for many years it was a “could”.  Upwards of 90% of the time the explain plan and execution plans were the same. 

As time when on, new features were introduced that made the road really split.  To name a few of the big ones, Bind Peeking, Histograms, Adaptive Cursor Sharing, and now Adaptive Query Optimization.   What this means is that the explain plan and the execution plan now are less likely to be the same.  Let’s take a look at a relatively simple example to illustrate what is going on.

select /*+ gather_plan_statistics*/ b.object_name, b.object_type, a.username
 from allusers_tab a, big_tab b
 where a.username = b.owner
  and b.object_type = 'PROCEDURE'
  and a.username not in ('SYS','SYSTEM')

When do an explain plan in 12.1.0.2 the very first time I get this plan (I’m just showing the basic plan here so it’s a little easier to read):

Plan hash value: 1213713745

------------------------------------------------------------------
|   Id  | Operation                            | Name            |
------------------------------------------------------------------
|     0 | SELECT STATEMENT                     |                 |
|  *  1 |  HASH JOIN                           |                 |
|-    2 |   NESTED LOOPS                       |                 |
|-    3 |    NESTED LOOPS                      |                 |
|-    4 |     STATISTICS COLLECTOR             |                 |
|  *  5 |      INDEX FULL SCAN                 | USERNAME_PK     |
|- *  6 |     INDEX RANGE SCAN                 | BIG_OBJTYPE_IDX |
|- *  7 |    TABLE ACCESS BY INDEX ROWID       | BIG_TAB         |
|  *  8 |   TABLE ACCESS BY INDEX ROWID BATCHED| BIG_TAB         |
|  *  9 |    INDEX RANGE SCAN                  | BIG_OBJTYPE_IDX |
------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A"."USERNAME"="B"."OWNER")
   5 - filter("A"."USERNAME"<>'SYS' AND "A"."USERNAME"<>'SYSTEM')
   6 - access("B"."OBJECT_TYPE"='PROCEDURE')
   7 - filter("A"."USERNAME"="B"."OWNER" AND "B"."OWNER"<>'SYS' AND B"."OWNER"<>'SYSTEM')
   8 - filter("B"."OWNER"<>'SYS' AND "B"."OWNER"<>'SYSTEM')
   9 - access("B"."OBJECT_TYPE"='PROCEDURE')
Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)

This is Adaptive Query Optimization kicking in with adaptive plans, notice that the optimizer can’t decide if a double nested loop or a hash join is better for these two tables.  So it says “I’ll try both”.  Meaning that at run time it will decide which one to use as the query runs.  A little scary, eh?  So right away we really don’t know which on it will use.

If I run the query just once and look at the plan it’s the same plan with the same plan hash value (1213713745), still the default and alternate plan are shown.  But if I run it again (two runs of the query) I get this plan:

Plan hash value: 3435153054

----------------------------------------------------------------
| Id  | Operation                            | Name            |
----------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                 |
|   1 |  NESTED LOOPS                        |                 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| BIG_TAB         |
|*  3 |    INDEX RANGE SCAN                  | BIG_OBJTYPE_IDX |
|*  4 |   INDEX UNIQUE SCAN                  | USERNAME_PK     |
----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("B"."OWNER"<>'SYS' AND "B"."OWNER"<>'SYSTEM'))
   3 - access("B"."OBJECT_TYPE"='PROCEDURE')
   4 - access("A"."USERNAME"="B"."OWNER")
       filter(("A"."USERNAME"<>'SYS' AND "A"."USERNAME"<>'SYSTEM'))
Note
-----
   - statistics feedback used for this statement

Now a new plan pops up, this is another part of Adaptive Query Optimization “Statistics Feedback” that causes the query to be re-optimized.   And it’s different from the other two plans.  Notice now it’s a “normal” nested loop join of the two tables.   Also at this point the optimizer has created a couple of SQL Plan Directives, also new in 12. These basically tell the optimizer that last time it did a query like this, it didn’t get the cardinality right so do some dynamic sampling to get it right.  (These directives only tell the optimizer to do dynamic sampling at the moment, but the door is open for other types of directives in the future.)

A really cool thing about SQL Plan Directives is that they are not based on a query, as in they are not tied to a SQL_ID.  They are tied to a “query expression”.  Think a predicate here.  This means that another query (with a different SQL_ID) that uses the same predicate can use one of these directives. 

Now here is where the story really takes an interesting twist (as if it hasn’t already).  If I run it again it goes back to the adaptive query! 

But wait there’s more!  If I flush the shared pool and rerun the query with the plan directives in place, then it picks up the single nested loop plan right from the get go and that is the “only” plan, as in if I run it multiple times I still get this plan (also the explain plan is the same).  It looks like this, check out the notes section where it tells me it’s using 2 SQL Plan Directives:

Plan hash value: 3435153054

----------------------------------------------------------------

| Id  | Operation                            | Name            |

----------------------------------------------------------------

|   0 | SELECT STATEMENT                     |                 |

|   1 |  NESTED LOOPS                        |                 |

|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| BIG_TAB         |

|*  3 |    INDEX RANGE SCAN                  | BIG_OBJTYPE_IDX |

|*  4 |   INDEX UNIQUE SCAN                  | USERNAME_PK     |

----------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter(("B"."OWNER"<>'SYS' AND "B"."OWNER"<>'SYSTEM'))

   3 - access("B"."OBJECT_TYPE"='PROCEDURE')

   4 - access("A"."USERNAME"="B"."OWNER")

       filter(("A"."USERNAME"<>'SYS' AND "A"."USERNAME"<>'SYSTEM'))

Note

-----

   - dynamic statistics used: dynamic sampling (level=2)

   - 2 Sql Plan Directives used for this statement

AH!  So what to do?  Well there is a really good chance that the explain plan and execution plan will be different now.  You will really need to run a query a few times before you know what plan is going to be used.  Initially the explain plan is at least questionable as to how accurately it is showing you the plan that will be used over time. 

There is still at least one thing good about the explain plan.  Notice that thru all of this it did show correctly how the predicates would be applied.  Not it terms of which step it would be applied to since the steps did change in the different plans.  For example, it did show the transitive predicate of the “a.username not in ('SYS','SYSTEM')” being pushed from ALLUSERS_TAB to BIGTAB.  So even if it doesn’t show you the plan that will eventually get used, it’s very likely to show you what will happen to your predicates.  That alone can be the key to success to understanding what is going on with your query.

String Theory (sort of) Getting the Trace File Name

Today’s little brain twister was “get the trace file name for the current session”.  

Sure it’s all there in V$DIAG_INFO (since 11) as in:

SQL> select value from v$diag_info where name = 'Default Trace File';

VALUE

-------------------------------------------------------------------------------------

C:\APP\RVANDYKE\diag\rdbms\hotsos\hotsos\trace\hotsos_ora_9240_HARNESS46_113853.trc

SQL>

But I want just the last bit, hotsos_ora_9240_HARNESS46_113853.trc.  So after a bit of re-learning on the sting functions of Oracle I can up with this:

SQL> select

  2  substr((

  3  replace((select value from v$diag_info where name = 'Default Trace File'),

  4  (select value from v$diag_info where name = 'Diag Trace'),'')),2) Tracefilename

  5  from dual;

TRACEFILENAME

------------------------------------

hotsos_ora_9240_HARNESS46_113853.trc

SQL>

The value from the second inner query with the 'Diag Trace' part gets just the directory path for the file. By replace that with a NULL (the '' part) I have just the name, except it leaves behind a \ or / depending on your OS at the beginning of the string like this:

SQL> select

  2  replace((select value from v$diag_info where name = 'Default Trace File'),

  3  (select value from v$diag_info where name = 'Diag Trace'),'') Tracefilename

  4  from dual;

TRACEFILENAME

--------------------------------------

\hotsos_ora_9240_HARNESS46_113853.trc

SQL>

So the outer substr will chop off that unwanted character and Ta-Da!  I have just the file name.   

Isn’t this SQL stuff the best!?