max 5% of pga_aggregate_target for a single serial session

From: Roger Xu <roger_xu_at_dp7uptx.com>
Date: Tue, 30 Dec 2003 12:59:25 -0800
Message-ID: <F001.005DB484.20031230125925@fatcity.com>

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). Received on Tue Dec 30 2003 - 14:59:25 CST