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

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