Wednesday, November 24, 2010

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

Tuesday, November 23, 2010

Autotrace , plustrace role

Using AUTOTRACE in SQL*Plus

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