JL Computer Consultancy

The 10g Plan Table

Dec 2005


Here’s a little detail that may add a little extra insight to your execution plans when you use autotrace, or any variation of explain plan in 10g, brought to you courtesy of Siim Kask, one of the people attending my Trouble-shooting tutorial in Estonia this month.

In 10g, there are two scripts to create plan_tables.  One of them is $ORACLE_HOME/rdbms/admin/utlxplan.sql which creates a table called plan_table in your current schema. The other is $ORACLE_HOME/rdbms/admin/catplan.sql, which is usually called during database installation from catproc.sql; this script creates a global temporary table (GTT) with the “on commit preserve rows” option called plan_table$ in the sys schema, grants access to public, and creates a public synonym plan_table for it.  (If you are not running 10g, it is a smart idea to copy this approach, although you may feel happier creating the table in the system schema.)

Both scripts create various columns as type numeric, e.g.

        cost        numeric,

        cardinality numeric,

        bytes       numeric,

        ...

        cpu_cost    numeric,

        io_cost     numeric,

        temp_space  numeric,

When you look at the definitions using the SQL*Plus describe command, numeric turns into number(38) – which means no decimal places. But an interesting thing happens if you change the cost, and cpu_cost to allow higher precision – number(38,10) say. You discover that the code that generates the plan will actually populate these columns with high precision values (although 10 d.p. on the cpu_cost is probably pointless). For example:


rem

rem   This example starts with a one-off change to

rem   the global temporary table owned by SYS.

rem

 

alter table plan_table$ modify cpu_cost number(38,10);

alter table plan_table$ modify cost number(38,10);

 

explain plan for

select

      source

from  source$

where obj# = 33

;

 

select

      lpad(' ',depth*2) || operation "Operation",

      object_name,

      cost, io_cost, cpu_cost

from

      plan_table

order by id

;

 

 

Operation            OBJECT_NAME                COST    IO_COST   CPU_COST

-------------------- -------------------- ---------- ---------- ----------

SELECT STATEMENT                          20.0820252         20   479388.8

  TABLE ACCESS       SOURCE$              20.0820252         20   479388.8

    INDEX            I_SOURCE1            5.03565924          5   208407.2

 

3 rows selected.

 


If you are wondering where final cost of 20.0820252came from, the first thing to check is the setting for the CPU speed and single block read time (see below) which happen to be 671 (million operations per second) and 8.71 (milliseconds) in this case, then

            Cost     =          io_cost + (cpu_cost / 671000000) * (1/0.00871)

                        =          20 + (cpu time in seconds) * (single block reads per second)

                        =          20 + 0.08202518308150985300483 …

Having this extra precision (perhaps to fewer decimal places) may be helpful in some cases because the typical output from autotrace and dbms_xplan otherwise has to allow for rounding errors on a line by line bases – the code carries the high precision into subsequent multiplications – and can produce apparently contradictory numbers when rounding occurs on each line.


 

select      pname, pval1

from        aux_stats$

where       pname in ('CPUSPEED', 'SREADTIM')

;

 

PNAME               PVAL1

-------------- ----------

SREADTIM             8.71
CPUSPEED              671

 

2 rows selected.

 


Addenda (17th Dec 2005)

This note produced a flurry of interest and email.

The first point was highlighted by Connor McDonald. If you adopt the global temporary table approach, you must make sure it uses the ‘on commit preserve rows’ option (which I have added to the notes above). This is necessary for two reasons.

First, there are various examples of queries (using subquery factoring, grouping sets or star transformations, for example) which can create a form of global temporary table to hold intermediate results. The execution plan for the DML statement populating these temporary objects will be inserted into the plan table in a recursive operation using an autonomous transaction – which means you get a recursive commit, which would immediately lose the recursive plan if the plan table were create with ‘on commit delete rows’.

Secondly, not only do you lose the recursive plan, you also get unexpected Oracle errors as the explain plan call runs:

ORA-00604: error occurred at recursive SQL level 1
ORA-14450: attempt to access a transactional temp table already in use


Another little detail that came out was one revealed by Melanie Caffrey in a comment http://tkyte.blogspot.com/2005/12/every-day.html#comments on Tom Kyte’s blog, where he had supplied a URL to this article. It looks like this feature isn’t really a feature; it’s a bug which has been fixed in 10g release 2. You don’t get fractional costs reported in the cost column in my 10.2.0.1 database – which is a nuisance, but not a problem, because you can always take the arithmetic I’ve demonstrated above using the CPUspeed and sreadtim and include it in your own scripts to report on the plan table.


Finally, a little accidental discovery – you can get fractional costs in 8i, 9i, and 10g, even with CPU costing disabled. I was re-running some tests of the way that index joins work, and discovered that (amongst other oddities of the costing) the value reported in my modified plan_table was showing a small fractional component in the cost column. Following this up, I re-ran some of my tests for bitmap index costing, and found that the fractional parts of the cost that I had previously been digging out of 10053 trace files were, indeed, being reported in the plan_table. Too bad that they disappear again in 10gR2.


Addendum (30th Dec 2005)

Clearly one should never say “finally” about Oracle – there’s always something more to discover. Here’s a 10g execution plan from dbms_xplan.display(). Note how the cost column includes a (%CPU) figure in parentheses:

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |       |   329   (1)| 00:00:04 |

|   1 |  SORT AGGREGATE    |      |     1 |       |            |          |

|   2 |   TABLE ACCESS FULL| T2   | 48000 |       |   329   (1)| 00:00:04 |

---------------------------------------------------------------------------

When I redefined the cost column to be number(38,10) instead of the default number(38), I got a string of decimal places after the 329, and the (1) was pushed out of existence. Since that CPU percentage can be a useful clue to where time could be spent, you may not want to lose it.


Back to Index of Topics