Oracle Database - EXPLAIN PLAN and Autotrace Comparison

> Database > Oracle Database

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

2 - 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

Advertising

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