Oracle's X $ Table Series Introduction - X $ KSLLCLASS
Author: eygle
Source: http://blog.eygle.com
Date: January 16, 2005
«HRAY NASDAQ | Blog Home | How to Set Auto Login in Windows XP»
X $ Ksllclass - [K] Ernel [S] ERVICE [L] OCK [L] Atches [class]
Starting from Oracle9ir2, Oracle allows you to classify the Latch, and different categories can be used for different _spin_count values. This avoids previous versions, once modified _spin_count parameters affect all LATCH issues. Thereby, this parameter can be controlled to a certain extent for the excessive consumption of the CPU.
The following is the query output of x $ ksllclass:
SQL> SELECT INDX, Spin, Yield, Waittime from x $ ksllclass;
Indx Spin Yield Waittime
---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0 20000 0 1
1 20000 0 1
2 20000 0 1
3 20000 0 1
4 20000 0 1
5 20000 0 1
6 20000 0 1
7 20000 0 1
Rows SELECTED.
For example, our database system has experienced a more serious Cache Buffers Chains competition. In order to reduce its SLEEEP, we can modify the latch to modify its _SPIN_COUNT value separately.
SQL> SELECT LATCH #, Name from V $ latchname where name = 'cache buffers chains'
Latch # name
---------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------
98 cache buffers chains
We can modify the initialization parameters as follows, and then restart the database:
_LATCH_CLASS_1 = "10000" _latch_classes = "98: 1"
SQL> SELECT LATCH #, Name from V $ latchname where name = 'cache buffers chains'
Latch # name
---------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------
98 cache buffers chains
SQL> ALTER system set "_latch_class_1" = 10000 scope = spfile;
SYSTEM altered.
SQL> ALTER System Set "_latch_classes" = "98: 1" scope = spfile;
System altered.
SQL> Startup force;
Oracle Instance Started.
Total System Global Area 80811208 Bytes
FIXED SIZE 451784 BYTES
Variable size 37748736 bytes
Database buffers 41943040 BYTES
Redo buffers 667648 bytes
Database mounted.
Database opened.
SQL> Show Parameter Latch
Name Type Value
----------------------------------- --- ---------------------------
_LATCH_CLASS_1 STRING 1000
_LATCH_CLASSS STRING 98: 1
SQL> SELECT INDX, Spin, Yield, Waittime from x $ ksllclass;
Indx Spin Yield Waittime
---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0 20000 0 1
1 10000 0 1
2 20000 0 1
3 20000 0 1
4 20000 0 1
5 20000 0 1
6 20000 0 1
7 20000 0 1
Rows SELECTED.
SQL>
Thus, some important Latch alone can be controlled.
SQL> SELECT A.KSLLDNAM, B.KSLLTNUM, B.CLASS_KSLLT
2 from x $ kslld a, x $ ksllt b
3 where a.kslldadr = B.Addr
4 and b.class_ksllt> 0;
KSLLDNAM KSLTNUM CLASS_KSLLT
---------------------------------------------- ---- -----------------
Process Allocation 3 2
Cache Buffers CHAINS 98 1
For more reference: Richmond Shee, Kirtikumar Deshpande and K Gopalakrishnan "Oracle Wait Interface"
