Understanding Oracle QUERY PLAN - A 10 minutes guide

What is Query Execution Plan?

When you fire an SQL query to Oracle, Oracle database internally creates a query execution plan in order to fetch the desired data from the physical tables. The query execution plan is nothing but a set of methods on how the database will access the data from the tables. This query execution plan is crucial as different execution plans will need different cost and time for the query execution.

How the Execution Plan is created actually depends on what type of query optimizer is being used in your Oracle database. There are two different optimizer options – Rule based (RBO) and Cost based (CBO) Optimizer. For Oracle 10g, CBO is the default optimizer. Cost Based optimizer enforces Oracle to generate the optimization plan by taking all the related table statistics into consideration. On the other hand, RBO uses a fixed set of pre-defined rules to generate the query plan. Obviously such fixed set of rules may not always be able to create the plan that is most efficient in nature. This is because an efficient plan will depend heavily on the nature and volume of tables’ data. Because of this reason, CBO is preferred over RBO.

Understanding Oracle Query Execution Plan

But this article is not for comparing RBO and CBO (In fact, there is not much point in comparing these two). This article will briefly help you understand,

1. How can we see Query Execution Plan
2. How do we understand (or rather interpret) the execution plan.
   

So let’s begin. I will be using Oracle 10g server and SQL *Plus client to demonstrate all the details.

Oracle Full Table Scan (FTS)

Let’s start by creating a simple product table with the following structure,

ID number(10)
NAME varchar2(100)
DESCRIPTION varchar2(255)
SERVICE varchar2(30)
PART_NUM varchar2(50)
LOAD_DATE date

Next I will insert 15,000 records into this newly created table (data taken from one of my existing product table from one of my client’s production environment).

Remember, currently there is no index on the table.

So we start our journey by writing a simple select statement on this table as below,

SQL> explain plan for select * from product;
Explained.


SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------
Plan hash value: 3917577207
-------------------------------------
| Id  | Operation          | Name   |
-------------------------------------
|   0 | SELECT STATEMENT   |        |
|   1 |  TABLE ACCESS FULL | PRODUCT|
-------------------------------------

Note
-----
   - rule based optimizer used (consider using cbo)

Notice that optimizer has decided to use RBO instead of CBO as Oracle does not have any statistics for this table. Let’s now build some statistics for this table by issuing the following command,

SQL> Analyze table product compute statistics;

Now let’s do the same experiment once again,

SQL> explain plan for select * from product;
Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------
Plan hash value: 3917577207
-----------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes |
-----------------------------------------------------
|   0 | SELECT STATEMENT   |         | 15856 |  1254K|
|   1 |  TABLE ACCESS FULL | PRODUCT | 15856 |  1254K|
-----------------------------------------------------

You can easily see that this time optimizer has used Cost Based Optimizer (CBO) and has also detailed some additional information (e.g. Rows etc.)

The point to note here is, Oracle is reading the whole table (denoted by TABLE ACCESS FULL) which is very obvious because the select * statement that is being fired is trying to read everything. So, there’s nothing interesting up to this point.

Index Unique Scan

Now let’s add a WHERE clause in the query and also create some additional indexes on the table.

SQL> create unique index idx_prod_id on product (id) compute statistics;

Index created.

SQL>  explain plan for select id from product where id = 100;

Explained.

SQL>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------
Plan hash value: 2424962071

---------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes |
---------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     1 |     4 |
|*  1 |  INDEX UNIQUE SCAN | IDX_PROD_ID |     1 |     4 |
---------------------------------------------------------

So the above statement indicates that CBO is performing Index Unique Scan. This means, in order to fetch the id value as requested, Oracle is actually reading the index only and not the whole table. Of course this will be faster than FULL TABLE ACCESS operation shown earlier.

Table Access by Index RowID

Searching the index is a fast and an efficient operation for Oracle and when Oracle finds the desired value it is looking for (in this case id = 100), it can also find out the rowid of the record in product table that has id = 100. Oracle can then use this rowid to fetch further information if requested in query. See below,

SQL> explain plan for select * from product where id = 100;

Explained.

SQL>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------
Plan hash value: 3995597785

----------------------------------------------------------
| Id  | Operation                   | Name   |Rows | Bytes|
----------------------------------------------------------
|   0 | SELECT STATEMENT            |        |   1 |   81 |
|   1 |  TABLE ACCESS BY INDEX ROWID| PRODUCT|   1 |   81 |
|*  2 |   INDEX UNIQUE SCAN         | IDX_PROD_ID | 1 |   |
----------------------------------------------------------

TABLE ACCESS BY INDEX ROWID is the interesting part to check here. Since now we have specified select * for id = 100, so Oracle first use the index to obtain the rowid of the record. And then it selects all the columns by the rowid.

Index Range Scan

But what if we specify a >, or between criteria in the WERE clause instead of equality condition? Like below,

SQL> explain plan for select id from product where id <10 

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------
Plan hash value: 1288034875

-------------------------------------------------------
| Id  | Operation        | Name        | Rows  | Bytes |
-------------------------------------------------------
|   0 | SELECT STATEMENT |             |     7 |    28 |
|*  1 |  INDEX RANGE SCAN| IDX_PROD_ID |     7 |    28 |
-------------------------------------------------------

So this time CBO goes for an Index Range Scan instead of INDEX UNIQUE SCAN. The same thing will normally happen if we use a between clause also.

Index Fast Full Scan

Now, let’s see another interesting aspect of INDEX scan here by just altering the “ 10”. Before we see the outcome, just remind yourself that there are 15000 over products with their ids starting from 1 to 15000+. So if we write “10” we are likely to get almost 14990+ records in return. So does Oracle go for an INDEX RANGE SCAN in this case? Let’s see,

SQL> explain plan for select id from product where id>10;

Explained.

SQL>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------
Plan hash value: 2179322443

--------------------------------------------------------
| Id  | Operation            | Name       | Rows |Bytes |
--------------------------------------------------------
|   0 | SELECT STATEMENT     |            | 15849|63396 |
|*  1 |  INDEX FAST FULL SCAN| IDX_PROD_ID| 15849|63396 |
---------------------------------------------------------

So, Oracle is actually using a INDEX FAST FULL SCAN to “quickly” scan through the index and return the records from table. This scan is "quick" because unlike index full scan or index unique scan, INDEX FAST FULL SCAN utilizes multiple-block input-output (I/O) whereas the formers utilizes single block I/O.

Note on QUERY PLAN

FTS or Full Table Scan

• Whole table is read upto high water mark
• Uses multiblock input/output
  
• Buffer from FTS operation is stored in LRU end of buffer cache
  

Index Unique Scan

• Single block input/output

Index Fast Full Scan

• Multi block i/o possible
• Returned rows may not be in sorted order
  

Index Full Scan

• Single block i/o
• Returned rows generally will be in sorted order
  

So I think we covered the basics of simple SELECT queries running on a single table. In the next article, We will move forward to understand how does the query plan change when we join more than one table - that is, we will know how to read the query plan when there is join between tables.