Hi Johnson,
I think you may be "reading just a tad bit too much"
into what I wrote regarding the "SORT_AREA_SIZE
increase" recommendation. The rationale for that was
not based on "disk sorts are always faster than memory
sorts or vice versa". It was based on the fact that
data for "global temporary tables (GTT)" are "stored"
in the sort area of a session's PGA, and when the sort
area becomes full, the session starts writing the data
for the GTTs to temporary segments (or extents of an
already allocated TRUE temp segment using the sort
extent pool algorithm).
If the TEMP tablespace is of type "temporary", then
the data blocks in the database buffer cache are NOT
used. If the TEMP tablespace is of type "permanent",
then the temp segments (for sorts and for GTT data
larger than SORT_AREA_SIZE) are written to blocks in
the database buffer cache, which in-turn is written to
the files of the temporary tablespace by DBWR (Like
how TEMP tablespaces used to work prior to 7.3 when
they were of type "permanent").
If the temporary tablespace is of type "permanent"
then the temp segments (that store the data for the
GTTs) are constantly allocated and deallocated (if the
data in the GTTs are retained only at the
transaction-level or if sessions live only for a very
short duration). That can cause contention for the ST
enqueue, plus as mentioned before, it also uses blocks
in the database buffer cache. If the same set of
blocks are accessed repeatedly (constantly re-reading
the data from the GTT within the same session or
transaction as the case may be), then the cache
buffers chains latch will be required to perform
"logical I/O" on the said blocks. In that case,
setting a larger SORT_AREA_SIZE will help in storing
more "GTT data" in the PGA, rather than the Database
Buffer Cache. A key factor to note about GTTs is that
"it is local to a user's session or transaction" and
is stored in the sort area of the PGA, so long as it
can fit there. Anything that does not fit goes to the
user's temporary tablespace.
Obviously, while engaging in any performance tuning
engagement, we have to track the "offending SQL" that
is causing the problem and this can be done by tracing
your way back from V$SESSION_WAIT to V$SESSION to
V$SQLAREA or V$SQL, to determine what is causing the
problem.
Regarding the "strange behavior" of the queries on
your system (after increasing SORT_AREA_SIZE), one
possibility could be that your system could have been
experiencing "memory starvation". The "blindspot" that
gets most people while setting large sort areas is
that for queries that are accessing tables or indexes
with a "degree of parallelism of n", the amount of
memory that could be allocated for those queries is (n
x SORT_AREA_SIZE). Now if a bunch of users(y) are
executing these queries and you have a large enough
pool of PARALLEL_MAX_SERVERS, then memory consumption
becomes a product of (y x n x SORT_AREA_SIZE). To make
things interesting, if a bunch of tables(z) is
referenced in the query and they all have a degree of
parallelism > 1, then the memory consumption jumps to
(z x y x n x SORT_AREA_SIZE). You can see where I am
going with this.
In closing, Oracle recommends setting
SORT_MULTIBLOCK_READ_COUNT to 2 and I am still trying
to find out "why is it 2?". I will post a message when
I determine the rationale behind that recommendation.
Until then, you may be better served by leaving that
at 2.
Hope that clarifies things a bit,
Gaja
--- Johnson Poovathummoottil <[EMAIL PROTECTED]>
wrote:
> 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 <[EMAIL PROTECTED]>
> 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)"
> > <[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
>
=== message truncated ===
=====
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).
