November 23, 2010

Shared latches by Oracle version

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: [545]… 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: [545]… diagnostic files. In Oracle 11g I had to set “_diag_daemon”=false and restart the instance to disable DIAG traces and save disk space.

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 /

VERSION

COUNT(*)

8.0.6.3.0

3

8.1.7.4.0

9

9.2.0.8.0

19

10.1.0.5.0

40

10.2.0.4.0

51

11.1.0.7.0

83

11.2.0.2.0

91

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.

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.

ADDENDUM
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: