Reading the Oracle Explain Plan

Explain plan will display the execution path of a SQL query or how the SQL query will process at runtime. It is easy to get the explain plan by using EXPLAIN PLAN FOR query. The output table PLAN_TABLE is also automatically created for each Oracle user, and it is the default where Explain Plan will insert the results.

As there are a many factors involved in an execution path of a query, reading the results seems to be a challenge. A comprehensive understanding of an Explain Plan may involve knowledge of other Oracle features, and will come with practice, here I have noted down few explanations for getting started on dissecting the explain plan:

I have the following tables created for the below analysis.

Create Table Query: 

Explain Plan Query 1:

  

Usually Explain Plan is read by starting from rightmost bottom entry in the "Operation" column. Next we move on to the immediate left entry one after another. For multiple entries on the same level we consider the top most first.

·   In the above output an "INDEX UNIQUE SCAN" (id 2) is done on the index SYS_C005975(default created for the primary key on table), as we have specified the indexed column with unique value.

·   Next we move on to the left entry (id 1) "TABLE ACCESS BY INDEX ROWID". The rowid from the previous index scan is used to locate the rows in the table. Note that, since we tried to get "name" column which is not part of the index, the table access path is required. If the index includes the columns in the select query then the table access would not have happened. For e.g. "Explain Plan for Select p_id from products where p_id=1".

·   Finally, the leftmost "SELECT STATEMENT" implies that the result set is returned to the user.

Explain Plan Query 2: 

·   We start at id 3 where an index scan is done on "SYS_C005974" of column pi.id to fetch the rowids in the PROD_INFORMATION table. An index scan is usually consumed by the next up left entry of Table access, we can consider id 3 and id 2 to be the same. Operation id 2 uses rowids returned from operation at id 3 to fetch the rows from PROD_INFORMATION table.

·   Moving on to id 4, it does a full table access of PRODUCTS. TABLE ACCESS FULL is usually done incase of un-existing indexes, it will read all the table rows and filter out the ones that do not meet the criteria in the where clause.

·   In the next operation we move on to id 1, where a NESTED LOOPS Join occurs between PROD_INFORMATION table(id 2) and PRODUCTS table(id4). For every row in PROD_INFORMATION(outer table access by index), Oracle will compare to each row of PRODUCTS(inner table access by full table scan). The inner table rows are iterated for every row returned from the index scan of the outer table.

·   Finally the select statement at id 0 will return the result to the user.

Explain Plan Query 3: 

·   In this output, id 6 does a index scan of PRODUCTS table and its consumed by the immediate above id 5, we consider both id 5 and 6 to be the same.

Since both id 4 and id 5 are on the same level, we consider id 4 to be the start of execution plan in this operation.

·   In id 4, there is a full table access of INVENTORY table according to the criteria specified in where clause for inventory table. You can see the Predicate Information for operation id 4.

 ·   On id 6, there is an index unique scan on PRODUCTS table primary key with the criteria we can see in Predicate Information operation id 6.

·   Operation at id 5 takes the rowids information from id 6 and then from the where clause(p.prod_info in (1,2,3)) of our query. From Oracle docs "To access a table by rowid, Oracle first obtains the rowids of the selected rows, either from the statement's WHERE clause or through an index scan"

·   At Id 3 we see a Nested Loop Join of operation id 4 and 5. Oracle takes each of the result from operation id 4 and iterates over every row of operation id 5, to fetch matching rows.

·   Operation id 2 does a full table scan on PROD_INFORMATION to filter the matching rows.

·   The results from operation id 2 and 3 are used by the HASH JOIN(id 1). Hash Joins usually work on large data sets. The result of the join is finaly returned to the user by the Select Statement.

Please note that reading and properly interpreting a plan table will need time and practice.