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

Your "global memory bound" statistic from v$pgastat says that max work area size is 100M. Maybe this 5% rule doesn't apply with large pga_aggregate_targets. The documentation claims that this value can be adjusted during db workload, so you might want to try to run your operation several times in a row and see which amount is used for workareas then.

But as an alternative (as written few times earlier here), you can use "_smm_max_size" for manually increasing this "global memory bound" and allowing larger workareas.

Note that pga target calculation algorithms seem to be quite complicated, thus you might not see the expected result right after first execution, workarea sizes seem to be continuously changing for same query, even though nothing hash changed in data (except internal workarea execution statistics).

It seems that currently less than 30MB is used for your one-pass workarea operation. I don't trust this MAX value in v$sql_workarea_active that much, because in my brief tests on 9.2.0.4/W2k the workarea usage always jumped quite high for a short time in beginning of operation, but then quickly decreased back to smaller value until it finished.

Try to set _smm_max_size to 200M (parameter is set in kilobytes) and see whether any more memory gets used (monitor WORK_AREA_SIZE instead of MAX). If not, then maybe optimizer & QESMM (query execution service memory manager) knows that throwing extra 100MB of memory won't help your operation anyway, since it will still remain an "one-pass" operation (which means that intermediate results have to be stored in temp tablespace once).

Tanel.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, December 30, 2003 10: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: 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).