支援資料庫版本：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 )));

PGA基礎知識

相關文章