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

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

  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: Tuesday, December 30, 2003 8:59 PM

> Hi,
>
> First of all, thank you to all answered my last question.
> Now I have another question related to my last one.
> In my system, pga_aggregate_target is set to 3GB and I
> think a session would have approximately 150MB work area
> before temp space is needed (5% of 3GB).
> But I did a test, it only used 90MB max. Anyone has a explanation?
>
> Thanks,
>
> Roger Xu
>
> SQL>
> 1 select sid
> 2 ,ACTIVE_TIME
> 3 ,WORK_AREA_SIZE
> 4 ,EXPECTED_SIZE expected
> 5 ,ACTUAL_MEM_USED actual
> 6 ,MAX_MEM_USED max
> 7 ,NUMBER_PASSES pass
> 8 ,TEMPSEG_SIZE tempsize
> 9 from v$sql_workarea_active;
>
> SID ACTIVE_TIME WORK_AREA_SIZE EXPECTED ACTUAL MAX
PASS TEMPSIZE
> ---------- ----------- -------------- ---------- ---------- ---------- ---
------- ----------
> 13 1644005675 29966336 29966336 24232960 91504640
1 470712320
>
> SQL> select * from v$pgastat;
>
> NAME VALUE UNIT
> ---------------------------------------- ---------- ------------
> aggregate PGA target parameter 3221225472 bytes
> aggregate PGA auto target 2861061120 bytes
> global memory bound 104857600 bytes
> total PGA inuse 62332928 bytes
> total PGA allocated 188590080 bytes
> maximum PGA allocated 188590080 bytes
> total freeable PGA memory 81330176 bytes
> PGA memory freed back to OS 1677459456 bytes
> total PGA used for auto workareas 20333568 bytes
> maximum PGA used for auto workareas 91521024 bytes
> total PGA used for manual workareas 0 bytes
> maximum PGA used for manual workareas 0 bytes
> over allocation count 0
> bytes processed 3.4667E+10 bytes
> extra bytes read/written 0 bytes
> cache hit percentage 100 percent
>
> 16 rows selected.
>
> ________________________________________________________________________
> This email has been scanned for all viruses by the MessageLabs Email
> Security System. For more information on a proactive email security
> service working around the clock, around the globe, visit
> http://www.messagelabs.com
> ________________________________________________________________________
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Roger Xu
> INET: roger_xu_at_dp7uptx.com
>
> 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).

-- 
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).