Can Session PGA exceed PGA target??

Frequently asked questions: How and what will be amount of RAM used by my session (PGA)?? Can Session PGA can exceed PGA target??

PGA_AGGREGATE_TARGET: Its' a goal of an upper limit.

1)A serial session: Will use a small % of pga target area, about 5% or less. so for e.g. If u have set pga target to 100MB one would expect to use no more than about 5MB per work area(Sort/Hash)

As the workload on server goes up(More concurrent queries, concurrent users) the amount of PGA memory allocated to one session goes down. The Db will try to keep the sum of all PGA under the threshold.

2)A parallel query by theory may use up to 30% of Pga target.

As per theory we can use PGA target to control overall amount of PGA memory used by the instance. The Instance will attempt to stay within the bounds of PGA target, but if it can not, it will not stop processing. It will be forced to exceed the threshold.

SQL> set autotrace off

SQL> show parameter db_name

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_name                              string      vineet

SQL>

SQL> sho parameter pga

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

pga_aggregate_target                 big integer 194M

 For proving it i am creating a package.

Package created.

SQL>CREATE OR REPLACE package demo_pkg

 as type array is table of char(2000) index by binary_integer;

g_data array;

end;

/

Package created.

Now we'll measure the amount of memory our session is currently using in PGA/UGA

SQL> select a.name,to_char(b.value,'999,999,999')value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.name like '%ga memory%'

  2  ;

NAME                                              VALUE

---------------------------------------------------------------- 

session uga memory                                1,282,380

session uga memory max                            1,282,380

session pga memory                                2,088,532

session pga memory max                            2,088,532---->2 MB

So our session is using 2 MB of memory.Now we'll run a query against BIG_TABLE

SQL> set autotrace traceonly statistics;

SQL> select * from big_table order by 1,2,3,4;

50863 rows selected.

Statistics

----------------------------------------------------------

        406  recursive calls

          0  db block gets

        754  consistent gets

        700  physical reads

          0  redo size

    3631364  bytes sent via SQL*Net to client

      37686  bytes received via SQL*Net from client

       3392  SQL*Net roundtrips to/from client

          3  sorts (memory)-----------------------Memory Sort

          0  sorts (disk)-------------------------Not used any Temp segment

      50863  rows processed

As one can easily see that sort was done in memory

We again fire our statitsics query to find out the value our session is taking

SQL> set autotrace off

SQL> select a.name,to_char(b.value,'999,999,999')value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.nam

e like '%ga memory%';

NAME                                             VALUE

----------------------------------------------------------------

session uga memory                              1,282,380

session uga memory max                          6,571,832

session pga memory                              2,088,532

session pga memory                       7,462,484->  Memory Increased

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

SQL> select a.name,to_char(b.value,'999,999,999')value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.nam

e like '%ga memory%';

NAME                                    VALUE

---------------------------------------------------------------- 

session uga memory                     313,021,948

session uga memory max                 313,021,948

session pga memory                     313,843,284

session pga memory                     313,843,284-> Here one can refer it exceeded PGA Target 194MB

We already exceeded PGA that Db itself can’t control. But Oracle Db is aware of what we have done. It does not ignore the memory it can not control. Rather it recognizes that the memory is being used. So if we re run the same query we see that this time we sorted to disk.

SQL> set autotrace traceonly statistics;

SQL> select * from big_table order by 1,2,3,4;

50863 rows selected.

Statistics

----------------------------------------------------------

          6  recursive calls

          4  db block gets

        702  consistent gets

        705  physical reads

          0  redo size

    3631364  bytes sent via SQL*Net to client

      37686  bytes received via SQL*Net from client

       3392  SQL*Net roundtrips to/from client

          0  sorts (memory)

          1  sorts (disk)-------------------------See here sorting is on disk now

      50863  rows processed

So beacuse some PGA memory is outside of oracle's control,it is easy for us to exceed the PGA target..

SO PGA is not a hard limit!! 

Enjoy Learning!!