数据字典视图之:V$LATCH、X$KSLLT、X$KSLLD 结构

« 数据字典视图之:V$LATCH_CHILDREN 结构 | Blog首页 | 20090811-墨墨、书书、Oracle »

v$latch视图记录了数据库重要的Latch信息，该信息来自底层基础表，由内存中提取，其结构为(来自Oracle 10gR2)：

SELECT d.inst_id, d.kslldadr, la.latch#, d.kslldlvl, d.kslldnam, d.kslldhsh,
       la.gets, la.misses, la.sleeps, la.immediate_gets, la.immediate_misses,
       la.waiters_woken, la.waits_holding_latch, la.spin_gets, la.sleep1,
       la.sleep2, la.sleep3, la.sleep4, la.sleep5, la.sleep6, la.sleep7,
       la.sleep8, la.sleep9, la.sleep10, la.sleep11, la.wait_time
FROM x$kslld d,
       (SELECT   kslltnum latch#, SUM (kslltwgt) gets, SUM (kslltwff) misses,
                 SUM (kslltwsl) sleeps, SUM (kslltngt) immediate_gets,
                 SUM (kslltnfa) immediate_misses, SUM (kslltwkc)
                                                                waiters_woken,
                 SUM (kslltwth) waits_holding_latch, SUM (ksllthst0)
                                                                    spin_gets,
                 SUM (ksllthst1) sleep1, SUM (ksllthst2) sleep2,
                 SUM (ksllthst3) sleep3, SUM (ksllthst4) sleep4,
                 SUM (ksllthst5) sleep5, SUM (ksllthst6) sleep6,
                 SUM (ksllthst7) sleep7, SUM (ksllthst8) sleep8,
                 SUM (ksllthst9) sleep9, SUM (ksllthst10) sleep10,
                 SUM (ksllthst11) sleep11, SUM (kslltwtt) wait_time
            FROM x$ksllt
        GROUP BY kslltnum) la
WHERE la.latch# = d.indx;

由其体系结构可以看到，v$latch来自两个底层表：X$KSLLT和X$KSLLD.
这两个表名称及含义如下：

X$KSLLT [K]ernel [S]ervice [L]ock Management Latch statistics + Child latches
X$KSLLD [K]ernel [S]ervice [L]ock Management Latch [D]escriptor (name)

X$KSLLD字段结构定义如下：

SQL> desc x$kslld
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ADDR                                               RAW(4)
INDX                                               NUMBER
INST_ID                                            NUMBER
KSLLDNAM                                           VARCHAR2(50)
KSLLDADR                                           RAW(4)
KSLLDLVL                                           NUMBER
KSLLDHSH                                           NUMBER

X$KSLLT定义结构如下：

SQL> desc x$ksllt
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ADDR                                               RAW(4)
INDX                                               NUMBER
INST_ID                                            NUMBER
KSLLTNUM                                           NUMBER
KSLLTNGT                                           NUMBER
KSLLTNFA                                           NUMBER
KSLLTWGT                                           NUMBER
KSLLTWFF                                           NUMBER
KSLLTWKC                                           NUMBER
KSLLTWSL                                           NUMBER
KSLLTWTH                                           NUMBER
KSLLTMSX                                           NUMBER
KSLLTMXS                                           NUMBER
KSLLTMSW                                           NUMBER
KSLLTWSX                                           NUMBER
KSLLTWXS                                           NUMBER
KSLLTWSW                                           NUMBER
KSLLTHST0                                          NUMBER
KSLLTHST1                                          NUMBER
KSLLTHST2                                          NUMBER
KSLLTHST3                                          NUMBER
KSLLTHST4                                          NUMBER
KSLLTHST5                                          NUMBER
KSLLTHST6                                          NUMBER
KSLLTHST7                                          NUMBER
KSLLTHST8                                          NUMBER
KSLLTHST9                                          NUMBER
KSLLTHST10                                         NUMBER
KSLLTHST11                                         NUMBER
KSLLTCNM                                           NUMBER
KSLLTWHR                                           NUMBER
KSLLTWHY                                           NUMBER
KSLLTWTT                                           NUMBER
KSLLTHDT                                           NUMBER
KSLLTDNT                                           NUMBER
KSLLTWTW                                           NUMBER
YIELDS_KSLLT                                       NUMBER
MISSES_WL_KSLLT                                    NUMBER
YIELDS_WL_KSLLT                                    NUMBER
SLEEPS_WL_KSLLT                                    NUMBER
CLASS_KSLLT                                        NUMBER

V$LATCH视图在Oracle 10gR2里显示大约有388种不同的Latch：

SQL> select count(*) from v$latch;

COUNT(*)
----------
       388

Execution Plan
----------------------------------------------------------
Plan hash value: 3943956501

--------------------------------------------------------------------------------
| Id | Operation            | Name    | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |     1 |    39 |     2 (100)| 00:00:01 |
|   1 | SORT AGGREGATE      |         |     1 |    39 |            |          |
|* 2 |   HASH JOIN          |         |     1 |    39 |     2 (100)| 00:00:01 |
|* 3 |    FIXED TABLE FULL | X$KSLLD |     1 |    26 |     0   (0)| 00:00:01 |
|   4 |    VIEW              |         |   100 | 1300 |     1 (100)| 00:00:01 |
|   5 |     HASH GROUP BY    |         |   100 | 27300 |     1 (100)| 00:00:01 |
|   6 |      FIXED TABLE FULL| X$KSLLT |   100 | 27300 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("LA"."LATCH#"="D"."INDX")
   3 - filter("D"."INST_ID"=USERENV('INSTANCE'))

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          0 consistent gets
          0 physical reads
          0 redo size
        412 bytes sent via SQL*Net to client
        400 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed