FAQ
How does ASMM handle a system with high volumes of non-shareable SQL? When
manually sizing the shared pool, we have always been reminded that a large
shared pool size could lead to serious performance problems strictly because
of the size of the pool. Assuming you have this situation in an ASMM
environment, how does Oracle handle it? Does the shared pool grow so large
that the other auto-sized caches are affected? Do the advisors recommend
increasing the sga_target to accommodate a huge shared pool and if so, what
are the performance ramifications? Are they similar to those encountered
with large shared pools in a manually sized environment?



I realize that one of the answers is to fix the app. However, I'm trying to
understand how ASMM would handle this situation in case it isn't a good
solution for some applications.



Thanks



Susan White



This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email

Search Discussions

  • Tanel Poder at Aug 19, 2006 at 4:23 am
    I just saw an issue few days ago (10.1.0.3 on SPARC) where shared_pool took
    8000MB of 8GB SGA_TARGET, leaving buffer cache to 96MB for this data
    warehouse.


    Eventually the instance crashed due some failed granule allocation operation
    (trying to get even more memory for shared pool). And that was so even
    though SGA advisories "advised" to increase buffer cache instead and
    decrease shared pool.


    SGA_TARGET is useful in cases:
    1) small to medium database, DBA doesn't want to waste 2 minutes to
    configure those parameters manually (once)
    2) databases with fluctuating workload (1 large batch at night, 10000 OLTP
    users during day) under physical memory pressure.

    Then it's good to have ASMM juggling around with memory to get better
    performance for different workloads.


    If you don't have memory pressure - you don't need ASMM. Especially if it's
    causing you problems on its own.


    Tanel.


    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Susan White
    Sent: Saturday, August 19, 2006 04:53
    To: 'Oracle-L_at_freelists.org'
    Subject: ASMM and poor SQL caching

    How does ASMM handle a system with high volumes of non-shareable SQL? When
    manually sizing the shared pool, we have always been reminded that a large
    shared pool size could lead to serious performance problems strictly because
    of the size of the pool. Assuming you have this situation in an ASMM
    environment, how does Oracle handle it? Does the shared pool grow so large
    that the other auto-sized caches are affected? Do the advisors recommend
    increasing the sga_target to accommodate a huge shared pool and if so, what
    are the performance ramifications? Are they similar to those encountered
    with large shared pools in a manually sized environment?



    I realize that one of the answers is to fix the app. However, I'm trying to
    understand how ASMM would handle this situation in case it isn't a good
    solution for some applications.



    Thanks



    Susan White



    This email has been scanned by the MessageLabs Email Security System.
    For more information please visit http://www.messagelabs.com/email
  • Fairlie rego at Aug 19, 2006 at 9:21 am
    Hi Susan,

      We have had issues when using ASMM that the library cache keeps on growing and we have to flush the shared pool every 10 days. This is because of non shareable SQL where cursors do not get shared because of language mismatch between the client and the server.
      Regards,
    -Fairlie

    Susan White wrote:

    How does ASMM handle a system with high volumes of non-shareable SQL? When manually sizing the shared pool, we have always been reminded that a large shared pool size could lead to serious performance problems strictly because of the size of the pool. Assuming you have this situation in an ASMM environment, how does Oracle handle it? Does the shared pool grow so large that the other auto-sized caches are affected? Do the advisors recommend increasing the sga_target to accommodate a huge shared pool and if so, what are the performance ramifications? Are they similar to those encountered with large shared pools in a manually sized environment?

      I realize that one of the answers is to fix the app. However, I’m trying to understand how ASMM would handle this situation in case it isn’t a good solution for some applications.

      Thanks

      Susan White

    This email has been scanned by the MessageLabs Email Security System.
    For more information please visit http://www.messagelabs.com/email

              Fairlie Rego
    Senior Oracle Consultant

      http://el-caro.blogspot.com/
      M: +61 406 537 397

    Get your email and more, right on the new Yahoo.com
  • Charles Schultz at Aug 19, 2006 at 1:15 pm
    I would have to weigh in with Tanel and Fairlie on this, although our story
    is more sodden with bugs.
    http://www.freelists.org/archives/oracle-l/05-2006/msg00172.html

    Like Tanel said for smaller databases (his point #1), ASMM should be fine -
    not really necessary per se, but it will do the job. I disagree about point
    #2, in light of my previous note, but I can accept that there might be
    situations were ASMM would be appropriate, as long as you are aware of the
    bugs and limitations of the feature.

    Even under 10.2.0.2, we have sga_target = 0 - we were scarred (and scared)
    pretty good under 10.2.0.1.
    On 8/18/06, Susan White wrote:

    How does ASMM handle a system with high volumes of non-shareable SQL?
    When manually sizing the shared pool, we have always been reminded that a
    large shared pool size could lead to serious performance problems strictly
    because of the size of the pool. Assuming you have this situation in an
    ASMM environment, how does Oracle handle it? Does the shared pool grow so
    large that the other auto-sized caches are affected? Do the advisors
    recommend increasing the sga_target to accommodate a huge shared pool and if
    so, what are the performance ramifications? Are they similar to those
    encountered with large shared pools in a manually sized environment?



    I realize that one of the answers is to fix the app. However, I'm trying
    to understand how ASMM would handle this situation in case it isn't a good
    solution for some applications.



    Thanks



    Susan White



    ______________________________________________________________________
    This email has been scanned by the MessageLabs Email Security System.
    For more information please visit http://www.messagelabs.com/email
    ______________________________________________________________________
    --
    Charles Schultz

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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedAug 18, '06 at 8:53p
activeAug 19, '06 at 1:15p
posts4
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase