OraFAQ Forum: Performance Tuning » Memory_target and sga

Home » RDBMS Server » Performance Tuning » Memory_target and sga_target in oracle 11gR2 (Linux, Oracle 11g, 11.2.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Memory_target and sga_target in oracle 11gR2 [message #573477]

Sat, 29 December 2012 06:41

suresh.wst
Messages: 53
Registered: June 2008
Location: Hyderabad

Member

Hi folks,

I am aware that from 11g, memory_target is sufficient for memeory management between SGA and PGA.

what happens if MEMORY_TARGET set to non-zero and SGA_TARGET set to zero values in a 11g database? Does it enable automatic memory management within the SGA?

We regularly hit by ORA-4031 errors. Also, memory_target advisory (v$memory_target_advice) does not show any advisory information.

for eg:

memory_max_target = 500m
memory_target = 500m

and

sga_max_size=500m
sga_target=0

Thanks in advance for the answer.

Thanks,
Suresh

Report message to a moderator

Re: Memory_target and sga_target in oracle 11gR2 [message #573485 is a reply to message #573477]

Sat, 29 December 2012 09:13

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

ORA-04031: unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")
 *Cause:  More shared memory is needed than was allocated in the shared
          pool.
 *Action: If the shared pool is out of memory, either use the
          DBMS_SHARED_POOL package to pin large packages,
          reduce your use of shared memory, or increase the amount of
          available shared memory by increasing the value of the
          initialization parameters SHARED_POOL_RESERVED_SIZE and
          SHARED_POOL_SIZE.
          If the large pool is out of memory, increase the initialization
          parameter LARGE_POOL_SIZE.

The answer depends on which area you lack space.

Regardds
Michel

[Updated on: Sat, 29 December 2012 09:13]

Report message to a moderator

Re: Memory_target and sga_target in oracle 11gR2 [message #573488 is a reply to message #573477]

Sat, 29 December 2012 11:15

John Watson
Messages: 8963
Registered: January 2010
Location: Global Village

Senior Member

What do you mean by this,Quote:

memory_target advisory (v$memory_target_advice) does not show any advisory information.

are you (for example) saying that the view has no rows?

I would raise the memory target. 500m is not very much.

Report message to a moderator

Re: Memory_target and sga_target in oracle 11gR2 [message #573527 is a reply to message #573488]

Sun, 30 December 2012 09:29

suresh.wst
Messages: 53
Registered: June 2008
Location: Hyderabad

Member

Hi John,

Yes. V$memory_target_advice does not show any rows.

Also database is not more than 30G.

Thanks,
Suresh

Report message to a moderator

Re: Memory_target and sga_target in oracle 11gR2 [message #573529 is a reply to message #573527]

Sun, 30 December 2012 09:33

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

show parameter statistics_level

And as I said:

Quote:

The answer depends on which area you lack space.

So the COMPLETE error message is mandatory.

Regards
Michel

Report message to a moderator

Re: Memory_target and sga_target in oracle 11gR2 [message #573531 is a reply to message #573527]

Sun, 30 December 2012 09:33

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

do as below


SQL> connect / as sysdba
Connected.
SQL> select * from v$memory_target_advice;

MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR    VERSION
----------- ------------------ ------------ ------------------- ----------
        327                .75         1873              1.0074          0
        436                  1         1859                   1          0
        545               1.25         1826               .9825          0
        654                1.5         1826               .9824          0
        763               1.75         1826               .9824          0
        872                  2         1826               .9824          0

6 rows selected.

Report message to a moderator

Re: Memory_target and sga_target in oracle 11gR2 [message #573888 is a reply to message #573531]

Thu, 03 January 2013 19:32

alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California

Senior Member

When Oracle does memory management, it hicups. I like to remove as much memory management as possible to avoid these types of hicups. For a 47 gigabyte database I use the following settings.

INSTANCE CURRENT_MEMORY_VALUE NAME
-------- -------------------- ------------------------------
CSCDAP1  0                    memory_max_target
CSCDAP1  0                    memory_target
CSCDAP1  27917287424          sga_target
CSCDAP1  32212254720          sga_max_size
CSCDAP1  4294967296           result_cache_max_size
CSCDAP1  5368709120           db_cache_size
CSCDAP1  6442450944           db_recycle_cache_size
CSCDAP1  6442450944           db_keep_cache_size
CSCDAP1  0                    shared_pool_size
CSCDAP1  524288000            shared_pool_reserved_size

Report message to a moderator

Previous Topic:	OCI client result cache
Next Topic:	SQL Plan Baseline Trouble!!!

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 18 00:46:56 CST 2025