The EXPLAIN PLAN program can be used in standalone fashion without the TKPROF application.

In order to use EXPLAIN PLAN in this way, the user must create a "PLAN" table in their tablespace. This can either be done with the supplied SQL script XPLAINPL.SQL which is located in the directory pointed at by the ORA_RDBMS logical in VMS or in the $oracle_home/rdbms/admin directory on most UNIX systems. Under ORACLE7 the script has been renamed to UTLXPLAIN.SQL.

The XPLAINPL.SQL procedure creates the PLAN_TABLE. As an alternative, the user can create a table with any name they choose, but the table must have the same columns and data types as PLAN_TABLE.

Click here to view the characteristics of the EXPLAIN PLAN output table.

When TKPROF is run using the EXPLAIN option, the table is created and dropped automatically. If the PLAN_TABLE is created for use in EXPLAIN PLAN it is permanent.

The table should have the DELETE command issued against it between runs of EXPLAIN PLAN or duplicate rows will be inserted into the table and into any output generated based on the table.

Once the PLAN_TABLE is generated, the user issues the EXPLAIN PLAN command from within SQLPLUS to generate output to the table.