www.eygle.com

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

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

作者：eygle |【转载时请以超链接形式标明文章出处和作者信息及本声明】
链接：http://www.eygle.com/archives/2009/08/v_dollar_latch.html

---

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

-The End-

By eygle on 2009-08-11 12:45 | Comments (2) | Posted to Knowledge Base | Edit |

相关文章	随机文章
数据字典视图之:V$LATCH_CHILDREN 结构 数据字典视图之：DBA_LOGSTDBY_LOG结构 2PC、XA、DTP与两阶段提交 数据字典视图之：v$dlm_traffic_controller 数据字典视图之：V$LOCK_ELEMENT 结构	How Many knowledge you do not Know from this Operation? 如何简单测试Rman的备份恢复功能? Oracle 9.2.0.8 及 10.2.0.4 Patch地址 言论之：马云 与 巴菲特 如何在VCS中创建共享磁盘

留言 (2)

Posted by: wajoynece at August 11, 2009 5:32 PM

Posted by: eygle at August 12, 2009 8:38 AM

发表留言:

Name(*):
Email(*):

You Link: Remember Me? YesNo
验证码(*): (输入验证码后方可评论,谢谢支持)
Comments: