FAQ
Hi,



Does anyone know if there is any way to force ASMM memory component to
shrink. Eg. Instance has just been bounced and say shared pool is using 1GB
and db_cache is using only 100MB. If one wanted to forcibly shrink shared
pool without bouncing the instance, is there any way to do this?



Cheers,

Vishal

Search Discussions

  • Finn Jorgensen at Dec 1, 2007 at 10:01 pm
    alter system set shared_pool_size=500M scope=memory;
    alter system set db_cache_size=600M scope=memory;

    Finn
    On Dec 1, 2007 11:59 AM, Vishal Gupta wrote:

    Hi,



    Does anyone know if there is any way to force ASMM memory component to
    shrink. Eg. Instance has just been bounced and say shared pool is using 1GB
    and db_cache is using only 100MB. If one wanted to forcibly shrink shared
    pool without bouncing the instance, is there any way to do this?



    Cheers,

    Vishal
    --
    http://www.freelists.org/webpage/oracle-l
  • Yasin Baskan at Dec 1, 2007 at 11:24 pm
    That may not shrink the shared pool. That just sets a lower limit for
    shared pool, does not shrink it to that value immediately.



    http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/create.ht
    m#sthref391





    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Finn Jorgensen
    Sent: Sunday, December 02, 2007 12:02 AM
    To: vishal_at_vishalgupta.co.uk
    Cc: oracle-l_at_freelists.org
    Subject: Re: Forcing ASMM component to shrink



    alter system set shared_pool_size=500M scope=memory;

    alter system set db_cache_size=600M scope=memory;



    Finn

    On Dec 1, 2007 11:59 AM, Vishal Gupta wrote:

    Hi,



    Does anyone know if there is any way to force ASMM memory component to
    shrink. Eg. Instance has just been bounced and say shared pool is using
    1GB and db_cache is using only 100MB. If one wanted to forcibly shrink
    shared pool without bouncing the instance, is there any way to do this?



    Cheers,

    Vishal



    Bu mesaj ve onunla iletilen tum ekler gonderildigi kisi ya da kuruma ozel ve Bankalar Kanunu geregince, gizlilik yukumlulugu tasiyor olabilir. Bu mesaj, hicbir sekilde, herhangi bir amac icin cogaltilamaz, yayinlanamaz ve para karsiligi satilamaz; mesajin yetkili alicisi veya alicisina iletmekten sorumlu kisi degilseniz, mesaj icerigini ya da eklerini kopyalamayiniz, yayinlamayiniz, baska kisilere yonlendirmeyiniz ve mesaji gonderen kisiyi derhal uyararak bu mesaji siliniz. Bu mesajin iceriginde ya da eklerinde yer alan bilgilerin dogrulugu, butunlugu ve guncelligi Bankamiz tarafindan garanti edilmemektedir ve bilinen viruslere karsi kontrolleri yapilmis olarak yollanan mesajin sisteminizde yaratabilecegi zararlardan Bankamiz sorumlu tutulamaz.

    This message and the files attached to it are under the privacy liability in accordance with the Banking Law and confidential to the use of the individual or entity to whom they are addressed. This message cannot be copied, disclosed or sold monetary consideration for any purpose. If you are not the intended recipient of this message, you should not copy, distribute, disclose or forward the information that exists in the content and in the attachments of this message; please notify the sender immediately and delete all copies of this message. Our Bank does not warrant the accuracy, integrity and currency of the information transmitted with this message. This message has been detected for all known computer viruses thence our Bank is not liable for the occurrence of any system corruption caused by this message
  • Allan Nelson at Dec 2, 2007 at 2:16 am
    You will have to push some of the memory you took away from the shared pool
    to another memory pool. Shrinks don't do anything if the memory freed is
    not allocated elsewhere.

    Allan
    On Dec 1, 2007 10:59 AM, Vishal Gupta wrote:

    Hi,



    Does anyone know if there is any way to force ASMM memory component to
    shrink. Eg. Instance has just been bounced and say shared pool is using 1GB
    and db_cache is using only 100MB. If one wanted to forcibly shrink shared
    pool without bouncing the instance, is there any way to do this?



    Cheers,

    Vishal
    --
    http://www.freelists.org/webpage/oracle-l
  • Vishal Gupta at Dec 3, 2007 at 10:13 pm
    Sander,



    See reply of Yasin Baskan. He has answered my concern. That if free memory
    is not there ie. Its already allocated to some other component, then you
    will get following error.



    ORA-00384: Insufficient memory to grow cache





    From: Sander Westphal
    Sent: 02 December 2007 09:57
    Cc: vishal_at_vishalgupta.co.uk; oracle-l_at_freelists.org; anelson77388_at_gmail.com
    Subject: Re: Forcing ASMM component to shrink





    I do agree with Allan;

    So you can set the lower-limit of the cache size to a higher value if realy
    needed.

    The instance will use your this setting as the lower bound, or the smallest
    size that

    particular area may be.







    Cheers, Sander Westphal

    OCP 8i,9i, 10G

    and your Dutch colleague.



    On Dec 2, 2007 3:16 AM, Allan Nelson wrote:

    You will have to push some of the memory you took away from the shared pool
    to another memory pool. Shrinks don't do anything if the memory freed is
    not allocated elsewhere.

    Allan



    On Dec 1, 2007 10:59 AM, Vishal Gupta wrote:

    Hi,



    Does anyone know if there is any way to force ASMM memory component to
    shrink. Eg. Instance has just been bounced and say shared pool is using 1GB
    and db_cache is using only 100MB. If one wanted to forcibly shrink shared
    pool without bouncing the instance, is there any way to do this?



    Cheers,

    Vishal
  • Vishal Gupta at Dec 4, 2007 at 8:27 am
    I have found the way to forcibly shrink shared pool dynamically.
    Documentation stats that ASMM can only increase shared pool, it can not
    shrink it. To shrink shared_pool_size immediately, you have to switch to
    manual mode. After shrinking you can again switch back to ASMM mode.





    <http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/create.htm#s
    thref383>
    http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/create.htm#st
    href383

    Automatic Tuning and the Shared Pool

    When the automatic shared memory management feature is enabled, the internal
    tuning algorithm tries to determine an optimal size for the shared pool
    based on the workload. It usually converges on this value by increasing in
    small increments over time. However, the internal tuning algorithm typically
    does not attempt to shrink the shared pool, because the presence of open
    cursors, pinned PL/SQL packages, and other SQL execution state in the shared
    pool make it impossible to find granules that can be freed. Therefore, the
    tuning algorithm only tries to increase the shared pool in conservative
    increments, starting from a conservative size and stabilizing the shared
    pool at a size that produces the optimal performance benefit.

    Currently ASMM is enabled.

    SQL> alter system set sga_target = 300M;

    System altered.



    SQL> alter system set shared_pool_size = 0;

    System altered.



    SQL> alter system set db_cache_size = 0;

    System altered.



    SQL> show sga

    Total System Global Area 314572800 bytes

    Fixed Size 1261564 bytes

    Variable Size 222298116 bytes

    Database Buffers 88080384 bytes

    Redo Buffers 2932736 bytes

    Lets try to shink shared pool and increase db_cache_size



    SQL> alter system set shared_pool_size = 75M;

    System altered.



    SQL> alter system set db_cache_size = 200M;

    alter system set db_cache_size = 200M

    *

    ERROR at line 1:

    ORA-32017: failure in updating SPFILE

    ORA-00384: Insufficient memory to grow cache



    Now lets switch to manual mode temporarily



    SQL> alter system set sga_target = 0;

    System altered.



    SQL> alter system set shared_pool_size = 75M;

    System altered.



    SQL> alter system set db_cache_size = 200M;

    System altered.



    SQL> show sga

    Total System Global Area 314572800 bytes

    Fixed Size 1261564 bytes

    Variable Size 100663300 bytes

    Database Buffers 209715200 bytes

    Redo Buffers 2932736 bytes

    Voilla !!! we have successfully decreased shared pool and increased
    db_cache_size.



    Now let switch back to ASMM mode.



    SQL> alter system set sga_target = 300M;

    System altered.



    SQL> alter system set shared_pool_size = 0;

    System altered.



    SQL> alter system set db_cache_size = 0;

    System altered.



    SQL> show sga

    Total System Global Area 314572800 bytes

    Fixed Size 1261564 bytes

    Variable Size 92274692 bytes

    Database Buffers 218103808 bytes

    Redo Buffers 2932736 bytes

    Regards,

    Vishal Gupta
  • Tanel Poder at Dec 4, 2007 at 4:17 pm
    Note that Oracle 10.2 *does* shrink shared pool automatically if needed...
    it can even keep buffer cache buffers in shared pool starting from 10.2...


    http://www.orafaq.com/maillist/oracle-l/2006/08/22/0958.htm


    --
    Regards,
    Tanel Poder
    http://blog.tanelpoder.com <http://blog.tanelpoder.com/>



    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Vishal Gupta
    Sent: Tuesday, December 04, 2007 16:28
    To: oracle-l_at_freelists.org
    Subject: RE: Forcing ASMM component to shrink

    I have found the way to forcibly shrink shared pool dynamically.
    Documentation stats that ASMM can only increase shared pool, it can not
    shrink it. To shrink shared_pool_size immediately, you have to switch to
    manual mode. After shrinking you can again switch back to ASMM mode.





    <http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/create.htm#s
    thref383>
    http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/create.htm#st
    href383

    Automatic Tuning and the Shared Pool

    When the automatic shared memory management feature is enabled, the internal
    tuning algorithm tries to determine an optimal size for the shared pool
    based on the workload. It usually converges on this value by increasing in
    small increments over time. However, the internal tuning algorithm typically
    does not attempt to shrink the shared pool, because the presence of open
    cursors, pinned PL/SQL packages, and other SQL execution state in the shared
    pool make it impossible to find granules that can be freed. Therefore, the
    tuning algorithm only tries to increase the shared pool in conservative
    increments, starting from a conservative size and stabilizing the shared
    pool at a size that produces the optimal performance benefit.



    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedDec 1, '07 at 4:59p
activeDec 4, '07 at 4:17p
posts7
users5
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase