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

3 RACGRUD Toad.exe session logical reads 14284994
3 RACGRUD Toad.exe sql execute elapsed time 916439031
3 RACGRUD Toad.exe user I/O wait time 777296854
3 SSKYPRD backup incr datafile concurrency wait time 567628
3 SSKYPRD backup incr datafile opened cursors cumulative 1327
3 SSKYPRD backup incr datafile session cursor cache hits 1225
3 SSKYPRD backup incr datafile user I/O wait time 11993832
Analyzing the preceding output, it's an indication that module TAP_BULK_DATA_LOADER has more resource
utilization compared to the other module and now high DB CPU and DB Time on both instances in the cluster
(recall that the database service is only configured to run on instance 1 and instance 2 of the cluster).
Step 4
Once the highly resource-intensive MODULE of the application has been identified, the next step is to drill down further
to determine what part(s) of the code could be consuming a high amount of resources or having slow response times.
Script: MVRACPDnTap_SrvActSum.sql
SELECT INST_ID INT,
ACTION,
STAT_NAME,
SUM(VALUE)
FROM GV$SERV_MOD_ACT_STATS
GROUP BY INST_ID,ACTION,STAT_NAME;
INST SVI ACTION STAT_NAME SUM(VALUE)
---- --- ---------------------------- ------------------------------ ----------
1 TAPS FILE_NON_BULK_DATA_LOADER DB CPU 9999
1 TAPS FILE_NON_BULK_DATA_LOADER DB time 10066
1 TAPS FILE_NON_BULK_DATA_LOADER sql execute elapsed time 1018
1 TAPS SSK_INST_FILE_PROCESS_STATUS DB CPU 3652942186
1 TAPS SSK_INST_FILE_PROCESS_STATUS DB time 2.5375E+11
1 TAPS SSK_INST_FILE_PROCESS_STATUS application wait time 2793563
1 TAPS SSK_INST_FILE_PROCESS_STATUS cluster wait time 1.9224E+11
1 TAPS SSK_INST_FILE_PROCESS_STATUS concurrency wait time 2.8272E+10
1 TAPS SSK_INST_FILE_PROCESS_STATUS db block changes 11579205
1 TAPS SSK_INST_FILE_PROCESS_STATUS execute count 4662872
1 TAPS SSK_INST_FILE_PROCESS_STATUS gc cr block receive time 797851
1 TAPS SSK_INST_FILE_PROCESS_STATUS gc cr blocks received 790614
1 TAPS SSK_INST_FILE_PROCESS_STATUS gc current block receive time 1743286
1 TAPS SSK_INST_FILE_PROCESS_STATUS gc current blocks received 1270887
1 TAPS SSK_INST_FILE_PROCESS_STATUS opened cursors cumulative 3200371
1 TAPS SSK_INST_FILE_PROCESS_STATUS parse count (total) 1111103
1 TAPS SSK_INST_FILE_PROCESS_STATUS parse time elapsed 49285922
1 TAPS SSK_INST_FILE_PROCESS_STATUS physical reads 47759
1 TAPS SSK_INST_FILE_PROCESS_STATUS redo size 2074315644
1 TAPS SSK_INST_FILE_PROCESS_STATUS session cursor cache hits 1.5891E+11
1 TAPS SSK_INST_FILE_PROCESS_STATUS session logical reads 149802353
1 TAPS SSK_INST_FILE_PROCESS_STATUS sql execute elapsed time 2.3728E+11
1 TAPS SSK_INST_FILE_PROCESS_STATUS user I/O wait time 849840546