x oracle,Oracle的X$表系列介绍之-X$KSLLCLASS

以下是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

8 rows selected.

比如我们的数据库系统经历较为严重的cache buffers chains竞争，为了降低其SLEEEP次数，我们可以对该Latch进行针对性分类邦定，单独修改其_SPIN_COUNT值。

SQL> select latch#,name from v$latchname where name='cache buffers chains';

LATCH# NAME

---------- ----------------------------------------------------------------

98 cache buffers chains

我们可以如下修改初始化参数，而后重新启动数据库:

_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 10000

_latch_classes 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

8 rows selected.

SQL>

由此，单独控制一些重要Latch成为可能。

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 KSLLTNUM CLASS_KSLLT

---------------------------------------------- ---------- -----------

process allocation 3 2

cache buffers chains 98 1

更多请参考:

Richmond Shee, Kirtikumar Deshpande and K Gopalakrishnan的《Oracle Wait Interface》

历史上的今天...

>>

2019-01-16文章:

2013-01-16文章:

2010-01-16文章:

2008-01-16文章:

2007-01-16文章:

2006-01-16文章: