Optimize Distributed Workload - Expert Oracle RAC Performance Diagnostics and Tuning - page 521

Similarly, the following trcsess command will extract trace information from the trace files that pertain to
service SRV1 and module ORDERS but will contain all actions:
trcsess output=trcRead.trc service=SRV1 action=Mixed module=ORDERS ssky1_ora_*.trc
Workshop
The areas of applications experiencing problems cannot always be identified. Many times, slow response times are
reported, however, not the module or action that caused the problem. Many times a small operation by one user can
cause the entire system to go slow.
Step 1
Troubleshooting a performance issue to drill down to specific areas of the application can be done in one of two ways:
first by adding tags into the application and logging every step of the application with a timestamp to measure the
elapsed time every step of the code took; then from the log file, analyze the step that took the longest elapsed time.
Another simpler and more permanent method would be to use the DBMS_APPLICATION_INFO package, and the
procedures to name the module and action are implemented.
GV$SESSION is a view that provides insight into what modules and actions are being executed at a given point in time:
Script: MVRACPDnTap whoractive.sql
SELECT S.inst_id,
s.service_name,
To_char(s.logon_time, 'mm/dd hh24:mi ') logon_time,
s.module,
s.action,
s.program
FROM gv$session s
WHERE username IS NOT NULL
AND status = 'ACTIVE'
ORDER BY logon_time,
inst_id;
Ins
ID SERVICE LOGON_TIME MODULE ACTION_PROGRAM
--- -------- ----------- -------------------- --------------------------
1 TAPS 11/26 18:12 CRE_BULK_DATA_LOADER SSK_INST_BULK_DATA_LOADER
2 TAPS 11/26 18:12 CRE_BULK_DATA_LOADER SSK_INST_BULK_DATA_LOADER
2 TAPS 11/26 18:12 JDBC Thin Client
1 TICKS 11/26 18:13 CRE_BULK_DATA_LOADER SSK_INST_XML_DATA_STATUS J
2 TICKS 11/26 18:13 CRE_BULK_DATA_LOADER SSK_INST_FILE_PROCESS_STAT
2 TICKS 11/26 18:14 CRE_BULK_DATA_LOADER SSK_INST_FILE_PROCESS_STAT
4 FIPS 11/26 19:58 CRE_BULK_DATA_LOADER SSK_INST_FILE_PROCESS_STAT
4 FIPS 11/28 09:00 JDBC Thin Client JDBC Thin Client
1 TAPS 11/28 09:36 CRE_BULK_DATA_LOADER SSK_INST_BULK_DATA_LOADER
3 QRTZ 11/28 10:40 JDBC Thin Client JDBC Thin Client
4 FIPS 11/28 10:45 CRE_BULK_DATA_LOADER SSK_INST_FILE_PROCESS_STAT
4 FIPS 11/28 08:45 JDBC Thin Client JDBC Thin Client
4 FIPS 11/28 09:00 JDBC Thin Client JDBC Thin Client
4 FIPS 11/28 09:18 JDBC Thin Client JDBC Thin Client