ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle9i中的PGA自动管理

Oracle9i中的PGA自动管理

原创 Linux操作系统 作者:husthxd 时间:2004-11-23 00:00:00 0 删除 编辑
总结: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

1M2M分别为在unixwindows下的os所占用的内存。

用该值乘上估算的连接可以大致计算出所有连接PGA所占用的内存空间。

 

2.         Oracle9i中的PGA自动管理

Oracle9i中提供了自动管理PGA的新特性,可以自动并动态的在各个Session之间调整内存分配。PGA可以分类分为可调整和非可调整,可调整内存主要用于DSS系统中。

1)        PGA相关的初始化参数:

AWORKAREA_SIZE_POLICY

可选值为’AUTO’’MANUAL’

设置为MANUALPGA的分配和管理与之前的数据库版本一致。设置为AUTO则使用9i的新特性自动管理PGA

B. PGA_AGGREGATE_TARGET

默认值为0,如果WORKAREA_SIZE_POLICY设置为AUTO,该值定义最大可用的PGA内存。

PGA_AGGREGATE_TARGET不但限制了全局PGA可用数而且限制了工作区域的大小。比如单个sql的操作不能超过MIN5%* 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 areaPGA内存总数。该值表示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

/

DFinding 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

/

EFinding 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

/

FFinding 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_POLICYAUTO,则Oracle会自动忽略*_area_size等初始化参数并自动管理PGA内存。通过一系列的动态视图可以更好的配置PGA自动管理和监控PGA的使用。值得一提的是在Oracle的某些版本上PGA自动管理可能存在bug,在执行需要使用大量内存的排序或连接操作时会出现ora04031错误。

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

请登录后发表评论 登录
全部评论
长期从事政务、金融等行业产品研发和架构设计工作,ITPUB数据库版块资深版主,对Oracle、PostgreSQL以及大数据等相关技术有深入研究。现就职于广州云图数据技术有限公司,系统架构师。

注册时间:2007-12-28

  • 博文量
    1250
  • 访问量
    3724850