自动pga管理.docx
- 文档编号:11678811
- 上传时间:2023-03-30
- 格式:DOCX
- 页数:12
- 大小:41.32KB
自动pga管理.docx
《自动pga管理.docx》由会员分享,可在线阅读,更多相关《自动pga管理.docx(12页珍藏版)》请在冰豆网上搜索。
自动pga管理
自动pga管理—原理及优化
在一个复杂的数据库系统中会同时运行着许多sql语句,这些语句当中会有进行sort,hash_join等等很多操作,它们都必须分配一些内存进行这些操作,在oracle的dedicatedserver环境中,这些内存将会被分配到pga中,如果pga的内存分配管理不当将会导致系统内存不足,操作系统将会频繁pgaein/out,会降低数据库的整体性能,所以正确管理pga内存分配是一件相当重要的事情.
在oracle9i以前的版本中,我们可以通过手工修改sort_area_size,hash_area_size等值控制pga的使用率,使用这种分配方法会存在一个弊端,因为数据库中存在成千上万条不同的sql语句,它们之中有的需要很大的内存需要,有的需要的很少,如果指定一个大的sort_area_size或hash_area_size,虽然保证了某些语句的执行速度,但是pga总体内存将会过度分配,严重时会导致操作系统pagein/out,降低整个数据的性能.如果sort_area_size或hash_area_size设置的太小了,那么某些语句将会执行想多长的时间,会影响应用程序的响应速度.所以如何手工设置合适的pga参数在oracle9i推出之前一直是很多dba心头的痛.不过这种情况在oracle9i推出后已经得到了比较好的解决,9i的pga自动管理功能很好得解决了这个问题.下面让我们开始学习pga自动管理.
什么是pga内存自动管理?
Oracle8i或更早版本中用SORT_AREA_SIZE和HASH_AREA_SIZE参数来指定每个session可用到的最大排序和hash内存大小.
在Oracle9i里面,用PGA_AGGREGATE_TARGET参数来指定所有session一共使用最大pga内存的上限.这个参数可以被动态的更改,赋值范围从10M-4096G-1bytes.
9i里还提供了WORKAREA_SIZE_POLICY参数用于开关pga内存自动管理功能.
AUTO:
自动管理
MANUAL:
手工管理
当WORKAREA_SIZE_POLICY设置为manual时,pga的内存分配还是使用sort_area_size等参数来分配,设置为auto时,sort_area_size等参数将被忽略.注意,9i里WORKAREA_SIZE_POLICY默认被设置为auto.
在OLAP和DSS系统中通常会存在很多相当复杂的语句,它们会进行多表关联,处理很大数据量,会存在很多sort和hashjoin,这些语句将会需要很多内存空间去执行.当这些内存分配后,oracle进程将会在其中进行排序或构造hash表,通常的话分配的内存越多sql语句将会执行得越快.我们把这段执行的区域叫做workarea,把能进行完全内存操作的workarea大小叫做optimalsize.与之对应的还有onepasssize,multipasssize
optimal:
所有操作都在内存中进行进行
onepass:
使用最小写磁盘操作,大部分在内存中进行
multipass:
当workarea太小的话将会发生大量磁盘操作,性能急剧下降
这里针对sort和hashjoin还是有点区别,当进行sort操作的话,当分配的内存介于optimal与onepass之间的话语句的响应时间是不会随着内存的增加而减少,但是如果是hashjoin,如果增加内存,它的响应时间将会随之降低,这是和sort,hashjoin内步执行方式有关,有兴趣的话可以参考sort,hash相关文档.
-------------------------------------------------------------------------------
当使用sharedserver时,workarea将在sga的largepool中分配,同时pga自动内存管理将被忽略,所有workarea内存管理还是来自于sort_area_size,hash_area_size等参数的设置.
从上面这张图我们也可以发现pga内存大小对响应时间还是影响很大的,所以我们应该尽量让它们都在内存中进行,一个典型的OLTP环境的话需要
workareaexecution-optimal>=90%
workareaexecution-multiplass=0%
现在我们知道了pga内存分配的重要性,也知道pga自动管理的优点,那让我们深入到pga自动管理里面看看内部是怎么实现的.
pga自动管理是采用feedbackloop算法来管理内存分配的,来看一张图
我们来把这个图走一遍,当一条语句开始执行,它会通过localmemorymanager注册一个activeworkareaprofile,profile里面包括了这个workarea的一系列属性,它的类型(sort,hash-join,……),它当前执行需要的minimum,one-passandoptimal内存大小,它的并行度,它当前在使用的内存等.由于profile经常被更新,所以所有activeprofiles基本可以反映出当前内存需要和当前在使用的内存.有了这些profile信息,后台进程globalmemorymanager就可以计算出每个activeworkarea的memorybound,globalmemorymanager没隔3秒将会更新一下memorybound,localmemorymanager得到memorybound后将会计算出每个activestatement的所要分配的内存大小,在这里被称为expectsize.然后每个activestatement将会在自己所分配到的expectsize内存里面进行操作.计算expectsize的时候还需要符合5个规则
规则1:
expectedsize不能小于minimummemory需求.
规则2:
expectedsize不能大于optimalmemory需求.
规则3:
如果bound介于minimum和optimal之间,那么将会使用bound值作为expectsize.sort操作除外,因为sort操作并不会从多余内存中得到利益,上面也已经提到,所以当sort操作时将会取onepass做为expectsize.
规则4:
如果这个workarea时并行执行的,那么它的expectsize和它的并行度成正比.
规则5:
最后,expectsize不能超过5%pga_aggregate_target大小或100M,并行操作下不能超过30%pga_aggregate_target大小.
这里面sqlmemorytarget,globalmemorybound,dirftmanagement都运用了一系列机制来保证pga自动内存管理的稳定性,在这里不再详述.
现在大家都了解了pga自动内存管理的原理了,让我们做一些实验来验证一下以上的理论.
SQL>altersystemsetpga_aggregate_target=10M;
Systemaltered.
SQL>altersystemsetworkarea_size_policy=auto;
Systemaltered.
执行一个排序
selectdistinct*fromawhererownum<500000;
Elapsed:
00:
01:
00.5
*ViewSQLworkarea
SQL>select
sql_text,operation_type,policy,last_memory_used/1024/1024,last_execution,
last_tempseg_sizefromv$sqll,v$sql_workareaa
wherel.hash_value=a.hash_value
andsql_text='selectdistinct*fromawhererownum<500000';
SQL_TEXTOPERATION_TYPE
----------------------------------------------------------------
selectdistinct*fromawhererownum<500000GROUPBY(SORT)
POLICYLAST_MEMORY_USED/1024/1024LAST_EXECUTIONLAST_TEMPSEG_SIZE
-----------------------------------------------------------------
AUTO.530PASSES12288000
可以看到这里使用到了0.5M的内存,再把pga_aggregate_target改大为40M
SQL>altersystemsetpga_aggregate_target=40M;
Systemaltered.
SQL>selectdistinct*fromawhererownum<500000;
Elapsed:
00:
00:
38.36
SQL>select
sql_text,operation_type,policy,last_memory_used/1024/1024,last_execution,
last_tempseg_sizefromv$sqll,v$sql_workareaa
wherel.hash_value=a.hash_value
andsql_text='selectdistinct*fromawhererownum<500000';
SQL_TEXTOPERATION_TYPE
----------------------------------------------------------------
selectdistinct*fromawhererownum<500000GROUPBY(SORT)
POLICYLAST_MEMORY_USED/1024/1024LAST_EXECUTIONLAST_TEMPSEG_SIZE
-----------------------------------------------------------------
AUTO21PASSES12288000
可以看到内存使用达到2M,也正好是5%pga_aggregate_target,这里证明了expectsize不能超过5%pga_aggregate_target的理论.
再把pga_aggregate_target改成10G
SQL>altersystemsetpga_aggregate_target=10G;
Systemaltered.
SQL>selectdistinct*fromawhererownum<500000;
Elapsed:
00:
00:
30.23
SQL>select
sql_text,operation_type,policy,last_memory_used/1024/1024,last_execution,
last_tempseg_sizefromv$sqll,v$sql_workareaa
wherel.hash_value=a.hash_value
andsql_text='selectdistinct*fromawhererownum<500000';
SQL_TEXTOPERATION_TYPE
----------------------------------------------------------------
selectdistinct*fromawhererownum<500000GROUPBY(SORT)
POLICYLAST_MEMORY_USED/1024/1024LAST_EXECUTIONLAST_TEMPSEG_SIZE
-----------------------------------------------------------------
AUTO19.35OPTIMAL
可以看到这时候使用了19.35M内存,所有排序操作都在内存中完成,语句的执行时间也从00:
01:
00.5减少到了00:
00:
30.23.
上面还提到了expectsize不能超过100M,我们来看一下是否能测试出来.
SQL>selectname,value/1024/1024/1024fromv$parameter
wherename='pga_aggregate_target';
NAMEVALUE/1024/1024/1024
-------------------------------------------------------
pga_aggregate_target10
SQL>selectdistinct*froma;
5000000rowsselected
Elapsed:
00:
09:
35.42
SQL>selectsql_text,operation_type,policy,last_memory_used/1024/1024,
last_execution,last_tempseg_sizefromv$sqll,v$sql_workareaa
wherel.hash_value=a.hash_value
andsql_text='selectdistinct*froma’
SQL_TEXTOPERATION_TYPEPOLICY
-----------------------------------------------------
selectdistinct*fromaGROUPBY(SORT)AUTO
LAST_MEMORY_USED/1024/1024LAST_EXECUTIONLAST_TEMPSEG_SIZE
---------------------------------------------------------------
92.3567321PASS129138688
为什么不能用到5%pga_aggregate_target=0.05*10g=500M的内存呢,这是由于有隐藏参数_pga_max_size来控制,每个session只能用到一半_pga_max_size值大小的内存,_pga_max_size默认的大小为200M.我们看看改了_pga_max_size的效果如何.
SQL>altersystemset"_pga_max_size"=500M;
Systemaltered
SQL>selectdistinct*froma;
5000000rowsselected
Elapsed:
00:
08:
55.23
SQL>selectsql_text,operation_type,policy,last_memory_used/1024/1024,
last_execution,last_tempseg_sizefromv$sqll,v$sql_workareaa
wherel.hash_value=a.hash_value
andsql_text='selectdistinct*froma';
SQL_TEXTOPERATION_TYPEPOLICY
-----------------------------------------------------
selectdistinct*fromaGROUPBY(SORT)AUTO
LAST_MEMORY_USED/1024/1024LAST_EXECUTIONLAST_TEMPSEG_SIZE
---------------------------------------------------------------
198.56346OPTIMAL
可以看到198.56346的内存被使用并且全部在内存中执行.
再来看一下并发情况下pga自动内存管理的效果.
首先看一下20个并发的情况
SQL>selectvalue/1024/1024fromv$parameter
wherename='pga_aggregate_target';
VALUE/1024/1024
---------------
50
selectsql_text,operation_type,policy,last_memory_used/1024/1024,
last_execution,last_tempseg_sizefromv$sqll,v$sql_workareaa
wherel.hash_value=a.hash_value
andsql_text='selectdistinct*froma';
SQL_TEXTOPERATION_TYPE
------------------------------------------------------------------
selectdistinct*fromaGROUPBY(SORT)
POLICYLAST_MEMORY_USED/1024/1024LAST_EXECUTIONLAST_TEMPSEG_SIZE
----------------------------------------------------------------------
AUTO2.3286741PASS6995868
这时用了2.328674/50=4.6%pga_aggregate_target
换成40个并发
SQL>selectsql_text,operation_type,policy,last_memory_used/1024/1024,
last_execution,last_tempseg_sizefromv$sqll,v$sql_workareaa
wherel.hash_value=a.hash_value
andsql_text='selectdistinct*froma';
SQL_TEXTOPERATION_TYPE
------------------------------------------------------------------
selectdistinct*fromaGROUPBY(SORT)
POLICYLAST_MEMORY_USED/1024/1024LAST_EXECUTIONLAST_TEMPSEG_SIZE
----------------------------------------------------------------------
AUTO.5214515PASSES7995392
我们可以看到0.52145/50=0.010429=1%,由于没有足够的内存让40个进程进行onepass操作,所以这里每个进程只用到了1%左右的内存进行multipass操作.
再来看看如果100个并发的情况会怎么样
SQL>selectsql_text,operation_type,policy,last_memory_used/1024,
last_execution,last_tempseg_sizefromv$sqll,v$sql_workareaa
wherel.hash_value=a.hash_value
andsql_text='selectdistinct*froma';
SQL_TEXTOPERATION_TYPE
------------------------------------------------------------------
selectdistinct*fromaGROUPBY(SORT)
POLICYLAST_MEMORY_USED/1024LAST_EXECUTIONLAST_TEMPSEG_SIZE
------------------------------------------------------------------
AUTO595105PASSES110256384
注意,100个并发的情况下已经出现overalloc的情况了,每个进程使用了0.6M,100个进程已经使用超过pga_aggregate_target的值了,这是因为memorybound每3秒才由globalmemorymanagement更新一次,发布的memorybound还未及时反映到内存使用情况导致.但是这也说明一种情况,就是pga_aggregate_target是可以被突破的,虽然这种情况很少见.我们也可以从v$pgastat中看到overalloc的信息.
SQL>select*fromv$pgastatwhereNAMElike‘over%’;
NAMEVALUEUNIT
overallocationcount15483
PGAAdvice功能
自从9irelease2开始,oracle提供了pgaadvice功能,主要通过2张视图
v$pga_target_advice,v$pga_target_advice_histogram实现建议功能.看一下两张视图的结构
v$pga_target_advice
Column
Datatype
Description
PGA_TARGET_FOR_ESTIMATE
NUMBER
估计的pga_aggregate_target大小(bytes)
PGA_TARGET_FACTOR
NUMBER
PGA_TARGET_FOR_ESTIMATE除以当前pga_aggregate_target得出的值
ADVICE_STATUS
VARCHAR2(3)
指示advice开启或关闭
BYTES_PROCESSED
NUMBER
所有workarea操作的数据量(bytes)
ESTD_EXTRA_BYTES_RW
NUMBER
估计设置pga_aggregate_target为PGA_TARGET_FOR_ESTIMATE时读写byte数
ESTD_PGA_CACHE_HIT_PERCENTAGE
NUMBER
估计内存命中率
ESTD_OVERALLOC_COUNT
NUMBER
估计overalloc次数
v$pga_target_advice_histogram
Colu
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 自动 pga 管理
![提示](https://static.bdocx.com/images/bang_tan.gif)