Depending on the size of the tables being scanned and the nature of the of
the database, full scans aren't necessarily a bad thing. A full scan on a 2
block table is faster than a table access by index. When joining with a
medium to large sized tables, full scans with a hash join can be much faster
than a join an index join. You need to identify the SQL responsible for the
scans and see what it's trying to do. If it's scanning very small tables,
consider pinning them in the buffer cache.
--
Chuck Hamilton
chuck_hamilton_at_yahoo.com
"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.)
- application/x-pkcs7-signature attachment: smime.p7s
Received on Mon Oct 08 2001 - 11:03:09 CDT
