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

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.

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

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, December 31, 2003 4:14 PM

>
> 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
> http://www.jlcomp.demon.co.uk

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).