eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

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

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

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-


历史上的今天...
    >> 2006-08-11文章:
           江湖传闻-Blogspot再次解封

无觅

By eygle on 2009-08-11 12:45 | Comments (2) | FAQ | 2365 |

2 Comments

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表的全名应该怎么查呢?


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com