Re: Index vs. table scans in statspack reports

"Rick Denoire" <100.17706_at_germanynet.de> wrote in message news:410dsvslo44cpdf3s06493hi5qufqbh5l7_at_4ax.com...
> I recently tuned an OLTP type of DB by changing following init
> parameters:
>
> DB_FILE_MULTIBLOCK_READ_COUNT=64 (formerly 16)
> OPTIMIZER_INDEX_COST_ADJ=30 (formerly default=100)
> OPTIMIZER_INDEX_CACHING=70 (formerly default=0)
>
> As a result, some "long" lasting operations (15-20 min) run 20 times
> faster, while short operations (several seconds) got slower (up to
> half a minute).
>
> Observing disk activity (iostat on Unix), I found out that the
> application uses far more resources now, i.e., when a query is
> started, data transfer rate rises dramatically indicating a massive
> I/O operation with I/O sizes about 450K with sustained 30MB/s.
>
> My intention was to improve full table scans when they take place, but
> not to favor them more than before (this is a canned DB so one should
> be carefull). In order to compensate for the increased willingness of
> Oracle to do full table scans, I set the optimizer parameters as shown
> above, so indexes should be still used in a healthy extend.
>
> My suspicion is that Oracle is choosing to many full table scans now,
> so perhaps I should decrease OPTIMIZER_INDEX_COST_ADJ still further,
> and/or increase OPTIMIZER_INDEX_CACHING. Or should I change the
> optimizer_mode to first_rows?
>
> Now my question: How do I recognize a general shift from index based
> execution paths towards full table scans (or the opposite) from
> STATSPACK reports? This is a 7x24 production DB and opportunities to
> change anything are rare. I can't afford to try blindly (statspack is
> setup and running hourly).
>
> You might say: "Why don't you look at the statspack reports and
> compare figures?". Well, these are a lot of figures. I lack the real
> experience to deal with and soundly interpret the numbers (although I
> am trying). Some magnitudes seem to be meaningless at all.
>
> Oracle 8.1.7.0/Solaris.
>
> Thanks a lot
> Rick Denoire

Off the top of my head, but "table fetch by rowid" indicates an index access. There's also one called "table scans (long tables)" and "table scans (short tables)". A general shift to scans would send the long tables one through the roof.

Those statistics are in the 'Instance Activity' section of the statspack report, which should be just after the last of the 'most expensive SQL statements' section... but I'm looking at 9.2's report, and I'm sure extra bits have been added since 8i.

By the way: don't forget www.oraperf.com.

Regards
HJR Received on Thu Nov 27 2003 - 20:27:30 CST