Hello everyone,
I've been working on this database (11.2.0.3 on AIX 6.1) trying to improve the performance of some batch processes from an ERP system developed by my company. These are all processes that run fine in other environments, but here the clock times are horrible for the load and I've been seeing 10% CPU, 90% CPU Wait for nearly every process. Below is the end section of a very long trace file where you can see the "invisible wait".
I know how to cause this: if I run something in the OS with higher priority than Oracle's, that's what I'll get. "CPU starvation" I think is the name for it. My question is: what else can cause this ? If the OS people say that nothing else was on the machine, how can I investigate the root cause ?
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 762529 3.24 16.60 0 3 0 0
Execute 8334641 5593.35 51349.14 344238 2115862 12349440 1341634
Fetch 7048666 1142.66 5978.90 385152 58108531 2068 7944263
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 16145836 6739.25 57344.65 729390 60224396 12351508 9285897
Misses in library cache during parse: 734
Misses in library cache during execute: 731
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 620864 0.62 4858.21
Disk file operations I/O 42 0.00 0.00
latch: shared pool 8 0.04 0.06
asynch descriptor resize 6 0.00 0.00
direct path write temp 2 0.01 0.01
direct path read temp 94 0.03 0.57
db file scattered read 2129 0.44 23.90
log file switch completion 15 0.12 0.98
latch: cache buffers lru chain 2 0.00 0.00
resmgr:cpu quantum 15 0.00 0.03
latch: object queue header operation 2 0.00 0.00
8180 user SQL statements in session.
609 internal SQL statements in session.
8789 SQL statements in session.
288 statements EXPLAINed in this session.
********************************************************************************
Trace file: dbkpv_ora_811258_ARREC_BXA_AUTOMATICA.trc
Trace file compatibility: 11.1.0.7
Sort options: prsela fchela exeela
1 session in tracefile.
8180 user SQL statements in trace file.
609 internal SQL statements in trace file.
8789 SQL statements in trace file.
754 unique SQL statements in trace file.
288 SQL statements EXPLAINed using schema:
KIPREV.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
25357662 lines in trace file.
57345 elapsed seconds in trace file.