Difference Between SQL TRACE, EXPLAIN PLAN and TKPROF.

Difference Between SQL TRACE, EXPLAIN PLAN and TKPROF.

Overview Of SQL TRACE
-------------------------------
The diagnostic tool 'sql trace' provides performance information about individual SQL statements and generates the following statistics for each statement:

* parse, execute, and fetch counts
* CPU and elapsed times
* physical reads and logical reads
* number of rows processed
* misses on the library cache

This information is input to a trace (.trc) file and sql trace can be enabled/disabled for a session or an instance.

Setting Initialization Parameters:

1.SQL_TRACE
Enable/Disable SQL Trace for the instance.(TRUE/FALSE)

2.TIMED_STATISTICS
Enable/Disable the collection of timed statistics, such as CPU and elapsed times.(TRUE/FALSE)

3.USER_DUMP_DEST
Specifies the destination for the trace file.

Enabling/Disabling SQL Trace:

ALTER SESSION SET SQL_TRACE = TRUE;
ALTER SESSION SET SQL_TRACE = FALSE;

Enabling/Disabling TIMED_STATISTICS:

ALTER SYSTEM SET TIMED_STATISTICS = TRUE;
ALTER SESSION SET TIMED_STATISTICS = FALSE:

Trace Files
-----------
Oracle will generate trace (.trc) files for every session where the value of SQL_TRACE = TRUE and write them to the USER_DUMP_DEST destination. If tracing has been enabled for the instance then individual trace files will be generated for each session, unless otherwise disabled (see above). Note, that the generated files may be owned by an operating system user other than your own so you may have to get this user to grant you access before you can use TKPROF to format them.

Using TKPROF
-------------------
The TKPROF facility accepts as input an SQL trace file and produces a formatted output file.

Simple Example
-------------------
This example shows TKPROF being run to format a trace file named "dsdb2_ora_18468.trc" and writing it to a formatted output file named "dsdb2_trace.out".

$ TKPROF dsdb2_ora_18468.trc dsdb2_trace.out SYS=NO EXPLAIN=SCOTT/TIGER

The EXPLAIN PLAN Command
----------------------------------------
The EXPLAIN PLAN command displays the execution plan chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. A statement's execution plan is the sequence of operations that Oracle performs to execute the statement. By examining the execution plan, you can see exactly how Oracle executes your SQL statement. This information can help you determine whether the SQL statement you have written takes advantage of the indexes available.

Creating the Output Table
-------------------------------
Before you can issue an EXPLAIN PLAN statement, there must exist a table to hold its output, you do either of the following:

* Run the SQL script "UTLXPLAN.SQL" to create a sample output table called PLAN_TABLE in your schema.

* Issue a CREATE TABLE statement to create an output with any name you choose.  You can then issue an EXPLAIN PLAN statement and direct its output to this table.  Any table used to store the output of the EXPLAIN PLAN command must have the same column names and datatypes as the PLAN_TABLE

SQL Trace Facility Statistics
----------------------------------
TKPROF lists the statistics for a SQL statement returned by the SQL trace facility in rows and columns.  Each row corresponds to one of
three steps of SQL statement processing:

* PARSE
This step translates the SQL statement into an execution plan. This includes checks for proper security authorization and checks or the existence of tables, columns, and other referenced objects.

* EXECUTE
This step is the actual execution of the statement by Oracle. For INSERT, UPDATE, and DELETE statements, this step modifies the data.  For SELECT statements, the step identifies the selected rows.

* FETCH
This step retrieves rows returned by a query.Fetches are only performed for SELECT statements.