TKPROF ED EXPLAIN PLAN

TKPROF ED EXPLAIN PLAN
  • 29
    Gen

TKPROF ED EXPLAIN PLAN

twittergoogle_pluslinkedin

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 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’;