Grokbase › Groups › Oracle › oracle-l › August 2007
FAQ
Looking for ideas/comments/suggestions..........

We changed cursor_sharing from EXACT to SIMILAR last Saturday morning at
6am.
Did that via an "ALTER SESSION SET....." command.
The environment is large (7TB) but not very busy on Saturday morning.
We had cursor_sharing=similar in multiple test environments, for many
months without issue.

At 9:15am, we started to generated ora-04031 errors.
By 9:30am, we could no longer sign-on.
We ended up shutting down the middle-tier, and deleting all of the
OS processes on the DB server, that represented connections to the
instance.
At that point, we were able to signon, backout the change, and startup the
middle-tier again.

In the alert log, we saw the ora-04031 errors, and also many (thousands)
of messages saying
"PMON failed to acquire latch, see PMON dump".

The PMON trace file show the same entry, repeated thousands of times:
"PMON unable to acquire latch 2c4cb108 library cache
possible holder pid = 1000 ospid=42586708"

Any ideas on what happened would be appreciated.
Thanks.

Dan Hubler
Database Administrator
Aurora Healthcare
daniel.hubler_at_aurora.org

Search Discussions

  • Kurt Van Meerbeeck at Aug 7, 2007 at 3:01 pm ⇧
    What Oracle version - is this by any chance a 10.2.0.1 ?

    cheers,
    Kurt
    ----- Oorspronkelijk bericht -----
    Van: daniel.hubler_at_aurora.org
    Verzonden: dinsdag, augustus 7, 2007 04:12 PM
    Aan: oracle-l_at_freelists.org
    Onderwerp: Cursor_sharing=similar.....failed

    Looking for ideas/comments/suggestions..........

    We changed cursor_sharing from EXACT to SIMILAR last Saturday morning at 6am.
    Did that via an "ALTER SESSION SET....." command.
    The environment is large (7TB) but not very busy on Saturday morning.
    We had cursor_sharing=similar in multiple test environments, for many months without issue.

    At 9:15am, we started to generated ora-04031 errors.
    By 9:30am, we could no longer sign-on.
    We ended up shutting down the middle-tier, and deleting all of the
    OS processes on the DB server, that represented connections to the instance.
    At that point, we were able to signon, backout the change, and startup the middle-tier again.

    In the alert log, we saw the ora-04031 errors, and also many (thousands) of messages saying
    "PMON failed to acquire latch, see PMON dump".

    The PMON trace file show the same entry, repeated thousands of times:
    "PMON unable to acquire latch 2c4cb108 library cache
    possible holder pid = 1000 ospid=42586708"

    Any ideas on what happened would be appreciated.
    Thanks.

    Dan Hubler
    Database Administrator
    Aurora Healthcare
    daniel.hubler_at_aurora.org
  • Daniel.hubler_at_aurora.org at Aug 7, 2007 at 3:05 pm ⇧
    Sorry......should have put that in...........

    This is Oracle v9.2.0.5 running on OpenVMS v7.3-2

    Dan Hubler
    Database Administrator
    Aurora Healthcare
    Cerner Support Team
    414-647-3257
    daniel.hubler_at_aurora.org

    "Kurt Van Meerbeeck"
    08/07/2007 10:00 AM
    Please respond to
    kurtvm_at_pandora.be

    To
    daniel.hubler_at_aurora.org, oracle-l_at_freelists.org
    cc

    Subject
    Re: Cursor_sharing=similar.....failed

    What Oracle version - is this by any chance a 10.2.0.1 ?


    cheers,
    Kurt
    ----- Oorspronkelijk bericht -----
    Van: daniel.hubler_at_aurora.org
    Verzonden: dinsdag, augustus 7, 2007 04:12 PM
    Aan: oracle-l_at_freelists.org
    Onderwerp: Cursor_sharing=similar.....failed

    Looking for ideas/comments/suggestions..........

    We changed cursor_sharing from EXACT to SIMILAR last Saturday morning at
    6am.
    Did that via an "ALTER SESSION SET....." command.
    The environment is large (7TB) but not very busy on Saturday morning.
    We had cursor_sharing=similar in multiple test environments, for many
    months without issue.

    At 9:15am, we started to generated ora-04031 errors.
    By 9:30am, we could no longer sign-on.
    We ended up shutting down the middle-tier, and deleting all of the
    OS processes on the DB server, that represented connections to the
    instance.
    At that point, we were able to signon, backout the change, and startup the
    middle-tier again.

    In the alert log, we saw the ora-04031 errors, and also many (thousands)
    of messages saying
    "PMON failed to acquire latch, see PMON dump".

    The PMON trace file show the same entry, repeated thousands of times:
    "PMON unable to acquire latch 2c4cb108 library cache
    possible holder pid = 1000 ospid=42586708"

    Any ideas on what happened would be appreciated.
    Thanks.

    Dan Hubler
    Database Administrator
    Aurora Healthcare
    daniel.hubler_at_aurora.org
  • Kerber, Andrew W. at Aug 7, 2007 at 3:51 pm ⇧
    I cant tell you exactly what your issue is, but I know for a fact that
    cursor_sharing= similar or cursor_share=force both have serious issues
    in oltp systems. I suspect when a sql command wants to use the same
    cursor, it tries to use the same lock. Did Oracle advise you to
    implement cursor sharing? It can work wonders on a purely reporting
    system, but implementing it in a cerner environment strikes me as a poor
    idea.



    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of
    daniel.hubler_at_aurora.org
    Sent: Tuesday, August 07, 2007 10:06 AM
    To: kurtvm_at_pandora.be
    Cc: oracle-l_at_freelists.org
    Subject: Re: Cursor_sharing=similar.....failed



    Sorry......should have put that in...........

    This is Oracle v9.2.0.5 running on OpenVMS v7.3-2

    Dan Hubler
    Database Administrator
    Aurora Healthcare
    Cerner Support Team
    414-647-3257
    daniel.hubler_at_aurora.org

    "Kurt Van Meerbeeck"

    08/07/2007 10:00 AM

    Please respond to
    kurtvm_at_pandora.be

    To

    daniel.hubler_at_aurora.org, oracle-l_at_freelists.org

    cc



    Subject

    Re: Cursor_sharing=similar.....failed







    What Oracle version - is this by any chance a 10.2.0.1 ?


    cheers,
    Kurt
    ----- Oorspronkelijk bericht -----
    Van: daniel.hubler_at_aurora.org
    Verzonden: dinsdag, augustus 7, 2007 04:12 PM
    Aan: oracle-l_at_freelists.org
    Onderwerp: Cursor_sharing=similar.....failed

    Looking for ideas/comments/suggestions..........

    We changed cursor_sharing from EXACT to SIMILAR last Saturday morning at
    6am.
    Did that via an "ALTER SESSION SET....." command.
    The environment is large (7TB) but not very busy on Saturday morning.
    We had cursor_sharing=similar in multiple test environments, for many
    months without issue.

    At 9:15am, we started to generated ora-04031 errors.
    By 9:30am, we could no longer sign-on.
    We ended up shutting down the middle-tier, and deleting all of the
    OS processes on the DB server, that represented connections to the
    instance.
    At that point, we were able to signon, backout the change, and startup
    the middle-tier again.

    In the alert log, we saw the ora-04031 errors, and also many (thousands)
    of messages saying
    "PMON failed to acquire latch, see PMON dump".

    The PMON trace file show the same entry, repeated thousands of times:
    "PMON unable to acquire latch 2c4cb108 library cache
    possible holder pid = 1000 ospid=42586708"

    Any ideas on what happened would be appreciated.
    Thanks.

    Dan Hubler
    Database Administrator
    Aurora Healthcare
    daniel.hubler_at_aurora.org

    NOTICE: This electronic mail message and any attached files are confidential. The information is exclusively for the use of the individual or entity intended as the recipient. If you are not the intended recipient, any use, copying, printing, reviewing, retention, disclosure, distribution or forwarding of the message or any attached file is not authorized and is strictly prohibited. If you have received this electronic mail message in error, please advise the sender by reply electronic mail immediately and permanently delete the original transmission, any attachments and any copies of this message from your computer system. Thank you.

    --
    http://www.freelists.org/webpage/oracle-l
  • Bryan Michael Lenihan at Aug 7, 2007 at 4:10 pm ⇧
    Daniel,



    I do not know if this what you are encountering, but by the nature of the
    CURSOR_SHARING=SIMULAR several child parses are created based on the literal
    values. There are several metalink and blog messages out there about the
    treeing effect when using CURSOR_SHARING as SIMULAR. We are running
    10.2.0.3 and we had to apply a couple of patches for SIMULAR and FORCED.



    From what I understand - SIMULAR checks the literal values and see if the
    plan changes based on their values. If it does, then it stores the plan as
    a child plan and creates a small tree in the shared pool. If the statement
    is run enough the treeing effect can take up a large amount of memory. We
    actually saw this pattern as you are describing. I felt it was because of
    the traffic - test usually does not have the user load as a production
    database.



    Thanks,

    Bryan

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of daniel.hubler_at_aurora.org
    Sent: Tuesday, August 07, 2007 10:13 AM
    To: oracle-l_at_freelists.org
    Subject: Cursor_sharing=similar.....failed



    Looking for ideas/comments/suggestions..........

    We changed cursor_sharing from EXACT to SIMILAR last Saturday morning at
    6am.
    Did that via an "ALTER SESSION SET....." command.
    The environment is large (7TB) but not very busy on Saturday morning.
    We had cursor_sharing=similar in multiple test environments, for many months
    without issue.

    At 9:15am, we started to generated ora-04031 errors.
    By 9:30am, we could no longer sign-on.
    We ended up shutting down the middle-tier, and deleting all of the
    OS processes on the DB server, that represented connections to the instance.

    At that point, we were able to signon, backout the change, and startup the
    middle-tier again.

    In the alert log, we saw the ora-04031 errors, and also many (thousands) of
    messages saying
    "PMON failed to acquire latch, see PMON dump".

    The PMON trace file show the same entry, repeated thousands of times:
    "PMON unable to acquire latch 2c4cb108 library cache
    possible holder pid = 1000 ospid=42586708"

    Any ideas on what happened would be appreciated.
    Thanks.

    Dan Hubler
    Database Administrator
    Aurora Healthcare
    daniel.hubler_at_aurora.org
  • David Barbour at Aug 7, 2007 at 5:47 pm ⇧
    Daniel,

    Not sure what platform or version of Oracle you're on, but I did run into
    something similar on AIX with Oracle v9.2.0.5. An overview is in
    MetalinkNote #3639594.8. It's a bug. Check the note to see if your
    platform.version fits for this issue. Not sure if there's a patch for this
    - we were in the process of upgrading to 9.2.0.7 and the problem disappered.
    *

    "*

    When PMON cleans up a dead process then it may notice an
    in-progress "ALTER SYSTEM SET PARAMETER" command and assume
    (incorrectly) that this was from the failed process. It may
    then try to clean up the in-progress command and so conflict
    with the running session performing the ALTER SYSTEM command.

    This shows up as numerous "PMON failed to acquire latch, see PMON dump"
    messages."
    On 8/7/07, daniel.hubler_at_aurora.org wrote:


    Looking for ideas/comments/suggestions..........

    We changed cursor_sharing from EXACT to SIMILAR last Saturday morning at
    6am.
    Did that via an "ALTER SESSION SET....." command.
    The environment is large (7TB) but not very busy on Saturday morning.
    We had cursor_sharing=similar in multiple test environments, for many
    months without issue.

    At 9:15am, we started to generated ora-04031 errors.
    By 9:30am, we could no longer sign-on.
    We ended up shutting down the middle-tier, and deleting all of the
    OS processes on the DB server, that represented connections to the
    instance.
    At that point, we were able to signon, backout the change, and startup the
    middle-tier again.

    In the alert log, we saw the ora-04031 errors, and also many (thousands)
    of messages saying
    "PMON failed to acquire latch, see PMON dump".

    The PMON trace file show the same entry, repeated thousands of times:
    "PMON unable to acquire latch 2c4cb108 library cache
    possible holder pid = 1000 ospid=42586708"


    Any ideas on what happened would be appreciated.
    Thanks.




    Dan Hubler
    Database Administrator
    Aurora Healthcare
    daniel.hubler_at_aurora.org
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedAug 7, '07 at 2:12p
activeAug 7, '07 at 5:47p
posts6
users5
websiteoracle.com

5 users in discussion

Daniel.hubler_at_aurora.org: 2 posts Kurt Van Meerbeeck: 1 post David Barbour: 1 post Kerber, Andrew W.: 1 post Bryan Michael Lenihan: 1 post

Content

People

Support

Translate

site design / logo © 2023 Grokbase