Latch, mutex and beyond

January 18, 2011

Spin tales: Part 3. Non-standard latch classes in Oracle 9.2-11g

Filed under: DTrace,Latch,shared latch,_spin_count — andreynikolaev @ 8:20 pm

In version 9.2 Oracle introduced new possibilities for fine grain latch tuning. Latch can be assigned to one of eight classes. Each class can have a different spin and wait policy. In addition, exclusive and shared latches behave differently.

By default, all the latches except “process allocation latch” belong to the standard class 0. In my previous posts, I discussed how the standard class exclusive and shared latches spin and wait. Now, it is the time to explore the non-standard class latch behaviors.

Parameter _latch_classes can be used to assign latches to classes. For the following demonstrations, I assigned two not used in my single-instance database latches to class 3. These were the exclusive “second spare latch” (latch#=46) and shared “gcs pcm hashed value bucket hash” (latch#=103) :

SQL> alter system set "_latch_classes"='46:3 103:3' scope=spfile;
SQL> startup force;
...
SQL> select name,latch#,class_ksllt
   from v$latchname join x$ksllt on (latch# = kslltnum)
   where class_ksllt>0;
NAME                                                   LATCH# CLASS_KSLLT
-------------------------------------------------- ---------- -----------
process allocation                                          3           2
second spare latch                                         46           3
gcs pcm hashed value bucket hash                          103           3

Eight complementary parameters _latch_class_0 to _latch_class_7 can be used to adjust spin and wait policies of latch classes. Oracle externalize these policies in x$ksllclass fixed table. Each row in x$ksllclass corresponds to _latch_class_n parameter. The default x$ksllclass content looks like:

SQL>  set markup html on
SQL>select * from x$ksllclass;

ADDR INDX INST_ID SPIN YIELD WAITTIME SLEEP0 SLEEP1 SLEEP2 SLEEP3 SLEEP4 SLEEP5 SLEEP6 SLEEP7
5C9EDEB0 0 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000
5C9EDEDC 1 1 20000 0 1 1000 1000 1000 1000 1000 1000 1000 1000
5C9EDF08 2 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000
5C9EDF34 3 1 20000 0 1 1000 1000 1000 1000 1000 1000 1000 1000
5C9EDF60 4 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000
5C9EDF8C 5 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000
5C9EDFB8 6 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000
5C9EDFE4 7 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000

Row 0 values and _latch_class_0 parameter have the special meaning. Class 0 is the only latch class that uses wait until post. Spin count of standard class exclusive latches is determined by the SPIN column of row 0. SLEEP… columns of class 0 seem not to be used at all.
Latches belonging to non-default classes always use timed waits. Wait of non-default class latch cannot be interrupted by interprocess post.

For the demonstration purpose, I will change the static parameter _latch_class_3 to

SQL> alter system set "_latch_class_3"="100 2 3 10000 20000 30000 40000 50000 60000 70000 80000" scope=spfile;
SQL> startup force;
ORACLE instance started.
...
SQL>  set markup html on
SQL> select * from x$ksllclass where indx<=3;
ADDR INDX INST_ID SPIN YIELD WAITTIME SLEEP0 SLEEP1 SLEEP2 SLEEP3 SLEEP4 SLEEP5 SLEEP6 SLEEP7
5C9EDEB0 0 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000
5C9EDEDC 1 1 20000 0 1 1000 1000 1000 1000 1000 1000 1000 1000
5C9EDF08 2 1 20000 0 1 8000 8000 8000 8000 8000 8000 8000 8000
5C9EDF34 3 1 100 2 3 10000 20000 30000 40000 50000 60000 70000 80000

Consecutive lexemes from _latch_class_3 string fill the columns in the corresponding row of x$ksllclass. Again, I will trace the latch acquisition using DTrace and the latch_spin_trace.sql script. In my previous posts, I introduced several DTrace scripts to trace various aspects of latch acquisition. This final version of spin.d enables probes (triggers) to trace latch acquisition functions and system calls. The script also counts the latch spin cycles and trace wait timeouts for first 8 waits. My platform is Oracle 10.2.0.4 32 bit for Solaris X86, however the results are generic.
Let me investigate the non-standard class exclusive latch acquisition:

$ sqlplus /nolog @latch_spin_trace.sql 46
...
kslgetl(0x50006434,1,2,3)		 - KSL GET exclusive Latch# 46
  sskgslgf(0x50006434)		 - immediate latch get
  kslges(0x50006434, ...)		 - wait get of exclusive latch
      sskgslspin(0x50006434)
        ... previous call repeated 100 times.
    yield()				 -    Yield 1
      sskgslspin(0x50006434)
        ... previous call repeated 100 times.
    yield()				 -    Yield 2
      sskgslspin(0x50006434)
        ... previous call repeated 100 times.
    pollsys(...,timeout=10 ms,...)	 - Sleep 1. End of first spin and yield loop.
      sskgslspin(0x50006434)
        ... previous call repeated 100 times.
    yield()				 -    Yield 1
      sskgslspin(0x50006434)
        ... previous call repeated 100 times.
    yield()				 -    Yield 2
      sskgslspin(0x50006434)
        ... previous call repeated 100 times.
     pollsys(...,timeout=20 ms,...)	 - Sleep 2 End of second spin and yield loop.
...    pollsys(...,timeout=30 ms,...)	 - Sleep 3
...    pollsys(...,timeout=40 ms,...)	 - Sleep 4
...    pollsys(...,timeout=50 ms,...)	 - Sleep 5
...    pollsys(...,timeout=60 ms,...)	 - Sleep 6
...    pollsys(...,timeout=70 ms,...)	 - Sleep 7
...    pollsys(...,timeout=80 ms,...)	 - Sleep 8
...    pollsys(...,timeout=80 ms,...)	 - Sleep 9
...

We observe that latch was acquired by repetitive spinning upto x$ksllclass.spin value and yielding the CPU. The yield() system call causes the current process to yield its execution and go to the end of runnable processes queue. This syscall gives the latch holder process a possibility to run and free the latch. Then the latch acquirer spins again. This cycle repeated upto x$ksllclass.yield times. As a result the effective spin count for latch is spin*(yield+1). This also applies to class 0 latches.

After unsuccessful spin for the non-standard class latch, the process sleeps. The first wait timeout is x$ksllclass.sleep1 microseconds. Then the process repeats the “spin and yield” cycle and sleeps for x$ksllclass.sleep2 and so on. Timeouts for the first eight waits are determined by corresponding sleepN column. All further sleeps will be for x$ksllclass.sleep7 microseconds. On Solaris platform, these timeouts are rounded to centisecond precision. In 10046 trace these latch sleeps look like:

WAIT #0: nam='latch free' ela= 10886 address=1342202932 number=46 tries=1 obj#=-1 tim=2606523663853
WAIT #0: nam='latch free' ela= 19922 address=1342202932 number=46 tries=2 obj#=-1 tim=2606523683857
WAIT #0: nam='latch free' ela= 30482 address=1342202932 number=46 tries=3 obj#=-1 tim=2606523714428
WAIT #0: nam='latch free' ela= 39937 address=1342202932 number=46 tries=4 obj#=-1 tim=2606523754471
WAIT #0: nam='latch free' ela= 49579 address=1342202932 number=46 tries=5 obj#=-1 tim=2606523804150
WAIT #0: nam='latch free' ela= 59067 address=1342202932 number=46 tries=6 obj#=-1 tim=2606523863313
WAIT #0: nam='latch free' ela= 68641 address=1342202932 number=46 tries=7 obj#=-1 tim=2606523932063
WAIT #0: nam='latch free' ela= 79066 address=1342202932 number=46 tries=8 obj#=-1 tim=2606524011243
WAIT #0: nam='latch free' ela= 79144 address=1342202932 number=46 tries=9 obj#=-1 tim=2606524090495
...

Non-standard class shared latch acquisition in X mode is generally the same:

$ sqlplus /nolog @latch_spin_trace.sql 103
...
Latch spin trace for pid: 10547
kslgetsl(0x50009C9C,1,2,3,16)		 - KSL GET Shared Latch# 103
  kslgess(0x50009C9C, ...)		 - wait get of shared latch
      sskgslspin(0x50009C9C)
        ... previous call repeated 2000 times. 
    yield()				 -    Yield 1
      sskgslspin(0x50009C9C)
        ... previous call repeated 2000 times. 
    yield()				 -    Yield 2
      sskgslspin(0x50009C9C)
        ... previous call repeated 2000 times. 
    pollsys(...,timeout=10 ms,...)	 - Sleep 1
...

The difference is that spin of the shared latch X get is governed by the dynamic _spin_count parameter. As shown in my previous post, the shared latch S get do not spin at all.
The same pattern applies to all the latch classes.

In this post, I discussed how the non-standard class latches spin and wait in Oracle 10g. This is crucial for effective tuning of Oracle latch performance.

About these ads

7 Comments »

  1. One of the Most Useful Information about Oracle Latches from 9i to 11g.
    Great work in all 3 Spin Tales Keep it

    Comment by Prashanth Subbakrishna - Oracle DBA , Bangalore — July 20, 2011 @ 1:37 pm | Reply

  2. Sorry for Typo Error .
    Keep it Up! Congrats!

    Prashanth Subbakrishna

    Comment by Prashanth Subbakrishna - Oracle DBA , Bangalore — July 20, 2011 @ 1:38 pm | Reply

  3. Hi,

    Please let me know the exact meaning of Standard and Non-standard Class Latches here .

    Thanks.

    Comment by sn — September 11, 2011 @ 2:15 am | Reply

    • Since Oracle 9.2 each latch can be assigned to one of eight classes using _latch_classes parameter.
      If not specified, all the latches except “process allocation latch” belong to the standard class 0.

      Comment by andreynikolaev — September 11, 2011 @ 4:33 am | Reply

  4. According to the output given in the 3 articles , “immediate gets” [not wiiling to wait] statistic is applicable only for
    Standard and Non-standard exclusive latches . This sskgslgf call .Please let me know.

    Regards,
    Prashanth Subbakrishna

    Comment by Prashanth Subbakrishna — September 27, 2011 @ 2:21 am | Reply

  5. It looks like x$ksllt structure is no more from Oracle 11g.Do you know the equivalent to get latch class information.

    Comment by orapsdba — November 25, 2011 @ 8:30 pm | Reply

    • Hello!
      Before Oracle 11g all the latches were externalized to SQL in x$ksllt. View v$latch used GROUP BY on top of this fixed table.

      In 11g Oracle changed this. Now parent (fixed SGA) latches are externalized in x$kslltr_parent, and child (allocated on startup) latches are in x$kslltr_children.

      11g fixed table x$kslltr merges these data without SQL level GROUP BY.
      All this tables have CLASS_KSLLT column

      Beware: select from x$kslltr acquires all parent latches and their children

      Comment by andreynikolaev — November 26, 2011 @ 11:00 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 65 other followers

%d bloggers like this: