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

SQS 5 1374787 72728 1374787 10273.62
SYS$USERS 1 1653405 286593 1653405 22.71
SYS$USERS 2 95729887 7191936 95729887 39.40
SYS$USERS 3 192615 184326 192615 25.55
SYS$USERS 4 34397286 22534832 34397286 26.67
SYS$USERS 5 102555733 67790131 102555733 27.11
From the preceding output, DQC (data quality control) has the highest activity, and the elapsed time numbers
are really high.
Step 3
Because the database service associated with the application has been identified, the next step is to analyze and check
the data to understand which MODULE in the service requires performance optimization:
Script: MVRACPDnTap_SrvModSum.sql
SELECT INST_ID INT,
MODULE,
STAT_NAME,
SUM(VALUE)
FROM GV$SERV_MOD_ACT_STATS
GROUP BY INST_ID, MODULE, STAT_NAME;
INST SERVICE MODULE STAT_NAME SUM(VALUE)
---- ---------- ----------------------- ----------------------------------- ----------
1 TAPS TAP_BULK_DATA_LOADER DB CPU 9999
1 TAPS TAP_BULK_DATA_LOADER DB time 10066
1 TAPS TAP_BULK_DATA_LOADER sql execute elapsed time 1018
1 TAPS TAP_BULK_DATA_LOADER DB CPU 3653014175
1 TAPS TAP_BULK_DATA_LOADER DB time 2.5375E+11
1 TAPS TAP_BULK_DATA_LOADER application wait time 2793563
1 TAPS TAP_BULK_DATA_LOADER cluster wait time 1.9224E+11
1 TAPS TAP_BULK_DATA_LOADER concurrency wait time 2.8272E+10
1 TAPS TAP_BULK_DATA_LOADER db block changes 11579470
1 TAPS TAP_BULK_DATA_LOADER execute count 4663044
1 TAPS TAP_BULK_DATA_LOADER gc cr block receive time 797861
1 TAPS TAP_BULK_DATA_LOADER gc cr blocks received 790623
1 TAPS TAP_BULK_DATA_LOADER gc current block receive time 1743295
1 TAPS TAP_BULK_DATA_LOADER gc current blocks received 1270908
1 TAPS TAP_BULK_DATA_LOADER opened cursors cumulative 3200379
1 TAPS TAP_BULK_DATA_LOADER parse count (total) 1111111
1 TAPS TAP_BULK_DATA_LOADER parse time elapsed 49286200
1 TAPS TAP_BULK_DATA_LOADER physical reads 47766
1 TAPS TAP_BULK_DATA_LOADER redo size 2074354084
1 TAPS TAP_BULK_DATA_LOADER session cursor cache hits 1.5891E+11
1 TAPS TAP_BULK_DATA_LOADER session logical reads 149803130
1 TAPS TAP_BULK_DATA_LOADER sql execute elapsed time 2.3728E+11
1 TAPS TAP_BULK_DATA_LOADER user I/O wait time 849885638
1 TAPS TAP_BULK_DATA_LOADER user calls 1462272
1 TAPS TAP_BULK_DATA_LOADER user commits 1333064