支持数据库版本：10gR2

1. PGA 构成
private SQL area
Session Memory
SQL Work Areas

2. 重要参数

2-1. pga_aggregate_target (PGA 自动管理分配的总计内存的大小, 不够时 oracle 自动申请额外内存)
在 OLTP 系统中, 典型 PGA 内存设置应该是总内存的较小部分(20%), 剩下 80% 分配给 SGA
OLTP: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%
在 DSS 系统中, 由于会运行一些很大的查询, 典型的 PGA 内存最多分配 70% 的内存
DSS: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%

alter system set pga_aggregate_target=512m scope=both;

2-2. workarea_size_policy (PGA 自动管理)
alter system set workarea_size_policy=auto scope=both;
alter system set workarea_size_policy=manual scope=both;

2-3. _pga_max_size
select ksppinm "Name", ksppstvl/1024/1024||'M' "Value", ksppdesc "Desc"
from x$ksppi x, x$ksppcv y
where x.indx = y.indx and ksppinm ='_pga_max_size';

2-4. 其他参数
# 某些参数是 PGA 手动管理时需要设置的
sort_area_size
sort_area_retained_size
hash_area_size
hash_join_enable
bitmap_merge_area_size
create_bitmap_area_size
open_cursors

3. 重要视图
3-1. 查看当前 PGA 状态
select * from v$pgastat;

3-2. PGA 指导
select
    pga_target_for_estimate/1024/1024 ||'M' "Estimate PGA Target",
    estd_pga_cache_hit_percentage "Cache Hit(%)",
    estd_extra_bytes_rw/1024/1024 ||'M' "Extra Read/Write",
    estd_overalloc_count "Over alloc count"
from v$pga_target_advice;

3-3. 查看数据库启动以来的排序情况
select * from V$SYSSTAT where name like '%sort%';

3-4. 获得引发排序的语句
SELECT /*+ rule */ DISTINCT a.SID,a.process,a.serial#,TO_CHAR (a.logon_time, 'YYYYMMDD HH24:MI:SS') LOGON,a.osuser,TABLESPACE,b.sql_text FROM v$session a,v$sql b,v$sort_usage c WHERE a.sql_address=b.address(+) AND a.sql_address=c.sqladdr;

3-5. 查看当前/所有会话 PGA 使用情况
select name,value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.name like '%pga%';

select spid,program,pga_max_mem,pga_alloc_mem,pga_used_mem,pga_freeable_mem
from v$process
where spid in (select spid from v$process where addr in (select paddr from v$session where sid in (select distinct sid from v$mystat)));

select spid,program,pga_max_mem,pga_alloc_mem,pga_used_mem,pga_freeable_mem from v$process where PROGRAM like '%oracle%'

3-6. 查看当前/所有会话 PGA 各个区域使用情况
select p.PROGRAM,p.SPID,pm.CATEGORY,pm.ALLOCATED,pm.USED,pm.MAX_ALLOCATED
from v$process p,v$process_memory pm
where p.PID=pm.PID and p.SPID in (select spid from v$process where addr in (select paddr from v$session where sid in (select distinct sid from v$mystat )));

ITPub博客

PGA基础知识

最新文章