high "library cache: mutex X" contention

From: Eagle Fan <eagle.f_at_gmail.com>
Date: Tue, 31 Jul 2012 12:09:12 +0800
Message-ID: <CANHUaOpM4eWdDB31sPXWoQeHT8nZoDK7Fx4ago4AzxpKUX7-=A_at_mail.gmail.com>

Hi:
Database version is 11.2.0.3.0. It's using shared server mode.

There were a lot of sessions waiting on "library cache: mutex X" wait events.

The contention object is "SET TRANSACTION READ WRITE" which is coming from a PL/SQL.

I used Andrey's script to get some information as attached.

BLKS is the blocking session sid and RFC is the reference count. The BLKS is different, my understanding is v$session is not a consistent view, so the blocking session was changed when I was querying the view.

We have similar databases running on 11203 and 10204. The issue doesn't happen in 10204 version. We saw there were a lot of opened cursors in 11203 version.

11.2.0.3:

SQL> select sql_text,count(*) from v$open_cursor where hash_value=2672764718 group by sql_text;

SQL_TEXT                                                       COUNT(*)

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

SET TRANSACTION READ WRITE                                        42108

10.2.0.3:

SQL> select sql_text,count(*) from v$open_cursor where hash_value=2672764718 group by sql_text;

SQL_TEXT                                                       COUNT(*)

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

SET TRANSACTION READ WRITE                                          127

Oracle support's explanation was:

*it does seem you have a recursive call for this statement*

• *

*this does seem to be closer to what is described in bug - 9406631*

• * The issue in this bug involves true contention on an object,i.e we have the routine recursively calling itself and opening a new cursor, even while previously opened cursors for the same SQL are still open. Each new cursor for an existing SQL will require us to take a new library cache lock on the child cursor handle and this requires that handle's mutex in X mode

There are 3 bugs referenced in bug 9406631

Bug 9239863 , bug 9282521 and Bug 10411618 are the core bug fixes

out of which 2 are fixed in 12.1 and one in 11.2

You can consider applying one or both the fixes.

Also, if its possible to consider not opening the same cursor repeatedly on the same SQL , that will help

But all of these 3 fixes don't fix the open cursor problem, they just create multiple copies of hot library cache objects and introduces new mutex waiting scheme.

The root cause of opening cursor is not fixed.

Do you have experience on this issue?

Thanks very much in advance for any input.

--
Eagle Fan (www.dbafan.com)



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

Received on Mon Jul 30 2012 - 23:09:12 CDT