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

I've alway understood that joining occurs always in two steps, first two tables, then their result (row source) with next table and so on, so there's no need for more than 2 hash tables for example?

Tanel.

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

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

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