Execution Plans - Troubleshooting Oracle Performance - page 293

Figure 10-1 shows the syntax of the EXPLAIN PLAN statement. The following parameters are available:
•

statement specifies for which SQL statement the execution plan should be provided. The
following SQL statements are supported: SELECT , INSERT , UPDATE , MERGE , DELETE , CREATE
TABLE , CREATE INDEX , and ALTER INDEX .

•

id specifies a name to distinguish between several execution plans stored in the plan table.
Any string of up to 30 characters is supported. This parameter is optional. The default value
is NULL .

•

table specifies the name of the plan table where the information about the execution plan is
inserted. This parameter is optional. The default value is plan_table . Whenever necessary,
it's possible to specify a schema name as well as a database link name with the usual syntax:
schema.table@dblink .

Figure 10-1. Syntax of the EXPLAIN PLAN statement

It's important to recognize that the EXPLAIN PLAN statement is a DML statement, not a DDL statement. This
means it doesn't perform an implicit commit of the current transaction. It simply inserts rows into the plan table.
To execute the EXPLAIN PLAN statement, the privileges to execute the SQL statement that is passed as a parameter
are needed. Note that when working with views, appropriate privileges on all underlying tables and views are required
as well. Because that's counterintuitive, look at the following example. Notice how the user is able to execute a query
referencing the user_objects view but isn't able to execute the EXPLAIN PLAN statement for the very same query:

SQL> SELECT count(*) FROM user_objects;

COUNT(*)
----------
29

SQL> EXPLAIN PLAN FOR SELECT count(*) FROM user_objects;
EXPLAIN PLAN FOR SELECT count(*) FROM user_objects
*
ERROR at line 1:
ORA-01039: insufficient privileges on underlying objects of the view

As pointed out by the error message, the user lacks the SELECT privilege on one or several data dictionary tables
referenced by the user_objects view.

The Plan Table
The plan table is where the EXPLAIN PLAN statement writes the output. If the plan table doesn't exist, an error is
raised. A default plan table is owned by SYS , and a public synonym named plan_table exposes the table to all users.
Whenever a private table is needed, it's good practice to manually create it with the utlxplan.sql script, available
under the directory $ORACLE_HOME/rdbms/admin . If a plan table is manually created, you shouldn't forget to
drop it and recreate it again in case of a database upgrade. In fact, it happens that new attributes are added