Oracle9i中的PGA自动管理

总结：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错误。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/6906/viewspace-21648/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/6906/viewspace-21648/