数据库hang优化一则

数据库因为连接进程满了导致数据库hang住了

z4as8031:op36hz > ./netlog_pass.pl -t '18-JUL-2014 17:[0-9]' op36hz.log

18-JUL-2014 17:0 => TOTAL:445

18-JUL-2014 17:1 => TOTAL:438

18-JUL-2014 17:2 => TOTAL:849

18-JUL-2014 17:3 => TOTAL:3946

18-JUL-2014 17:4 => TOTAL:5906

18-JUL-2014 17:5 => TOTAL:3119

看了下这个段时间的awr报告的top 5

 

看了下几个top都是关于内存的，前面3个当shared_pool设置的不够的时候，会出现，第四个也是关于内存的sga的。

猜测这个问题与第四个有关系。

 

第四个找了下文章：因为sga_target设置太小，而内存组件直接的条件太过于频繁导致！

下面是关于SGA: allocation forcing component growth在metlink上的处理方法！

 

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Information in this document applies to any platform.
***Checked for relevance on 24-Jul-2013***

Symptoms

When using Automatic Shared Memory Management (ASMM) or Automatic Memory Management (AMM)  by setting either SGA_TARGET or MEMORY_TARGET, you may see locking issues when the following event is encountered:

'SGA: allocation forcing component growth'

This may or may not be accompanied by ORA-4031 errors.

Cause

ASMM and AMM use an auto-tuning memory management scheme. With either of these architectures enabled, memory can be moved automatically between components in the SGA (such as the buffer cache and shared pool) in order to fill a memory request in one of these components.

The 'SGA: allocation forcing component growth' can occur if the SGA_TARGET or MEMORY_TARGET is set too low for the instance, and there has to be frequent moving of the memory between the components of the SGA like the shared pool and the buffer cache.

Solution

The 'SGA: allocation forcing component growth' can often be reduced by increasing the setting of either the SGA_TARGET (for an ASMM setup) or MEMORY_TARGET (for an AMM setup). Enlarging the SGA_TARGET or MEMORY_TARGET will allow for the various SGA components to grow without the need for "stealing" memory of another component.

Increasing by at least 10% would be a good starting point and it can be tuned further if needed.

 

 

原因：sga内存之间的相互切换，争用导致。处理办法，禁用sga_target,手动分配内存参数

原来的参数

#*.sga_max_size=1008291200

#*.sga_target=1008291200

 

现在已经禁用sga_target,内存参数也静态设置

*.streams_pool_size=0

*.db_cache_size=1536M

*.pga_aggregate_target=1000M

*.shared_pool_reserved_size=122M

*.shared_pool_size=768M

*.java_pool_size=80M

*.large_pool_size=176M