[转]Two new hidden parameters starting from 11.2.0.x

Starting Oracle 11.2.0.x, Oracle has introduced two new hidden parameters which indeed brings a change in the instance abort and memory setting behaviour.

1. _datafile_write_errors_crash_instance

The above parameter is “TRUE” by default and cause the instance to be aborted if any write to a datafile which fails due to an IO error. If it is set to false then the behaviour reverts to the previous behaviour such that a write error to a datafile offlines the file (If it is a system file, then the database will be aborted)

2. _memory_imm_mode_without_autosga 

The above parameter is “TRUE” by default and starting 11.2.0.x this parameter still resize the memory when neither SGA_TARGET nor MEMORY_TARGET is set. This parameter is mainly introduced to avoid ORA-4031 errors, really good for small shops but not for big shops

_memory_imm_mode_without_autosga - no really ! don't resize my sga ! I mean it !

Wow - I need a mental note for this one ! (hence the blogpost)

 

So you've disable Automatic Memory Management (AMM/ASMM) on your new 11.2 instance - memory_target and sga_target are set to zero ... because ... the dba knows best!

You've configure large pages - locked the sga in memory - manual configured buffercache etc - you've got the luxury of finally having a machine with gigs and gigs of memory.

 

And then one day you find out that your buffercache is 256Mb in size, the large pool is now 20Gb (coming from 1Gb) and the alert file is screaming ORA-4031, ORA-4031 !!!

 

How did that happen !!!!!! We didn't use automatic memory management ! You scream and yell - who touched the instance parameters..... no fool would set a large pool of 20Gb and a buffer cache of 256Mb manually.

Looking in the audit logs reveals nobody touched the instance ... it seems AMM/ASMM *is* at play - how can that be if we disabled it ?

 

You open a SR with support and everything becomes clear now :

- we just ignore your settings and do AMM anyway - if you don't want that set this underscore parameter :

alter system set "_memory_imm_mode_without_autosga"=FALSE scope=both;

 

Quote - "This is expected behavior in 11.2 for immediate memory allocation requests, which added this as a new feature when automatic memory management was disabled."

Stupid me !

 

note on metalink "SGA Re-Sizes Occurring Despite AMM/ASMM Being Disabled (MEMORY_TARGET/SGA_TARGET=0) [ID 1269139.1]"

Technorati Tags: amm ora-4031 sga resize _memory_imm_mode_without_autosga

这个是上面的翻译;

11gR2的自动共享内存管理，霸道！

Oracle数据库的内存管理，随着版本的更新不断的发生着深刻的变化。自Oracle10g引入自动共享内存管理（ASMM），解决了 SGA各个组件内存分配问题；在Oracle11g中又引入了自动内存管理（AMM），解决了SGA和PGA的分配问题。但在很多的生产库中，可能并不会 使用这些功能（可以简单的通过设置memory_target和sga_target为0来实现）。

如果你使用的是oracle11gR2的数据库，拥有超大的内存，你禁用了自动内存管理，配置了大页面，将sga锁定在内存中，并且手工配置了 buffer cache等内存组件。可某一天你发现buffer cache才256m，而large pool达到惊人的20g（你之前仅设置为1g），告警日志里疯狂的报着ORA-4031的错误。

看到这些情况，dba一定会觉得受了莫大的冤屈，因为我们已经禁用的自动共享内存管理！！！而且再蠢也不会设置20g的large pool和256m的buffer cache。

事实上，oracle忽略了我们的设置而继续着AMM的方式管理内存，真正禁用掉AMM需要设置一个隐含参数。

alter system set “_memory_imm_mode_without_autosga”=FALSE scope=both;

This is expected behavior in 11.2 for immediate memory allocation requests, which added this as a new feature when automatic memory management was disabled.  — metalink ID 1269139.1

My god！Oracle越来越霸道了！！！

最后补充一下,如果你的DB是超大的OLTP系统,而且你disable了AMM 和ASMM, 你可以考虑使用这个false的值. 但是你的系统不是很大的,那么就继续用default value把!