Re: Cursor_sharing=similar.....failed

  • From: "David Barbour" <david.barbour1@xxxxxxxxx>
  • To: daniel.hubler@xxxxxxxxxx
  • Date: Tue, 7 Aug 2007 13:47:30 -0400


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 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"

On 8/7/07, daniel.hubler@xxxxxxxxxx <daniel.hubler@xxxxxxxxxx> 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@xxxxxxxxxx

Other related posts: