Like to hear experiences with using sga_target in Oracle 10G.

We still get ora-4031 although using sga_target means that Oracle manages memory it self.
Looks like you still have to set shared_pool_size to a reasonable size.

Regards,
Han

Hi Han,

no Problems with the automatic memory management in all my Baan-Installations. (running on SLES8, SLES9 and W2003)
Are you shure that automatic memory management is enabled ?

greetz
martin

Hi Martin,

Yes I am sure.

I use sga_target to give oracle the memory and set shared_pool_size as well to make sure that Oracle reserve a minimum for it.

On my production server:
sga_target=1150M
shared_pool_size=500M

On test server (with less memory)
sga_target=500M
shared_pool_size=350M

Like to know what values you use.

Regards,
Han

Hi Han,

hier is an example from one of my DB (a very big one)


Automatic Shared Memory Management Aktiviert
Gesamte SGA-Größe (MB)


SGA-Komponente Aktuelle Zuweisung (MB)
Shared Pool 864
Buffer Cache 2208
Large Pool 16
Java Pool 16
Andere 48

And here a small one :

Automatic Shared Memory Management Aktiviert
Gesamte SGA-Größe (MB)



SGA-Komponente Aktuelle Zuweisung (MB)
Shared Pool 328
Buffer Cache 88
Large Pool 4
Java Pool 152
Andere 8




As you could see, the automatic shared memory management must be activated by Enterprise Mangager, an don't set any memory parameter as shared_pool_size ...
Oracle set these parameter automaticly.

martin

The SGA_TARGET is the maximum Oracle *tries* to run in. The maximum is set by SGA_MAX_SIZE. If the amount of memory is more than this, you get the error as you describe. The memory is needed can be effected by a lot of parameters like cursor_space_for_time and session_cached_cursors.

BTW: To enable Automatic Shared Memory Management, the STATISTICS_LEVEL parameter should not be BASIC. The default for STATISTICS_LEVEL is TYPICAL.

SQL> SELECT statistics_name, activation_level FROM v$statistics_level;

SQL> SELECT component, current_size FROM v$sga_dynamic_components;

Hope this helps,
Dick