Tools and Utilities - Expert Oracle RAC Performance Diagnostics and Tuning - page 190

Script: MVRACPDnTap_ServiceStats.sql
SELECT INST_ID INT,
SERVICE_NAME SERVICE,
STAT_NAME,
VALUE
FROM GV$SERVICE_STATS
WHERE VALUE > 0
AND SERVICE_NAME ='TAPS'
ORDER BY INST_ID,VALUE;
INT SERVICE STAT_NAME VALUE
---- --------------- ----------------------------------- ---------------
1 TAPS gc current blocks received 416271
TAPS gc current block receive time 425438
TAPS gc cr block receive time 718109
TAPS user commits 809199
TAPS gc cr blocks received 1095112
TAPS parse count (total) 1200805
TAPS user calls 1204285
TAPS session cursor cache hits 3216342
TAPS execute count 4008106
TAPS application wait time 4256936
TAPS opened cursors cumulative 4864680
TAPS db block changes 5257103
TAPS parse time elapsed 77470767
TAPS user I/O wait time 221427713
TAPS redo size 749127052
TAPS session logical reads 2771428476
TAPS concurrency wait time 26596215043
TAPS cluster wait time 30073988481
TAPS DB CPU 53958463764
TAPS sql execute elapsed time 140380379249
TAPS DB time 151669575163
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 :
Script: MVRACPDnTap_ServModActStats.sql
SELECT INST_ID INT,
AGGREGATION_TYPE,
SERVICE_NAME SERVICE,
MODULE,
ACTION,
STAT_NAME,
VALUE
FROM GV$SERV_MOD_ACT_STATS
WHERE VALUE > 0
ORDER BY INST_ID,VALUE DESC;
Service, module, and action level statistics illustrated in Figure 6-23 can be viewed from the Enterprise Manager
under the Top Consumers section.