Troubleshoot Oracle Event : Library Cache: mutex X & High SQL version counts

-- Resolving Oracle Event : Library cache: mutex X
-- Resolving high version count ( bug in Oracle 11.2.0.1 / 11.2.0.2.0 - in RAC)

-- To find if high version count observed or not
Steps:
1) Take an AWR report
2) Go to : Main report --> SQL Statistics --> SQL ordered by Version Count
3) If counts are withing duble digit, then ok. If it is observed any kind of triple digit or more than 100, i.e., you are facing high version count for the queries.

-- What is SQL version count in Oracle?
when you issue a SQL statement, the database searches the library cache to find a cursor with matching SQL text. Then it can happen that even though the text matches, there are some other differences that prevent you from using existing cursor (e.g. different optimizer settings, different NLS settings, different permissions etc.). In such cases, a new child cursor is created. So basically child cursors are different versions of the same SQL statement.

If you have SQL statements with thousands of versions, this could mean a problem for your shared pool (child cursors taking up lots of space and causing fragmentation), as well as a potential for performance problems due to plan instability (if the same SQL text is parsed to a new plan every time, sooner or later it will be a bad plan). That's why AWR report has this list.

According to Oracle support, up to a couple of hundreds versions doesn't indicate a problem (cursor sharing mechanism isn't perfect), but when you have thousands or tens of thousands of versions, you should check your cursor sharing settings (first of all, CURSOR_SHARING parameter).

Click here to read More from Oracle Blog

Sample AWR report snap-shot when high version count observed:

-- Techical desrciption

According to notes 9282521.8 and 9239863.8 describing the patches, the enhancements should be used:
When there is true contention on a specific library cache object….

For example:- A package that is so hot (heavily accessed ) in library cache will be contended and the sessions appear to be waited on Library Cache: mutex X.

There are many bugs and cases appeared in metalink with mutexes where in the below case is just a one of them.

Disclaimer:- Do not test in production

The below script is just calling dbms_application_info package and when executed concurrently in many sessions it may cause the contention on library cache.

declare
i number;
begin
for i in 1..1000000
loop
   execute immediate ‘begin dbms_application_info.set_client_info(”mutex”);end;’;
end loop;
end;
/

As the sessions running, generate a awr report and you can see the Wait event library cache: mutex X in concurrency class.

So this is evident that you are having latch(mutex) issue.

How to overcome this.?

Oracle gives the ability to create a multiple clones of the hot objects in library cache and the sessions will access/use them individually rather contending for one.

Please note, its not pin (pin in the library cache), its marking the library cache object as hot to allow oracle to create multiple copies of the same.

Solution 1: Prior to 11gR2

a) Parameter "_kgl_hot_object_copies" controls the maximum number of copies.

b) Complementary parameter _kgl_debug marks hot library cache objects as a candidate for cloning.

Syntax of this parameter can be found in MOS descriptions of bugs 9684368, 11775293 and others. One form of such marking is

"_kgl_debug"="name=’schema=’ namespace= debug=33554432?

With our example the syntax would be,

SQL> alter system set "_kgl_debug"="name='DBMS_APPLICATION_INFO' schema='SYS' namespace=1 debug=33554432?, "name='DBMS_APPLICATION_INFO' schema='SYS' namespace=2 debug=33554432' scope=spfile;

SQL> alter system set "_kgl_hot_object_copies"= 255 scope=spfile;

Solution 2: 11gr2 onwards

dbms_shared_pool.markhot(
schema IN VARCHAR2,
objname IN VARCHAR2,
namespace IN NUMBER DEFAULT 1, — library cache namespace to search
global IN BOOLEAN DEFAULT TRUE); — If TRUE mark hot on all RAC instances

or

dbms_shared_pool.markhot(
hash IN VARCHAR2, — 16-byte hash value for the object
namespace IN NUMBER DEFAULT 1,
global IN BOOLEAN DEFAULT TRUE);

exec dbms_shared_pool.markhot(‘SYS’,’DBMS_APPLICATION_INFO’,1);
exec dbms_shared_pool.markhot(‘SYS’,’DBMS_APPLICATION_INFO’,2);
exec dbms_shared_pool.markhot(hash=>3222383532,NAMESPACE=>0);

The namespace can be found with the following query (Andrey.Nikolaev blog)

col name format a20
col cursor format a12 noprint
col type format a7
col LOCKED_TOTAL heading Locked format 99999
col PINNED_TOTAL heading Pinned format 99999999
col EXECUTIONS heading Executed format 99999999
col NAMESPACE heading Nsp format 999
set wrap on
set linesize 80
select *
  from (select case
                 when (kglhdadr = kglhdpar) then
                  'Parent'
                 else
                  'Child ' || kglobt09
               end cursor,
               kglhdadr ADDRESS,
               substr(kglnaobj, 1, 20) name,
               kglnahsh hash_value,
               kglobtyd type,
               kglobt23 LOCKED_TOTAL,
               kglobt24 PINNED_TOTAL,
               kglhdexc EXECUTIONS,
               kglhdnsp NAMESPACE
          from x$kglob
         order by kglobt24 desc)
 where rownum <= 10;

--- Found version count history

--set pages 2000 lines 100
SELECT b.*
FROM v$sqlarea a ,
TABLE(version_rpt(a.sql_id)) b
WHERE loaded_versions >=100;

-- Most effective and easy solution
Note : Bug fixed in Oracle 11.2.0.3

-- Check the parameter

SQL> show parameter optimizer_secure_view_merging;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_secure_view_merging boolean     TRUE


-- If TRUE, then chnage the below paramter:

sql> alter system set optimizer_secure_view_merging=FALSE;

-- Verified changed or not
SQL> show parameter optimizer_secure_view_merging;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_secure_view_merging boolean     FALSE

Sample AWR report snap-shot when high version minimized after changing the above parameter:

When I applied one of production database server where we had high version count issue, then amazingly it reduced. You can see the difference from both snap-shots.

Sure your version count will be minimized.