The EXPLAIN PLAN facility helps you tune SQL by letting you see the execution plan selected by the Oracle optimizer for a SQL statement. During SQL tuning, you may have to rewrite your queries and experiment with optimizer hints. The EXPLAIN PLAN tool is great for this experimentation, as it immediately lets you know how the query will perform with each change in the code. Because the utility gives you the execution plan without executing the code, you save yourself from having to run untuned software to see whether the changes were beneficial or not. Understanding an EXPLAIN PLAN is critical to understanding query performance. It provides a window into the logic of the Oracle optimizer regarding its choice of execution plans.
The output of the EXPLAIN PLAN tool goes into a table, usually called PLAN_TABLE, where it can be queried to determine the execution plan of statements. In addition, you can use GUI tools, such as OEM or TOAD, to get the execution plan for your SQL statements without any fuss. In OEM, you can view the explain statements from the Top Sessions or the Top SQL charts.
A walkthrough of an EXPLAIN PLAN output takes you through the steps that would be undertaken by the CBO to execute the SQL statement. The EXPLAIN PLAN tool indicates clearly whether the optimizer is using an index, for example. It also tells you the order in which tables are being joined and helps you understand your query performance. More precisely, an EXPLAIN PLAN output shows the following:
• The tables used in the query and the order in which they’re accessed.
• The operations performed on the output of each step of the plan. For example, these could be sorting and aggregation operations.
• The specific access and join methods used for each table mentioned in the SQL statement.
• The cost of each operation.
Oracle creates the PLAN_TABLE as a global temporary table, so all the users in the database can use it to save their EXPLAIN PLAN output. However, you can create a local plan table in your own schema by running the utlxplan.sql script, which is located in the $ORACLE_HOME/rdbms/admin directory.
The script, among other things, creates the plan table, where the output of the EXPLAIN PLAN utility is stored for your viewing. You are free to rename this table. Here’s how you create the plan table so you can use the EXPLAIN PLAN feature:
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql
Table created.
Creating the EXPLAIN PLAN
To create an EXPLAIN PLAN for any SQL data manipulation language statement, you use a SQL statement similar to that shown in Listing 19-2.
Listing 19-2. Creating the EXPLAIN PLAN
SQL> EXPLAIN PLAN
2 SET statement_id = 'test1'
3 INTO plan_table
4 FOR select p.product_id,i.quantity_on_hand
5 FROM oe.inventories i,
6 oe.product_descriptions p,
7 oe.warehouses w
8 WHERE p.product_id=i.product_id
9 AND i.quantity_on_hand > 250
10 AND w.warehouse_id = i.warehouse_id;
Explained.
Producing the EXPLAIN PLAN
You can’t easily select the columns out of the PLAN_TABLE table because of the hierarchical nature of relationships among the columns. Listing 19-3 shows the code that you can use so the EXPLAIN PLAN output is printed in a form that’s readable and shows clearly how the execution plan for the statement looks.
Listing 19-3. Producing the EXPLAIN PLAN
SQL> SELECT lpad(' ',level-1)||operation||' '||options||' '||
2 object_name "Plan"
3 FROM plan_table
4 CONNECT BY prior id = parent_id
5 AND prior statement_id = statement_id
6 START WITH id = 0 AND statement_id = '&1'
7 ORDER BY id;
Enter value for 1: test1
old 6: START WITH id = 0 AND statement_id = '&1'
new 6: START WITH id = 0 AND statement_id = 'test1'
Plan
--------------------------------------------------------
SELECT STATEMENT
HASH JOIN
NESTED LOOPS
TABLE ACCESS FULL INVENTORIES
INDEX UNIQUE SCAN WAREHOUSES_PK
INDEX FAST FULL SCAN PRD_DESC_PK
6 rows selected.
Interpreting the EXPLAIN PLAN Output
Reading an EXPLAIN PLAN is somewhat confusing in the beginning, and it helps to remember these simple principles:
• Each step in the plan returns output in the form of a set of rows to the parent step.
• Read the plan outward starting from the line that is indented the most.
• If two operations are at the same level in terms of their indentation, read the top one first.
• The numbering of the steps in the plan is misleading. Start reading the EXPLAIN PLAN output from the inside out. That is, read the most indented operation first.
In the example shown earlier in Listing 19-3 (I reproduce the plan output after the code), Oracle uses the INVENTORIES table as its driving table and uses the following execution path:
SELECT STATEMENT
HASH JOIN
NESTED LOOPS
TABLE ACCESS FULL INVENTORIES
INDEX UNIQUE SCAN WAREHOUSES_PK
INDEX FAST FULL SCAN PRD_DESC_PK The plan output is as follows:
1. Oracle does a full table scan of the INVENTORIES table.
2. Oracle performs an index unique scan of the WAREHOUSES table using its primary key
index.
3. Oracle performs a nested loop operation to join the rows from steps 1 and 2.
4. Oracle performs an index fast full scan of the product_descriptions table using its primary
key, PRD_DESC_PK.
5. In the final step, Oracle performs a hash join of the set from step 3 and the rows resulting
from the index full scan of step 4.
Using the output of the EXPLAIN PLAN, you can quickly see why some of your queries are taking much longer than anticipated. Armed with this knowledge, you can fine-tune a query until an acceptable performance threshold is reached. The wonderful thing about the EXPLAIN PLAN is that you never have to execute any statement in the database to trace the execution plan of the statement.
Comments