Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: max 5% of pga_aggregate_target for a single serial session

Re: max 5% of pga_aggregate_target for a single serial session

From: Jonathan Lewis <>
Date: Sun, 04 Jan 2004 13:14:24 -0800
Message-ID: <>

Your sample query would only have needed one active area.

Another thought: although the maximum you are allowed is 150M, Oracle may decide that there is no point in giving it to you because it won't improve your performance significantly, but it might benefit other people if some of the memory is held back.

For example:

    To get an optimal (in-memory only) sort,     you need 200M. To get a one-pass sort     you need 90M. Your limit is 150M.

    Oracle may decline to give you the extra     60 M past the memory required for a     one-pass sort, because whatever memory     you get you still have to write and re-read     the whole data set to disc, so the extra     60M won't change things significantly.


Jonathan Lewis

  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:

Three-day seminar:
see ____UK___November

The Co-operative Oracle Users' FAQ

> select BELNR,count(*)
> from sapr3.bsis
> group by BELNR
> order by BELNR
> This was the SQL running at that time.
> -----Original Message-----
> Sent: Tuesday, December 30, 2003 5:44 PM
> To: Multiple recipients of list ORACLE-L
> It is possible for a single session to require more
> than one sort or hash area at a time. For example
> a 4 table hash could require 3 in-memory hash
> tables (and therefore use 3 x hash_area_size
> in the days before pga_agg_target).
> Possibly your session used 150MB, but had
> multiple areas open at once, of which the largest
> was 90MB.- are the definitions of the columns
> completely unambiguous, or is there room for
> error in interpreting their use ?
> Regards
> Jonathan Lewis

Please see the official ORACLE-L FAQ:
Author: Jonathan Lewis

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (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 Sun Jan 04 2004 - 15:14:24 CST

Original text of this message