Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: max 5% of pga_aggregate_target for a single serial session

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

From: Roger Xu <roger_xu_at_dp7uptx.com>
Date: Tue, 30 Dec 2003 13:49:25 -0800
Message-ID: <F001.005DB48D.20031230134925@fatcity.com>








no. it used 800 MB of tempspace in the end.
(also see the tempsize column output from the query of  the v$sql_workarea_active view)
-----Original Message-----
From: Jared.Still@radisys.com [mailto:Jared.Still@radisys.com]
Sent: Tuesday, December 30, 2003 3:35 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: max 5% of pga_aggregate_target for a single serial session


90 Meg was all it needed?




"Roger Xu" <roger_xu@dp7uptx.com>
Sent by: ml-errors@fatcity.com

 12/30/2003 12:59 PM
 Please respond to ORACLE-L

       
        To:        Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
        cc:        
        Subject:        max 5% of  pga_aggregate_target for a single serial session



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



________________________________________________________________________
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 - 15:49:25 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US