Database Reference
In-Depth Information
1 TAPS SSK_INST_FILE_PROCESS_STATUS user calls 1462108
1 TAPS SSK_INST_FILE_PROCESS_STATUS user commits 1333015
2 TAPS FILE_NON_BULK_DATA_LOADER DB CPU 20996
2 TAPS FILE_NON_BULK_DATA_LOADER DB time 23010
2 TAPS FILE_NON_BULK_DATA_LOADER concurrency wait time 1853
2 TAPS FILE_NON_BULK_DATA_LOADER sql execute elapsed time 1497
1 TAPS DB CPU 44398241
1 TAPS DB time 4281592766
1 TAPS cluster wait time 2873017407
1 TAPS concurrency wait time 386393096
1 TAPS db block changes 150845
1 TAPS execute count 42158
1 TAPS gc cr block receive time 14266
1 TAPS gc cr blocks received 6701
The output from the preceding query indicates there are two actions that are part of the module identified in
the previous step that are candidates for investigation. Both actions have high DB Time and DB CPU time apart from
cluster wait time and concurrency wait time .
To drill down further into the code and identify the problem, a closer look at the code that is part of the action
is required. If the process is currently active, then this information could be obtained from either GV$SESSION or
GV$ACTIVE_SESSION_HISTORY by checking the ACTION , MODULE , SERVICE_NAME , and the SQL_ID against the view. If the
application is structurally written with each ACTION identified by a separate name, information from the GV$SESSION
view would be helpful. Otherwise, more than one SQL_ID may be associated with the ACTION , and further investigation
may be required.
Step 6
With the information collected from the previous steps, we try to identify the query. Because there are several actions
for the given module, it may be practical to identify queries associated with this ACTION .
Querying the GV$ACTIVE_HISTORY_SESSION for the action revealed several queries executed from multiple
modules all having the same ACTION .
SELECT COUNT (*),
inst_id,
sql_id,
action,
event,
wait_time,
wait_class
FROM gv$active_session_history
GROUP BY inst_id,
sql_id,
action,
event,
wait_time,
wait_class
ORDER BY sql_id;
 
Search WWH ::




Custom Search