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!?

Singing in the key of primary - Why your primary keys should be artificial

I believe very firmly that a primary key should always be an artificial key.  Really.  Many folks talk about having a “natural key” that can be used as a primary key.  I believe that this can appear to work but will eventually fail.  So let’s take a look at what this is all about.  

First and foremost why do we have a primary key?  Most folks believe it is there so we can select the record quickly.  After all if I have the primary key for a row it will be a unique look up since the primary key by definition is unique and not null (otherwise it’s not a primary key).  That is true, sort of. 

A primary key is there so we can join it to another table.  Really, that’s why they exist.  Not so you and I can select the record directly, it’s there so we can join this table to another and get rows that are related to one and other.  The classic simple example is the good old Oracle tables EMP and DEPT.  In the DEPT table we have the column DEPTNO as its primary key.  In EMP table one of its columns is DEPTNO which points to the record in the DEPT table that the employee works in.  This is the standard primary key to foreign key relationship in a relational database.  This relationship is the very cornerstone that the whole relational model stands on.  Without this nothing else really matters.

So what is a good column to have a primary key on?  Of course out of the gate it has to be unique and not null.  But what else? One other thing that should be true is that they don’t change.  And a problem when you look for a “natural key” is that you can’t guarantee that it wouldn’t change or remain unique.  If the data in the primary key column is generated outside of your database, there is no way you can have a 100% guarantee it’s solid and will stay that way for ever.

Another thing to consider is that it really should be one column, not several.  Think again about why a primary key exists, to be a foreign key in another table.  If it’s one column it’s easy to “push” that to another table.  But what about 2 or 3 columns?  Now it starts to get cumbersome.  Also what about a many to many relationship?  The primary key for both tables is in the resolution table, both sides have 2 columns each, now it’s a 4 column resolution table not 2 columns.  So what?  Multiply that out about a million times and you see it starts to be an issue. And since there is likely an index on the resolution table as well, this will cause the index to approach twice the size.

Sure there are database level features to combat these like compression and partitioning and all that.  But what if we had made better choices to start with?  Then it would be longer before we have to invoke such things, if at all.  For features that require a license fee, this could result in a financial savings. And these features just push the performance and storage problems down the road; they are still there just hidden for the moment.

The primary key should have nothing to do with reality.  If it does, it’s just a matter of time before it changes; the only constant in the universe is change after all.  Once it changes that is where the real problems can come in.  Imaging having to redo all the primary keys in a core table of your application.  Scary thought I know. 

So what should they be?  To me the answer is simple - Every primary key should be an artificial key.  The best key would be generated by a unique random number or string generator.  But that is at least really hard to do so the next best thing is a numeric sequencer.   Also I would purpose that in a really well designed system this key is not displayed to the users at all.  Remember this thing is ultimately for the relationships between tables.  Not really for row selection.  I’m all for other alternate keys like an employee ID that is based on the date of hire, SSNs, product codes, account numbers, or similar columns to be used for select rows.  Those are excellent and might even be unique and not null, well maybe the not null part will work at least.   The unique part might work for a while at least. Remember the argument for using SSNs for a person’s unique ID? Well that might be a problem.

Unique identifiers are very different from primary keys.  They sure can seem to be the same and sometime even have the same attributes (like unique and not null) but one is really for row selection (an identifier) and the other for setting up for foreign keys (the primary key).