Resolving Library cache: mutex X

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;