Using SQL_TRACE for database diagnostics
Http://www.eygle.com/case/Use.sql_trace.to.Diagnose.database.htm
The package dbms_system is defined as follows:
SQL > desc dbms_system
PROCEDURE ADD_PARAMETER_VALUE
Argument Name Type In/Out Default?
PARNAME VARCHAR2 IN
VALUE VARCHAR2 IN
SCOPE VARCHAR2 IN DEFAULT
SID VARCHAR2 IN DEFAULT
POSITION BINARY_INTEGER IN DEFAULT
PROCEDURE DIST_TXN_SYNC
Argument Name Type In/Out Default?
INST_NUM NUMBER IN
PROCEDURE GET_ENV
Argument Name Type In/Out Default?
VAR VARCHAR2 IN
VAL VARCHAR2 OUT
PROCEDURE KCFRMS
PROCEDURE KSDDDT
PROCEDURE KSDFLS
PROCEDURE KSDIND
Argument Name Type In/Out Default?
LVL BINARY_INTEGER IN
PROCEDURE KSDWRT
Argument Name Type In/Out Default?
DEST BINARY_INTEGER IN
TST VARCHAR2 IN
PROCEDURE READ_EV
Argument Name Type In/Out Default?
IEV BINARY_INTEGER IN
OEV BINARY_INTEGER OUT
PROCEDURE REMOVE_PARAMETER_VALUE
Argument Name Type In/Out Default?
PARNAME VARCHAR2 IN
VALUE VARCHAR2 IN
SCOPE VARCHAR2 IN DEFAULT
SID VARCHAR2 IN DEFAULT
PROCEDURE REMOVE_PARAMETER_VALUE
Argument Name Type In/Out Default?
PARNAME VARCHAR2 IN
POSITION BINARY_INTEGER IN
SCOPE VARCHAR2 IN DEFAULT
SID VARCHAR2 IN DEFAULT
PROCEDURE SET_BOOL_PARAM_IN_SESSION
Argument Name Type In/Out Default?
SID NUMBER IN
SERIAL# NUMBER IN
PARNAM VARCHAR2 IN
BVAL BOOLEAN IN
PROCEDURE SET_EV
Argument Name Type In/Out Default?
SI BINARY_INTEGER IN
SE BINARY_INTEGER IN
EV BINARY_INTEGER IN
LE BINARY_INTEGER IN
NM VARCHAR2 IN
PROCEDURE SET_INT_PARAM_IN_SESSION
Argument Name Type In/Out Default?
SID NUMBER IN
SERIAL# NUMBER IN
PARNAM VARCHAR2 IN
INTVAL BINARY_INTEGER IN
PROCEDURE SET_SQL_TRACE_IN_SESSION
Argument Name Type In/Out Default?
SID NUMBER IN
SERIAL# NUMBER IN
SQL_TRACE BOOLEAN IN
PROCEDURE WAIT_FOR_EVENT
Argument Name Type In/Out Default?
EVENT VARCHAR2 IN
EXTENDED_ID BINARY_INTEGER IN
TIMEOUT BINARY_INTEGER IN
First, open the sql_trace function for other windows. This function can be turned on before or after the execution of sql.
-- enable sql_trace function
Exec dbms_system.set_sql_trace_in_session (42, 1853, true)
-- disable the sql_trace function
Exec dbms_system.set_sql_trace_in_session (42, 1853, false)
-- query the location of trace files:
Select c.value | |'/'| | d.instance_name | |'_ ora_' | | a.spid | | '.trc' trace
FROM v$process a, v$session b, v$parameter c, v$instance d
WHERE a.addr = b.paddr
AND b.audsid = userenv ('sessionid')
AND c.name = 'user_dump_dest'
Second, open the 10046 event for other windows. This function can be turned on before or after the execution of sql.
-- start the 10046 event
Exec dbms_system.set_ev (42, 1853, 10046, 12, 12)
-- close the 10046 event
Exec dbms_system.set_ev (42, 1853, 100, 46, 0 and 0)
-- query the location of trace files:
Select c.value | |'/'| | d.instance_name | |'_ ora_' | | a.spid | | '.trc' trace
FROM v$process a, v$session b, v$parameter c, v$instance d
WHERE a.addr = b.paddr
AND b.audsid = userenv ('sessionid')
AND c.name = 'user_dump_dest'
