The EXPLAIN PLAN command allows developers to view the query execution plan that the Oracle optimizer uses to execute a SQL statement. This command is very helpful in improving the performance of SQL statements because it does not actually execute the SQL statement—it only outlines the plan and inserts this execution plan in an Oracle table. Prior to using the EXPLAIN PLAN command, a file called utlxplan.sql (located in the same directory as catalog.sql, typically ORACLE_HOME/rdbms/admin) must be executed under the Oracle account that will be executing the EXPLAIN PLAN command.
The script creates a table called PLAN_TABLE that the EXPLAIN PLAN command uses to insert the query execution plan in the form of records. This table can then be queried and viewed to determine if the SQL statement needs to be modified to force a different execution plan. Oracle supplies queries to use against the plan table, too: utlxpls.sql and utlxplp.sql. Either will work, but utlxplp.sql is geared toward parallel queries. An EXPLAIN PLAN example is shown next (executed in SQL*Plus).
Q. Why use EXPLAIN without TRACE?
A. The statement is not executed; it only shows what will happen if the statement is executed.
Q. When do you use EXPLAIN without TRACE?
A. When the query will take an exceptionally long time to run.
The procedures for running TRACE vs. EXPLAIN are demonstrated here:
Q. How do I use EXPLAIN by itself?
A. Follow these steps:
1. Find the script; it is usually in the ORACLE_HOME/rdbms/admin directory:
2. Execute the script utlxplan.sql in SQLPLUS:
This script creates the PLAN_TABLE for the user executing the script. You can create your own PLAN_TABLE, but use Oracle’s syntax—or else!
3. Run EXPLAIN PLAN for the query to be optimized (the SQL statement is placed after the FOR clause of the EXPLAIN PLAN statement):
4. Optionally, you can also run EXPLAIN PLAN for the query to be optimized using a tag for the statement:
TIP
Use the SET STATEMENT_ID = ‘your_identifier’ when the PLAN_TABLE will be populated by many different developers. I rarely use the SET STATEMENT_ID statement. Instead, I explain a query, look at the output, and then delete from the PLAN_TABLE table. I continue to do this (making changes to the query), until I see an execution plan that I think is favorable. I then run the query to see if performance has improved. If multiple developers/DBAs are using the same PLAN_TABLE, the SET STATEMENT_ID (case sensitive) is essential to identifying a statement.
5. Select the output from the PLAN_TABLE:
TIP
Use EXPLAIN instead of TRACE so you don’t have to wait for the query to run. EXPLAIN shows the path of a query without actually running the query. Use TRACE only for multiquery batch jobs to find out which of the many queries in the batch job are slow.
TIP
You can use the utlxpls.sql and utlxplp.sql queries provided by Oracle to query the plan table without having to write your own query and without having to format the output.
Leave a Reply