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).

gerardnico@orcl>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.

gerardnico@orcl>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.
gerardnico@orcl>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

gerardnico@orcl>delete from plan_table;

0 rows deleted.

gerardnico@orcl>explain plan for
  2  select * from t where collection_year = 2002;

Explained.

gerardnico@orcl>@?/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

Autotrace Output

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

gerardnico@orcl>set autotrace traceonly explain
gerardnico@orcl>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