Check PGA/UGA Usage

a) To Check Uga/ Pga Memory Usage.


SQL > select a.name, b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name like '%ga %'
5 /

NAME VALUE
------------------------------ ----------
session uga memory 67532
session uga memory max 71972
session pga memory 144688
session pga memory max 144688



SQL> show parameter sort_area

NAME TYPE VALUE
------------------------------------ ------- ------------------------------
sort_area_retained_size integer 65536
sort_area_size integer 65536


SQL> set pagesize 10

SQL> set pause on

SQL> select * from all_objects order by 1, 2;

SQL> set pause off

SQL> select a.name, b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name like '%ga %'
5 /
NAME VALUE
------------------------------ ----------
session uga memory 67524
session uga memory max 174968
session pga memory 291336
session pga memory max 291336

As you can see, our memory usage went up – we've done some sorting of data. Our UGA temporarily
increased by about the size of SORT_AREA_RETAINED_SIZE while our PGA went up a little more. In
order to perform the query and the sort (and so on), Oracle allocated some additional structures that our
session will keep around for other queries. Now, let's retry that operation but play around with the size
of our SORT_AREA:

SQL> alter session set sort_area_size=1000000;

Session altered.

SQL> select a.name, b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name like '%ga %'
5 /

NAME VALUE
------------------------------ ----------
session uga memory 63288
session uga memory max 174968
session pga memory 291336
session pga memory max 291336
4 rows selected.

SQL> show parameter sort_area
NAME TYPE VALUE
------------------------------------ ------- ------------------------------
sort_area_retained_size integer 65536
sort_area_size integer 1000000

SQL> select * from all_objects order by 1, 2;

SQL> set pause off
SQL> select a.name, b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name like '%ga %'
5 /

NAME VALUE
------------------------------ ----------
session uga memory 67528
session uga memory max 174968
session pga memory 1307580
session pga memory max 1307580



As you can see, our PGA has grown considerably this time. By about the 1,000,000 bytes of
SORT_AREA_SIZE we are using. It is interesting to note that the UGA did not move at all in this case.
We can change that by altering the SORT_AREA_RETAINED_SIZE as follows:

SQL> alter session set sort_area_retained_size=1000000;
Session altered.

SQL> select a.name, b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name like '%ga %'
5 /
NAME VALUE
------------------------------ ----------
session uga memory 63288
session uga memory max 174968
session pga memory 1307580
session pga memory max 1307580
4 rows selected.


SQL> show parameter sort_area
NAME TYPE VALUE
------------------------------------ ------- ------------------------------
sort_area_retained_size integer 1000000
sort_area_size integer 1000000


SQL> select * from all_objects order by 1, 2, 3, 4;
...(control C after first page of data) ...
SQL> select a.name, b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name like '%ga %'
5 /
NAME VALUE
------------------------------ ----------
session uga memory 66344
session uga memory max 1086120
session pga memory 1469192
session pga memory max 1469192



Ref : Expert Oracle