Put your tips and tricks online - Share your knowledge! Login | Register
 
 
  Search     Advanced search
 

Home | Ask Question | Add tip | Questions | My tips | Recent tips & tricks | Suggest a category | FAQ | Forums

 
 
 
 Category : Home > Databases > Oracle > Performance Tuning     

How to enable trace in Oracle


1. Enable trace at instance level

Put the following line in init.ora. It will enable trace for all sessions and the background
processes

sql_trace = TRUE

to disable trace:

sql_trace = FALSE

- or -

to enable tracing without restarting database run the following command in sqlplus

SQLPLUS> ALTER SYSTEM SET trace_enabled = TRUE;

to stop trace run:

SQLPLUS> ALTER SYSTEM SET trace_enabled = FALSE;


2. Enable trace at session level

to start trace:

ALTER SESSION SET sql_trace = TRUE;

to stop trace:

ALTER SESSION SET sql_trace = FALSE;

- or - 

EXECUTE dbms_session.set_sql_trace (TRUE);
EXECUTE dbms_session.set_sql_trace (FALSE);

- or -

EXECUTE dbms_support.start_trace;
EXECUTE dbms_support.stop_trace;


3. Enable trace in another session

Find out SID and SERIAL# from v$session. For example:

SELECT * FROM v$session WHERE osuser = OSUSER;

to start trace:

EXECUTE dbms_support.start_trace_in_session (SID, SERIAL#);

to stop trace:

EXECUTE dbms_support.stop_trace_in_session (SID, SERIAL#);

- or -

EXECUTE dbms_system.set_sql_trace_in_session (SID, SERIAL#, TRUE);
EXECUTE dbms_system.set_sql_trace_in_session (SID, SERIAL#, FALSE);

- or -

-- Oracle 10.1
EXECUTE DBMS_SYSTEM.set_sql_trace_in_session(sid=>SID, serial#=>SERIAL, sql_trace=>TRUE);
EXECUTE DBMS_SYSTEM.set_sql_trace_in_session(sid=>SID, serial#=>SERIAL, sql_trace=>FALSE);


-- to trace session with wait events
alter session set events '10046 trace name context forever, level 8';

-- to see wait events and bind variables
alter session set events '10046 trace name context forever, level 12';

-- to see wait events and bind variables for a different session
exec sys.dbms_system.set_ev([SID], [SERIAL], 10046, 12, '');
exec dbms_system.set_ev(si=>123, se=>1234, ev=>10046, le=>8, nm=>' ');


  Options
 
   del.icio.us  |  newsvine  |  digg  |  furl  |  google  |  yahoo  |  Ma.gnolia  |  vigillar  |  reddit  |  technorati  |  icerocket  |  pubsub

     (Average: 3.36 / Votes: 39)   Rate this tip:    

Start discussion or add comment to this tip

  Details
Tip reference : #45
views : 120509
Added on : 11/03/05
Submited by : h8dk97
 
Send a message Send a message Printer friendly output Printer friendly output
Display this member's tips Display this member's tips (214)
 
 
<< Previous Next >>
 Most viewed tips 
  Databases > Oracle > Security : How to unlock Oracle user account  
  Databases > Oracle > Performance Tuning : How to enable trace in Oracle  
  Databases > Oracle > DR : Adding new datafiles to standby database  
  Operating Systems > Unix : How to kill Unix user session  
   
  All categories
Databases | Programming | Hardware | Operating Systems | Networking | Internet | ERP / CRM | Games & Multimedia | Graphics & Design | Miscellaneous | Mobile Phones / Tablets | Office Software
 
 

Home |  FAQ |  Terms of Use |  Privacy Policy

© 2005 tiplib.com