About

Martin Klier

usn-it.de

Oracle Explain Plans or Execution Plans: Guess or Reality?

Oracle finally desupported the rule-based optimizer with version 10g. Since 9i, users are good adviced to make themself familiar with the cost-based-optimizer (CBO). In order to understand what happens with your statement after parsing with CBO involved, and to be able to tune your queries, displaying, understanding and optimizing explain- or execution plans is essential.

First of all, what’s a explain- or execution plan of a query? It’s a set of operations that the DB has to perform in order to deliver the correct result set. The plan is computed by the (cost-based) optimizer based on several conditions in the “environment” of the queried object (usually a table): It considers indexes, optimizer statistics, histograms, load, memory, and so on, and looks for an optimal path to achieve the goal. Thus, this plan dictates if the query runs in a efficient or wasteful way. The result set itself is – hopefully – always the same.

I don’t like to elaborate the whole tuning chapter here, because you can find real pro’s on this topic on the net, just to mention two: Karen Morton, Richard Foote and many more. This post is just a quick sheet on displaying plans with Oracle’ own toolset.

But initially you should understand a basic difference:

Explain plans

are guesswork. Usually a good guess, and you don’t have to run the query in the whole against your database. That’s useful if performance is on the edge, and/or you can’t specify your bind variable contents literally for some reason. But this estimate suffers from blur that increases with the complexity of your query. Don’t let you be fooled by the simplicity of my example!

SQL> explain plan for
select * from test;

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   998K|  9749K|   503   (2)| 00:00:07 |
|   1 |  TABLE ACCESS FULL| TEST |   998K|  9749K|   503   (2)| 00:00:07 |
--------------------------------------------------------------------------

As you can see, they are simple, but it may be a lie.

Execution plans

are reality. But the major disadvantage is, that you have to run your query with all variables filled in first and you get the plan afterwards. As a reward, it’s the unandorned truth.

SQL> set serveroutput off;

SQL> SELECT /*+ gather_plan_statistics */ * from TEST;
ID       DESC     STATUS
---------- ---------- ----------
982147          7          1
982148          8          1
<... much more ...>

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID  1n20agkzap41v, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ * from TEST
Plan hash value: 1357081020
------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS FULL| TEST |      1 |    998K|   1000K|00:00:01.00 |   68760 |
------------------------------------------------------------------------------------

You see, it delivers E(stimated)-Rows vs. (really) A(ccessed)-Rows to allow you to compare both of them. For comparision, multiply Starts with E-Rows.

The hint “gather_plan_statistics” makes the CBO collect additional metrics for the E/A-Rows display, it does not affect the path selection in any way.

Just a nearly-OT hint: If E-Rows*Starts and A-Rows differ too much, maybe in a magnitude of factor thousand (x 1000), you might have severe trouble with your statistics and/or histograms. This frequently leads to using table accesses instead of index accesses or vice versa!

Regards
Usn

Get Oracle User DDL with dbms_metadata
Oracle: Specify the number of occurrences before metric alerting starts

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.