Oracle9i中的PGA自動管理

Oracle9i中的PGA自動管理

1.         Oracle9i之前的PGA管理

需要手工設定HASH_AREA_SIZE/SORT_AREA_SIZE等引數，每個連線所佔用的PGA大概為

unix:

1M+HASH_AREA_SIZE+SORT_AREA_SIZE

windows:

2M+HASH_AREA_SIZE+SORT_AREA_SIZE

1M、2M分別為在unix和windows下的os所佔用的記憶體。

用該值乘上估算的連線可以大致計算出所有連線PGA所佔用的記憶體空間。

2.         Oracle9i中的PGA自動管理

在Oracle9i中提供了自動管理PGA的新特性，可以自動並動態的在各個Session之間調整記憶體分配。PGA可以分類分為可調整和非可調整，可調整記憶體主要用於DSS系統中。

1)        跟PGA相關的初始化引數：

A．WORKAREA_SIZE_POLICY

可選值為’AUTO’和’MANUAL’。

設定為MANUAL，PGA的分配和管理與之前的資料庫版本一致。設定為AUTO則使用9i的新特性自動管理PGA。

B． PGA_AGGREGATE_TARGET

預設值為0，如果WORKAREA_SIZE_POLICY設定為AUTO，該值定義最大可用的PGA記憶體。

PGA_AGGREGATE_TARGET不但限制了全域性PGA可用數而且限制了工作區域的大小。比如單個sql的操作不能超過MIN（5%* PGA_AGGREGATE_TARGET,100M）

並行操作不能超過30% PGA_AGGREGATE_TARGET/(並行度)。

2)        確定PGA_AGGREGATE_TARGET的大小

A．估算：

對於OLTP系統

PGA_AGGREGATE_TARGET = (<> * 80%) * 20%

對於DSS系統

PGA_AGGREGATE_TARGET = ( * 80%) * 50%

這種估算基於80％記憶體用於Oracle，其中對於OLTP系統有20％用於PGA，而對於DSS系統有50％用於PGA。

B．透過動態效能檢視監控PGA的使用

V$PGASTAT：

該檢視提供了例項級別上PGA記憶體使用的統計資訊。

SELECT *FROM V$PGASTA;

其中幾個關鍵列的解析：

aggregate PGA auto target : 在自動模式下所能用於work area的PGA記憶體總數。該值表示PGA記憶體的可調整部分。

total PGA used for auto workarea: 系統使用的實際可調整的PGA記憶體。

total PGA in used: 正在使用的PGA記憶體總數。

3)        監控PGA的使用

A．以下查詢顯示例項中活動的工作區域的使用情況，但低於64K的排序操作在該檢視中不會顯示。

SELECT to_number(decode(SID, 65535, NULL, SID)) sid,
operation_type OPERATION,trunc(EXPECTED_SIZE/1024) ESIZE,
trunc(ACTUAL_MEM_USED/1024) MEM, trunc(MAX_MEM_USED/1024) "MAX MEM",
NUMBER_PASSES PASS, trunc(TEMPSEG_SIZE/1024) TSIZE
FROM V$SQL_WORKAREA_ACTIVE
ORDER BY 1,2

/

B．以下查詢返回使用OPTIMAL 記憶體的比例

select

trunc (

       (sum(case when name like 'workarea executions - optimal' 

                                       then value else 0 end) *100) /

       (

                  sum(case when name like 'workarea executions - optimal'   

then value else 0 end) +

                  sum(case when name like 'workarea executions - one pass'  

                                             then value else 0 end)         +                  

                                             sum(case when name like 'workarea executions - multipass' 

                                             then value else 0 end)

        )

        ) optimal_percent

from v$sysstat

where name like 'workarea executions - %'

/

C．定位需要最多記憶體的前十個工作區域

select * 

from

       (select workarea_address, operation_type, policy, estimated_optimal_size

       from v$sql_workarea

        order by estimated_optimal_size DESC)

where ROWNUM <=10

/

D．Finding the percentage of work areas using maximum memory:

select operation_type, total_executions * 100  / optimal_executions "%cache"

From v$sql_workarea

Where policy='AUTO'

And optimal_executions > 0

Order By operation_type

/

E．Finding the top ten biggest work areas currently allocated in the system:

select c.sql_text, w.operation_type, top_ten.wasize

From (Select *

      From (Select workarea_address, actual_mem_used wasize

            from v$sql_workarea_active

            Order by actual_mem_used)

      Where ROWNUM <=10) top_ten,

      v$sql_workarea w,

      v$sql c

Where    w.workarea_address=top_ten.workarea_address

        And c.address=w.address

        And c.child_number = w.child_number

        And c.hash_value=w.hash_value

/

F．Finding the percentage of memory that is over and under allocated:

select  total_used,

        under*100/(total_used+1) percent_under_use,

        over*100/(total_used+1)   percent_over_used

From

        ( Select

                sum(case when expected_size > actual_mem_used 

                                       then actual_mem_used else 0 end) under,

                sum(case when expected_size<> actual_mem_used 

                                       then actual_mem_used else 0 end) over,

                sum(actual_mem_used) total_used

        From v$sql_workarea_active

        Where policy='AUTO') usage

/ 

3.         結論

如果設定WORKAREA_SIZE_POLICY為AUTO，則Oracle會自動忽略*_area_size等初始化引數並自動管理PGA記憶體。透過一系列的動態檢視可以更好的配置PGA自動管理和監控PGA的使用。值得一提的是在Oracle的某些版本上PGA自動管理可能存在bug，在執行需要使用大量記憶體的排序或連線操作時會出現ora－04031錯誤。