Divide the mutex contention for function and procedure

This is a well-known from Andrey Nikolayev blog, how to divide mutex contention for packages and cursors.

For package:
 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;
 alter system set “_kgl_hot_object_copies”= 10 scope=spfile;

For cursor:
 alter system set "_kgl_debug" = "hash='4d1ef2753f3bb11043fd2f61c011abac' debug=33554432" scope=spfile;
 alter system set “_kgl_hot_object_copies”= 10 scope=spfile;

But what about Functions and Procedures? What syntax should we use ?
Let’s run the query and see what is happening in our system.

OS: REL7 64
DB version: 12.1.0.2.0

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 type like 'FUNC%' or type like 'PROC%'  

CURSOR  ADDRESS           NAME         HASH_VALUE  TYPE    LOCKED_TOTAL PINNED_TOTAL EXECUTIONS  NAMESPACE
Parent  000000007B69E810  FORMAT_DATE  3111443796  FUNCTION      119         187       99371       1
Parent  000000007BEFE460  SYS_IXMLAGG  4008213881  FUNCTION      45          45        0           1
Parent  000000007E882E38  GET_ADDRESS  3903151007  FUNCTION      28          37        21          1
..........

In all cases we see 1 for NAMESPACE. I am going to clone the FORMAT_DATE function.
Let’s make sure that there is only one instance of FORMAT_DATE function is in the memory.

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 name='FORMAT_DATE'

CURSOR  ADDRESS           NAME         HASH_VALUE  TYPE    LOCKED_TOTAL PINNED_TOTAL EXECUTIONS  NAMESPACE
Parent  000000007B69E810  FORMAT_DATE  3111443796  FUNCTION      119         187       99371       1

We see just one instance of FORMAT_DATE function. Let’s clone the FORMAT_DATE function.

alter system set "_kgl_debug"="name='FORMAT_DATE' schema='CMS' namespace=1 debug=33554432" scope=spfile;
alter system set "_kgl_hot_object_copies"= 10 scope=spfile;
restart instance ....

By default, Oracle created approximately “number of CPU Cores” hot objects copies. You can adjust this number by _kgl_hot_object_copies parameter.
After all check again.

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 name='FORMAT_DATE'

CURSOR  ADDRESS           NAME         HASH_VALUE TYPE     LOCKED_TOTAL PINNED_TOTAL EXECUTIONS NAMESPACE
Parent	000000007BF1B3C8  FORMAT_DATE  2206514549 FUNCTION     2	 2	       0	 1
Parent	000000007E1F4310  FORMAT_DATE  4244497168 FUNCTION     2	 2	       0	 1
Parent	000000007B489908  FORMAT_DATE  3951634923 FUNCTION     2	 2	       0	 1
Parent	000000007E8C90D0  FORMAT_DATE  3111443796 FUNCTION     1	 0	       0	 1

There are 4 instances of FORMAT_DATE function and this count increases (up to 10 which was set above) / decreases depends on load.