Does the Oracle Explain Plan command really show the execution plan that will be used?

When it comes to SQL tuning we often need to look at the execution plan for a SQL statement to determine where the majority of the time is spent. But how we generate that execution plan can have a big impact on whether or not the plan we are looking at is really the plan that is used.

The two most common methods used to generate the execution plan for a SQL statement are:

So, what can cause the plans to differ?

Bind Variables

However when we use the EXPLAIN PLAN command for our statement, we get a full table scan plan and a cardinality estimate of 170 rows.

So, why didn’t the explain plan command do the literal replacement?

The cursor generated by an EXPLAIN PLAN command is not shareable by design. Since the cursor isn’t shared there is no point in doing the literal replacement that would allow the cursor to be shared. Therefore the explain plan command does not replace the literals.

To demonstrate that the EXPLAIN PLAN command cursors are not shared, I ran our example queries two more times and then queried V$SQL.

For the EXPLAIN PLAN version of the statement, no literal replace occurred and each execution created a new child cursor (0,1,2). Demonstrating that no cursor sharing occurs with the explain plan command.

So, what I have a few extra cursors. What’s the big deal?

Adaptive Plans

In Oracle Database 12c Adaptive Plans enable the optimizer to defer the final plan decision for a statement, until execution time.

The optimizer instruments it’s chosen plan (the default plan), with statistics collectors so that at runtime, it can detect if its cardinality estimates, differ greatly from the actual number of rows seen by the operations in the plan. If there is a significant difference, then the plan or a portion of it can be automatically adapted to avoid suboptimal performance on the first execution of a SQL statement.

Currently only the join method or the parallel query distribution methods can adapt.

As you can see the initial plan the optimizer came up with was a NESTED LOOP join, when the final plan was in fact a HASH JOIN. If you only use the EXPLAIN PLAN command you would never know a completely different join method was used.

Related posts:

A leader is someone that works with a team to achieve goals and accomplish tasks. However, a leader is more than that — they are an inspiring force that encourages members to be productive and…

Thanksgiving Thoughts 2017. I do not celebrate Thanksgiving, but I would like to use this time and space to show how grateful I am for the following:.

I used to be the biggest pessimist on the planet. Seriously. There was no half full or half empty glass. That sucker was empty. That pessimistic attitude went clear back to childhood and I carried it…