|
Category :
Performance Tunimg
DB Version
:
Oracle 10G
OS Details :
Sun Solaris 10
The dbms_monitor
package has increased the tracing capabilities in 10G. Here's an
example on the usage of the dbms_monitor package to enable tracing:
Example 1
(Normal session level tracing that's equivalent of event 10046):
--
From Session 1
SQL>
select sid,serial# from v$session where sid = 307;
307 507
-- From Session 1
SQL>
exec dbms_monitor.SESSION_TRACE_ENABLE(307,507,true,true)
SQL> select
SQL_TRACE,SQL_TRACE_WAITS,SQL_TRACE_BINDS from v$session where sid =
307;
DISABLED FALSE
FALSE
-- Looks like Activity needs
to happen on the session on which tracing has been enabled to have
oracle change the status on the SQL_TRACE* columns within the
v$session view
--
From Session 2 (sid : 307)
SQL>
select * from test_table;
--
From Session 1
SQL>
select SQL_TRACE,SQL_TRACE_WAITS,SQL_TRACE_BINDS from v$session where
sid = 307;
ENABLED TRUE TRUE
Example 2
(Enhanced session level tracing using service name, action, module and
client identifier):
SQL> select distinct
service_name from v$session;
SERVICE_NAME
----------------------------------------------------------------
SYS$BACKGROUND
SYS$USERS
--
Let's enable tracing for all sessions with service_name of SYS$USERS
and monitor all modules and actions
SQL>
exec dbms_monitor.SERV_MOD_ACT_TRACE_ENABLE( 'SYS$USERS',dbms_monitor.all_modules,dbms_monitor.all_actions)
Note: Enabling this
level of tracing (service_name, module, action) does not seem to cause
oracle to update v$session's SQL_TRACE* columns (atleast in 10.2.0.1
on sun solaris 10!)
--
Client Id specific tracing can
be enabled using dbms_monitor.CLIENT_ID_TRACE_ENABLE
Note: This is
different from setting client_info thro'
dbms_application_info.SET_CLIENT_INFO. We need to populate
client_indentifier column and client_info
--
Session1
SQL>
exec dbms_session.set_identifier('TEST_CLIENT')
--
Session2
SQL>
exec dbms_monitor.client_id_trace_enable(client_id => 'TEST_CLIENT')
Note: Enabling this level
of tracing (client_identifier) does not seem to cause oracle to update
v$session's SQL_TRACE* columns (atleast in 10.2.0.1 on sun solaris
10!)
SQL> exec
dbms_monitor.client_id_trace_disable(client_id => 'TEST_CLIENT')
Monitor Tracing:
--
An alternative way to find out as to whether tracing for a session has
been enabled or not using the dba_enabled_Traces view
select sid,serial#,trace_type,primary_id
client_identifier,QUALIFIER_ID1 module, QUALIFIER_ID2 action from
dba_enabled_traces right outer join v$session on
(client_identifier = primary_id or (service_name = primary_id and (nvl(module,'?')
= nvl(QUALIFIER_ID1,'?') or nvl(action,'?') = nvl(QUALIFIER_ID2,'?')
)))
where primary_id is not null
/ |
