TRACE IN ORACLE DATABASE

TRACE IN ORACLE DATABASE

Current session trace:

NORMAL TRACE:

alter session set tracefile_identifer=’XXX’;   — trace identifier
alter session set sql_trace = true;

FULL LEVEL TRACE with WAIT and BIND :

alter session set tracefile_identifer=’XXX’;   — trace identifier
alter session set events = ‘10046 trace name context forever, level 12’;

Other running session trace:

NORMAL TRACE:

execute dbms_system.set_sql_trace_in_session ($sid,$serial,true);

FULL LEVEL TARCE with WAIT and BIND :

execute dbms_system.set_ev($sid,$serial,10046,12,’’);

TRACE FILE LOCATION:

<DIAGNOSTIC_DEST>/diag/rdbms/<SID>/trace

TKPROF :
tkprof utility can translate the trace file into a more human readable format

tkprof <tracefile> <outfile> explain=username/password sort ='(prsela,exeela,fchela)’

explain=user/password    Connect to ORACLE and issue EXPLAIN PLAN.
prsela  elapsed time parsing
exeela  elapsed time executing
fchela  elapsed time fetching

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s