Saturday 27 August 2011

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!!



5 comments:

Ajay Rai said...

Good One .

Pradnya said...

very basic things................
must read

nishant said...

well done bro...

Akhand Singh said...

very well explained..!!!

Vineet Arya said...

Thanks Akhand..

Post a Comment