今天在eygle的网站上看到了一篇关于死事务的恢复进度查询的文章，里面介绍x$ktuxe表的使用。因此想对X$表进行一个整理，以便在今后使用的时候能方便的查询。如果大家有其他使用X$表的经验，也欢迎补充。
在oracle 9i R2的版本，有394个X$表：
|
1 2 3 4 5 |
sys@ORALOCAL(192.168.0.22)> select count(*) from v$fixed_table where upper(NAME) like 'X$%'; COUNT(*) ---------- 394 |
以下是一些用过的或者常用的X$表：
目前已经小结的有：
1.X$KTUXE
2.X$KGLPN
3.X$KGLOB
4.X$KSMSP
5.X$KCCCP
6.X$KSMLRU
7.X$KSQRS
8.X$KGLLK
9.X$KTSSO
1.X$KTUXE——[K]ernel [T]ransaction [U]ndo Transa[x]tion [E]ntry
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
sys@ORALOCAL(192.168.0.22)> desc X$KTUXE ##主要用来查看scn和smon正在清理的undo block Name Null? Type ----------------------------------------------------- -------- --------------------- ADDR RAW(4) INDX NUMBER INST_ID NUMBER KTUXEUSN NUMBER KTUXESLT NUMBER KTUXESQN NUMBER KTUXERDBF NUMBER KTUXERDBB NUMBER KTUXESCNB NUMBER KTUXESCNW NUMBER ##9i之前版本根据max(ktuxescnw * power(2, 32) + ktuxescnb) 算出当前scn KTUXESTA VARCHAR2(16) KTUXECFL VARCHAR2(24) ## 如果值为'DEAD'，表示有事务在做回滚；NONE表示正常； KTUXEUEL NUMBER KTUXEDDBF NUMBER KTUXEDDBB NUMBER KTUXEPUSN NUMBER KTUXEPSLT NUMBER KTUXEPSQN NUMBER KTUXESIZ NUMBER ##需要回滚的undo block大小 |
2.x$kglpn——[K]ernel [G]eneric [L]ibrary Cache Manager object [P]i[N]s
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
sys@ORALOCAL(192.168.0.22)> desc x$kglpn ##主要用来处理library cache pin holder Name Null? Type ----------------------------------------------------- -------- ------------ ADDR RAW(4) INDX NUMBER INST_ID NUMBER KGLPNADR RAW(4) KGLPNUSE RAW(4) KGLPNSES RAW(4) KGLPNHDL RAW(4) ##关联v$session_wait中event为library cache pin的P1RAW，再关联v$session，可以查出sid和serial# KGLPNLCK RAW(4) KGLPNCNT NUMBER KGLPNMOD NUMBER ##如果值为3，表示为library cache pin的holder；如果值为0，表示为waiter KGLPNREQ NUMBER ##如果值为0，表示为library cache pin的holder；如果值为2，表示为waiter KGLPNDMK NUMBER KGLPNSPN NUMBER |
3.x$kglob——–[K]ernel [G]eneric [L]ibrary Cache Manager [OB]ject
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 |
sys@ORALOCAL(192.168.0.22)> desc x$kglob ##主要用来查看library cache 的对象 Name Null? Type ----------------------------------------------------- -------- ------------------------ ADDR RAW(4) INDX NUMBER INST_ID NUMBER KGLHDADR RAW(4) ##关联v$session_wait中event为library cache pin的P1RAW KGLHDPAR RAW(4) KGLHDCLT NUMBER KGLNAOWN VARCHAR2(64) ##当前处于library cache pin的owner KGLNAOBJ VARCHAR2(1000) ##当前处于library cache pin的对象 KGLNADLK VARCHAR2(64) KGLNAHSH NUMBER KGLNATIM DATE KGLNAPTM DATE KGLHDNSP NUMBER KGLHDLMD NUMBER KGLHDPMD NUMBER KGLHDFLG NUMBER KGLHDOBJ RAW(4) KGLHDLDC NUMBER KGLHDIVC NUMBER KGLHDEXC NUMBER KGLHDLKC NUMBER KGLHDKMK NUMBER KGLHDDMK NUMBER KGLHDAMK NUMBER KGLOBFLG NUMBER KGLOBSTA NUMBER KGLOBTYP NUMBER KGLOBHS0 NUMBER KGLOBHS1 NUMBER KGLOBHS2 NUMBER KGLOBHS3 NUMBER KGLOBHS4 NUMBER KGLOBHS5 NUMBER KGLOBHS6 NUMBER KGLOBHS7 NUMBER KGLOBHD0 RAW(4) KGLOBHD1 RAW(4) KGLOBHD2 RAW(4) KGLOBHD3 RAW(4) KGLOBHD4 RAW(4) KGLOBHD5 RAW(4) KGLOBHD6 RAW(4) KGLOBHD7 RAW(4) KGLOBPC0 NUMBER KGLOBPC6 NUMBER KGLOBTP0 RAW(4) KGLOBT00 NUMBER KGLOBT01 NUMBER KGLOBT02 NUMBER KGLOBT04 NUMBER KGLOBT05 NUMBER KGLOBT06 NUMBER KGLOBT07 NUMBER KGLOBT08 NUMBER KGLOBT09 NUMBER KGLOBT10 NUMBER KGLOBT11 NUMBER KGLOBT12 NUMBER KGLOBT13 NUMBER KGLOBT14 NUMBER KGLOBT15 NUMBER KGLOBT16 NUMBER KGLOBT17 NUMBER KGLOBT18 NUMBER KGLOBT19 NUMBER KGLOBT20 NUMBER KGLOBT21 NUMBER KGLOBT22 NUMBER KGLOBT23 NUMBER KGLOBT24 NUMBER KGLOBT25 NUMBER KGLOBT26 NUMBER KGLOBT28 NUMBER KGLOBT29 NUMBER KGLOBT30 NUMBER KGLOBT31 NUMBER KGLOBT27 NUMBER KGLOBT32 NUMBER KGLOBT33 NUMBER KGLOBTL0 NUMBER KGLOBTL1 NUMBER KGLOBTS0 VARCHAR2(64) KGLOBTS1 VARCHAR2(64) KGLOBTN0 NUMBER KGLOBTN1 NUMBER KGLOBTN2 NUMBER KGLOBTN3 NUMBER KGLOBTN4 NUMBER KGLOBTN5 NUMBER KGLOBTS2 VARCHAR2(64) KGLOBTT0 DATE |
4.X$KSMSP——[K]ernal [S]torage [M]emory Management [S]GA Hea[P]
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
sys@ORALOCAL(192.168.0.22)> desc X$KSMSP ## 主要用于查看shared pool中chunk的信息 Name Null? Type ----------------------------------------------------- -------- ------------------ ADDR RAW(4) INDX NUMBER INST_ID NUMBER KSMCHIDX NUMBER KSMCHDUR NUMBER KSMCHCOM VARCHAR2(16) KSMCHPTR RAW(4) KSMCHSIZ NUMBER KSMCHCLS VARCHAR2(8) ##shared pool 的chunk的类型，如R-free，R-freea，free，freeabl，perm，recr KSMCHTYP NUMBER KSMCHPAR RAW(4) |
5.X$KCCCP——[K]ernel [C]ache [C]ontrolfile management [c]heckpoint [p]rogress
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
sys@ORALOCAL(192.168.0.22)> desc X$KCCCP ##主要查看checkpoint的heartbeat Name Null? Type ----------------------------------------------------- -------- -------------- ADDR RAW(4) INDX NUMBER INST_ID NUMBER CPTNO NUMBER CPSTA NUMBER CPFLG NUMBER CPDRT NUMBER CPRDB NUMBER CPLRBA_SEQ NUMBER CPLRBA_BNO NUMBER CPLRBA_BOF NUMBER CPODR_SEQ NUMBER CPODR_BNO NUMBER CPODR_BOF NUMBER CPODS VARCHAR2(16) CPODT VARCHAR2(20) CPODT_I NUMBER CPHBT NUMBER ##checkpoint的心跳，每隔3秒变化一次，并且写入到控制文件中。 CPRLS VARCHAR2(16) CPRLC NUMBER CPMID NUMBER CPSDR_SEQ NUMBER CPSDR_BNO NUMBER CPSDR_ADB NUMBER |
6.X$KSMLRU——[K]ernal [S]torage [M]emory Management [LRU]
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
sys@ORALOCAL(192.168.0.22)> desc X$KSMLRU ##looking for the cause of memory allocations in the shared pool Name Null? Type ----------------------------------------------------- -------- --------------- ADDR RAW(4) INDX NUMBER INST_ID NUMBER KSMLRIDX NUMBER KSMLRDUR NUMBER KSMLRCOM VARCHAR2(20) KSMLRSIZ NUMBER KSMLRNUM NUMBER ##Number of items flushed from the shared pool KSMLRHON VARCHAR2(32) KSMLROHV NUMBER KSMLRSES RAW(4) ##Session performing the allocation.Join to V$SESSION.SADDR |
7.X$KSQRS——[K]ernel [S]ervice en[Q]ueue [R]e[S]ource
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
sys@ORALOCAL(192.168.0.22)> desc X$KSQRS ##查找由于受到enqueue lock的数据库资源 sys@ORALOCAL(10.1.19.13)> desc X$KSQRS 名称 是否为空? 类型 ----------------------------------------------------- -------- ---------------- ADDR RAW(4) INDX NUMBER INST_ID NUMBER KSQRSID1 NUMBER KSQRSID2 NUMBER KSQRSIDT VARCHAR2(2) KSQRSFLG NUMBER |
8.X$KGLLK——[K]ernel [G]eneric [L]ibrary Cache Manager object [L]oc[K]s
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
SQL> desc X$KGLLK ##主要用来查看library cache 的对象的锁 名称 是否为空? 类型 ----------------------------------------- -------- ---------------------------- ADDR RAW(4) INDX NUMBER INST_ID NUMBER KGLLKADR RAW(4) KGLLKUSE RAW(4) ##关联v$session的saddr这一列，即session的地址。 KGLLKSES RAW(4) KGLLKSNM NUMBER ##关联v$session的sid KGLLKHDL RAW(4) ##关联v$session_wait的p1raw列，表明library cache lock的锁地址。也对应于x$kglob的kglhdadr这一列。 KGLLKPNC RAW(4) KGLLKPNS RAW(4) KGLLKCNT NUMBER KGLLKMOD NUMBER ##代表的锁”占用”模式，0-> ‘None’, 1->’Null’, 2-> ‘Share’, 3-> ‘Exclusive’,其它值->’Unknown’ KGLLKREQ NUMBER ##代表”请求”模式,0-> ‘None’, 1->’Null’, 2-> ‘Share’, 3-> ‘Exclusive’,其它值->’Unknown’,另一个角度说明0->lock的占有者,其它->锁的请求者。 KGLLKFLG NUMBER KGLLKSPN NUMBER KGLLKHTB RAW(4) KGLNAHSH NUMBER KGLLKSQLID VARCHAR2(13) KGLHDPAR RAW(4) KGLHDNSP NUMBER USER_NAME VARCHAR2(30) KGLNAOBJ VARCHAR2(60) ##包含了在librarky cache中的对象上执行命令的语句的前80个字符。 |
9.X$KTSSO——[K]ernal [T]ransaction [S]ort [S]egment
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
SQL> desc X$KTSSO ##主要用来查看占据temp表空间的session sid --9i的： Name Null? Type ----------------------------------------------------- -------- ------ ADDR RAW(8) INDX NUMBER INST_ID NUMBER KTSSOSES RAW(8) ##对应v$session的saddr KTSSOSNO NUMBER KTSSOTSN VARCHAR2(31) KTSSOCNT NUMBER KTSSOSEGT NUMBER ##表示占据temp段的类型:1,SORT/2,HASH/ 3,DATA,/4,INDEX/5, LOB_DATA/6, LOB_INDEX/UNDEFINED KTSSOFNO NUMBER KTSSOBNO NUMBER KTSSOEXTS NUMBER KTSSOBLKS NUMBER ##占据的temp段的block数，注意如果是temporary table，也在此显示，显示结果也为TEMP KTSSORFNO NUMBER --10g的： 名称 是否为空? 类型 ----------------------------------------- -------- ---------------------------- ADDR RAW(4) INDX NUMBER INST_ID NUMBER KTSSOSES RAW(4) KTSSOSNO NUMBER KTSSOTSN VARCHAR2(31) KTSSOCNT NUMBER KTSSOSEGT NUMBER KTSSOFNO NUMBER KTSSOBNO NUMBER KTSSOEXTS NUMBER KTSSOBLKS NUMBER KTSSORFNO NUMBER KTSSOOBJD NUMBER KTSSOOBJN NUMBER ##对应的dba_objects中的object id KTSSOTSNUM NUMBER |
附：X$表命名规则：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 |
** INTERNAL ONLY ** This is a summary list of X$ Table Definitions - Last revision was 7.3.2 The main purpose of this note is to show the naming conventions. [K]ernel Layer [2]-Phase Commit [G]lobal [T]ransaction [E]ntry X$K2GTE - Current 2PC tx X$K2GTE2 - Current 2PC tx [C]ache Layer [B]uffer Management Buffer [H]ash X$BH - Hash Table Buffer LRU Statistics X$KCBCBH - [C]urrent [B]uffers (buckets) - lru_statistics X$KCBRBH - [R]ecent [B]uffers (buckets) - lru_extended Buffer [WAIT]s X$KCBWAIT - Waits by block class X$KCBFWAIT - Waits by File [W]orking Sets - 7.3 or higher X$KCBWDS - Set [D]escriptors [C]ontrol File Management [C]ontrol [F]ile List - 7.0.16 or higher X$KCCCF - Control File Names & status [D]atabase [I]nformation X$KCCDI - Database Information Data [F]iles X$KCCFE - File [E]ntries ( from control file ) X$KCCFN - [F]ile [N]ames [L]og Files X$KCCLE - Log File [E]ntries X$KCCLH - Log [H]istory ( archive entries ) Thread Information X$KCCRT - [R]edo [T]hread Information [F]ile Management X$KCFIO - File [IO] Statistics [L]ock Manager Component ( LCK ) [H]ash and Bucket Tables - 7.0.15 to 7.1.1, and 7.2.0 or higher X$KCLFH - File [H]ash Table X$KCLFI - File Bucket Table X$LE - Lock [E]lements X$LE_STAT - Lock Conversion [STAT]istics X$KCLFX - Lock Element [F]ree list statistics - 7.3 or higher X$KCLLS - Per LCK free list statistics - 7.3 or higher X$KCLQN - [N]ame (hash) table statistics - 7.3 or higher [R]edo Component [M]edia recovery - kcra.h - 7.3 or higher X$KCRMF - [F]ile context X$KCRMT - [T]hread context X$KCRMX - Recovery Conte[X]t [F]ile read X$KCRFX - File Read Conte[X]t - 7.3 or higher Reco[V]ery Component [F]ile [H]eaders X$KCVFH - All file headers X$KCVFHMRR - Files with [M]edia [R]ecovery [R]equired X$KCVFHONL - [ONL]ine File headers [K]ompatibility Management - 7.1.1 or higher X$KCKCE - [C]ompatibility Segment [E]ntries X$KCKTY - Compatibility [TY]pes X$KCKFM - Compatibility [F]or[M]ats ( index into X$KCKCE ) [D]ata Layer Sequence [N]umber Component X$KDNCE - Sequence [C]ache [E]ntries - 7.2 or lower [S]equence Enqueues - common area for enqueue objects X$KDNSSC - [C]ache Enqueue Objects - 7.2 or lower X$KDNSSF - [F]lush Enqueue Objects - 7.2 or lower X$KDNST - Cache [ST]atistics - 7.2 or lower Inde[X] Block Component X$KDXHS - Index [H]i[S]togram X$KDXST - Index [ST]atistics [G]eneric Layer [H]eap Manager X$KGHLU - State (summary) of [L]R[U] heap(s) - defined in ksmh.h [I]nstantiation Manager [C]ursor [C]ache X$KGICC - Session statistics - defined in kqlf.h X$KGICS - System wide statistics - defined in kqlf.h [L]ibrary Cache Manager ( defined and mapped from kqlf ) Bind Variables X$KKSBV - Library Object [B]ind [V]ariables Object Cache X$KGLOB - All [OB]jects X$KGLTABLE - Filter for [TABLE]s X$KGLBODY - Filter for [BODY] ( packages ) X$KGLTRIGGER - Filter for [TRIGGER]s X$KGLINDEX - Filter for [INDEX]es X$KGLCLUSTER - Filter for [CLUSTER]s X$KGLCURSOR - Filter for [CURSOR]s Cache Dependency X$KGLDP - Object [D]e[P]endency table X$KGLRD - [R]ead only [D]ependency table - 7.3 or higher Object Locks X$KGLLK - Object [L]oc[K]s Object Names X$KGLNA - Object [NA]mes (sql text) X$KGLNA1 - Object [NA]mes (sql text) with newlines - 7.2.0 or higher Object Pins X$KGLPN - Object [P]i[N]s Cache Statistics X$KGLST - Library cache [ST]atistics Translation Table X$KGLTR - Address [TR]anslation Access Table X$KGLXS - Object Access Table Authorization Table - 7.1.5 or higher X$KGLAU - Object Authorization table Latch Cleanup - 7.0.15 or higher X$KGLLC - [L]atch [C]leanup for Cache/Pin Latches [K]ompile Layer [S]hared Objects X$KKSAI - Cursor [A]llocation [I]nformation - 7.3.2 or higher [L]oader [L]ibrary X$KLLCNT - [C]o[NT]rol Statistics X$KLLTAB - [TAB]le Statistics [M]ulti-Threaded Layer [C]ircuit component X$KMCQS - Current [Q]ueue [S]tate X$KMCVC - [V]irtual [C]ircuit state [M]onitor Server/dispatcher [D]ispatcher X$KMMDI - [D]ispatcher [I]nfo (status) X$KMMDP - [D]ispatcher Config ( [P]rotocol info ) [S]erver X$KMMSI - [S]erver [I]nfo ( status ) X$KMMSG - [SG]a info ( global statistics) X$KMMRD - [R]equest timing [D]istributions s[Q]l Version and Option Layer Kernel [V]ersions X$VERSION - Library versions Kernel [O]ptions - 7.1.3 or higher X$OPTION - Server Options [Q]uery Layer [D]ictionary Cache Management X$KQDPG - [PG]a row cache cursor statistics [F]ixed Tables/views Management X$KQFCO - Table [CO]lumn definitions X$KQFDT - [D]erived [T]ables X$KQFSZ - Kernel Data structure type [S]i[Z]es X$KQFTA - Fixed [TA]bles X$KQFVI - Fixed [VI]ews X$KQFVT - [V]iew [T]ext definition - 7.2.0 or higher [R]ow Cache Management X$KQRST - Cache [ST]atistics X$KQRPD - [P]arent Cache [D]efinition - 7.1.5 or higher X$KQRSD - [S]ubordinate Cache [D]efinition - 7.1.5 or higher [S]ervice Layer [B]ackground Management [D]etached Process X$KSBDD - Detached Process [D]efinition (info) X$KSBDP - Detached [P]rocess Descriptor (name) X$MESSAGES - Background Message table [I]nstance [M]anagement - 7.3 or higher X$KSIMAT - Instance [AT]tributes X$KSIMAV - [A]ttribute [V]alues for all instances X$KSIMSI - [S]erial and [I]nstance numbers [L]ock Management [E]vent Waits X$KSLED - Event [D]escriptors X$KSLEI - [I]nstance wide statistics since startup X$KSLES - Current [S]ession statistics [L]atches X$KSLLD - Latch [D]escriptor (name) X$KSLLT - Latch statistics [ + Child latches @ 7.3 or higher ] X$KSLLW - Latch context ( [W]here ) descriptors - 7.3 or higher X$KSLPO - Latch [PO]st statistics - 7.3 or higher X$KSLWSC- No[W]ait and [S]leep [C]ount stats by Context -7.3 or higher [M]emory Management [C]ontext areas X$KSMCX - E[X]tended statistics on usage - 7.3.1 or lower Heap Areas X$KSMSP - SGA Hea[P] X$KSMPP - [P]GA Hea[P] - 7.3.2 and above X$KSMUP - [U]GA Hea[P] - 7.3.2 and above X$KSMHP - Any [H]ea[P] - 7.3.2 and above X$KSMSPR- [S]hared [P]ool [R]eserved List - 7.1.5 or higher [L]east recently used shared pool chunks X$KSMLRU - LR[U] flushes from the shared pool [S]GA Objects X$KSMSD - Size [D]efinition for Fixed/Variable summary X$KSMSS - Statistics (lengths) of SGA objects SGA [MEM]ory X$KSMMEM - map of the entire SGA - 7.2.0 or higher X$KSMFSV - Addresses of [F]ixed [S]GA [V]ariables - 7.2.1 or higher [P]arameter Component X$KSPPI - [P]arameter [I]nfo ( Names ) X$KSPPCV - [C]urrent Session [V]alues - 7.3.2 or above X$KSPPSV - [S]ystem [V]alues - 7.3.2 or above En[Q]ueue Management X$KSQDN - Global [D]atabase [N]ame X$KSQEQ - [E]n[Q]ueue Object X$KSQRS - Enqueue [R]e[S]ource X$KSQST - Enqueue [S]tatistics by [T]ype [U]ser Management [C]ost X$KSUCF - Cost [F]unction (resource limit) [L]icence X$KSULL - Licence [L]imits [L]anguage Manager X$NLS_PARAMETERS - NLS parameters X$KSULV - NLS [V]alid Values - 7.1.2 or higher [MY] [ST]atistics X$KSUMYSTA - [MY] [ST]atisics (current session) [P]rocess Info X$KSUPL - Process (resource) [L]imits X$KSUPRLAT - [LAT]ch Holder X$KSUPR - Process object [R]esource X$KSURU - Resource [U]sage [S]tatistics X$KSUSD - [D]escriptors (statistic names) X$KSUSGSTA - [G]lobal [ST]atistics [SE]ssions X$KSUSECST - Session status for events X$KSUSESTA - Session [STA]tistics X$KSUSECON - [CON]nection Authentication - 7.2.1 or higher X$KSUSE - [SE]ssion Info X$KSUSIO - [S]ystem [IO] statistics per session [T]imer X$KSUTM - Ti[M]e in 1/100th seconds Instance [X] X$KSUXSINST - [INST]ance state [T]race management X$TRACE - Current traced events X$TRACES - All possible traces X$KSTEX - Code [EX]ecution - 7.2.1 or higher E[X]ecution Management Device/Node [A]ffinity - 7.3.2 and above X$KSXAFA - Current File/Node Affinity [T]ransaction Layer Table [A]ccess [D]efinition X$KTADM - D[M]L lock [C]ontrol Component X$KTCXB - Transaction O[B]ject [S]or[T] Segments - 7.3 or higher X$KTSTSSD - [S]ort [S]egment [D]escriptor - per tablespace statistics [T]ablespace X$KTTVS - [V]alid [S]aveundo [U]ndo X$KTURD - Inuse [D]escriptors X$KTUXE - Transaction [E]ntry (table) - 7.3.2 or above Performance Layer [V] - 7.0.16 or higher [I]nformation tables X$KVII - [I]nitialisation Instance parameters X$KVIS - [S]izes of structure elements X$KVIT - [T]ransitory Instance parameters Security Layer [Z] [D]ictionary Component X$KZDOS - [OS] roles [S]ecurity State X$KZSPR - Enabled [PR]ivileges X$KZSRO - Enabled [RO]les [R]emote Logins - 7.1.1 or higher X$KZSRT - [R]emote Password File [T]able entries E[X]ecution Layer Parallel Query (Execute [F]ast) - 7.1.1 or higher [P]rocess and Queue Manager Statistics - 7.1.3 or higher X$KXFPYS - S[YS]tem Statistics X$KXFPDP - [D]etached [P]rocess (slave) statistics X$KXFQSROW - Table [Q]ueue Statistics - 7.3.2 or higher [C]oordinator Component X$KXFPCST - Query [ST]atistics X$KXFPCMS - [M]essage [S]tatistics X$KXFPCDS - [D]equeue [S]tatistics [S]lave Component X$KXFPSST - Query [ST]atistics X$KXFPSMS - [M]essage [S]tatistics X$KXFPCDS - [D]equeue [S]tatistics [S]hared Cursor X$KXSBD - [B]ind [D]ata - 7.3.2 and above X$KXSCC - SQL [C]ursor [C]ache Data - 7.3.2 and above [N]etwork Layer - 7.0.15 or higher Network [CO]nnections X$UGANCO - Current [N]etwork [CO]nnections . |
2条评论
我是老宋，给你补充一个哈：
X$KGLLK——[K]ernel [G]eneric [L]ibrary Cache Manager object [L]oc[K]s
##主要用来查看library cache 的对象的锁
KGLLKHDL RAW(4)
##关联v$session_wait的p1raw列，表明library cache lock的锁地址。也对应于x$kglob的kglhdadr这一列。
KGLLKSNM RAW(4)
##关联v$session的sid
KGLLKUSE RAW(4)
##关联v$session的saddr这一列，即session的地址。
KGLNAOBJ RAW(4)
##包含了在librarky cache中的对象上执行命令的语句的前80个字符。
KGLLKMOD RAW(4)
##代表的锁”占用”模式，0-> ‘None’, 1->’Null’, 2-> ‘Share’, 3-> ‘Exclusive’,其它值->’Unknown’
KGLLKREP RAW(4)
##代表”请求”模式,0-> ‘None’, 1->’Null’, 2-> ‘Share’, 3-> ‘Exclusive’,其它值->’Unknown’,另一个角度说明0->lock的占有者,其它->锁的请求者。
re Rill：谢啦，老宋～～
看来最近对library cache lock/pin的研究很深嘛～～嘿嘿