1 PGA ( Program Global Area)

Pre 9i

* Image from here

• SORT_AREA_SIZE: Use by sort operation
• HASH_AREA_SIZE: Use by hash join operation
• CREATE_BITMAP_AREA_SIZE: use by building bitmap index. Larger value would help speed up create the bitmap index.
• MERGE_BITMAP_AREA_SIZE: use by merge comparison operation. Larger value would help speed up the merge operation.

* Image from here

• Session Memory: User information, such as username, login time.
• Persistent Area: store bind variables.
• Runtime area: store Query execution state

Show parameter pga_aggregate_target

show parameter area_size

Show the current session pga/uga usage

SELECT

  a.name as "NAME",

  b.value as "VALUE"

FROM

  v$statname a, v$sesstat b

WHERE

  a.statistic#=b.statistic#

AND

  (

    a.name LIKE 'session%ga memory%%'

  OR a.name LIKE '%direct temp%'

  )

AND sid=

  (

    SELECT DISTINCT

      sid

    FROM

      v$mystat

  );

Below example show when the sort area size is 64K (65536 byte), the disk sort count is 1.

-- Create test table

create table testpo as select * from dba_objects;

-- Collect statistic on the table

execute DBMS_STATS.GATHER_TABLE_STATS('SYS','testpo');

-- Enable the trace

set autotrace traceonly statistic;

select * from testpo order by 1;

After increase the sort area size to 128K, the disk count is still 1.

alter session set sort_area_size = 131072;

Once I increase the sort_area_size to 12800K, the disk sort become 0. The sort now is all happen in the memory. This also means if we have a lot of process need the sort operation, we have to allocate a lot of memory to each session for the sort area.

alter session set sort_area_size = 13107200;

PGA_AGGREGATE_TARGET

After 9i, Oracle add the PGA_AGGREGATE_TARGET to automatically manage the PGA memory instead it is fixed for the entire PGA. Every operation can use 5% of PGA_AGGREGATE_TARGET. In parallel operation, can use up to 30% memory. Once the process complete, the memory get release back to the pool for next process. So the same session start the next operation, it will need to request the memory again.

alter system set PGA_AGGREGATE_TARGET=150M;

 

PGA_AGGREGATE_TARGET is dynamic parameter. we don’t have to bounce the instance. Once the PGA_AGGREGATE_TARGET set greater  than 0. he WORKAREA_SIZE_POLICY change to auto automatically .

However, if you want to turn off the PGA_AGGREGATE_TARGET, we have to bounce the instance and set to spfile only. Otherwise, we would get the ORA-00093 error.

After we turn on PGA_AGGREGATE_TARGET, the same query we execute before has 0 disk sort . The SORT_AREA_SIZE,SORT_AREA_SIZE,CREATE_BITMAP_AREA_SIZE and MERGE_BITMAP_AREA_SIZE are not effective because of PGA_AGGREGATE_TARGET is enable.. The PGA_AGGREGATE_TARGET would automatically adjust the memory.

V$PGASTAT

Show the current PGA stat.

select * from V$PGASTAT

V$PROCESS

V$PROCESS contain the current active process.

Below query show the current active process and PGA memory usage

select pid,spid,program,PGA_USED_MEM,PGA_ALLOC_MEM,PGA_MAX_MEM,addr from v$process

Below query list all the user session that use PGA memory  more than 1MB

SELECT

  b.pid,

  b.spid ,

  a.sid,

  a.osuser,

  a.username,

  a.machine,

  a.process,

  a.logon_time,

  a.program,

  b.PGA_USED_MEM,

  b.PGA_ALLOC_MEM

FROM

  V$SESSION a,

  V$PROCESS B

WHERE

  a.PADDR                    = B.ADDR

AND B.PGA_USED_MEM/1024/1024 > 1

ORDER BY

  b.PGA_USED_MEM

 

V$PGA_TARGET_ADVICE

statistics_level needs to be TYPICAL or ALL in order to use V$PGA_TARGET_ADVICE.

select * from v$pga_target_advice

V$STATISTICS_LEVEL

This view shows what advice would be turn on by statistics_level .

select sl.statistics_name,sl.session_status,sl.system_status,sl.activation_level,sl.session_settable  from v$statistics_level sl;

V$SYSSTAT

Below query show the sorrt area status

select * from V$sysstat where name like '%sort%'

Reference

docs.oracle.com/cd/E25054_01/server.1111/e25789/memory.htm#autoId4