Enable the trace for application module in Oracle

Enable the trace for application module in Oracle

In oracle, we are able to trace the application particular module by its name.
So that it help us to find which query is running during that module and we can try to tune these queries.

1. Step to check the module name for enable the trace from v$session view

col service_name for a15 wrapped
col username for a15 wrapped
col module for a15 wrapped
col action for a15 wrapped
select s.username,s.service_name,s.module,s.action,s.status,s.sid,s.serial#
from v$session s,v$process p
where s.paddr=p.addr and service_name != 'SYS$BACKGROUND';

-------- ------------ ------------- ------ --------
SYS      orcl12c      SQL Developer        INACTIVE
SYS      SYS$USERS    sqlplus.exe          INACTIVE
SYS      SYS$USERS    sqlplus.exe          ACTIVE

In 11g Find the location of trace with following query:

SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

2. Enable the trace for specific module.
In example I am tracing for all the SQL’s queries running from SQL Developer tool.
Note: Please take care of service_name and Module name you got from first query.
You also keep in mind about all is case sensitive.During tracing module through the package “DBMS_MONITOR”. Take every parameter as case sensitivity as above query return output.
Example: orcl112c is in small letters so type as its for tracing SQL queries and same follows for module name.

For Enable:
EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(service_name => 'orcl12c', module_name => 'SQL Developer', waits => TRUE, binds => FALSE);
For Disable:
EXEC DBMS_MONITOR.serv_mod_act_trace_disable(service_name=>'orcl12c', module_name=>'SQL Developer');

Waits : True means include waiting information in trace,default true.
Binds : True means include bind information in trace.

3. If you want to set for all modules then used all_modules option in parameters:

For enable:
exec dbms_monitor.serv_mod_act_trace_enable('orcl12c',
For disable:
exec dbms_monitor.serv_mod_act_trace_disable('orcl12c');

4. For all the actions

For Enable:
exec dbms_monitor.serv_mod_act_trace_enable('orcl12c','SQL Developer',DBMS_MONITOR.ALL_ACTIONS,TRUE,FALSE,NULL);
For Disable:
exec dbms_monitor.serv_mod_act_trace_disable('orcl12c','SQL Developer');

Note: Enabled trace according to your need.

5. Verify the enabled trace with help of following view

select * from dba_enabled_aggregations;
select * from dba_enabled_traces;

6. Check the stats of the trace with help of following view

select aggregation_type,service_name,module,action,stat_name,value from v$serv_Mod_act_stats;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.