Our client reports slowness in a program takes about 12-13 hours that same program runs in about an hour in our database with the same data. Here is the tkprof output they sent us.. It's a huge file so I'm copying the overall run times from the tkprof. It looks like the db file sequential reads take the most time. Please give some suggestions on how to reduce this wait.. Since the same sql's/program run faster in our database with the same amount of data, I'm guessing it is not the application or the sql tuning.. Will increasing their db_block_buffers help? Please advice.
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.05 0.06 0 886 8 0
Execute 1267249 166.07 5217.69 60158 336754 3171892 157586
Fetch 1116142 2969.76 8992.03 73748 16401909 9455 4305627
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2383396 3135.89 14209.79 133906 16739549 3181355 4463213
Misses in library cache during parse: 5
Misses in library cache during execute: 30
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net break/reset to client 18694 0.03 8.71
SQL*Net message to client 3708663 0.00 3.01
SQL*Net message from client 3708663 349.68 5021.96
db file sequential read 133910 8.76 7561.10
control file sequential read 2187 0.00 0.02
Data file init write 178 0.08 0.11
direct path sync 81 1.57 23.54
flashback buf free by RVWR 89 0.97 12.53
control file parallel write 567 2.22 26.77
db file single write 81 2.60 5.80
log file sync 323 0.09 14.61
SQL*Net more data to client 586129 0.02 7.46
rdbms ipc reply 604 1.96 1111.22
db file scattered read 23 0.43 1.66
undo segment extension 13803 0.10 1358.43
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 18345 0.57 0.61 0 0 259 0
Execute 306998 25.04 2715.93 44678 39813 227194 33891
Fetch 294444 7.08 9.09 12 619345 0 264205
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 619787 32.69 2725.64 44690 659158 227453 298096
Misses in library cache during parse: 132
Misses in library cache during execute: 135
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.03 0.03
db file sequential read 44755 9.05 2435.33
control file sequential read 918 0.00 0.00
Data file init write 71 0.00 0.00
direct path sync 34 2.14 12.77
flashback buf free by RVWR 40 0.97 7.19
control file parallel write 238 0.63 10.78
db file single write 34 0.42 2.03
rdbms ipc reply 115 1.95 198.80
45 user SQL statements in session.
27182 internal SQL statements in session.
27227 SQL statements in session.
********************************************************************************
Trace file: trace1.trc
Trace file compatibility: 10.01.00
Sort options: default
1 session in tracefile.
45 user SQL statements in trace file.
27182 internal SQL statements in trace file.
27227 SQL statements in trace file.
80 unique SQL statements in trace file.
12020231 lines in trace file.
21774 elapsed seconds in trace file.