Home » RDBMS Server » Performance Tuning » Memory_target and sga_target in oracle 11gR2 (Linux, Oracle 11g, 11.2.0)
Memory_target and sga_target in oracle 11gR2 [message #573477] Sat, 29 December 2012 06:41 Go to next message
suresh.wst
Messages: 45
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
Re: Memory_target and sga_target in oracle 11gR2 [message #573485 is a reply to message #573477] Sat, 29 December 2012 09:13 Go to previous messageGo to next message
Michel Cadot
Messages: 58899
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
John Watson
Messages: 4500
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.
Re: Memory_target and sga_target in oracle 11gR2 [message #573527 is a reply to message #573488] Sun, 30 December 2012 09:29 Go to previous messageGo to next message
suresh.wst
Messages: 45
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
Re: Memory_target and sga_target in oracle 11gR2 [message #573529 is a reply to message #573527] Sun, 30 December 2012 09:33 Go to previous messageGo to next message
Michel Cadot
Messages: 58899
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Memory_target and sga_target in oracle 11gR2 [message #573531 is a reply to message #573527] Sun, 30 December 2012 09:33 Go to previous messageGo to next message
BlackSwan
Messages: 22703
Registered: January 2009
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.


Re: Memory_target and sga_target in oracle 11gR2 [message #573888 is a reply to message #573531] Thu, 03 January 2013 19:32 Go to previous message
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 
Previous Topic: OCI client result cache
Next Topic: SQL Plan Baseline Trouble!!!
Goto Forum:
  


Current Time: Wed Aug 27 04:06:18 CDT 2014

Total time taken to generate the page: 0.10063 seconds