Hi Gaja,
I notice that you have advised Bruce to increase
SORT_AREA_SIZE to gain proformance lost due to
frequent allocation of temp segments. I too belived in
the theory that disk sorts are always faster than
memory sorts untill I stumbled on my own problem
which leaves me a little confused.
I had been noticing that direct path read amd write
were always among top 5 wait events in my data
warehouse. This warehouse loads data during weekends
and during week days all data and index tablespaces
are read only. We have four or five large fact tables
( 30 to 45GB) on which some users do some heavy
sorting. So in order to improve sorting I wanted to
make my temp tablespace datafiles QUICK I/O. But
veritas advised against it. So I made the temp
tablespace datafiles, tempfiles with local management.
Though this helped finding that we had SORT_ARE_SIZE =
1M and SORT_MULTIBLOCK_READ_COUNT = 2. I tested a few
queries with a larger SORT_AREA_SIZE (84M) and
SOR_MULTIBLOCK_READ_COUNT = 4. I was assumming this
should help. But all queries ran slower with larger
SORT_AREA_SIZE.
Could you throw some light on why this happens?
- Gaja Krishna Vaidyanatha <oraperfman_at_yahoo.com>
wrote:
> 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)"
> <Bruce.Reardon_at_comalco.riotinto.com.au> 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:bruce.reardon_at_comalco.riotinto.com.au
> >
> >
> >
> > 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
>
=== message truncated ===
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: Johnson Poovathummoottil
INET: joni_65_at_yahoo.com
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: ListGuru_at_fatcity.com (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).
Received on Thu Jun 21 2001 - 14:17:48 CDT
