Generate tkprof output the 10046 trace files using following syntax

 # (change the file name, username & password):

    tkprof sys=no explain=/

   

    tkprof ${1} ${1}.log sys=no explain=sysadm/wdutsrff \

    waits=yes \

    sort=exeqry,fchqry,prsqry

    sort=exeela,exeqry,fchela,fchqry,prsela,prsqry

    sort=exeela,fchela,prsela

Hope this helps. Regards Rupam

10046 Trace a Session sid and serial #

Step 1

Get sid and serial #

Step 2

Start trace

sqlplus as sysdba
exec sys.dbms_system.set_ev(130, 54378, 10046, 12, '');  # where (sid=130 and serial#=54378)

Step 3

end trace

exec sys.dbms_system.set_ev(130, 54378, 10046, 0, '');

Hope this helps. Regards Rupam

oradebug 10053 using pid

How to Trace SQL Using oradebug

To collect the event 10053 trace file, the following syntax was used in SQLPlus:

step 1
get pid

step 2
SQL> connect / as sysdba
SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug event 10053 trace name context forever, level 1
SQL> ...enter your query here...
SQL> oradebug event 10053 trace name context off
SQL> oradebug tracefile_name

Top of Form

Hope this helps. Regards Rupam

oradebug 10046 using pid

How to Trace SQL Using oradebug

To collect the event 10046 trace file, the following syntax was used in SQLPlus:

step 1
get pid

step 2

Set trace level 10046, 12

SQL>  connect / as sysdba
SQL > oradebug setospid $ospid
SQL > oradebug unlimit
SQL > oradebug event 10046 trace name context forever , level 12

SQL > ...enter your query here...
SQL > oradebug event 10046 trace name context off
SQL> oradebug tracefile_name
SQL > exit

where ospid is the pid number from step 1

Notes :
Level
1 (SQL_TRACE),
4 (SQL_TRACE+BINDS),
8 (SQL_TRACE+WAITS),
12(SQL_TRACE+BINDS+WAITS)

Hope this helps. Regards Rupam

explain plan

Explain plan

steps:-

1. create plan table (global temporary table)

  sqlplus / as sysdba

 @?/rdbms/admin/catplan

2. populate plan table

   Note : 235530.1

   SQL> explain plan for

             

3. Displaying The Execution Plan

SQL> set linesize 150 

>  select plan_table_output from table(dbms_xplan.display('PLAN_TABLE',null,'ALL'));

Or > select * from  table(dbms_xplan.display('plan_table',null,'serial'));

Or > select * from table(dbms_xplan.display);

 

Or >  @?/rdbms/admin/utlxpls

Or > select * from table(dbms_xplan.display(null, null));

Or  > select plan_table_output from table(dbms_xplan.display('plan_table',null,'advanced'));

  More details can be found in $ORACLE_HOME/rdbms/admin/dbmsxpln.sql

Hope this helps. Regards Rupam

10053 trace

10053 trace is useful to get detailed information about SQL execution
 
# set event  10053 and trace sqlplus
alter session set tracefile_identifier = e10053_literal_Test3;
alter session set events '10053 trace name context forever, level 1';
run sql
alter session set events '10053 trace name context off';
exit from sqlplus

# use tkprof to generate report
$ tkprof
Usage: tkprof tracefile outputfile [explain= ] [table= ]
              [print= ] [insert= ] [sys= ] [sort= ]



Hope this helps. Regards Rupam

10046 trace , tkprof

Tracing sql using 10046 event




# set trace 10046 event and trace sql
connect to user using sqlplus
alter session set tracefile_identifier = e10046_literal_test3;
alter session set events '10046 trace name context forever, level 12';
run sql
alter session set events '10046 trace name context off';
exit from sqlplus


Generate report from the trace using tkprof

use tkprof from unix prompt
Generate tkprof output the 10046 trace files using following syntax (change the file name, username & password):
tkprof sys=no explain=/

sort options for tkprof

• sort=exeqry,fchqry,prsqry
• sort=exeela,exeqry,fchela,fchqry,prsela,prsqry
• sort=exeela,fchela,prsela

Hope this helps. Regards Rupam

Autotrace , plustrace role

Using AUTOTRACE in SQL*Plus

Steps
 Creating the PLUSTRACE ROLE
 Granting the PLUSTRACE ROLE to the user
 Using autotrace

Trace for database

-- These may be used to start tracing at database level

EXECUTE dbms_monitor.database_trace_enable;

EXECUTE dbms_monitor.database_trace_enable (binds=>TRUE);

EXECUTE dbms_monitor.database_trace_enable (waits=>TRUE);

-- This may be used to start tracing at instance level

EXECUTE dbms_monitor.database_trace_enable (instance_name=>’RAC1);

-- All outstanding traces can be displayed in an Oracle Enterprise Manager report or

with the DBA_ENABLED_TRACES or v$client_stats views.

-- In the DBA_ENABLED_TRACES view, you can determine detailed information about how a trace was enabled, including the trace type. The trace type specifies whether

the trace is enabled for client identifier, session, service, database, or a

combination of service, module, and action.

Hope this helps. Regards Rupam

Trace levels

# Oracle Trace  Levels

     0 - No trace. Like switching sql_trace off.

     2 - The equivalent of regular sql_trace.

     1 (SQL_TRACE)

     4 (SQL_TRACE+BINDS)

     8 (SQL_TRACE+WAITS)

     12(SQL_TRACE+BINDS+WAITS)

Hope this helps. Regards Rupam

How To Trace Problem SQL

Trace Other's Session

exec sys.dbms_system.set_ev(130, 54378, 10046, 8, ''); #(sid=130 and serial#=54378)
exec sys.dbms_system.set_ev(130, 54378, 10046, 0, '');

LE - Level eg: 1 (SQL_TRACE), 4 (SQL_TRACE+BINDS), 8 (SQL_TRACE+WAITS), or 12(SQL_TRACE+BINDS+WAITS)

Trace Own Session

Set session parameter, if any

alter session set "_optim_peek_user_binds"=false;

Generate 10046 and 10053 traces for query

-- Set event 10046 and run the query.
alter session set tracefile_identifier = ee10046_literal;
alter session set events '10046 trace name context forever, level 12';
@sql
alter session set events '10046 trace name context off';
exit


-- Set event 10053 and run the query.
alter session set tracefile_identifier = ee10053_literal;
alter session set events '10053 trace name context forever, level 1';
@sql
alter session set events '10053 trace name context off';
exit

Generate tkprof output the 10046 trace files using following syntax (change the file name, username & password):
tkprof tracefile outputfile sys=no explain=query_username/password