FAQ
Hi everybody:

We have a HP-UX Itanium Server with 28Gb of memory, 4 cores, Oracle
11.1.0.7. it often hangs waiting for flushing shared pool, manually or
automatically. Memory Target is 18Gb.

the ASMM set the following parameters

__db_cache_size=5368709120
__java_pool_size=67108864
__large_pool_size=67108864
__oracle_base='/prod01/app/oracle'#ORACLE_BASE set from environment
__pga_aggregate_target=6710886400
__sga_target=12616466432
__shared_io_pool_size=0
__shared_pool_size=6912212992
__streams_pool_size=67108864

when the shared pool is full, sometimes we get that most processes wait for
library cache: mutex X. It takes up to 2 hours to flush the shared pool and
sometimes we have to kill the smon because the instances doesn't accept any
conection to shutdown the instance.

Has anybody had issues with flushing the shared pool?

Search Discussions

  • Mathias Magnusson at Apr 24, 2009 at 3:55 pm
    Surely, flushing the shared pool is far from being a best practice. I don't
    think that is the solution, as it is probably not solving the real problem

    Isn't the size of SGA contributing? Would it not be that as you have
    increased it to be this beg more things are kept there and as a result more
    CPU can be spent on spinning for a latch?

    There seems to have been a few issues with this in 11G, but it also seems
    that they were *supposed* to have ben fixed in 11g. It may be worthwhile
    getting Oracle support to tell you if you are running into a bug.

    If it's not a bug, then tracking the actual issue may help more than
    figuring out issues with flushing the shared pool unless that is the actual
    thing you ned to make work. It sounds as if you are rather trying to fix
    issues with a workaround you're using.

    Mathias

    2009/4/24 Alfonso León
    Hi everybody:

    We have a HP-UX Itanium Server with 28Gb of memory, 4 cores, Oracle
    11.1.0.7. it often hangs waiting for flushing shared pool, manually or
    automatically. Memory Target is 18Gb.

    the ASMM set the following parameters

    __db_cache_size=5368709120
    __java_pool_size=67108864
    __large_pool_size=67108864
    __oracle_base='/prod01/app/oracle'#ORACLE_BASE set from environment
    __pga_aggregate_target=6710886400
    __sga_target=12616466432
    __shared_io_pool_size=0
    __shared_pool_size=6912212992
    __streams_pool_size=67108864

    when the shared pool is full, sometimes we get that most processes wait for
    library cache: mutex X. It takes up to 2 hours to flush the shared pool and
    sometimes we have to kill the smon because the instances doesn't accept any
    conection to shutdown the instance.

    Has anybody had issues with flushing the shared pool?




    --
    Alfonso Leon
    --
    http://www.freelists.org/webpage/oracle-l
  • John Hallas at Apr 24, 2009 at 6:39 pm
    Is CPU a problem at other times, are you hitting high CPU usage most of the time or is it just when the shared_pool is getting fragmented?
    What is the total memory on the server

    Have you checked the number of loads and executions in the shared_pool to see if pinning packages can help

    Whilst this is only a stab in the dark, page target seems high relative to cache and shared_pool

    John

    From: oracle-l-bounce_at_freelists.org On Behalf Of Mathias Magnusson
    Sent: 24 April 2009 16:56
    To: aleon68_at_gmail.com
    Cc: oracle-l
    Subject: Re: Problem flushing shared pool on 11.1.0.7 HP-UX Itanium

    Surely, flushing the shared pool is far from being a best practice. I don't think that is the solution, as it is probably not solving the real problem

    Isn't the size of SGA contributing? Would it not be that as you have increased it to be this beg more things are kept there and as a result more CPU can be spent on spinning for a latch?

    There seems to have been a few issues with this in 11G, but it also seems that they were *supposed* to have ben fixed in 11g. It may be worthwhile getting Oracle support to tell you if you are running into a bug.

    If it's not a bug, then tracking the actual issue may help more than figuring out issues with flushing the shared pool unless that is the actual thing you ned to make work. It sounds as if you are rather trying to fix issues with a workaround you're using.

    Mathias
    2009/4/24 Alfonso León >
    Hi everybody:

    We have a HP-UX Itanium Server with 28Gb of memory, 4 cores, Oracle 11.1.0.7. it often hangs waiting for flushing shared pool, manually or automatically. Memory Target is 18Gb.

    the ASMM set the following parameters

    __db_cache_size=5368709120
    __java_pool_size=67108864
    __large_pool_size=67108864
    __oracle_base='/prod01/app/oracle'#ORACLE_BASE set from environment
    __pga_aggregate_target=6710886400
    __sga_target=12616466432
    __shared_io_pool_size=0
    __shared_pool_size=6912212992
    __streams_pool_size=67108864

    when the shared pool is full, sometimes we get that most processes wait for library cache: mutex X. It takes up to 2 hours to flush the shared pool and sometimes we have to kill the smon because the instances doesn't accept any conection to shutdown the instance.

    Has anybody had issues with flushing the shared pool?

    --
    Alfonso Leon

    ______________________________________________________________________
    Wm Morrison Supermarkets Plc is registered in England with number 358949. The registered office of the company is situated at Gain Lane, Bradford, West Yorkshire BD3 7DL. This email and any attachments are intended for the addressee(s) only and may be confidential.

    If you are not the intended recipient, please inform the sender by replying to the email that you have received in error and then destroy the email.
    If you are not the intended recipient, you must not use, disclose, copy or rely on the email or its attachments in any way.

    Wm Morrison Supermarkets PLC accepts no liability or responsibility for anything said in the email or its attachments and gives no warranty as to accuracy. It is the policy of Wm Morrison Supermarkets PLC not to enter into any contractual or other obligations by email.

    Although we have taken steps to ensure the email and its attachments are virus-free, we cannot guarantee this or accept any responsibility,
    and it is the responsibility of recipients to carry out their own virus checks.
    ______________________________________________________________________
    --
    http://www.freelists.org/webpage/oracle-l
  • Alfonso León at May 4, 2009 at 4:30 pm
    Hello Every One. thanks for your concert, but I gave up I used the
    parameter CURSOR_SHARING=SIMILAR. and the comsumption of shared pool went
    down to less than 1Gb and don't raise over that limit. I know if the
    implication on the execution plan and the issue would reapears if the shared
    pool goes up again. But the problem was very serious, the system hanging
    every 3-4 hours is not a nice enviroment.

    Again thanks for your help.
    On Fri, Apr 24, 2009 at 1:39 PM, John Hallas wrote:

    Is CPU a problem at other times, are you hitting high CPU usage most of
    the time or is it just when the shared_pool is getting fragmented?

    What is the total memory on the server



    Have you checked the number of loads and executions in the shared_pool to
    see if pinning packages can help



    Whilst this is only a stab in the dark, page target seems high relative to
    cache and shared_pool



    John


    ------------------------------

    *From:* oracle-l-bounce_at_freelists.org [mailto:
    oracle-l-bounce@freelists.org] *On Behalf Of *Mathias Magnusson
    *Sent:* 24 April 2009 16:56
    *To:* aleon68_at_gmail.com
    *Cc:* oracle-l
    *Subject:* Re: Problem flushing shared pool on 11.1.0.7 HP-UX Itanium



    Surely, flushing the shared pool is far from being a best practice. I don't
    think that is the solution, as it is probably not solving the real problem



    Isn't the size of SGA contributing? Would it not be that as you have
    increased it to be this beg more things are kept there and as a result more
    CPU can be spent on spinning for a latch?



    There seems to have been a few issues with this in 11G, but it also seems
    that they were *supposed* to have ben fixed in 11g. It may be worthwhile
    getting Oracle support to tell you if you are running into a bug.



    If it's not a bug, then tracking the actual issue may help more than
    figuring out issues with flushing the shared pool unless that is the actual
    thing you ned to make work. It sounds as if you are rather trying to fix
    issues with a workaround you're using.


    Mathias

    2009/4/24 Alfonso León

    Hi everybody:

    We have a HP-UX Itanium Server with 28Gb of memory, 4 cores, Oracle
    11.1.0.7. it often hangs waiting for flushing shared pool, manually or
    automatically. Memory Target is 18Gb.

    the ASMM set the following parameters

    __db_cache_size=5368709120
    __java_pool_size=67108864
    __large_pool_size=67108864
    __oracle_base='/prod01/app/oracle'#ORACLE_BASE set from environment
    __pga_aggregate_target=6710886400
    __sga_target=12616466432
    __shared_io_pool_size=0
    __shared_pool_size=6912212992
    __streams_pool_size=67108864

    when the shared pool is full, sometimes we get that most processes wait for
    library cache: mutex X. It takes up to 2 hours to flush the shared pool and
    sometimes we have to kill the smon because the instances doesn't accept any
    conection to shutdown the instance.

    Has anybody had issues with flushing the shared pool?




    --
    Alfonso Leon



    ______________________________________________________________________
    Wm Morrison Supermarkets Plc is registered in England with number 358949.
    The registered office of the company is situated at Gain Lane, Bradford,
    West Yorkshire BD3 7DL. This email and any attachments are intended for the
    addressee(s) only and may be confidential.

    If you are not the intended recipient, please inform the sender by replying
    to the email that you have received in error and then destroy the email.
    If you are not the intended recipient, you must not use, disclose, copy or
    rely on the email or its attachments in any way.

    Wm Morrison Supermarkets PLC accepts no liability or responsibility for
    anything said in the email or its attachments and gives no warranty as to
    accuracy. It is the policy of Wm Morrison Supermarkets PLC not to enter into
    any contractual or other obligations by email.

    Although we have taken steps to ensure the email and its attachments are
    virus-free, we cannot guarantee this or accept any responsibility,
    and it is the responsibility of recipients to carry out their own virus
    checks.
    ______________________________________________________________________
    --
    Alfonso Leon

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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedApr 24, '09 at 1:56p
activeMay 4, '09 at 4:30p
posts4
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase