« 数据字典视图之:V$LATCH_CHILDREN 结构 | Blog首页 | 20090811-墨墨、书书、Oracle »
数据字典视图之:V$LATCH、X$KSLLT、X$KSLLD 结构
作者:eygle | 【转载请注出处】|【云和恩墨 领先的zData数据库一体机 | zCloud PaaS云管平台 | SQM SQL审核平台 | ZDBM 数据库备份一体机】
链接:https://www.eygle.com/archives/2009/08/v_dollar_latch.html
v$latch视图记录了数据库重要的Latch信息,该信息来自底层基础表,由内存中提取,其结构为(来自Oracle 10gR2):链接:https://www.eygle.com/archives/2009/08/v_dollar_latch.html
SELECT d.inst_id, d.kslldadr, la.latch#, d.kslldlvl, d.kslldnam, d.kslldhsh,由其体系结构可以看到,v$latch来自两个底层表:X$KSLLT和X$KSLLD.
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;
这两个表名称及含义如下:
X$KSLLT [K]ernel [S]ervice [L]ock Management Latch statistics + Child latchesX$KSLLD字段结构定义如下:X$KSLLD [K]ernel [S]ervice [L]ock Management Latch [D]escriptor (name)
SQL> desc x$kslldX$KSLLT定义结构如下:
Name Null? Type
----------------------------------------- -------- ----------------------------
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
KSLLDNAM VARCHAR2(50)
KSLLDADR RAW(4)
KSLLDLVL NUMBER
KSLLDHSH NUMBER
SQL> desc x$kslltV$LATCH视图在Oracle 10gR2里显示大约有388种不同的Latch:
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
SQL> select count(*) from v$latch;-The End-
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
历史上的今天...
>> 2006-08-11文章:
By eygle on 2009-08-11 12:45 | Comments (2) | FAQ | 2365 |
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表的全名应该怎么查呢?
Metalink上有个说明文档,网上也有很多转载:
http://www.eygle.com/archives/2004/11/oracle_x_table.html