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

Oracle Database Backup Report - v$rman_backup_job_details

/*

-- rman - Query using V$RMAN_BACKUP_JOB_DETAILS is taking too long to execute both in SQL*Plus and also in DATABASE CONSOLE.

-- doc : 420200.1

*/

Steps :-

1. sqlplus as sysdba

2. execute following procedure

Sql> exec dbms_stats.DELETE_TABLE_STATS('SYS','X$KCCRSR'); # deletes the statistics on the fixed object.

Sql> exec dbms_stats.LOCK_TABLE_STATS('SYS','X$KCCRSR');   # lock that object so that statistics will not be collected in future.

  

Hope this helps. Regards Rupam

Database usage

Database feature usage report 

Method 1

1. Login to oem

2. Select database

3. Select server tab

4. Select database Feature Usage View

Method 2

1. sqlplus as system

2. execute following sql statement

SELECT output

FROM TABLE(dbms_feature_usage_report.display_text);

Hope this helps. Regards Rupam

List CPU patch applied to database

List CPU patch applied to database 

1. sqlplus  as system

2. execute the following sql statement

  set linesize 90 

 

  col action format a7

  col version format a10

  col id format 99999

  col when format a20

  select d.name,to_char(r.action_time,'YYYY/MM/DD.hh24:mi')    when,r.action,r.version,r.id,r.bundle_series

  from v$database d left outer join dba_registry_history r

  on r.bundle_series in ('CPU','PSU');

Hope this helps. Regards Rupam