Another tracing tool


If you've read my previous tracing note, then it will be pretty obvious what the internals of the next package is doing. A new procedure (which is undocumented) has surfaced as from 8.0.6, but can be applied for all releases of Oracle from 7.2 and above.

DBMS_SUPPORT gives a simpler interface to starting and stopping tracing and also the flexibility to get bind variable values as well as control over waits as well.

Procedures and Functions in DBMS_SUPPORT

The functions / procedures in this package are:
MySid Function
Start_Trace Procedure
Stop_Trace Procedure
Start_Trace_In_Session Procedure
Stop_Trace_In_Session Procedure

FUNCTION MySid

Arguments: None
Returns: Current SID as a NUMBER.

This simply returns the SID of the current session. The function has RNPS, WNPS and WNDS RESTRICT_REFERENCES clause and so can be used in SELECT operations.

Eg: SELECT DBMS_SUPPORT.MYSID from DUAL;

PROCEDURE Start_Trace

Arguments:
waits IN BOOLEAN DEFAULT TRUE
binds IN BOOLEAN DEFAULT FALSE

This procedure starts tracing in the current session. The trace output is written, like SQL_TRACE, to the user trace file. The default is to trace everything SQL_TRACE would capture plus WAIT information.

PROCEDURE Stop_Trace

Arguments: None

PROCEDURE Start_Trace_In_Session

Arguments:
sid IN NUMBER
serial IN NUMBER
waits IN BOOLEAN DEFAULT TRUE
binds IN BOOLEAN DEFAULT FALSE

This is like START_TRACE except that it allows tracing to be started in a different session. For ease of use SERIAL may be set to NULL (or 0) to denote the current session with the given SID.

PROCEDURE Stop_Trace_In_Session

Arguments:
sid IN NUMBER
serial IN NUMBER

This is like STOP_TRACE except that it allows tracing to be stopped in a different session. For ease of use the SERIAL may be specified as NULL to denote the current session with the supplied SID.