Hi Bruce,
Not sure whether you got a response on this, so here
is one. First of all, I am hoping that you have some
kind of performance problem on your hand, that you are
trying to solve and that led you to checking out the
"wait events" in your database. If so, great. If not
our discussion is purely theoritical, and I do
sincerely hope that you don't look at percentages once
and conclude that you have a problem. I am assuming
that the "latch free" wait event for the cache buffers
chains latch occurs frequently and shows up in
V$SESSION_WAIT.
Contention or waits for the "cache buffers chains
latch" usually indicates that there is "too much
logical I/O" that is being performed in your
environment. Contrary to common knowledge, logical I/O
is not 3 orders of magnitude faster than physical I/O
in an Oracle environment, as there is a lot more that
goes on when Oracle performs a logical I/O, than just
"reading blocks from memory". So reducing logical I/O
should also be one of the primary efforts one takes in
tuning efforts.
The cache buffers chains latch is a scarce resource
that needs to be acquired for performing logical I/O
and can cause serious contention (which you are
probably experiencing). This could be caused because
of the use of GTT with tablespace type of "Permanent",
as the blocks for temporary segment need to be
processed via the database buffer cache, when the size
of the data processed by the GTT (at the transaction
or the session level) exceeds the size of
SORT_AREA_SIZE. Your system may also be experiencing
severe contention for the "ST enqueue" as a result of
you changing your temporary tablespace to type
"permanent", as there could be constant allocation and
deallocation of temp segments, which requires the ST
enqueue.
One solution to your problem is to upgrade to 8.1.7
(if possible and hoping that the bug is fixed) and
flip your temporary tablespace back to type
"temporary". Another option is to increase the size of
SORT_AREA_SIZE, so that you reduce the frequency with
which your sessions generate temporary segments. You
also should look into the option of creating a
"locally managed temporary tablespace" with the CREATE
TEMPORARY TABLESPACE command which again will
alleviate the contention for blocks in the database
buffer cache.
Hope that helps,
Gaja
--- "Reardon, Bruce (CALBBAY)"
<[EMAIL PROTECTED]> wrote:
> Hi,
>
> Our database is experiencing a very large number of
> waits on the cache
> buffers chains latch.
> I know the child latch# is 242 (details below).
>
> We are on Oracle 8.1.5.1.1 on NT4.
>
> The problems seem to have started appearing after
> starting large scale use
> of Global Temporary tables (GTT).
> Our temp tablespace was of type temporary but a
> suggestion from Oracle was
> to change this to Permanent (due to GTT related bugs
> in 815).
>
> This was done and the database was restarted but the
> waits are still
> occurring.
>
> What else should I try to look for?
>
> Thanks,
> Bruce Reardon
> mailto:[EMAIL PROTECTED]
>
>
>
> Our top waits in general are:
>
> SQL> @system_times
>
> EVENT
> TIME_WAITED
>
----------------------------------------------------------------
> -----------
> PX Idle Wait
> 581928737
> PX Deq: Execution Msg
> 278990599
> CPU used by this session
> 3812597
> latch free
> 202949
> db file sequential read
> 200926
> SQL*Net more data to client
> 73342
> db file scattered read
> 59797
> enqueue
> 19755
>
> Using Ixora's latch_sleeps script I get the
> following output:
>
> SQL> @latch_sleeps
> LATCH TYPE IMPACT
> SLEEP RATE WAITS HOLDING
> LEVEL
> ------------------------------------- -----------
> ---------- -------------
> -----
> cache buffers chains 12642171
> 0.39% 804
> 1
> library cache 7122
> 0.00% 48564
> 5
> Checkpoint queue latch 7034
> 0.03% 24370
> 7
> session allocation 346
> 0.04% 762
> 5
> parallel query stats 198
> 4.31% 0
> 8
> messages 113
> 0.01% 1499
> 8
> shared pool 88
> 0.00% 320
> 7
> cache buffers lru chain 41
> 0.00% 20765
> 3
> process queue reference 14
> 0.00% 276
> 4
> query server freelists 2
> 0.01% 10
> 6
> multiblock read objects 1
> 0.00% 6
> 3
> redo writing 1
> 0.00% 165
> 5
> parallel query alloc buffer 1
> 0.00% 206
> 6
>
> 13 rows selected.
>
> and from latch_gets.sql
>
> LATCH TYPE SIMPLE GETS
> SPIN GETS
> SLEEP GETS
> ------------------------------ ------------------
> --------------------
> --------------------
> cache buffers chains 3149562975 97.08%
> 82063196 2.53%
> 12562449 0.39%
>
>
>
> Virtually all of these waits are on the 1 child
> latch:
>
> LATCH TYPE
> SLEEPS
> LATCHES
> ------------------------------------------------
> --------------------
> ----------
> cache buffers chains 0
> 951
> 1
> to 2
> 61
> 3
> to 5
> 5
> 10
> to 13
> 3
> 131
> 1
> 652
> 1
>
> 4802
> 1
>
> 12596539
> 1
>
> The child latch in question is child# 242 (from
> querying v$latch_children).
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Reardon, Bruce (CALBBAY)
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
--------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
=====
Gaja Krishna Vaidyanatha
Director, Storage Management Products,
Quest Software, Inc.
Co-author - Oracle Performance Tuning 101
__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail
http://personal.mail.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Gaja Krishna Vaidyanatha
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
