Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Index vs. table scans in statspack reports

Re: Index vs. table scans in statspack reports

From: Rick Denoire <100.17706_at_germanynet.de>
Date: Sat, 29 Nov 2003 01:33:22 +0100
Message-ID: <1bkfsv0lle1aat1rgguvqru5ghb48o1qsk@4ax.com>


"Howard J. Rogers" <hjr_at_dizwell.com> wrote:

>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.

You shouln't have mentioned oraperf. I made statspack reports spanning eleven days before doing the changes, and a second report of eleven days after the changes, then I went to oraperf. At least the "fancy" HTML version of oraperf output omits important information.

I have been studying the reports for several hours now. Almost every possible metrics shows that the changes had a possitive effect, the most important one being CURSOR_SHARING=force and SESSION_CACHED_CURSORS=10. These settings led to a dramatic decrease of parsing activity.

Regarding the original question of index access/full scans ratio, it turns out that wait times for *both* kind of operations decreased after the changes, but at the same time, the count for *both* of these operations increased. This info together with the fact that in general less logical reads, less physical reads are taking place now, points to changed execution plans rather than to a shift. If I have the chance, I will try different settings to see if such a shift is visible in the report numbers.

The only kind of operation that runs worse now is direct path read/write. I should mention that I changed the degree of parallelism of a couple of large tables (several million entries) from 2 to 4. Well, parallel query is another complicated stuff to analyze.

Besides, some latch called "cache buffers chains kcbgtcr: kslbegin" (?) is rating now a sixfold increase in the number of sleeps (bad). Don't understand this point now.

So why users were reporting slightly longer execution times for short operations? Because they were asked about performance just after restarting the instance, which then of course has an empty buffer... By the way, is there some method to artificially repopulate the DB buffers after a restart?

To my surprise, the most expensive query in terms of number of gets is a simple:
SELECT LIMS_ENV.OPERATOR_ID FROM DUAL; (10,955,538 gets and 2,190,735 executions).

Still learning.

Bye
Rick Denoire Received on Fri Nov 28 2003 - 18:33:22 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US