http://VietEnglish.com

http://Edgar411.com

http://ForumGrouper.com

http://hpricot.com

tkprofDemo.html
02-04-2002 Bikle
-----------------

A Simple Demonstration of TKPROF

This page gives some syntax associated with using the TKPROF utility
to help me study an SQL statement.

First, we make sure that we have access to a 'PLAN' table:

planTable.html


Next, we fire up sqlplus and issue a couple ALTER SESSION commands:

ALTER SESSION SET sql_trace        = true;
ALTER SESSION SET timed_statistics = true;

Then, we issue the SQL command we want to look at.  A demo script
is linked below:

demoSQL.txt

Next, we locate the resulting .trc file which landed in the
directory known as user_dump_dest.

A demo file is displayed below:

demoTrc.txt

In order for users other than oracle to read .trc files in user_dump_dest,
the DBA needs to set the following init.ora parameter:

_TRACE_FILES_PUBLIC = true

Then, we run a simple shell command to convert the .trc contents into
a more readable form:

$ORACLE_HOME/bin/tkprof 'ora_demo.trc tkprof.out explain=scott/s'

A demo of us running the above command is displayed below:

tkprofDemo.txt

One way to quickly get Explain Plan output is to make use of the
SQL command listed below:

SET AUTOTRACE trace explain

Here is a demo:

AUTOTRACEdemo.txt

One problem with the the above method is that the Oracle kernel will
run the SQL command before it gives you the Explain Plan output.  If
the SQL command takes 2 hours to run, we want to work-around this
problem.  The 'EXPLAIN PLAN' statement is well suited for this goal:

ExplainPlan.txt

An important point to note from the above link is that we need to
run ANALYZE against tables we are interested in studying.