According to autotrace, I am not doing partition elimination - how do I fix this ?

The Oracle (tm) Users' Co-Operative FAQ

Why does AUTOTRACE not show partition pruning in the explain plan ?

Autotrace not showing partition pruning/elimination is bug 1426992, but, after investigation Oracle has decided that this is not an optimiser bug, but a bug in SQL*Plus. You can, with a bit of knowledge of your data and a little experimentation, deduce that partition pruning is taking place from the output of autotrace, but there are much easier ways !

The following demonstration shows the failings in autotraceand demonstrates a couple of other methods of determining whether or not your partitions are being pruned - or not.

Autotrace

First of all, create a simple table range partitioned over 6 different partitions, and fill it with some test data extracted from ALL_OBJECTS.

SQL> create table tab_part (part_key number(1), some_text varchar2(500))

  2  partition by range (part_key) (

  3  partition part_1 values less than (2),

  4  partition part_2 values less than (3),

  5  partition part_3 values less than (4),

  6  partition part_4 values less than (5),

  7  partition part_5 values less than (6),

  8  partition part_6 values less than (MAXVALUE) );
复制

Table created.
复制

SQL> insert /*+ append */ into tab_part

  2  select mod(rownum, 10), object_name

  3  from all_objects;
复制

24683 rows created.
复制

SQL> commit;
复制

Commit complete.
复制

Once the table has been filled, analyse it and see how the data has been spread over the various partitions. The first and last partitions have more data in them that the remaining four, hence the differing totals.

SQL> analyze table tab_part compute statistics;
复制

Table analyzed.
复制

SQL> select partition_name, num_rows

  2  from user_tab_partitions

  3  where table_name = 'TAB_PART'

  4  order by partition_name;
复制

PARTITION_NAME                   NUM_ROWS

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

PART_1                               4937

PART_2                               2469

PART_3                               2469

PART_4                               2468

PART_5                               2468

PART_6                               9872
复制

6 rows selected.
复制

Now that we have a table to work with, we shall see what autotrace has to say about partition elimination. First, however, note how many logical reads a full scan of the entire table needs : 

SQL> set autotrace on
复制

SQL> select count(*) from tab_part;
复制

  COUNT(*)

----------

     24683
复制

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=42 Card=1)

   1    0   SORT (AGGREGATE)

   2    1     PARTITION RANGE (ALL)

   3    2       TABLE ACCESS (FULL) OF 'TAB_PART' (Cost=42 Card=24683)
复制

Statistics

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

          0  recursive calls

          0  db block gets

        135  consistent gets

          0  physical reads

          0  redo size

        381  bytes sent via SQL*Net to client

        499  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed
复制

To read 24,683 rows of data Oracle had to perform 135 logical reads. Keep this in mind and note that the autotrace output shows a full table scan - as we would expect on an unindexed table. The next count should only look in a single partition :

SQL> select count(*) from tab_part where part_key = 7;
复制

  COUNT(*)

----------

      2468
复制

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=17 Card=1 Bytes=2)

   1    0   SORT (AGGREGATE)

   2    1     TABLE ACCESS (FULL) OF 'TAB_PART' (Cost=17 Card=2468 Bytes=4936)
复制

 
复制

Statistics

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

          0  recursive calls

          0  db block gets

         49  consistent gets

          0  physical reads

          0  redo size

        380  bytes sent via SQL*Net to client

        499  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed
复制

This seems to have again carried out a full table scan, but as we already know that a real FTS takes 135 logical reads, the fact that only 49 were required here should indicate that something is different. Autotrace's output is not showing partition elimination. If you didn't know how many reads were required to full scan the table, you would be hard pressed to determine that partition elimination had taken place in this scan.

Event 10053

There are other methods by which we can obtain a true picture of the plan used by the optimiser - a 10053 trace for example would show the details. I've never had to use a 10053 trace so I'm unfortunately not in a position to explain its use, I leave this as 'an exercise for the reader' as they say :o)

SQL_TRACE and TKPROF

I have used SQL_TRACE and TKPROF though, so here's what shows up when SQL_TRACE is set true.

SQL> set autotrace off
复制

SQL> alter session set sql_trace = true;
复制

Session altered.
复制

SQL> alter session set tracefile_identifier = 'PARTITION';
复制

Session altered.
复制

SQL> select count(*) from tab_part where part_key = 7;
复制

  COUNT(*)

----------

      2468
复制

SQL> alter session set sql_trace = false
复制

Session altered.
复制

At this point, exit from SQL*Plus and locate the trace file in USER_DUMP_DEST which has 'PARTITION' in it's name. This is the one you want to run through TKPROF. The output from this is shown below :

select count(*) from tab_part where part_key = 7
复制

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.01       0.00          0          0          0           0

Fetch        2      0.01       0.01          0         49          0           1

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

total        4      0.02       0.01          0         49          0           1
复制

Misses in library cache during parse: 0

Optimizer goal: CHOOSE

Parsing user id: 62  
复制

Rows     Row Source Operation

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

      1  SORT AGGREGATE (cr=49 r=0 w=0 time=10353 us)

   2468   TABLE ACCESS FULL TAB_PART PARTITION: 6 6 (cr=49 r=0 w=0 time=6146 us)
复制

The explain plan clearly shows that partition 6 was the start and stop partition in the scan. In addition, there were 49 logical reads performed to get at the count. This is identical to what we saw above with autotrace, except we get to see that partition pruning did actually take place.

Explain Plan

Back in SQL*Plus, there is another method that can be used. The old faithful EXPLAIN PLAN will show how partition pruning did take place.

SQL> explain plan

  2  set statement_id = 'Norman'

  3  for

  4  select count(*) from tab_part where part_key = 7;
复制

Explained.
复制

SQL> set lines 132

SQL> set pages 10000
复制

SQL> col operation format a20

SQL> col options format a15

SQL> col object_name format a15

SQL> col p_start format a15

SQL> col p_stop format a15

SQL> col level noprint
复制

SQL>  select level,lpad('  ', 2*level-1)||operation as operation,

  2   options,

  3   object_name,

  4   partition_start as p_start,

  5   partition_stop as p_stop,

  6   cardinality

  7   from plan_table

  8   where statement_id = 'Norman'

  9   start with id=0

 10   connect by prior id=parent_id

 11   order by level
复制

OPERATION            OPTIONS         OBJECT_NAME     P_START         P_STOP          CARDINALITY

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

 SELECT STATEMENT                                                                              1

   SORT              AGGREGATE                                                                 1

     TABLE ACCESS    FULL            TAB_PART        6               6                      2468
复制

Once again, the plan clearly shows that partition pruning takes place. The problem is that autotrace doesn't show it at all. Unless you really know how many blocks of data you have in a table and all of its partitions, you may find it difficult to determine whether or not you are seeing a 'true' plan when using partitioned tables and autotrace.

Note: Do you ever suffer from the PLAN_TABLE growing too big as developers fail to delete old rows from the table? Alternatively, do you forget to delete rows from the table?

Take a copy of $ORACLE_HOME/rdbms/admin/utlxplan.sql and edit it.

Change this :

create table PLAN_TABLE (

statement_id    varchar2(30), ...

filter_predicates varchar2(4000));
复制

To this :

create global temporary table PLAN_TABLE (

statement_id    varchar2(30), ...

filter_predicates varchar2(4000))

on commit preserve rows;
复制

Now login to SQL*Plus as SYS and :

sql> @?/rdbms/admin/utlxplan_edited    /* Or whatever your copy is called */
复制

sql> grant all on plan_table to public;
复制

sql> create public synonym PLAN_TABLE for SYS.PLAN_TABLE;
复制

Now when developers or DBAs use PLAN_TABLE and logout their rows will be deleted. A self-tidying PLAN_TABLE. Of course, this is no good if you want to keep rows in PLAN_TABLE between sessions.

DBMS_XPLAN

Under Oracle 9i (release 2 I think) there is a new PL/SQL package which you can use to show explain plans. The above statement could have its plan shown using this command :

SQL> Select * from table(dbms_xplan.display(statement_id=>'Norman'));
复制

or, if this was the only statement in my PLAN_TABLE :

SQL> Select * from table(dbms_xplan.display);
复制

There is much more information shown with this new feature than with a 'normal' explain plan and you don't have to worry about all that formatting either.

Summary

In summary, autotrace doesn't show partition elimination in Oracle up to versions 9i release 2. You should therefore be aware of this fact and use SQL_TRACE or EXPLAIN_PLAN to get at the true plan for the SQL you are trying to tune/debug.

Further reading:

Note: 166118.1 Partition Pruning/Elimination on Metalink. You will need a support contract to access Metalink.
Bug: 1426992 SQLPlus AUTOTRACE does not show correct explain plan for partition elimination. Again on Metalink.