TKPROF ED EXPLAIN PLAN

TKPROF
The TKPROF program converts Oracle trace files into a more readable form. If you have a problem query you can user TKPROF to get more information. The TKProf executable is located in the ORACLE HOME/bin directory.
Step:
sqlplus schema/password
set head off
ALTER SESSION SET sql_trace = true;
ALTER SESSION SET tracefile_identifier = trace_xxx;
run the query to be analyzed
ALTER SESSION SET sql_trace = false;
cd $ORACLE_HOME/bin
tkprof trace_xxx.trc trace_I_query.log
EXPLAIN PLAN
The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. A statement’s execution plan is the sequence of operations Oracle performs to run the statement.
The row source tree is the core of the execution plan. It shows the following information:
• An ordering of the tables referenced by the statement
• An access method for each table mentioned in the statement
• A join method for tables affected by join operations in the statement
• Data operations like filter, sort, or aggregation
In addition to the row source tree, the plan table contains information about the following:
• Optimization, such as the cost and cardinality of each operation
• Partitioning, such as the set of accessed partitions
• Parallel execution, such as the distribution method of join inputs
The EXPLAIN PLAN results let you determine whether the optimizer selects a particular execution plan, such as, nested loops join. It also helps you to understand the optimizer decisions, such as why the optimizer chose a nested loops join instead of a hash join, and lets you understand the performance of a query.
Step:
sqlplus schema/password
set head off
EXPLAIN PLAN SET statement_id=’xxx’
INTO PLAN_TABLE
FOR
run the query to be analyzed;
set markup html preformat on
set termout on
set verify off
set feedback on
set pagesize 6000
set linesize 200
set heading on
set array 1
set echo off
spool xxx_explain.log
select * from table (dbms_xplan.display(‘PLAN_TABLE’,’xxx’) );
spool off
DELETE plan_table WHERE statement_id=’xxx’;