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.
Received on Sat Nov 29 2003 - 02:44:53 CST
