advertisements
_____________________________________________________________________________________________________________________
1. Using DBMS_SYSTEM package:
Identify the session details (SID & Serial#) which we need to trace from v$session using the available information like Machine/osuser/username.
Query: Select sid, serial#, machine, osuser, program from v$session;
You have to enable the timed_statistics parameter to true which will gather the information such as CPU time, Process times etc.
Query: alter system set timed_statistics=true;
Start tracing using the sid and serial# information. You have to login as ‘sys as sysdba’
Query: EXEC DBMS_SYSTEM.set_sql_trace_in_session(SID,Serial#,True);
e.g: EXEC DBMS_SYSTEM.set_sql_trace_in_session(118,28234,True);
The above statement will provide you the trace on session id 118 and serial# 28234.
To stop or disable the session tracing which we started already.
Query: EXEC DBMS_SYSTEM.set_sql_trace_in_session(SID,Serial#,False);
e.g: EXEC DBMS_SYSTEM.set_sql_trace_in_session(118,28234,False);
2. Using DBMS_SUPPORT package
Identify the session details (SID & Serial#) which we need to trace from v$session using the available information like Machine/osuser/username.
Query: Select sid, serial#, machine, osuser, program from v$session;
You have to enable the timed_statistics parameter to true which will gather the information such as CPU time, Process times etc.
Query: alter system set timed_statistics=true;
DBMS_SUPPORT package can be created using the script dbmssupp.sql. You have to login as ‘sys as sysdba’ to execute this script.
Query/Script: @$ORACLE_HOME/rdbms/admin/dbmssupp.sql
To start tracing on user session, login as ‘sys as sysdba’ and execute following command.
Query/Script: EXEC DBMS_SUPPORT.start_trace_in_session(sid=>#, serial=>#, waits=>TRUE, binds=>TRUE);
EXEC DBMS_SUPPORT.start_trace_in_session(sid=>118, serial=>28234, waits=>TRUE, binds=>TRUE);
If you do not need the wait/bind simply change the waits/binds variables from TRUE to FALSE.
3. Using Alter session set events
Using this method we can generate trace with different levels.
These are the main levels with event 10046
Event# 10046 Level#1: standard trace output including parsing, executes and fetches plus more
Event# 10046 Level#4: Level1 + Bind variables
Event# 10046 Level#8: Level1 + waits
Event# 10046 Level#12: Level1 + Bind Variables & Waits
Method to trace
alter session set max_dump_file_size=unlimited;
alter session set timed_statistics=true;
alter session set tracefile_identifier=’10046’;
alter session set statistics_level=all;
To enable trace:
Alter session set events ‘10046 trace name context forever, level 12’;
In above statement instead of level 12 we can use different levels which mentioned above
To disable trace:
Alter session set events ‘10046 trace name context off’;
4. Using Oradebug
What is Oradebug: Is a oracle utility which can be invoked from SQL*PLUS. This can be used for tracing a session, many other, more global, database tracing functions. Oradebug requires sysdba privilege to execute.
How to use Oradebug:
Get the SPID from v$process. You can join the v$session table with v$process to get the SPID.
Query: select spid from v$process;
To enable the trace
SQL> oradebug setospid 12950
Here in oradebug also we can use different level of tracing with event 10046.
SQL> oradebug event 10046 trace name context forever, level 12
To disable the trace
SQL> oradebug event 10046 trace name context off.
To find out the trace file name
SQL> oradebug tracefile_name
We can use the oradebug for diagnosing the system hang problem. If there is no errors recorded in the alert log and the database is in hung state, connect as sysdba and execute the following.
SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug dump systemstate 10
The above will create a very large trace file from the system dump and the oradebug unlimit option should be used to override the maximum trace file size which is specified in init.ora.
Oradebug has the capability to only produce the trace output specifically for particular errors encountered. This command is useful for inconsistent errors. The trace file gets generated only when the particular error occurs.
SQL> oradebug event 942 trace name errorstack level 3
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment