How to disable automatic SGA tuning in 10g

On many of our production E-Business Suite 11.5.10.2 instances which are on 10.2.0.3 and 10.2.0.4, we have been getting ORA-600 and ORA-7445 errors. In one of the severity one TARs, for these errors, which were occuring when a business critical program was run, Oracle support advised us to disable automatic SGA tuning by setting sga_target=0. Here's an excerpt of the TAR:

ISSUE CLARIFICATION
====================

Multiple errors ORA-7445, ORA-3136 and ORA-4031's


.
ISSUE VERIFICATION
===================

Verified the issue by the trace file

Verified the issue by the alert log file


.
RESEARCH
=========

Point 1: We can see that next to Free memory the KGH: No Access is the biggest memory consumer.

This can been one of two things. The SGA_TARGET is configured too low causing frequent growing and shrinking of the SGA components to accomi
date the needs.

or

There is a known issue where the growing and shrinking is due to no minimums being set for the db_buffer_cache and the Shared_pool_size.

Bug 6528336 Automatic SGA may repeatedly shrink / grow the shared pool
Fixed-Releases: A204 B200

Details:
Alternating frequent shrink and grow of the buffer cache and
shared pool may be seen with automatic memory management enabled
causing various waits in sessions.


.
CAUSE DETERMINATION
====================

Bug 6528336 Automatic SGA may repeatedly shrink / grow the shared pool
Fixed-Releases: A204 B200


CAUSE JUSTIFICATION
====================

Known issue when KGH No Access show as large memory consumer.



.
PROPOSED SOLUTION(S)
======================
List the solution option(s) and/or workarounds here - What will work.

1). Apply the 10.2.0.4.0 patchset to get the full fix for the issue.

- or -

2). Disable ASMM by setting SGA_TARGET=0

- or -

3). Note that even after you have applied a fix, frequent resizing between shared poo
l and buffer cache can still lead to buildup of "KGH: NOACCESS" allocations. The
frequent resizing is an indication that sga_target is too low. Increase sga_tar
get . Also, recommend setting a minimum size for shared pool by setting value f
or parameter shared_pool_size to prevent it being shrunk below that level, thus
reducing chances for ORA-4031 to occur.

- or -

4). alter system set "_memory_broker_stat_interval"=999; --- 999sec between resizes

This will increase the time between resize to atleast 999 seconds.
This will reduce the number of resizes.

_memory_broker_stat_interval is in seconds,By Default it is 30 seconds.
You can set _memory_broker_stat_interval to a larger value







PROPOSED SOLUTION JUSTIFICATION(S)
====================================
Known to resolve the ORA-4031 KGH: No Access.



.
SOLUTION / ACTION PLAN
=======================
To implement the solution, please execute the following steps:


1). Apply the 10.2.0.4.0 patchset to get the full fix for the issue.

- or -

2). Disable ASMM by setting SGA_TARGET=0

- or -

3). Note that even after you have applied a fix, frequent resizing between shared pool and buffer cache can still lead to buildup
of "KGH: NOACCESS" allocations. The frequent resizing is an indication that sga
_target is too low. Increase sga_target . Also, recommend setting a minimum siz
e for shared pool by setting value for parameter shared_pool_size to prevent it
being shrunk below that level, thus reducing chances for ORA-4031 to occur.

- or -

4). alter system set "_memory_broker_stat_interval"=999; --- 999sec between resizes

This will increase the time between resize to atleast 999 seconds.
This will reduce the number of resizes.

_memory_broker_stat_interval is in seconds,By Default it is 30 seconds.
You can set _memory_broker_stat_interval to a larger value


Thanks
Kevin

Hi,

We can disable ASSM by setting sga_target to zero. However that will need us
to manually give the values for:

Database buffer cache (Default pool)
Shared pool
Large pool
Java pool
10gR2 the streams pool

This is as per metalink note 257643.1 which says that the above are set auto by
automatic tuning.

However Metalink Note 216205.1 says that large_pool_size, java_pool_size are
obsolete. Should we set these parameters ?

- Vikram



04-JUN-09 21:02:45 GMT

.
UPDATE
=======


The java_pool_size, and large_pool_size are not obsolete, they are not set manaully when SGA_TARGET is set.

Both are listed in the 10.2. reference guide and are not flagged as obsoleted.


I would suggest it might be easier for you to set minimums fort the shared_pool_size and the db_buffer_cache and leave the SGA_TARGET set.

The choice of all the above solutions is of course up to you.


Thanks
Kevin

We decided to disable automatic SGA tuning and set sga_target=0 and manually set values for db_cache_size, shared_pool_size and java_pool_size.

Section 3.4 of Metalink Note 295626.1 has the details about disabling automatic SGA or ASMM:

3.4 ASMM to Manual

You can revert the ASMM mechanism at any time by setting the SGA_TARGET value to 0.

In this case the current_size will be used by default as shown below:

SQL> alter system set sga_target=0;

System altered.

SQL> select component, current_size/1024/1024 "CURRENT_SIZE", min_size/1024/1024 "MIN_SIZE",
user_specified_size/1024/1024 "USER_SPECIFIED_SIZE", last_oper_type "TYPE" from v$sga_dynamic_components;

COMPONENT CURRENT_SIZE MIN_SIZE USER_SPECIFIED_SIZE TYPE
------------------------- ------------ ---------- ------------------- -------------
shared pool 104 80 104 GROW <<<<< 104 has become the current value
large pool 8 8 8 STATIC
java pool 48 48 48 STATIC
streams pool 12 0 12 GROW
DEFAULT buffer cache 24 24 24 SHRINK
KEEP buffer cache 0 0 0 STATIC
RECYCLE buffer cache 0 0 0 STATIC
DEFAULT 2K buffer cache 0 0 0 STATIC
DEFAULT 4K buffer cache 0 0 0 STATIC
DEFAULT 8K buffer cache 0 0 0 STATIC
DEFAULT 16K buffer cache 0 0 0 STATIC
DEFAULT 32K buffer cache 0 0 0 STATIC
OSM Buffer Cache 0 0 24 STATIC

If you shutdown the instance now, you will record the CURRENT_SIZE value for all the parameters.

After the restart, the MIN_SIZE values are equal to CURRENT_SIZE.

SQL> select component, current_size/1024/1024 "CURRENT_SIZE",

min_size/1024/1024 "MIN_SIZE",
user_specified_size/1024/1024 "USER_SPECIFIED_SIZE",

last_oper_type "TYPE" from v$sga_dynamic_components;

COMPONENT CURRENT_SIZE MIN_SIZE USER_SPECIFIED_SIZE TYPE
------------------------- ------------ ---------- ------------------
shared pool 104 104 104 STATIC
large pool 8 8 8 STATIC
java pool 48 48 48 STATIC
streams pool 12 12 12 STATIC
DEFAULT buffer cache 24 24 24 STATIC
KEEP buffer cache 0 0 0 STATIC
RECYCLE buffer cache 0 0 0 STATIC
DEFAULT 2K buffer cache 0 0 0 STATIC
DEFAULT 4K buffer cache 0 0 0 STATIC
DEFAULT 8K buffer cache 0 0 0 STATIC
DEFAULT 16K buffer cache 0 0 0 STATIC
DEFAULT 32K buffer cache 0 0 0 STATIC
OSM Buffer Cache 0 0 24 STATIC

It is strongly advised to use an spfile with your your instance as you will record any changes applied.

The ORA-600, ORA-7445 errors have disappeared after disabling automatic SGA tuning.