(if you quote someone -- you should provide a reference - I'm responding to you and your claim that they are "close to this formula")


I'm sorry -- but there is no way on earth you could come close to that except by accident.

the pga is equal to the amount of memory you have allocated.  period.  nothing more, nothing less.

did you know that sort area size is allocated *as needed* - if you set it to 512m, it won't allocate it all at startup?

did you know that you can have more than one sort area going at a time?


the pga is most definitely NOT "(1MB for Unix, 2MB for Windows) + sort_area_size + hash_area_size", definitely NOT.



ops$tkyte@ORA9IR2> show parameter area_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
bitmap_merge_area_size               integer     1048576
create_bitmap_area_size              integer     8388608
hash_area_size                       integer     1073741824
sort_area_size                       integer     536870912
workarea_size_policy                 string      MANUAL
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @mystat pga
ops$tkyte@ORA9IR2> set echo off

NAME                                VALUE
------------------------------ ----------
session pga memory                 229020
session pga memory max             294556

<b>one might be concerned that if it were sort area size/hash area size, i'd be using 1.5gig of ram, but I'm not</b>


ops$tkyte@ORA9IR2> begin
  2  for i in 1 .. 10000 loop my_pkg.l_data(i) := 'x'; end loop;
  3  end;
  4  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> @mystat pga
ops$tkyte@ORA9IR2> set echo off

NAME                                VALUE
------------------------------ ----------
session pga memory                4030108
session pga memory max            4095644

<b>but that plsql table is taking a bit of ram...</b>

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace traceonly statistics
ops$tkyte@ORA9IR2> select * from all_objects order by 1,2,3,4,5,6,7,8,9,10,11,12;

28018 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     114922  consistent gets
          0  physical reads
          0  redo size
    1957903  bytes sent via SQL*Net to client
      21036  bytes received via SQL*Net from client
       1869  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      28018  rows processed

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @mystat pga
ops$tkyte@ORA9IR2> set echo off

NAME                                VALUE
------------------------------ ----------
session pga memory                4104812
session pga memory max            7578220




<b>and it appears my sort used 3m -- out of 512 it was allowed to -- but gave it back (in 9i)</b>

 

not technically.

Oracle does not allow a single workarea to be larger than about 5% of the pga_aggregate_target.  A single sql statement might have need of multiple workareas.  A process might have multiple statements with multiple workareas.

consider how the workareas accumulate as I open cursors:

ops$tkyte@ORA9IR2> show parameter work
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy                 string      AUTO
ops$tkyte@ORA9IR2> show parameter pga
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 26214400
ops$tkyte@ORA9IR2> select 26214400*0.05/1024/1024 from dual;
 
26214400*0.05/1024/1024
-----------------------
                   1.25
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> variable a refcursor
ops$tkyte@ORA9IR2> variable b refcursor
ops$tkyte@ORA9IR2> variable c refcursor
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @mystat "session pga memory max"
ops$tkyte@ORA9IR2> set echo off
 
NAME                                VALUE
------------------------------ ----------
session pga memory max             286044
 
ops$tkyte@ORA9IR2> declare
  2          l_rec all_objects%rowtype;
  3  begin
  4          open :a for select * from all_objects order by 1,2,3,4,5,6,7,8,9,10,11,12,13;
  5          fetch :a into l_rec;
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> @mystat2 "session pga memory max"
ops$tkyte@ORA9IR2> set echo off
 
NAME                                    V DIFF
------------------------------ ---------- ----------------
session pga memory max            2064028        1,777,984
 
ops$tkyte@ORA9IR2> declare
  2          l_rec all_objects%rowtype;
  3  begin
  4          open :b for select * from all_objects order by 1,2,3,4,5,6,7,8,9,10,11,12,13;
  5          fetch :b into l_rec;
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> @mystat2 "session pga memory max"
ops$tkyte@ORA9IR2> set echo off
 
NAME                                    V DIFF
------------------------------ ---------- ----------------
session pga memory max            2653852        2,367,808
 
ops$tkyte@ORA9IR2> declare
  2          l_rec all_objects%rowtype;
  3  begin
  4          open :c for select * from all_objects order by 1,2,3,4,5,6,7,8,9,10,11,12,13;
  5          fetch :c into l_rec;
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> @mystat2 "session pga memory max"
ops$tkyte@ORA9IR2> set echo off
 
NAME                                    V DIFF
------------------------------ ---------- ----------------
session pga memory max            3178140        2,892,096
 

 

do you realize that the aggregate target is for the ENTIRE SYSTEM!

you are saying "16 mb to be shared by 329 sessions"


I have a feeling you'd be much happier with a MUCH MUCH higher setting.

and a statspack taken during your "normal operating time" for 15-30 minutes would give you a nice report telling you what your likely optimum size is.

(and the target is a target, we can and will exceed the target)

<quote src=Expert Oracle Database Architecture>
Using PGA_AGGREGATE_TARGET to Control Memory Allocation

Earlier, I wrote that “in theory” we can use the PGA_AGGREGATE_TARGET to control the overall amount of PGA memory used by the instance. We saw in the last example that this is not a hard limit, however. The instance will attempt to stay within the bounds of the PGA_AGGREGATE_TARGET, but if it cannot, it will not stop processing; rather, it will just be forced to exceed that threshold. 
Another reason this limit is “in theory” is because the workareas, while a large contributor to PGA memory, are not the only contributor to PGA memory. Many things contribute to your PGA memory allocation, and only the workareas are under the control of the database instance. If you create and execute a PL/SQL block of code that fills in a large array with data in dedicated server mode where the UGA is in the PGA, Oracle cannot do anything but allow you to do it. 
Consider the following quick example. We’ll create a package that can hold some persistent (global) data in the server:

ops$tkyte@ORA10G> create or replace package demo_pkg
  2  as
  3          type array is table of char(2000) index by binary_integer;
  4          g_data array;
  5  end;
  6  /
Package created.

Now we’ll measure the amount of memory our session is currently using in the PGA/UGA (I was using dedicated server in this example, so the UGA is a subset of the PGA memory):

ops$tkyte@ORA10G> select a.name, to_char(b.value, '999,999,999') value
  2    from v$statname a, v$mystat b
  3   where a.statistic# = b.statistic#
  4     and a.name like '%ga memory%';
 
NAME                           VALUE
------------------------------ ------------
session uga memory                1,212,872
session uga memory max            1,212,872
session pga memory                1,677,900
session pga memory max            1,677,900

So, initially we are using about 1.5MB of PGA memory in our session (as a result of compiling a PL/SQL package, running this query, etc.). Now, we’ll run our query against BIG_TABLE again using the same 256MB PGA_AGGREGATE_TARGET (this was done in an otherwise idle instance; we are the only session requiring memory right now):

ops$tkyte@ORA10GR1> set autotrace traceonly statistics;
ops$tkyte@ORA10GR1> select * from big_table order by 1,2,3,4;
50000 rows selected.
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        721  consistent gets
          0  physical reads
          0  redo size
    2644246  bytes sent via SQL*Net to client
      37171  bytes received via SQL*Net from client
       3335  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      50000  rows processed
ops$tkyte@ORA10GR1> set autotrace off

As you can see, the sort was done entirely in memory, and in fact if we peek at our session’s PGA/UGA usage, we can see how much we used:

ops$tkyte@ORA10GR1> select a.name, to_char(b.value, '999,999,999') value
  2    from v$statname a, v$mystat b
  3   where a.statistic# = b.statistic#
  4     and a.name like '%ga memory%';
 
NAME                           VALUE
------------------------------ ------------
session uga memory                1,212,872
session uga memory max            7,418,680
session pga memory                1,612,364
session pga memory max            7,838,284

The same 7.5MB of RAM we observed earlier. Now, we will proceed to fill up that CHAR array we have in the package (a CHAR datatype is blank-padded so each of these array elements is exactly 2,000 characters in length):

ops$tkyte@ORA10G> begin
  2          for i in 1 .. 100000
  3          loop
  4                  demo_pkg.g_data(i) := 'x';
  5          end loop;
  6  end;
  7  /
PL/SQL procedure successfully completed.

Upon measuring our session’s current PGA utilization after that, we find something similar to the following:

ops$tkyte@ORA10GR1> select a.name, to_char(b.value, '999,999,999') value
  2    from v$statname a, v$mystat b
  3   where a.statistic# = b.statistic#
  4     and a.name like '%ga memory%';
 
NAME                           VALUE
------------------------------ ------------
session uga memory              312,952,440
session uga memory max          312,952,440
session pga memory              313,694,796
session pga memory max          313,694,796

Now, that is memory allocated in the PGA that the database itself cannot control. We already exceeded the PGA_AGGREGATE_TARGET and there is quite simply nothing the database can do about it—it would have to fail our request if it did anything, and it will do that only when the OS reports back that there is no more memory to give. If we wanted, we could allocate more space in that array and place more data in it, and the database would just have to do it for us.
However, the database is aware of what we have done. It does not ignore the memory it cannot control; rather, it recognizes that the memory is being used and backs off the size of memory allocated for workareas accordingly. So if we rerun the same sort query we see that this time we sorted to disk—the database did not give us the 7MB or so of RAM needed to do this in memory since we had already exceeded the PGA_AGGREGATE_TARGET:

ops$tkyte@ORA10GR1> set autotrace traceonly statistics;
ops$tkyte@ORA10GR1> select * from big_table order by 1,2,3,4;
50000 rows selected.
 
Statistics
----------------------------------------------------------
          6  recursive calls
          2  db block gets
        721  consistent gets
        728  physical reads
          0  redo size
    2644246  bytes sent via SQL*Net to client
      37171  bytes received via SQL*Net from client
       3335  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
      50000  rows processed
ops$tkyte@ORA10GR1> set autotrace off

So, because some PGA memory is outside of Oracle’s control, it is easy for us to exceed the PGA_AGGREGATE_TARGET simply by allocating lots of really large data structures in our PL/SQL code. I am not recommending you do that by any means—I’m just pointing out that the PGA_AGGREGATE_TARGET is a more of a request than a hard limit. 
</quote>