Oracle execution plan Table[Reading]

Reading Oracle Plan Table

The Execution Plan

An execution plan shows the detailed steps necessary to execute a SQL statement. These steps are expressed as a set of database operators that consume and produce rows. The order of the operators and their implementations is decided by the query optimizer using a combination of query transformations and physical optimization techniques. While the display is commonly shown in a tabular format, the plan is in fact tree-shaped. For example, consider the following query based on the SH schema (Sales History):

SELECT prod_category, AVG(amount_sold) 
FROM sales s, products p 
WHERE p.prod_id = s.prod_id 
GROUP BY prod_category;

The tabular representation of this query's plan is:

Figure 1: Tabular shaped execution plan Result of the Select Statement

While the tree-shaped representation of the plan is:

Figure 2: Tree shaped execution plan

The tabular representation is a top-down, left-to-right traversal of the execution tree. When you read a plan tree you should start from the bottom left and work across and then up. In the above example, begin by looking at the leaves of the tree. In this case the leaves of the tree are implemented using a full table scans of the PRODUCTS and the SALES tables. The rows produced by these table scans will be consumed by the join operator. Here the join operator is a hash-join (other alternatives include nested-loop or sort-merge join). Finally the group-by operator implemented here using hash (alternative would be sort) consumes rows produced by the join-operator, and return the final result set to the end user.
This is a cost-based query, since the “cost” hint is used.
We should be suspicious of the TABLE ACCESS FULL statement, since this is very slow.
The student should recommend:

Check for proper index on this table.
Try re-explaining this query with a “rule” hint.