Oracle Database - EXPLAIN PLAN and Autotrace Comparison

Card Puncher Data Processing

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

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

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

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





Discover More
Sql Developer Autotrace
Oracle Database - Autotrace

Autotrace is: a system variable of sql plus can also be found in SQL Developer AUTOTRACE provides you: an execution plan (such as explain plan) and some important regarding its actual execution....
Card Puncher Data Processing
Oracle Database - Explain Plan

EXPLAIN PLAN is an Oracle SQL Command that tell you what the query plan for a given SQL would be : if executed it right now in the current session with the current settings For this purpose, it...



Share this page:
Follow us:
Task Runner