Re: db file scattered read

I would suggest tracking down the SQL that is doing these full table scans.

Steve

"Ed Stevens" <Ed_Stevens_at_nospam.noway.nohow> wrote in message news:3bbe1e48.87322933_at_ausnews.austin.ibm.com...
> 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.)
Received on Mon Oct 08 2001 - 09:03:00 CDT