“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?
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!
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:
So when reading the results, you’re not faced with several dozen irrelevant numbers, you can just skip to the good stuff!
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.