Sunday, October 28, 2012

0 PGA ( Program Global Area)

Pre 9i

http://docs.oracle.com/cd/E25054_01/server.1111/e25789/img/cncpt218.gif

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

Description of Figure 14-4 follows

* 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

image

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
  );

image

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;

image

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

alter session set sort_area_size = 131072;

image

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;

image

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 .

image

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.

image

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

V$PGASTAT

Show the current PGA stat.

select * from V$PGASTAT

image

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

image

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

image

 

V$PGA_TARGET_ADVICE

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

image

select * from v$pga_target_advice

image

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;

image

V$SYSSTAT

Below query show the sorrt area status

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

image

Reference

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

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates