PGA内存管理策略

悠悠 2022-04-14 06:20 232阅读 0赞

转自：http://zhidao.baidu.com/link?url=z9PNNWBWVAC6dlAX5\_KBRMoUSC0QctbwHEqMIJELKsUpEVDzadLwjrZ3Mn8M5Xcd\_c7pDZk7cW9s6CqWFI8jBa  
  
　　什么时候使用自动PGA内存管理？什么时候使用手动PGA内存管理？  
  
白天系统正常运行时适合使用自动PGA内存管理，让Oracle根据当前负载自动管理、分配PGA内存。  
  
夜里用户数少、进行维护的时候可以设定当前会话使用手动PGA内存管理，让当前的维护操作获得尽可能多的内存，加快执行速度。  
  
如：服务器平时运行在自动PGA内存管理模式下，夜里有个任务要大表进行排序连接后更新，就可以在该操作session中临时更改为手动PGA内存管理，然后分配大的SORT\_AREA\_SIZE和HASH\_AREA\_SIZE（50%甚至80%内存，要确保无其他用户使用），这样能大大加快系统运行速度，又不影响白天高峰期对系统造成的影响。  
  
操作命令  
  
会话级更改  
  
ALTER SESSION SET WORKAREA\_SIZE\_POLICY = \{AUTO | MANAUL\}；  
  
ALTER SESSION SET SORT\_AREA\_SIZE = 65536；  
  
ALTER SESSION SET HASH\_AREA\_SIZE = 65536；  
  
学以致用  
  
1，排序区：  
  
pga\_aggregate\_target为100MB，单个查询能用到5%也就是5MB时排序所需时间  
  
SQL> create table sorttable as select \* from all\_objects;  
  
表已创建。  
  
SQL> insert into sorttable (select \* from sorttable);  
  
已创建49735行。  
  
SQL> insert into sorttable (select \* from sorttable);  
  
已创建99470行。  
  
SQL> set timing on;  
  
SQL> set autotrace traceonly;  
  
SQL> select \* from sorttable order by object\_id;  
  
已选择198940行。  
  
已用时间: 00: 00: 50.49  
  
Session级修改排序区为30mb所需时间  
  
SQL> ALTER SESSION SET WORKAREA\_SIZE\_POLICY = MANUAL;  
  
会话已更改。  
  
已用时间: 00: 00: 00.02  
  
SQL> ALTER SESSION SET SORT\_AREA\_SIZE = 30000000;  
  
会话已更改。  
  
已用时间: 00: 00: 00.01  
  
SQL> select \* from sorttable order by object\_id;  
  
已选择198940行。  
  
已用时间: 00: 00: 10.76  
  
可以看到所需时间从50.49秒减少到10.31秒，速度提升很明显。  
  
2，散列区：  
  
pga\_aggregate\_target为100MB，单个查询能用到5%也就是5MB时表连接所需时间  
  
SQL> select /\*+ use\_hash(tb1 tb2)\*/ \* from sorttable tb1,sorttable tb2 where tb1.object\_id=tb2.object\_id;  
  
已选择49735行。  
  
已用时间: 00: 00: 40.50  
  
Session级修改散列区为30mb所需时间  
  
SQL> ALTER SESSION SET WORKAREA\_SIZE\_POLICY = MANUAL;  
  
会话已更改。  
  
已用时间: 00: 00: 00.01  
  
SQL> ALTER SESSION SET HASH\_AREA\_SIZE = 30000000;  
  
会话已更改。  
  
已用时间: 00: 00: 00.01  
  
SQL> select /\*+ use\_hash(tb1 tb2)\*/ \* from sorttable tb1,sorttable tb2 where tb1.object\_id=tb2.object\_id;  
  
已选择49735行。  
  
已用时间: 00: 00: 04.47  
  
所需时间由40.50秒提升到4.47秒，效果同样很明显。  
  
备注：以上实验皆执行全表扫描保证相关表读入缓冲区中，避免因数据没读入缓存造成误差。  
  
\---另一个网址的：  
总结一下：  
1。 使用并行参数，8个CPU, 可以用parallel 6 ,最多占用6个CPU, 正常情况下没问题（前提是其他应用没有占超过2个CPU的资源）  
2。 nologging, 绝对应该使用，会使速度大幅上升。（减少大量redo log)  
3。 PGA, 普通的auto pga最大才100M, 显然没有达到最好性能，应该使用manual pga  
alter session set workarea\_size\_policy=manual;  
alter session set hash\_area\_size=100000; -- hash\_area\_size 默认情况下会自动根据sort\_area\_size\*2来调，导致sort\_area\_size不能超过1G, 手动设了就没问题了。  
alter session set sort\_area\_size=2000000000; -- 在系统可用内存足够的情况下，最大可以到2G  
4。 设置高一点的db\_file\_multiblock\_read\_count 也有利于提高I/O性能。保证足够大的db\_cache\_size可以避免free\_buffer\_wait的出现  
5。 可以通过v$session\_longops 来监视进度