As I described in my previous post, Shared and Exclusive Oracle latches differ significantly. Shared latch behaves like enqueue. It has S and X incompatible modes. Moreover X mode serializes the shared latch. The contention for shared and exclusive latches has different patterns. This leads to different methods to tune such contentions.
But we do not know which latches are shared. Oracle never published the list of shared latches. Every time looking in the AWR or Statspack report we had to guess the type of contending latch. We only know that “cache buffer chains” latches became shared since in 9.2.
Oracle executable internally determines that latch is shared using flag hidden somewhere in x$kslld (v$latchname) structure. Google search shows that KSLLD means [K]ernel [S]ervice [L]ock [L]atch [D]escriptor. Unfortunately this shared flag was not externalized to SQL. It is possible to check the flag manually using oradebug peek or DTrace. But the flag offset is version and platform dependent. We need more systematic way to determine the latch type.
Oracle server uses different functions to acquire the latch. Exclusive latch is acquired by kslgetsl(). For the shared latch get Oracle uses kslgetsl() (kslgetsl_w() in 11g). Oradebug utility allows us to call this function manually. It is easy to find that kslgetsl() function performs internal check for the latch type. ORA-00600: … error will be raised if one attempts to acquire exclusive latch by kslgetsl().
This allows me to write scripts to produce list of shared latches. All that needed is to call kslgetsl() for each parent latch. If the call succeed, the latch is shared. I wrote two scripts to accomplish this:
- list_shared_latches.sql. The script creates the table shared_latches to store results and generates shell script list_shared_latches.sh to call is_latch_shared.sql script for each latch in v$latch_parent
- is_latch_shared.sql uses oradebug call kslgetsl(laddress,…) to acquire the latch and kslfre() to immediately release it. If the latch is exclusive, the call and the script fail. If the latch is shared, the call succeeds and the script adds a row to shared_latches table.
It is worth to mention that the scripts will produce a lot of ORA-00600: … diagnostic files. In Oracle 11g I had to set “_diag_daemon”=false and restart the instance to disable DIAG traces and save disk space.
SQL> @list_shared_latches … !sh list_shared_latches.sh sqlplus /nolog @is_latch_shared 0x50003C10 0 "event range base latch" sqlplus /nolog @is_latch_shared 0x50003C74 1 "post/wait queue" sqlplus /nolog @is_latch_shared 0x50003CD8 2 "hot latch diags" … ORA-00600: internal error code, arguments: , [0x50003F14], , , , , ,  Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production …
The scripts determine which latches are shared in this Oracle version:
SQL> select * from shared_latches; VERSION LATCH# NAME S ---------- ---------- ------------------------------ - 220.127.116.11.0 7 test shared non-parent l0 Y 18.104.22.168.0 15 session idle bit Y 22.214.171.124.0 16 client/application info Y 126.96.36.199.0 22 ksim group membership cache Y 188.8.131.52.0 25 parameter table management Y 184.108.40.206.0 48 resmgr group change latch Y …
I repeated this procedure for several major Oracle versions and consolidated the shared_latches tables by exp/imp. The results look interesting. I created separate page where you can download shared_latches table content. And here you can find the comparison of shared latches by Oracle version.
There are obvious trends. Shared latches appeared in Oracle 8.0. At first only 3 latches were shared. The number of shared latches increased with Oracle version
SQL> select version,count(*) from shared_latches 2 group by version 3 order by to_number(replace(version,'.','')) 4 /
The session idle bit was shared in all Oracle versions. Oracle 8.0 also had two unique shared latches: redo copy and undo global data . These latches were never shared since that time.
Oracle 8i used shared latches for Advance Queueing, Resource manager, Java and Multi-Threaded (now Shared) Server.
Since 9.2 Oracle used shared latches for buffer cache management and SGA mapping, Real Application Cluster and Streams. The famous cache buffers chains latches became shared in 9i.
Oracle 10g added shared latches to areas of SGA and library cache management, statistics and In Memory Undo.
And Oracle 11g added memory management, Results Cache and parameter table related shared latches.
Increased number of shared latches brought Oracle to new levels of performance and scalability. I would like to say “Thank you” for the Oracle Architects and Developers, who implemented these intriguing features.
The scripts work fine with 32bit Oracle version greater than 10g. Unfortunately 64bit oradebug call corrupts 64bits arguments since 10.2.0.3. I hope that at some time Oracle will fix this bug.
Ancient Oracle versions were more peculiar. In 9i sqlplus the WHENEVER SQLERROR did not caught the exception raised in oradebug. This was avoided using 10g sqlplus connected to 9i instance.
Oradebug 8i could not translate kslgetsl() symbolic function name. I had to do it manually and replace the symbolic names by addresses:
-bash-3.00$ nm -tx oracle | grep kslgetsl  |0x082fb9f8|0x00000594|FUNC |GLOB |0 |11 |kslgetsl ... oradebug call 0x082fb9f8 &1 1 2 3 8 ...
And Oracle 8.0 sqlplus did not even supported oradebug. I had to use almost forgotten svrmgrl. As a result my second script is_latch_shared.sql was modified for 8.0 like
connect / as sysdba set verify off set linesize 140 set pagesize 0 alter session set max_dump_file_size=0; spool sv1.sql select 'connect internal' from dual; select 'set stoponerror on' from dual; select 'alter session set max_dump_file_size=0;' from dual; select 'oradebug setmypid' from dual; select 'oradebug call 818768 &1 1 2 3 8' from dual; select 'oradebug call 824424 &1 ' from dual; /* if we are here, the latch was shared */ select 'insert into shared_latches(latch#,name,version,shared) select latch#,name,version,''Y'' from v$latch,v$instance where latch#=&2; ' from dual; select 'commit;' from dual; select 'exit' from dual; spool off !svrmgrl email@example.com exit