db file scattered read

Decided to give the analysis reports at Oraperf a try. Began taking 15 minutes worth of bstat/estat at the top of every hour through the work day. Fed the results to Oraperf. Consistently shows db_file_scattere_read to be by far the biggest culprit on wait time.

Total response time: 10 to 30 seconds
CPU: 1 to 6 seconds
Wait: 8 to 27 seconds
db_file_scattered_read is 57 to 62 pct of total wait time. db_file_sequential_read is third biggest wait time, with 3 to 7 pct of total wait.

Recommendation was to reduce db_file_multiblock_read_count in order to get the optimizer to favor index over table scans. So we reduced it from 256 to 32. Wait times went UP, ranging from 16 to 44 seconds, with db_file_scattered_read at 37 to 77 pct of total wait time. db_file_sequential_read went to 2d biggest wait event with 24 to 37 pct of total wait

Read some more docs, browsed MetaLink, decided that perhaps at 256 we were over some boundary at which Oracle took over and imposed its own values and at 32 it took mine, which were worse than what it had imposed at 256. Some readings on MetaLink suggested for OLTP apps on NT to set multiblock_read_count to 8, so we went with that. The phone lit up and I had to change it back to 32 before I could even get a reading. A few more calls from users and we went back to 256.

While I expected a percentage shift of total wait to move from scattered read to sequential read, I really didn't expect the total response to go down the toilet the way it did. The worst reponse time reading I had still showed scattered reads to be the major culprit. The shift in percentage of total wait wasn't so much from scattered reads to sequential as it was from SQLNet Msg from DBLink to sequential reads.

Comments? Observations?

Oracle 8.0.5 SE, NT 4.0, OLTP app.

--
Ed Stevens
(Opinions expressed do not necessarily represent those of my employer.)