Oracle Database - EXPLAIN PLAN and Autotrace Comparison

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


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

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

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

Data Science
Data Analysis
Statistics
Data Science
Linear Algebra Mathematics
Trigonometry

Powered by ComboStrap