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
from v$session s,v$process p
where s.paddr=p.addr and service_name != 'SYS$BACKGROUND';
USERNAME SERVICE_NAME MODULE ACTION STATUS -------- ------------ ------------- ------ -------- 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.
EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(service_name => 'orcl12c', module_name => 'SQL Developer', waits => TRUE, binds => FALSE);
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:
4. For all the actions
exec dbms_monitor.serv_mod_act_trace_enable('orcl12c','SQL Developer',DBMS_MONITOR.ALL_ACTIONS,TRUE,FALSE,NULL);
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;