Oracle Database - EXPLAIN PLAN and Autotrace Comparison

Setting up the test environment

First, I create a partition table and I generate random data with the DBMS_RANDOM package (PL/SQL - DBMS_RANDOM to generate random number, string and date).

[email protected]>create table t
  2  (
  3      collection_year int,
  4      data            varchar2(25)
  5  )
  6  PARTITION BY RANGE (COLLECTION_YEAR) (
  7      PARTITION PART_99 VALUES LESS THAN (2000),
  8      PARTITION PART_00 VALUES LESS THAN (2001),
  9      PARTITION PART_01 VALUES LESS THAN (2002),
 10      PARTITION PART_02 VALUES LESS THAN (2003),
 11      PARTITION the_rest VALUES LESS THAN (MAXVALUE)
 12  );

Table created.

[email protected]>declare
  2  year_random_value VARCHAR2(4) := NULL;
  3  begin
  4  for i in 1 .. 1000
  5  loop
  6    year_random_value := ROUND(DBMS_RANDOM.VALUE(1999,2004));
  7    insert into t values (year_random_value, 'data ' || year_random_value);
  8  end loop;
  9  commit;
 10  end;
 11  /

PL/SQL procedure successfully completed.
[email protected]>select collection_year, count(*) from t group by collection_year;

COLLECTION_YEAR   COUNT(*)
=============== ==========
           1999        107
           2000        213
           2001        183
           2002        217
           2004         92
           2003        188

6 rows selected.

EXPLAIN PLAN output

[email protected]>delete from plan_table;

0 rows deleted.

[email protected]>explain plan for
  2  select * from t where collection_year = 2002;

Explained.

[email protected]>@?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
=====================================================================================================
Plan hash value: 2931986080

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |   217 |  5859 |     3   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|      |   217 |  5859 |     3   (0)| 00:00:01 |     4 |     4 |
|*  2 |   TABLE ACCESS FULL    | T    |   217 |  5859 |     3   (0)| 00:00:01 |     4 |     4 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("COLLECTION_YEAR"=2002)

Note
-----
   - dynamic sampling used for this statement

18 rows selected.

We see with Oracle Database - Explain Plan :

  • that a full table scan will be performed in the table t
  • the cost for each step (3)
  • the expected number of rows to be returned
  • how many bytes of output would be returned
  • that only one partition is being accessed, as shown in the Pstart and Pstop Columns

The optimizer is guessing this information since we did not analyze the table

Autotrace Output

With Oracle Database - Autotrace, we can see this ouptut :

[email protected]>set autotrace traceonly explain
[email protected]>select * from t where collection_year = 2002;

Execution Plan
==========================================================
Plan hash value: 2931986080

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |   217 |  5859 |     3   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|      |   217 |  5859 |     3   (0)| 00:00:01 |     4 |     4 |
|*  2 |   TABLE ACCESS FULL    | T    |   217 |  5859 |     3   (0)| 00:00:01 |     4 |     4 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("COLLECTION_YEAR"=2002)

Note
-----
   - dynamic sampling used for this statement

Identically the same but be careful (I use the database version 10.2) and with a version 9.2, the partition information is missing.


Powered by ComboStrap