Using EXPLAIN PLAN, SQL Trace and TKPROF

SQL Trace facility and TKPROF
- access the efficiency of the SQL statements
- use these tools with EXPLAIN PLAN rather using EXPLAIN PLAN alone

TKPROF is be albe to
- format the contents of the trace file and place the output into a readable output file
- determine the execuaton plans of SQL statements
- create a SQL script that stores the statistics in the database

STEPS:

1. set initialization parameters
TIMED_STATISTICS
MAX_DUMP_FILE_SIZE
USER_DUMP_DEST

2. Enabling the SQL trace facility
- DBMS_SESSTION.SET_SQL_TRACE
- ALTER SESSION SET SQL_TRACE = TRUE;

3. Formatting Trace Files with TKPROF

TKPROF filename1 filename2 [ SORT = { option (option [option]...)} ]
[ PRINT = integer ] [ AGGREGATE = { YES NO } ]
[ INSERT = filename3 ]
[ SYS = { YES NO } ]
[ [ TABLE = schema.table ] EXPLAIN = user/password ]
[ RECORD = filename ]


-------------------
Using EXPLAIN PLAN
--------------------

- The EXPLAIN PLAN statement displays exercution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT and DELETE statements.
- before issuing an EXPLAIN PLAN statement, you must have a table to hold its output
- use UTLXPLAN.SQL to create the PLAN_TABLE
SQL> @e:\oracle\ora92\rdbms\admin\utlxplan.sql

- Displaying PLAN TABLE output: (most recent plan table )
UTLXPLS.SQL - Shows plan table output for serial processing
UTLXPLP.SQL - Shows plan table output with parallel execution columns