x$视图介绍（二）

Why are people so intensely interested in Oracle internals? Partly because internals information can be useful for tuning and troubleshooting. But also because Oracle Corporation has kept most of the internals secret, while revealing just enough to tantalize.

Oracle Internals guru, Steve Adams

参考：http://www.stormloader.com/yonghuang/computer/x$table.html

x$bh
[B]uffer [H]eader

缓冲区头信息。通过连接x$bh和v$latch_children可以找到某个latch所保护的具体的block
select obj, dbarfil, dbablk from x$bh a, v$latch_children b where a.hladdr = b.addr

也可以找出同一个block在data buffer cache中存在多少个副本（多版本一致性读）
select dbarfil, dbablk, count(*) from x$bh group by dbarfil, dbablk having count(*) > 2

注意:x$bh.id=dba_objects.data_object_id而不是dba_objects.object_id

x$k2gte
[K]ernel [2]-phase commit [G]lobal [T]ransaction [E]ntry

x$k2gte.k2gtdses = v$session.saddr
x$k2gte.k2gtdxcb = v$transcation.addr

x$kcbwait
[K]ernel [C]ache [B]uffer [WAIT]

x$kcbfwait
[K]ernel [C]ache [B]lock [F]ile [WAIT]

select count, time, name from v$datafile df, x$kcbfwait fw where fw.indx+1 = df.file#

x$kcbwds
[K]ernel [C]ache [B]uffer [W]orking [D]ata [S]et

x$kcccf[K]ernel [C]ache [C]ontrolfile management [C]ontrol[F]ile

下面的SQL可以获得控制文件的block size
select cfnam, (cffsz+1)*cfbsz from x$kcccf. cfbsz

x$kcccp
[K]ernel [C]ache [C]ontrolfile [C]heckpoint [P]rogress

x$kccdi
[K]ernel [C]ache [C]ontrolfile management [D]atabase [i]nformation

x$kccle
[K]ernel [C]ache [C]ontrolfile [L]ogfile [S]tatistics

x$kcfio
[K]ernel [C]ache [F]ile [I]/[O]

x$kclfh
[K]ernel [C]ache [L]ock [F]ile [H]eader

x$kclfi
[K]ernel [C]ache [L]ock [F]ile [I]ndex

x$kcluh
[K]ernel [C]ache [L]ock [U]ndo [H]eader

x$kclui
[K]ernel [C]ache [L]ock [U]ndo [I]ndex

x$kcrfx
[K]ernel [c]ache [R]edo [F]ile [C]ontext

x$kdxst
[K]ernel [D]ata inde[X] [ST]atus

index_stats的基表

x$kdxhs
[K]ernel [D]ata inde[X] [H]i[S]togram

index_histogram的基表

x$kghlu
[K]ernel [G]eneric [H]eap [L]R[U]s

x$kglcursor
[K]ernel [G]eneric [L]ibrary cache [CURSOR]

v$sql和v$sqlarea的基表

x$kgllk
[K]ernel [G]eneric [L]ibrary cache [L]oc[K]

dba_kgllock的基表

x$kglob
[K]ernel [G]eneric [L]ibrary cache [OB]ject

x$kglob.kglhdadr = v$session_wait.p1raw

x$kglpn
[K]ernel [G]eneric [L]ibrary [P]i[N]

dba_kgllock的基表

x$kglst
[K]ernel [G]eneric [L]ibrary cache [ST]atus

x$kqfco
[K]ernel [Q]uery [F]ixed table [CO]lumns

x$kqfta
[K]ernel [Q]uery [F]ixed [TA]ble

x$kqfco.kqfcotab=x$kqfta.indx

x$kqfdt
[k]ernel [Q]uery [F]ixed [D]erived [T]able

x$kqfp
[K]ernel [Q]uery [F]ixed [P]rocudure

disk_and_fixed_objects的基表

x$kqfsz
[K]ernel [Q]uery [F]ixed [S]i[Z]e

x$kqfvi[K]ernel [Q]uery [F]ixed [VI]ew

x$kqfvt
[K]ernel [Q]uery [F]ixed [V]iew [T]able

x$fsled
[K]ernel [S]ervice [E]vent [D]efinition

x$kslei
[K]ernel [S]ervice [E]vent for [I]nstance

x$ksles
[K]ernel [S]ervice [E]vent for [S]ession

x$ksmfs
[K]ernel [S]ervice ]M]emory [F]ixed [S]GA

x$ksmfsv
[K]ernel [S]ervice ]M]emory [F]ixed [S]GA variables

x$ksmjs
[K]ernel [S]ervice ]M]emory [J]ava pool [S]ummary

x$ksmlru
[K]ernel [S]ervice ]M]emory [LRU]

访问该表会清空该表的内容

x$ksmls
[K]ernel [S]ervice ]M]emory [L]arge pool [S]ummary

x$ksmmem
[K]ernel [S]ervice [MEM]ory

整个SGA的内存映射。下面的SQL可以查询数据库的版本
select ksmmmval from x$ksmmem where indx = 2 （64位的oracle可能为1）

x$ksmpp
[K]ernel [S]ervice [M]emory [P]GA hea[P]

x$ksmsd
[K]ernel [S]ervice [M]emory [S]GA [D]efinition

x$ksmsp
[K]ernel [S]ervice [M]emory [S]GA] hea[P]

x$ksmspr
[K]ernel [S]ervice [M]emory [S]hared [P]ool [R]eserved

x$ksmss
[K]ernel [S]ervice [M]emory [S]hared pool [S]ummary

x$ksmup
[K]ernel [S]ervice [M]emory [U]GA hea[P]

x$ksppcv
[K]ernel [S]ervice [P]arameter [C]urrent] (session) [V]alue

x$ksppi
[K]ernel [S]ervice [P]arameter [P]arameter [I]nfo

v$parameter, v$system_parameter and v$system_parameter2的基表。通过关联x$ksppcv和x$ksppi可以查询隐含参数
select a.ksppinm Parameter, a.ksppdesc Description, b.ksppstvl "Session Value", c.ksppstvl "Instance Value"
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx and a.ksppinm like '_%' escape '' order by 1

x$ksppsv
[K]ernel [S]ervice [P]arameter [S]ystem [V]alue

x$ksqeq
[K]ernel [S]ervice en[Q]ueue [E]n[Q]ueue

x$ksqrs
[K]ernel [S]ervice en[Q]ueue [RE]source

x$ksqst
[K]ernel [S]ervice en[Q]ueue [S]tatistics [T]ypes

x$ksulv
[K]ernel [S]ervice [U]ser [L]ocale [V]alue

x$ksulop
[K]ernel [S]ervice [U]ser [L]ong [OP]eration

x$ksupr
[K]ernel [S]ervice [U]ser [PR]ocess

x$ksuse
[K]ernel [S]ervice [U]ser [SE]ssion

x$ktcxb
[K]ernel [T]ransaction [C]ontrol object

v$transaction的基表

x$ktfbfe
[K]ernel [T]ransaction [F]ile [B]itmap [F]ree [E]xtent

LMT文件头的可用extent位图（相当于DMT的fet$）。dba_free_space的基表

x$ktfbhc
[K]ernel [T]ransaction [F]ile [B]itmap ??

每个datafile一条可用空间的记录

x$ktfbue
[K]ernel [T]ransaction [F]ile [B]itmap [U]sed [E]xtent

LMT文件头的已用extent位图（相当于DMT的uet$）

x$ktuxe
[K]ernel [T]ransaction [U]ndo transaction [E]ntry

9i之前，常用下面的语句获得当前SCN
select max(ktuxescnw * power(2, 32) + ktuxescnb) from x$ktuxe

x$kxfpsds
[K]ernel e[X]ecution [F]ast [P]rocess [S]lave [D]equeue [S]tatistics

x$kzsprv
[K]ernel security [S]ession [PR]i[V]ilege

v$enabledprivilege的基表（v$enabledprivilege是session_privs的基表）

x$kzsro
[K]ernel security [S]ession [RO]le

x$le
[L]ock [E]lement

select a.* from x$bh a, x$le b where a.le_addr = b.addr

x$le_stat
[L]ock [E]lement [STAT]us

x$message
(background process)[MESSAGE]s

x$trace
从9i开始，x$trace记录了跟踪事件的信息。下面的SQL显示了那些跟踪事件已经开启：
select event, count(*) from x$trace group by event

x$uganco
[U]ser [G]lobal [A]rea [N]etwork [CO]nnection

v$dblink的基表

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/193161/viewspace-50164/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/193161/viewspace-50164/