當前位置：首頁 > 编程语言 > php >内容正文

php

workarea8php,oracle PGA管理(算法)

發布時間：2025/3/20 php 32 豆豆

生活随笔收集整理的這篇文章主要介紹了 workarea8php,oracle PGA管理(算法) 小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

主要總結下pga分配算法,詳細概念單總結簡單理解pga,pga就是一個操作系統進程,或線程(WIN上)的專用內存 pmon,smon這些后臺進程都有自己的pga pga早期手動管理組成由sort_area_size,hash_area_size,bitmap_merge_size,create_bitmap_area_size(這些都叫工作

主要總結下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%'

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=*80%*20%

2.dss系統 pag_aggregate_target=*80%*50%

分析:留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 8431834oracle@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%'

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%'

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%'

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

------------------

SQL> select 5120/1024 from dual;

5120/1024

----------

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%'

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%'

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%'

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%'

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%'

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%'

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%'

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

-------------------

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%'

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%'

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%'

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 &nbsp

本文原創發布php中文網，轉載請注明出處，感謝您的尊重！

總結

以上是生活随笔為你收集整理的workarea8php,oracle PGA管理(算法)的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇：怎么用PHP语句做出增改删查功能,PHP
下一篇： java url连接,Java - 线