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

FROM GV$SERVICE_STATS
WHERE VALUE > 0
AND SERVICE_NAME ='SRV1'
ORDER BY VALUE;
INST SERVICE STAT_NAME VALUE
---- --------------- ----------------------------------- ----------
2 SRV1 parse count (total) 114332
2 SRV1 opened cursors cumulative 114574
2 SRV1 execute count 252873
2 SRV1 session logical reads 5254843
2 SRV1 redo size 21199172
2 SRV1 cluster wait time 27815562
2 SRV1 application wait time 87809921
2 SRV1 user I/O wait time 98546228
2 SRV1 concurrency wait time 2055384221
2 SRV1 DB CPU 2156249531
2 SRV1 sql execute elapsed time 6912286900
2 SRV1 parse time elapsed 8681424580
2 SRV1 DB time 9845032706
To identify the module and action type that caused the high DB time values, use the following script against the
view GV$SERV_MOD_ACT_STATS :
COL STAT_NAME FORMAT A35
COL MODULE FORMAT A10
COL SERVICE FORMAT A10
COL INST FORMAT 999
COL ACTION FORMAT A8
SELECT INST_ID INST,
AGGREGATION_TYPE,
SERVICE_NAME SERVICE,
MODULE,
ACTION,
STAT_NAME,
VALUE
FROM GV$SERV_MOD_ACT_STATS;
The benefits provided for monitoring activity at the service level do not stop here. Tracing user operations is also
available at the module and action level. Oracle generates one trace file per session connecting to the database using
the SERVICE_NAME . Users connecting to the database may get attached to any of the available instances supporting the
service. The advantage of tracing at this level is that when multiple trace files are generated from the current instance
or across instances in the cluster, data related to a specific action type can be grouped together. For example, the
following procedure will enable tracing of a service at the module and action level:
DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE (<SERVICE_NAME>,<MODULE NAME>,<ACTION TYPE>);
EXEC DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE ('SRV1', 'ORDERS', 'MIXED');