Summary Processes - Long running time

[Nikee]

Hello,

We ran the monthly summary processes and they took really long time to process. I am wondering may be you folks can help me out in understanding the problem with some details here I am provided.

Buffer Quality
---------------
Logical Reads Phy. Reads Phy. Writes Buffer Hit Ratio %
--------------- ------------ ------------ ------------------
2,802,401,612 457,620 7,199,685 100.0

Buffer Busy Waits
-----------------
Buffer busy waits Buffer wait time(s) Avg.(ms)
----------------- ------------------- --------
172 175 10.2


Buffer Busy Wait Details
------------------------
Class Count Time(s) Time (ms) Avg.(ms)
------------------------------ ----------- ----------- ----------- -------------
Waiting for segment header 1 9 90 90.0
Waiting for data block 169 166 1,660 9.8
Waiting for undo header 2 0 0 .0



Wait Events
-----------
Event Total waits Total timeouts Wait time (ms) Avg. wait. (ms)
------------------------------ -------------------- -------------------- -------------------- ---------------
rdbms ipc message 2,314,332 31,816 14,428,055 62
SQL*Net message from client 15,405 0 3,959,115 2,570
pmon timer 12,038 12,037 3,697,079 3,071
smon timer 126 120 3,687,093 292,626
wakeup time manager 1,164 1,164 3,576,679 30,727
log file parallel write 1,015,746 0 446,101 4
db file parallel write 13,268 0 148,044 112
db file sequential read 149,561 0 94,217 6
latch free 22,898 22,895 47,648 21
direct path read 31,584 0 23,013 7
control file parallel write 14,200 0 18,895 13
db file scattered read 35,647 0 10,548 3
log file switch completion 155 1 3,053 197
log buffer space 42 6 1,639 390
direct path write 12,240 0 1,347 1
log file single write 342 0 510 15
log file sync 51 1 473 93
file open 69,216 0 453 0
Null event 1 1 410 4,100
rdbms ipc reply 7 0 184 263
buffer busy waits 172 0 175 10
LGWR wait for redo copy 7,311 0 117 0
SQL*Net more data from client 99 0 82 8
control file sequential read 19,385 0 53 0
enqueue 2 0 33 165
process startup 7 0 32 46
refresh controlfile command 1,213 0 26 0
library cache pin 12 0 14 12
file identify 493 0 4 0
SQL*Net more data to client 917 0 4 0
SQL*Net message to client 15,431 0 3 0
reliable message 1 0 2 20
SQL*Net break/reset to client 66 0 2 0
log file sequential read 174 0 1 0
instance state change 2 0 0 0
single-task message 6 0 0 0


Thanks,
Nikee

[davey23uk]

all meaningless - how about the code, data sizes and an explain plan?

also learn how to format your posts properly to make them easier to read

[DaPi]

Cary Millsap would love this one:
100% hit ratio - PERFECT, except . . . .

. . . shame about 2 BILLION LIO's




As Davey says: need the code and explain plan.

[slimdave]

My wild stab-in-the-dark guess is that you are relying too much on row-by-row processing, and looking up individual rows by index. If so then you might benefit from a code overhaul to make better use of processing large amounts of data with fewer SQL executions.

[stmontgo]

Originally posted by DaPi
Cary Millsap would love this one:
100% hit ratio - PERFECT, except . . . .

. . . shame about 2 BILLION LIO's




As Davey says: need the code and explain plan.

really though the instance has not been shutdown since Oracle V6 so those LIO's just sort of average themselves out - or maybe it be useful to understand the time frame for the statistics being gather