Tuning -- EXPLAIN PLAN

 Statement to display the EXPLAIN PLAN

SELECT lpad(' ',level-1)||operation||' '||options||' '||
        object_name "Plan"
   FROM plan_table
CONNECT BY prior id = parent_id
        AND prior statement_id = statement_id
  START WITH id = 0 AND statement_id = '&1'
  ORDER BY id;

*********************************************************************************** 

Confused about how to understand Oracle Query Execution Plan? This 10 minutes step by step primer is the first of a two part article that will teach you exactly the things you must know about Query Plan.

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 

*******************************************************************************

EXPLAIN PLAN Examples

The following are EXPLAIN PLAN examples.

Example 1 EXPLAIN PLAN example_plan1

EXPLAIN PLAN SET statement_id = 'example_plan1' FOR SELECT full_name FROM per_all_people_f WHERE UPPER(full_name) LIKE 'Pe%' ; Plan --------------------------------------------- SELECT STATEMENT TABLE ACCESS FULL PER_ALL_PEOPLE_F

This plan shows execution of a SELECT statement. The table per_all_people_f is accessed using a full table scan.

• Every row in the table per_all_people_f is accessed, and the WHERE clause criteria is evaluated for every row.
• The SELECT statement returns the rows meeting the WHERE clause criteria.

Example 2 EXPLAIN PLAN example_plan2

EXPLAIN PLAN SET statement_id = 'example_plan2' FOR SELECT full_name FROM per_all_people_f WHERE full_name LIKE 'Pe%' ; Plan --------------------------------------------- SELECT STATEMENT TABLE ACCESS BY INDEX ROWID PER_ALL_PEOPLE_F INDEX RANGE SCAN PER_PEOPLE_F_N54

This plan shows execution of a SELECT statement.

• Index per_people_f_n54 is used in a range scan operation.
• The table per_all_people_f is accessed through ROWID. ROWIDs are obtained from the index in the previous step for keys that meet the WHERE clause criteria. When the table is accessed, any additional WHERE clause conditions that could not be evaluated during the range scan (because the column is present in the table and not in the index) are also evaluated.
• The SELECT statement returns rows satisfying the WHERE clause conditions (evaluated in previous steps).

Example 3 EXPLAIN PLAN example_plan3

EXPLAIN PLAN SET statement_id = 'example_plan3' FOR SELECT segment1, segment2, description, inventory_item_id FROM mtl_system_items msi WHERE segment1 = :b1 AND segment2 LIKE '%-BOM' AND NVL(end_date_active,sysdate+1) > SYSDATE ; Plan -------------------------------------------------- SELECT STATEMENT TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS INDEX RANGE SCAN MTL_SYSTEM_ITEMS_N8

This plan shows execution of a SELECT statement.

• Index mtl_system_items_n8 is used in a range scan operation. This is an index on (segment1, segment2, segment3). The range scan happens using the following condition:

  segment1 = :b1
  
  

  The rows that come out of this step satisfy all the WHERE clause criteria that can be evaluated with the index columns. Therefore, the following condition is also evaluated at this stage:

  segment2 LIKE '%-BOM' 
  
  

• The table per_all_people_f is accessed through ROWIDs obtained from the index in the previous step. When the table is accessed, any additional WHERE clause conditions that could not be evaluated during the range scan (because the column is present in the table and not in the index) are also evaluated. Therefore, the following condition is evaluated at this stage:

  NVL(end_date_active,sysdate+1) > SYSDATE
  
  

• The SELECT statement returns rows satisfying the WHERE clause conditions (evaluated in previous steps).

Example 4 EXPLAIN PLAN example_plan4

EXPLAIN PLAN SET statement_id = 'example_plan4' FOR SELECT h.order_number, l.revenue_amount, l.ordered_quantity FROM so_headers_all h, so_lines_all l WHERE h.customer_id = :b1 AND h.date_ordered > SYSDATE-30 AND l.header_id = h.header_id ; Plan -------------------------------------------------- SELECT STATEMENT NESTED LOOPS TABLE ACCESS BY INDEX ROWID SO_HEADERS_ALL INDEX RANGE SCAN SO_HEADERS_N1 TABLE ACCESS BY INDEX ROWID SO_LINES_ALL INDEX RANGE SCAN SO_LINES_N1

This plan shows execution of a SELECT statement.

• Index so_headers_n1 is used in a range scan operation. This is an index on customer_id. The range scan happens using the following condition:

  customer_id = :b1
  
  

• The table so_headers_all is accessed through ROWIDs obtained from the index in the previous step. When the table is accessed, any additional WHERE clause conditions that could not be evaluated during the range scan (because the column is present in the table and not in the index) are also evaluated. Therefore, the following condition is evaluated at this stage:

  h.date_ordered > sysdate-30
  
  

• For every row from so_headers_all satisfying the WHERE clause conditions, a range scan is run on so_lines_n1 using the following condition:

  l.header_id = h.header_id
  
  

• The table so_lines_all is accessed through ROWIDs obtained from the index in the previous step. When the table is accessed, any additional WHERE clause conditions that could not be evaluated during the range scan (because the column is present in the table and not in the index) are also evaluated. There are no additional conditions to evaluate here.
• The SELECT statement returns rows satisfying the WHERE clause conditions (evaluated in previous steps).