oracle PGA管理(演算法)
主要總結下pga分配演算法,詳細概念單總結
簡單理解pga,pga就是一個作業系統程式,或執行緒(WIN上)的專用記憶體
pmon,smon這些後臺程式都有自己的pga
pga早期手動管理組成由sort_area_size,hash_area_size,bitmap_merge_size,create_bitmap_area_size(這些都叫工作區)
手動缺點，不好回收和共享，會造成PGA記憶體過度消耗
PAG自動（9i開始支援）
1.pga_aggregate_target來指定所有session總計可以使用的最大pga記憶體（10M-4096G）
2.workarea_size_policy控制pag自動管理功能開啟或關閉，auto表示開啟（default）， manual表示關閉，9i auto只支援專用連線，共享連線不支援
10g都支援
_pga_max_size:控制pga最大大小
9i-10r1中,單個sql操作記憶體使用現在
1.對於序列操作,單sql操作pga分配原則,min(5%*pga_aggregate_target,100mb)
########5%*pga_aggregate_targe實際由_smm_max_size控制]
2.並行操作使用pga按 30%*pga_aggregate_target/dop(dop=並行度)
9I _pga_max_size與_smm_max_size
_pga_max_size>5%*pga_aggregate_target,_smm_max_size=5%*pga_aggregate_target'
_pga_max_size<5%*pga_aggregate_target,_smm_max_size=50*_pga_max_size
used_pga_mb=min(5%*pga_aggregate_target,50*_pga_max_size,_smm_max_size)
10r2,11g原則
1.序列操作
pga_aggregate_target<=500MB,_smm_max_size=20%*pga_aggregate_target
pga_aggregate_target between 500m and 1000M ,_smm_max_size=100M
pga_aggregate_target between 1001m and 2.5g,_smm_max_size=10%*pga_aggregate_target
pga_aggregate_target >2.5g,_smm_max_size=0.25GB
2.並行操作
50*pag_aggregate_target/dop
dop<=5時,_smm_max_size生效
dop>5時,_smm_px_max_size生效
_newsort_enabled 控制演算法規則,true用10g新演算法,false用9i演算法
SQL> set linesize 132
SQL> column name format a30
SQL> column value format a25
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2 FROM SYS.x$ksppi x, SYS.x$ksppcv y
3 WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
4
SQL> /
Enter value for par: newsort
old 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
new 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%newsort%'
NAME VALUE
------------------------------ -------------------------
DESCRIB
------------------------------------------------------------------------------------------------------------------------------------
_newsort_enabled TRUE
controls whether new sorts can be used as system sort
設定建議
1.oltp系統 pag_aggregate_target=
2.dss系統 pag_aggregate_target=
分析:留20%給os,其他80%給pga+sga,oltp ,pga佔 80%中20%,dss佔80%中50%
#檢視某個process使用情況
select pid,spid,username ,pga_used_mem,pga_alloc_mem,pga_freeable_mem,pga_max_mem,program from v$process where spid=&spid
SQL> select pid,spid,username ,pga_used_mem,pga_alloc_mem,pga_freeable_mem,pga_max_mem,program from v$process where spid=&spid;
Enter value for spid: 25510
old 1: select pid,spid,username ,pga_used_mem,pga_alloc_mem,pga_freeable_mem,pga_max_mem,program from v$process where spid=&spid
new 1: select pid,spid,username ,pga_used_mem,pga_alloc_mem,pga_freeable_mem,pga_max_mem,program from v$process where spid=25510
PID SPID USERNAME PGA_USED_MEM PGA_ALLOC_MEM
---------- ------------------------ --------------- ------------ -------------
PGA_FREEABLE_MEM PGA_MAX_MEM PROGRAM
---------------- ----------- ------------------------------------------------
33 25510 oracle 0 2664666
983040 8431834 oracle@dmk01 (PZ98)
pga_used_mem:程式使用的pga
pga_alloc_mem:分配給程式的pga
pga_freeable_mem:空閒
pga_max_mem:程式使用pga記憶體的最大
#檢視pga消耗到哪些專案上了
col program for a20
set linesize 1000
select p.program,p.pid,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=&spid;
SQL> col program for a20
SQL> set linesize 1000
SQL> select p.program,p.pid,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=&spid;
Enter value for spid: 25510
old 1: select p.program,p.pid,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=&spid
new 1: select p.program,p.pid,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=25510
PROGRAM PID CATEGORY ALLOCATED USED MAX_ALLOCATED
-------------------- ---------- --------------- ---------- ---------- -------------
oracle@dmk01 (PZ98) 33 SQL 0 0 2424808
oracle@dmk01 (PZ98) 33 PL/SQL 24840 17104 26928
oracle@dmk01 (PZ98) 33 Freeable 983040 0
oracle@dmk01 (PZ98) 33 Other 1656786 4997058
sql在workare中有3種方式
1.optimal:最優方式,所有處理可以在記憶體中完成
2.onepass:大部分操作可以在記憶體中完成,但交換到臨時表一次
3.multipass:多變互動臨時表,產生大量disk sort之類,效能最差
oracle建議
workarea execution_optimal>=90%
workarea execution_multipass=0%
#檢視系統中效能指標
select name,value,100*(value/decode((select sum(value) from v$sysstat where name like 'workarea execution%'),0,null,(select sum(value) from v$sysstat where name like 'workarea execution%')))pct from v$sysstat where name like 'workarea executions%';
SQL> select name,value,100*(value/decode((select sum(value) from v$sysstat where name like 'workarea execution%'),0,null,(select sum(value) from v$sysstat where name like 'workarea execution%')))pct from v$sysstat where name like 'workarea executions%';
NAME VALUE PCT
---------------------------------------------------------------- ---------- ----------
workarea executions - optimal 567832 99.9954214
workarea executions - onepass 24 .004226409
workarea executions - multipass 0 0
#查單個sql語句workarea使用情況
SELECT
b.sql_text,
a.operation_type,
a.policy,
a.last_memory_used/(1024*1024) as "Used MB" ,
a.estimated_optimal_size/(1024*1024) as "Est Opt MB",
a.estimated_onepass_size/(1024*1024) as "Est OnePass MB",
a.last_execution,
a.last_tempseg_size
FROM v$sql_workarea a,v$sql b
WHERE a.hash_value = b.hash_value
and a.hash_value = &hashvalue
/
#查session 使用pga
select a.name, b.value
from v$statname a, v$sesstat b
where a.statistic# = b.statistic#
and b.sid = &sid
and a.name like '%ga %'
order by a.name
/
#program 使用pga情況
SELECT
a.pga_used_mem "PGA Used",
a.pga_alloc_mem "PGA Alloc",
a.pga_max_mem "PGA Max"
FROM v$process a,v$session b
where a.addr = b.paddr
and b.sid= &sid
/
關於pga自動管理演算法：
oracle 採用的feedback loop實現的，當一個process執行sql語句時,先用local memory manager註冊一個active workarea profile,workarea profile是與記憶體管理器之間唯一的通訊介面
(既sql語句和記憶體管理器之間唯一介面),當sql語句執行完成對應的workarea profile刪除
,profile含這個workarea很多屬性（例如sql型別是hash join還是什麼之類，執行one pass,optimal操作記憶體大小等的後設資料）
workarea active profile集，通過local memory manager維護，存sga中,profile常常被更新(要求及時反映sql語句當前已消耗記憶體，及是否被交換到temp tablespace等資訊)，
所以active profile基本上就是pga記憶體需要和當前正在使用的pga記憶體,通過這些profile資訊
,global memory manager會計算出一個既可以限制記憶體使用又可以提高較好效能的global memory bound,這個值用於限制單個程式pga的上限，global memory manager每3S更新一次memory
bound,local memory manager得到memory bound後會計算每個active statement所需要分配的pga記憶體大小（execute size），然後每個active statement將會在自己所分配到的execute size
中計算
路線(一個環路)
active statement--->註冊workarea profile-->local memory manager(存sga中)-->set of active workarea profiles-->global memory manager--->計算出memory bound--->local memory manager
--->獲取workarea size--->active statement
其實原理很簡單，就是每個sql語句拿出資訊做成profile，然後交給local memory manager 然後做成profile集（大量profile），然後global memory manager通過profile集計算出memory bound
，把這個memory bound給 local memory manager，local memory manager 用memory bound計算出每個active statement的 execute size，然後每個active statement在自己分到的execute
size中計算
global memeory bound將影響 所有程式pga分配（限制單個程式pga的上限）
由ckpt實現,global memory manager 3s更新一次memory bound
SQL> select description ,dest from x$messages where lower(description) like 'sql memory%';
DESCRIPTION
----------------------------------------------------------------
DEST
----------------------------------------------------------------
SQL Memory Management Calculation
CKPT
SQL> select time,data from x$trace where lower(data) like '%sql memory%' order by seq#;
TIME
----------
DATA
--------------------------------------------------------------------------------
1.2688E+15
KSBCTI: (CKPT) : (timeout action) : acnum=[178] comment=[SQL Memory Management
Calculation]
1.2688E+15
KSBCTI: (CKPT) : (timeout action) : acnum=[178] comment=[SQL Memory Management
Calculation]
1.2688E+15
TIME
----------
DATA
--------------------------------------------------------------------------------
KSBCTI: (CKPT) : (timeout action) : acnum=[178] comment=[SQL Memory Management
Calculation]
10g r2,11g中，workarea管理記憶體分配，存在shared pool中（local memory manager）
SQL> select * from v$sgastat where name like 'work area%';
POOL NAME BYTES
------------ -------------------------- ----------
shared pool work area tab 265320
10g 測試
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> set linesize 132
SQL> column name format a30
SQL> column value format a25
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2 FROM SYS.x$ksppi x, SYS.x$ksppcv y
3 WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
4
SQL> /
Enter value for par: pga_max
old 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
new 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%pga_max%'
NAME VALUE
------------------------------ -------------------------
DESCRIB
------------------------------------------------------------------------------------------------------------------------------------
_pga_max_size 209715200
Maximum size of the PGA memory for one process
可以看到當pga_aggreate_target小於1g,_pga_max_size 預設為200MB
SQL> /
Enter value for par: smm_max
old 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
new 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%smm_max%'
NAME VALUE
------------------------------ -------------------------
DESCRIB
------------------------------------------------------------------------------------------------------------------------------------
_smm_max_size 26214
maximum work area size in auto mode (serial)
pga_aggregate_target <500M,_smm_max_size 預設為20%pga_aggregate_target=25.6M(其實_smm_max_size實際與_pga_max_size還有關係,單相關算髮已經不是9i的了,下面會證實)
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 128M
SQL>
SQL> select 26214/1024 from dual;
26214/1024
----------
25.5996094
SQL> select 0.2*128 from dual;
0.2*128
----------
25.6
_smm_max_size = 20%*pga_aggregate_target
修改_pga_max_size=10MB pga_aggregate_target =128M
SQL> alter system set "_pga_max_size"=10m;
System altered.
SQL> startup force 重起下庫,讓記憶體重新計算
ORACLE instance started.
Total System Global Area 268435456 bytes
Fixed Size 1266944 bytes
Variable Size 100666112 bytes
Database Buffers 159383552 bytes
Redo Buffers 7118848 bytes
Database mounted.
Database opened.
SQL> drop table t;
Table dropped.
SQL> create table t as select * from dba_objects;
Table created.
SQL> insert into t select * from dba_objects;
50067 rows created.
SQL> insert into t select * from dba_objects;
50067 rows created.
SQL> commit;
Commit complete.
SQL> select distinct sid from v$mystat;
SID
----------
159
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_pga_max_size big integer 10M
pga_aggregate_target big integer 128M
SQL> set linesize 132
column name format a30
SQL> SQL> column value format a25
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2 FROM SYS.x$ksppi x, SYS.x$ksppcv y
3 WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
4
SQL> /
Enter value for par: pga_max
old 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
new 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%pga_max%'
NAME VALUE
------------------------------ -------------------------
DESCRIB
------------------------------------------------------------------------------------------------------------------------------------
_pga_max_size 10485760
Maximum size of the PGA memory for one process
可以看到已經生效,單位位元組=10M
SQL> set linesize 132
SQL> column name format a30
SQL> column value format a25
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2 FROM SYS.x$ksppi x, SYS.x$ksppcv y
3 WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
4
SQL> /
Enter value for par: smm_max
old 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
new 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%smm_max%'
NAME VALUE
------------------------------ -------------------------
DESCRIB
------------------------------------------------------------------------------------------------------------------------------------
_smm_max_size 5120
maximum work area size in auto mode (serial)
可以看到 _smm_max_size 此時並不等於 20%*pga_aggregate_target而是 =5m 既50%_pga_max_size
SQL> select 10485760/1024/1024 from dual;
10485760/1024/1024
------------------
10
SQL> select 5120/1024 from dual;
5120/1024
----------
5
SQL> set autotrace traceonly stat
SQL> select * from t where rownum<50000 order by 1,2,3,4,5,6,7;
49999 rows selected.
Statistics
----------------------------------------------------------
37 recursive calls
8 db block gets
835 consistent gets
1382 physical reads
0 redo size
2837037 bytes sent via SQL*Net to client
37063 bytes received via SQL*Net from client
3335 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
49999 rows processed
SQL> select hash_value from v$sql where sql_text='select * from t where rownum<50000 order by 1,2,3,4,5,6,7'
2 ;
HASH_VALUE
----------
1281487883
SQL> SELECT
b.sql_text,
2 3 a.operation_type,
4 a.policy,
5 a.last_memory_used/(1024*1024) as "Used MB" ,
6 a.estimated_optimal_size/(1024*1024) as "Est Opt MB",
7 a.estimated_onepass_size/(1024*1024) as "Est OnePass MB",
8 a.last_execution,
9 a.last_tempseg_size
10 FROM v$sql_workarea a,v$sql b
11 WHERE a.hash_value = b.hash_value
12 and a.hash_value = &hashvalue
13 /
Enter value for hashvalue: 1281487883
old 12: and a.hash_value = &hashvalue
new 12: and a.hash_value = 1281487883
SQL_TEXT
--------------------------------------------------------------------------------
OPERATION_TYPE POLICY Used MB Est Opt MB Est OnePass MB LAST_EXECU
-------------------- ---------- ---------- ---------- -------------- ----------
LAST_TEMPSEG_SIZE
-----------------
select * from t where rownum<50000 order by 1,2,3,4,5,6,7
SORT (v2) AUTO 5.02832031 6.09960938 .98828125 1 PASS
6291456
可以看到記憶體被限制在5M內,現在操作是1 pass,如果 最優操作(全在記憶體裡 需要6M)
所以_smm_max_size就可以限制一個process pga記憶體 最大使用
此時_smm_max_size=5m是如下計算
1._pga_max_size<40%*pga_aggregate_size,此時_smm_max_size=50%_pga_max_size
2._pga_max_size>40%*pga_aggregate_siz,此時_smm_max_size按下面方式計算
pga_aggregate_target<=500MB,_smm_max_size=20%*pga_aggregate_target
pga_aggregate_target between 500m and 1000M ,_smm_max_size=100M
pga_aggregate_target between 1001m and 2.5g,_smm_max_size=10%*pga_aggregate_target
pga_aggregate_target >2.5g,_smm_max_size=0.25GB
修改pga_aggregate_target超過500m
SQL> alter system set pga_aggregate_target=501m;
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 268435456 bytes
Fixed Size 1266944 bytes
Variable Size 100666112 bytes
Database Buffers 159383552 bytes
Redo Buffers 7118848 bytes
Database mounted.
Database opened.
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_pga_max_size big integer 10M ~~~~~~~~~還未10MB
pga_aggregate_target big integer 501M
SQL>
SQL> set linesize 132
SQL> column name format a30
SQL> column value format a25
SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
SQL> 2 FROM SYS.x$ksppi x, SYS.x$ksppcv y
3 WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
4
SQL> /
Enter value for par: pga_max
old 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
new 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%pga_max%'
NAME VALUE
------------------------------ -------------------------
DESCRIB
------------------------------------------------------------------------------------------------------------------------------------
_pga_max_size 10485760
Maximum size of the PGA memory for one process
SQL> /
Enter value for par: smm_max
old 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
new 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%smm_max%'
NAME VALUE
------------------------------ -------------------------
DESCRIB
------------------------------------------------------------------------------------------------------------------------------------
_smm_max_size 5120 ~~~~~~~~~~~~~~~還為5M 單位kb
maximum work area size in auto mode (serial)
通過這個可以發現 影響_smm_max_size調整最直接相關的引數為_pga_max_size,由於_pga_max_size未調整. _smm_max_size沒變
所以可以得出結論pga_aggreate_target影響_pga_max_size,_pga_max_size影響_smm_max_size
可以看到 雖然pga_aggregate_target設定了501M,_PGA_MAX_SIZE應該為200M 自動調整為，但由於_pga_max_size手動調整的，所以要手動reset下 讓它自動預設（這樣就自動計算了）
SQL> alter system reset "_pga_max_size" scope=spfile sid='*' ;
System altered.
SQL> startup force
ORACLE instance started
Total System Global Area 268435456 bytes
Fixed Size 1266944 bytes
Variable Size 100666112 bytes
Database Buffers 159383552 bytes
Redo Buffers 7118848 bytes
Database mounted.
Database opened.
SQL> set linesize 132
SQL> column name format a30
SQL> column value format a25
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2 FROM SYS.x$ksppi x, SYS.x$ksppcv y
3 WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
4
SQL> /
Enter value for par: pga_max
old 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
new 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%pga_max%'
NAME VALUE
------------------------------ -------------------------
DESCRIB
------------------------------------------------------------------------------------------------------------------------------------
_pga_max_size 209715200 ~~~~~~~~~
Maximum size of the PGA memory for one process
按算髮pga_aggreate_target<1g,_pga_max_size =200m
SQL> /
Enter value for par: smm_max
old 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
new 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%smm_max%'
NAME VALUE
------------------------------ -------------------------
DESCRIB
------------------------------------------------------------------------------------------------------------------------------------
_smm_max_size 102400
maximum work area size in auto mode (serial)
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 501M
SQL>
102400/1024=100MB正好100MB
可以看到_pga_max_size=200M(pga_aggreate_target<1g,_pga_max_size default =200m)，按表面演算法pga_aggreate_target>500M _smm_max_size 為100MB
而實際內部因為
_pga_max_size=200M<40%*pga_aggregate_target,所以_smm_max_size =100M（50%*_pga_max_size）
(pga_aggreate_target影響_pga_max_size,_pga_max_size影響_smm_max_size)
有時候 手動修改_pga_max_size後 ，如果要讓演算法繼續執行，需要reset下，否則不使用演算法了.
改 pga_aggregate_target<500M
SQL> alter system set pga_aggregate_target=499m;
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 268435456 bytes
Fixed Size 1266944 bytes
Variable Size 100666112 bytes
Database Buffers 159383552 bytes
Redo Buffers 7118848 bytes
Database mounted.
Database opened.
SQL> set linesize 132
SQL> column name format a30
SQL> column value format a25
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2 FROM SYS.x$ksppi x, SYS.x$ksppcv y
3 WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
4
SQL> /
Enter value for par: pga_max
old 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
new 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%pga_max%'
NAME VALUE
------------------------------ -------------------------
DESCRIB
------------------------------------------------------------------------------------------------------------------------------------
_pga_max_size 209715200
Maximum size of the PGA memory for one process
SQL> /
Enter value for par: smm_max
old 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
new 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%smm_max%'
NAME VALUE
------------------------------ -------------------------
DESCRIB
------------------------------------------------------------------------------------------------------------------------------------
_smm_max_size 102195
maximum work area size in auto mode (serial)
SQL> select 102195/1024 from dual;
102195/1024
-----------
99.7998047 _smm_max_size=99.8m,_pga_max_size =200M
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 499M
SQL> select 499*0.2 from dual;
499*0.2
----------
99.8
SQL> select 0.4*499 from dual;
0.4*499
----------
199.6
分析 _pga_max_size >40%pga_aggregate_target ,所以_smm_max_size=20%*pga_aggregate_target =99.8M
手動改_pga_max_size為300M,pga_aggregate_target=501M
SQL> alter system set "_pga_max_size"=300M SCOPE=SPFILE;
System altered.
SQL> startup force;
ORACLE instance started.
Total System Global Area 268435456 bytes
Fixed Size 1266944 bytes
Variable Size 100666112 bytes
Database Buffers 159383552 bytes
Redo Buffers 7118848 bytes
Database mounted.
Database opened.
SQL> set linesize 132
SQL> column name format a30
SQL> column value format a25
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2 FROM SYS.x$ksppi x, SYS.x$ksppcv y
3 WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
4
SQL> /
Enter value for par: pga_max
old 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
new 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%pga_max%'
NAME VALUE
------------------------------ -------------------------
DESCRIB
------------------------------------------------------------------------------------------------------------------------------------
_pga_max_size 314572800
Maximum size of the PGA memory for one process
SQL> /
Enter value for par: smm_max
old 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
new 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%smm_max%'
NAME VALUE
------------------------------ -------------------------
DESCRIB
------------------------------------------------------------------------------------------------------------------------------------
_smm_max_size 102604
maximum work area size in auto mode (serial)
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_pga_max_size big integer 300M
pga_aggregate_target big integer 501M
SQL>
SQL> select 102604/1024 from dual;
102604/1024
-----------
100.199219
SQL> select 0.2*501 from dual;
0.2*501
----------
100.2
分析
_pga_max_size>40%*pga_aggregate_target,
按演算法 pga_aggregate_target>500M<1G,_smm_max_size =100MB(按pga_aggregate_target between 500m and 1000M ,_smm_max_size=100M),但實際還是按
_pga_max_size>40%*pga_aggregate_target,_smm_max_size=20%*pga_aggregate_target =100.2m
做一個極端的,_pga_max_size=600M,pga_aggregate_target=700M,如果按 pga_aggregate_target between 500m and 1000M 那麼_smm_max_size=100M,實際會是這樣嗎?
SQL> alter system set "_pga_max_size"=600m;
System altered.
SQL> alter system set pga_aggregate_target=700m;
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 268435456 bytes
Fixed Size 1266944 bytes
Variable Size 100666112 bytes
Database Buffers 159383552 bytes
Redo Buffers 7118848 bytes
Database mounted.
Database opened.
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_pga_max_size big integer 600M
pga_aggregate_target big integer 700M
SQL> set linesize 132
SQL> column name format a30
SQL> column value format a25
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2 FROM SYS.x$ksppi x, SYS.x$ksppcv y
3 WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
4
SQL>
SQL> /
Enter value for par: smm_max
old 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
new 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%smm_max%'
NAME VALUE
------------------------------ -------------------------
DESCRIB
------------------------------------------------------------------------------------------------------------------------------------
_smm_max_size 143360
maximum work area size in auto mode (serial)
可以看到_smm_max_size=140G沒有按,pga_aggregate_target>500M<1G時候應該=100m,_pga_max_size>40%*pga_aggregate_target,而是按20%*pga_aggreget_target算的=140G了
設定_pga_max_size=199g,pga_aggregate_target=501g
SQL> alter system set "_pga_max_size"=199M SCOPE=SPFILE;
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 268435456 bytes
Fixed Size 1266944 bytes
Variable Size 100666112 bytes
Database Buffers 159383552 bytes
Redo Buffers 7118848 bytes
Database mounted.
Database opened.
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_pga_max_size big integer 199M
pga_aggregate_target big integer 501M
SQL> set linesize 132
SQL> column name format a30
SQL> column value format a25
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2 FROM SYS.x$ksppi x, SYS.x$ksppcv y
3 WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
4
SQL> /
Enter value for par: smm_max
old 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
new 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%smm_max%'
NAME VALUE
------------------------------ -------------------------
DESCRIB
------------------------------------------------------------------------------------------------------------------------------------
_smm_max_size 101888
maximum work area size in auto mode (serial)
SQL> select 101888/1024 from dual;
101888/1024
-----------
99.5~~~~~~~~~~~~~~~
分析_pga_max_size<40%pga_aggregat_target,所以_smm_max_size=50%_pga_max_size
SQL> alter system set "_pga_max_size"=99M SCOPE=SPFILE;
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 268435456 bytes
Fixed Size 1266944 bytes
Variable Size 100666112 bytes
Database Buffers 159383552 bytes
Redo Buffers 7118848 bytes
Database mounted.
Database opened.
SQL> set linesize 132
SQL> column name format a30
SQL> column value format a25
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2 FROM SYS.x$ksppi x, SYS.x$ksppcv y
3 WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
4
SQL> /
Enter value for par: pga_max
old 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
new 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%pga_max%'
NAME VALUE
------------------------------ -------------------------
DESCRIB
------------------------------------------------------------------------------------------------------------------------------------
_pga_max_size 103809024
Maximum size of the PGA memory for one process
SQL> /
Enter value for par: smm_max
old 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
new 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%smm_max%'
NAME VALUE
------------------------------ -------------------------
DESCRIB
------------------------------------------------------------------------------------------------------------------------------------
_smm_max_size 50688
maximum work area size in auto mode (serial)
SQL> select 103809024/1024/1024 from dual
2 ;
103809024/1024/1024
-------------------
99
SQL> select 50688 /1024 from dual;
50688/1024
----------
49.5
_smm_max_size = 49.5
分析_pga_max_size<40%pga_aggregat_target,所以_smm_max_size=50%_pga_max_size
有文擋說_smm_max_size最大0.25GB,實際是這樣嗎
SQL> alter system set pga_aggregate_target=2g; 修改為2g
System altered.
SQL> startup force;
ORACLE instance started.
Total System Global Area 268435456 bytes
Fixed Size 1266944 bytes
Variable Size 100666112 bytes
Database Buffers 159383552 bytes
Redo Buffers 7118848 bytes
Database mounted.
Database opened.
SQL> set linesize 132
SQL> column name format a30
SQL> column value format a25
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2 FROM SYS.x$ksppi x, SYS.x$ksppcv y
3 WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
4
SQL> /
Enter value for par: pga_max
old 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
new 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%pga_max%'
NAME VALUE
------------------------------ -------------------------
DESCRIB
------------------------------------------------------------------------------------------------------------------------------------
_pga_max_size 419430400
Maximum size of the PGA memory for one process
_pga_max_size =20%*pga_aggreaget_target=400m
SQL> /
Enter value for par: smm_max
old 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
new 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%smm_max%'
NAME VALUE
------------------------------ -------------------------
DESCRIB
------------------------------------------------------------------------------------------------------------------------------------
_smm_max_size 204800
maximum work area size in auto mode (serial)
_pga_max_size<40%pga_aggregate_target,所以_smm_max_size=50%*_pga_max_size=200m
文件中當pga_aggrgate_target >1G情況時,其實還是按上面的 計算方式
_pga_max_size=20%*pga_aggregate_target
_smm_max_size=10%*pga_aggregate_target
一套負責的生產庫中pga (11g,11.1.0.7)
SQL> show parameter target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
memory_max_target big integer 0
memory_target big integer 0 **********************沒開
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 30G
SQL> set linesize 132
SQL> column name format a30
SQL> column value format a25
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2 FROM SYS.x$ksppi x, SYS.x$ksppcv y
3 WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
4
SQL>
SQL> /
Enter value for par: pga_max
old 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
new 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%pga_max%'
NAME VALUE
------------------------------ -------------------------
DESCRIB
------------------------------------------------------------------------------------------------------------------------------------
_pga_max_size 2147483648
Maximum size of the PGA memory for one process
SQL> /
Enter value for par: smm_max
old 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
new 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%smm_max%'
NAME VALUE
------------------------------ -------------------------
DESCRIB
------------------------------------------------------------------------------------------------------------------------------------
_smm_max_size 1048576
可以看到 _smm_max_size早就超過了250M 到達了1G,這個數是根據_pga_max_size決定
當pga_aggreate_size>1g時候,_pga_max_size=20%*pga_aggreate_size(pga_aggreate_size>10g時,_pga_max_size不變)
_pga_max_size=20%*10g<40%*pga_aggreaget_size,所以_smm_max_size=50%*_pga_max_size=1g
總結10g r2
PGA_AGGREATE_TARGET與_smm_max_size
pga_aggregate_target<=500MB,_smm_max_size=20%*pga_aggregate_target
pga_aggregate_target between 500m and 1000M ,_smm_max_size=100M
pga_aggregate_target between 1001m and 2.5g,_smm_max_size=10%*pga_aggregate_target
pga_aggregate_target,_pga_max_size 關係
_pga_max_size如果pga_aggregate_target<1g _pga_max_size=預設200MB
_pga_max_size如果pga_aggregate_target>1g,_pga_max_size=20%*pga_aggregate_target(>5G不再變化)
實際到內部
_smm_max_size為實際控制單個process使用pga上限,唯一可以相關他的引數是_pga_max_size,_pga_max_size被pga_aggregate_target相關
_pga_max_size<40%*pga_aggregate_target,_smm_max_size=50%*_pga_max_size，
其他時候（_pga_max_size>40%*pga_aggregate_target） _smm_max_size=20%PGA_AGGREGATe_TARGET
具體process可以使用的pga就是由_smm_max_size控制
> "_pga_max_size" is 200M by default.
That was true up to Oracle 10.1. Since 10.2 it is a dynamic parameter.
In 10.2 it is limited to 200M as long as pga_aggregate_target is smaller as 1GB.
When pga_aggregate_target is set to a larger value as 1GB then _pga_max_size= 20% of pga_aggregate_target .
> Is 350M of "pga_aggregate_target" effective even when "_pga_max_size" is 200M?
Yes it is still effective. The size of one work area (hash area, sort area) is not directly limited by _pga_max_size but by _smm_max_size (unit of this parameter is KBytes!). When you set pga_aggregate_target to 350M _smm_max_size should have a value like 71680 (71680KB => 70MB).
隨著版本的變化記憶體管理也發生變化
11g記憶體管理更加簡單了
只要設定memory_target=PGA+SGA這叫做amm特性，automatic memory management
設定後取代了pga_aggregate_target,sga_target,原理跟設定sga_target一樣，_pga_aggregate_target,_sga_target表示資料庫上次正常關閉時候記憶體分配的樣子
oracle啟動時候 會用 pga_aggregate_target,sga_target與_pga_aggregate_target,_sga_target,誰的值大用誰
SQL> show parameter target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
memory_max_target big integer 476M
memory_target big integer 476M ****************************
pga_aggregate_target big integer 60M
sga_target big integer 0
SQL> set linesize 132
SQL> column name format a30
SQL> column value format a25
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2 FROM SYS.x$ksppi x, SYS.x$ksppcv y
3 WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
4
SQL>
SQL> /
Enter value for par: pga_aggrega
old 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
new 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%pga_aggrega%'
NAME VALUE
------------------------------ -------------------------
DESCRIB
------------------------------------------------------------------------------------------------------------------------------------
pga_aggregate_target 62914560
Target size for the aggregate PGA memory consumed by the instance
__pga_aggregate_target 150994944
Current target size for the aggregate PGA memory consumed
可以看到實際pga為150M
SQL> /
Enter value for par: pga_max
old 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
new 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%pga_max%'
NAME VALUE
------------------------------ -------------------------
DESCRIB
------------------------------------------------------------------------------------------------------------------------------------
_pga_max_size 209715200
Maximum size of the PGA memory for one process
可以看到pga小於1g,_pga_max_size 預設為200M
SQL> /
Enter value for par: smm_max
old 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
new 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%smm_max%'
NAME VALUE
------------------------------ -------------------------
DESCRIB
------------------------------------------------------------------------------------------------------------------------------------
_smm_max_size 29491
maximum work area size in auto mode (serial)
_pga_max_size>40%__pga_aggregate_target 所以,_smm_max_size按__pga_aggregate_target演算法(此例中小於500M 那麼_smm_max_size=20*__pga_aggregate_target=30M).
#檢視pga建議
SELECT (SELECT ROUND(value/1024/1024,0) FROM v$parameter
WHERE name = 'pga_aggregate_target') "Current Mb"
, ROUND(pga_target_for_estimate/1024/1024,0) "Projected Mb"
, ROUND(estd_pga_cache_hit_percentage) "%"
FROM v$pga_target_advice
ORDER BY 2;
#PGA 常用的查詢
-PGASTAT
max memory used per session = min(5%*pga_aggregate_target,50%*_pga_max_size,_smm_max_size)
_sort_multiblock_read_count
SELECT NAME,VALUE VALUE_INT FROM V$PGASTAT;
select last_execution,count(1) from v$sql_workarea where POLICY='AUTO' group by last_execution;
select sum(OPTIMAL_EXECUTIONS) OPTIMAL,sum(ONEPASS_EXECUTIONS) ONEPASS ,sum(MULTIPASSES_EXECUTIONS) MULTIPASSES
from v$sql_workarea where POLICY='AUTO';
SELECT LOW_OPTIMAL_SIZE/1024 low_kb,(HIGH_OPTIMAL_SIZE+1)/1024 high_kb,
optimal_executions optimal, onepass_executions onepass, multipasses_executions multipasses
FROM v$sql_workarea_histogram
WHERE total_executions != 0;
select n.name,sum(s.value) value
from v$sesstat s,v$statname n where s.statistic#=n.statistic#
and n.name like 'workarea executions%'
group by n.name;
select n.name,sum(s.value) value
from v$sysstat s,v$statname n where s.statistic#=n.statistic#
and n.name like 'workarea executions%'
group by n.name;
SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
ESTD_OVERALLOC_COUNT
FROM v$pga_target_advice;
SELECT server, s.username, osuser, NAME, VALUE / 1024 / 1024, s.SID, s.serial#, spid
FROM v$session s, v$sesstat st, v$statname sn, v$process p
WHERE st.SID = s.SID
AND st.statistic# = sn.statistic#
AND sn.NAME LIKE 'session pga memory'
AND p.addr = s.paddr
AND VALUE > 10 * 1024 * 1024 --only show pga > 10M
ORDER BY VALUE DESC;
--PGA USAGE
SQL> select
sum(value)/1024/1024 Mb
from
v$sesstat s, v$statname n
where
n.STATISTIC# = s.STATISTIC# and
name = 'session pga memory';
col name format a30
select
sid,name,value
from
v$statname n,v$sesstat s
where
n.STATISTIC# = s.STATISTIC# and
name like 'session%memory%'
order by 3 asc;
SQL> select sum(bytes)/1024/1024 Mb from
(select bytes from v$sgastat
union
select value bytes from
v$sesstat s,
v$statname n
where
n.STATISTIC# = s.STATISTIC# and
n.name = 'session pga memory'
);
#接觸過許多9I 庫ＰＧＡ 給到40多Ｇ 但還是有不少多遍排序情況～原因就是 上面演算法問題
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-671716/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle PGA管理（一）Oracle
- Oracle PGA引數的管理Oracle
- 淺說Oracle PGA空間管理Oracle
- ORACLE記憶體管理 之一 ORACLE PGAOracle記憶體
- Oracle9i自動PGA管理(zt)Oracle
- Oracle9i中的PGA自動管理Oracle
- ORACLE 記憶體管理 之二 PGA v$pgastatOracle記憶體AST
- Oracle：PGA 簡介Oracle
- Oracle PGA詳解Oracle
- oracle PGA 構成Oracle
- ORACLE記憶體管理 之三 PGA v$sql_workarea_histogram v$pga_target_adviceOracle記憶體SQLHistogram
- Oracle9i 自動管理PGA記憶體(zt)Oracle記憶體
- oracle10g PGAOracle
- 如何優化oracle pga優化Oracle
- Oracle PGA自動管理在OLAP系統中的應用Oracle
- pga_aggregate_target 相關總結 -- Oracle PGAOracle
- 變更oracle 11.2.0.3 rac sga手工管理為sga及pga全自動管理Oracle
- ORACLE PGA程式全域性區Oracle
- Oracle Automatic PGA Memory ManagementOracle
- SGA和PGA記憶體管理記憶體
- PGA 記憶體的管理 (zt)記憶體
- oracle10g 修改 sga pgaOracle
- Oracle9i中的PGAOracle
- oracle pga使用情況常用指令碼：Oracle指令碼
- oracle體系結構梳理---SGA+PGAOracle
- Oracle PGA記憶體的配置和使用Oracle記憶體
- Oracle記憶體結構研究-PGA篇Oracle記憶體
- PGA的實際管理，設定和最佳化
- Oracle 體系結構 SGA 和PGA 總結Oracle
- 【TUNE_ORACLE】PGA_AGGREGATE_LIMIT詳解（一）PGA_AGGREGATE_TARGET的限制OracleMIT
- 【TUNE_ORACLE】PGA_AGGREGATE_LIMIT詳解（二）PGA_AGGREGATE_LIMIT的作用OracleMIT
- oracle12c新特性（9）--限制PGA的大小Oracle
- oracle例項記憶體（SGA和PGA）調整Oracle記憶體
- oracle資料庫記憶體分配（sga和pga）Oracle資料庫記憶體
- Oracle9i的動態SGA,PGA特性探索Oracle
- oracle實驗記錄 （PGA manual or auto 與hash join）Oracle
- 【SGA】【PGA】普適的Oracle記憶體分配策略Oracle記憶體
- 【TUNE_ORACLE】PGA_AGGREGATE_LIMIT詳解（三）PGA_AGGREGATE_LIMIT的大小設定OracleMIT