Re: Index vs. table scans in statspack reports

Jonathan Lewis wrote:
>
> Notes in-line
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> The educated person is not the person
> who can answer the questions, but the
> person who can question the answers -- T. Schick Jr
>
> One-day tutorials:
> http://www.jlcomp.demon.co.uk/tutorial.html
>
> Three-day seminar:
> see http://www.jlcomp.demon.co.uk/seminar.html
> ____UK___November
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> "Rick Denoire" <100.17706_at_germanynet.de> wrote in message
> news:1bkfsv0lle1aat1rgguvqru5ghb48o1qsk_at_4ax.com...
> >
> > 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.
> >
>
> It's usually safe in 8.1 to set session_cached_cursors quite
> a lot higher, perhaps in the region of 100 or so. Look at
> "the session cursor cache count" for each session to see if
> they are hitting 10, and "session cursor cache hits" to see
> how often their private caches are used. But if you have no
> issues with library cache / shared pool / row cache latches
> this won't make much more difference.
>
> > 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.
>
> There may also be some effect from the fact that if the
> system is running faster, people are using it more. So
> more queries are hitting it.
>
> >
> > 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.
> >
>
> An increase in degree gives you more slaves involved
> in each parallel scan - possibly they are colliding with
> each other more frequently. Could be affected by
> the size of direct write, the device stripe sizes and
> the number of logical devices if you are on a striped
> system. If the direct writes are for CTAS, create index,
> the same device level collision by concurrent slaves
> could be present there too.
>
> > 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.
>
> Not necessarily something to be worried about if sessions are
> not reporting a worthwhile amount of 'latch free' in their
> v$session_event. Check v$latch_children for cache buffers chains,
> and see if there is one particular latch child that is being hit badly.
> If so, then execute (as sys)
> select dbarfil, dbablk, tch, obj
> from x$bh
> where hladdr = {address of that latch child}
>
> This may give you a clue about the object that is
> the hot object. A high touch count (tch) means heavy
> use. The OBJ column maps to the data_object_id in
> dba_objects (or dataobj# in sys.obj$ if you want a
> less expensive check).
>
> If your version is pre 8.1.7.3, then you may be seeing
> the root block latch bug - there is a patch.
>
> >
> > 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).
> >
>
> This might be the latching culprit - 5 gets per scan means pre 9.0.
> Four of those gets are current block gets on the segment header block.
> You could cheat on this, and recreate DUAL as an IOT - but
> Oracle Support might decline to support you if you did.
> An upgrade to 9 would make this 3 logical I/Os per execution.

I recently obtained clarification from Oracle that its not supported to recreate SYS.DUAL as an IOT, but it is fully supported to create an IOT version under as many other schemas as you like.

Cheers
Connor Received on Sun Nov 30 2003 - 01:46:25 CST