ThatJeffSmith

Explain Plan and Autotrace Enhancements in Oracle SQL Developer 4

“Explain Plans are useless.”

Yikes. Really? Don’t we spend all day looking at execution plans?

To clarify, I would say that explain plans could be useless.

If you do an Explain, you’re actually looking at a theoretical plan, not the ‘actual’ plan.

From the docs:

With the query optimizer, execution plans can and do change as the underlying optimizer inputs change. EXPLAIN PLAN output shows how Oracle runs the SQL statement when the statement was explained. This can differ from the plan during actual execution for a SQL statement, because of differences in the execution environment and explain plan environment.

So for version 4.0 of SQL Developer, when you’re in the worksheet and you want to see a plan, you now have the option to look at the plans in V$SQL_PLAN for your query.

From the docs:

After the statement has executed, you can display the plan by querying the V$SQL_PLAN view. V$SQL_PLAN contains the execution plan for every statement stored in the cursor cache. Its definition is similar to the PLAN_TABLE. See “PLAN_TABLE Columns”.

The advantage of V$SQL_PLAN over EXPLAIN PLAN is that you do not need to know the compilation environment that was used to execute a particular statement. For EXPLAIN PLAN, you would need to set up an identical environment to get the same plan when executing the statement.

So what does this look like in SQL Developer now?

Note the drop-down control added to the Explain Plan button in the worksheet toolbar.

Note the drop-down control added to the Explain Plan button in the worksheet toolbar.

If you click on the drop-down bit of the Explain Plan button, what you’ll see are a list of the cached plans for that statement – that is, plans that were ACTUALLY used to execute the statement.

We’re parsing the text in the worksheet and finding the SQL IDs in the V$SQL_PLAN view that match.

As my almost-two year old son likes to now proclaim after doing something ‘awesome,’ TA-DA!

Here is the cached plan of when it was last executed.

Here is the cached plan of when it was last executed.

TA-DA indeed.

But wait, there’s more!

New Autotrace Option to Fetch All Rows

Previously when running an autotrace in SQL Developer, we would only fetch the first batch of records. This meant that you were missing a good bit of physical I/O, meaning that only doing a single fetch could have a big impact on statistics in certain cases. For example if the data being accessed was compressed or if the number of rows being returned was large, etc.

To alleviate that, we’ve added a new preference for Auto Trace:

Read all the rows, then show me the stats!

Read all the rows, then show me the stats!

So when reading the results, you’re not faced with several dozen irrelevant numbers, you can just skip to the good stuff!

Read all the rows, then show me the stats!

Read all the rows, then show me the stats!

Actually we added two preferences. The other check box on that screen says ‘Skip 0-Valued Statistics.’

No more dozens of entries with 0 stats…

Oh Sir, Just One More Thing…

Plans can be a bear to read. Several of you have requested that we enable the ‘zebra’ pattern property for the grids used to display plans. That’s been done for version 4.0, and if you look at the screen captures above, you can observe this your yourself.