Oracle X$ Tables
| 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 |
Warning The following list is just speculation; no guarantee of accuracy.
Oracle x$ "tables" are instance or session memory structures presented as tables and serve as base tables of most v$ views. Entries in the following list are added one at a time in the course of my study of Oracle internals by reading books and online materials (particularly at ixora), through my own lab test, by interpreting the text in v$fixed_view_definition if available, or because other nice folks email me. Obviously there're much more tables in x$kqfdt and x$kqfta than listed below. But generally I omit those whose usage is not interesting or its meaning is too obvious in v$fixed_view_definition.
Names of most x$ tables begin with x$k. The letter after "k" indicates what kernel layer this data structure belongs in. Check Chapter 1 of Steve Adams' book Oracle8i Internal Services to find out the functionality of each layer. Usually that knowledge can give you a hint at what an Oracle internal error is about. (If the letter "k" is preceded by "s", as in skgxp, then it's an OSD (operating system dependent) function or variable.) Letters after the first two represent sublayers, such as "l" in kgl means library cache. These are not easy to guess. Note:175982.1 has an extensive list of codes and is very informative.
| Table Name | Acronym Expanded | Comments |
| x$bh | buffer header (buffer hash according to J. Morle) | The most common use of this table is to find the object and the file# and block# of its header when there's high cache buffers chains latch contention: select obj, dbarfil, dbablk from x$bh a, v$latch_children b where a.hladdr = b.addr for the said latch (whose sleeps you think are too high). You can also use this table to see if a specific buffer has too many clones: select dbarfil, dbablk, count(*) from x$bh group by dbarfil, dbablk having count(*) > 2. Note that obj column matches dba_objects.data_object_id, not object_id. For performance reason, don't merge dba_extents with the query of x$bh that has a group by, unless you use in-line view and no_merge hint (see J. Lewis Practical Oracle8i, p.215) The tch column, touch count, records how many times a particular buffer has been accessed. Its flag column is explained by J. Lewis; explanation of state, mode and indx can be found in Anjo Kolk's paper. Tim is time the buffer touch happened (276392.996). Lru_flag is about the buffer's hot/cold feature (Ref and 221860.999); 8 is often used to find hot blocks. |
| x$k2gte | kernel 2-phase commit, global transaction entry | Mark Bobak's query (originally in Metalink forum thread 524821.994, where he further attributed authorship) uses this table to find sessions coming from or going to a remote database; in short, x$k2gte.k2gtdses matches v$session.saddr, .k2gtdxcb matches v$transaction.addr. It's more robust than this query, and better than checking for DX locks for outgoing sessions (since a DX lock only shows up in v$lock for the current distributed transaction session). |
| x$kcbbf | kernel cache, buffer ?? | Ref1 ("_db_handles") |
| x$kcbfwait | kernel cache, buffer file wait | A commonly used query breaks down the contents of v$waitstat into per-datafile statistics: select count, time, name from v$datafile df, x$kcbfwait fw where fw.indx+1 = df.file# |
| x$kcbsw | kernel cache, buffer statistics why | Note:34405.1 (select kcbwhdes, why0+why1+why2 "Gets", "OTHER_WAIT" from x$kcbsw s, x$kcbwh w where s.indx=w.indx and s."OTHER_WAIT">0 order by 3), Ref1 ("statistics about the way these [x$kcbwh] functions have been used") |
| x$kcbwait | kernel cache, buffer wait | |
| x$kcbwbpd | kernel cache, buffer workingset buffer pool descriptor | See 183770.999 for relationship to x$bh and x$kcbwds. Some people use this query to find how many blocks of a segment are in each buffer pool: select decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',7,'16K SUBCACHE',8,'32K SUBCACHE','UNKNOWN') subcache, bh.object_name,bh.blocks from x$kcbwds ds, x$kcbwbpd pd, (select /*+ use_hash(x) */ set_ds, o.name object_name, count(*) BLOCKS from obj$ o, x$bh x where o.dataobj#=x.obj and x.state!=0 and o.owner#!=0 and o.name='&mytable' group by set_ds, o.name) bh where ds.set_id>=pd.bp_lo_sid and ds.set_id<=pd.bp_hi_sid and pd.bp_size!=0 and ds.addr=bh.set_ds |
| x$kcbwds | kernel cache, buffer working data set | See above. |
| x$kcbwh | kernel cache, buffer ?? | See x$kcbsw for SQL. Ref1 ("different functions that may be used to perform different types of logical I/O"), Ref2 |
| x$kcccf | kernel cache, controlfilemanagement control file | In 10gR1, to find controlfile size as viewed at OS level but from inside Oracle, select cfnam, (cffsz+1)*cfbsz from x$kcccf. cfbsz is the controlfile log block size; should report the same as the command dbfsize controlfile ($ORACLE_HOME/bin/dbfsize is available on UNIX, regardless Oracle version.) In 10gR2, block size and file size are both in v$controlfile although Reference manual misses them. |
| x$kcccp | kernel cache, controlfile checkpoint progress | S. Adams and K Gopalakrishnan use this view to find how much the current redo log is filled. |
| x$kccdi | kernel cache, controlfilemanagement database information | |
| x$kccle | kernel cache, controlfile logfile entry | lebsz may be used to show redo logfile block size, usually 512; should report the same as the command dbfsize redologfile ($ORACLE_HOME/bin/dbfsize is available on UNIX only) |
| x$kcfio | kernel cache, file I/O | |
| x$kclcrst | kernel cache, lock, consistent read statistics | base table of v$cr_block_server or v$bsp, used to troubleshoot global cache cr requests |
| x$kclfh | kernel cache, lock file header | |
| x$kclfi | kernel cache, lock file index | |
| x$kcluh | kernel cache, lock undo header | |
| x$kclui | kernel cache, lock undo index | |
| x$kcrfx | kernel cache, redo file context | "columns bfs (buffer size) and bsz (block size). Dividing bfs by bsz gives mxr (the maximum number of blocks to read size)" (from Anjo Kolk's paper) |
| x$kdxst | kernel data, index status | used in catalog.sql to create index_stats |
| x$kdxhs | kernel data, index histogram | used in catalog.sql to create index_histogram |
| x$kewrtb | kernel ?, workload repository tables | See Note:555124.1 |
| x$kghlu | kernel generic, heap LRUs | |
| x$kglcursor | kernel generic, librarycache cursor | Base table for v$sql, v$sqlarea. Fixed view based on x$kglob according to x$kqfdt. See Note 1 or x$kglob for more details. One use of this table is for finding partially parsed SQLs because they cause parse failures (viewable in v$sysstat or v$sesstat). Their kglobt02 (command type) is 0, kglobt09 (child number) is 65535 for the child, SQL text length is cut to 20 chars, kglobt17 and kglobt18 (parsing and user schema) are 0 or 2147483644 (for 32-bit Oracle) depending on if it's parent or child, and obviously miss heap 6 (cursor body). Find them by select kglnaobj, kglnatim, kglobts0, kglnahsh from x$kglcursor where kglobt02 = 0 (kglobts0 is module; you can further restrict by kglnatim i.e. first_load_time). |
| x$kgllk | kernel generic, librarycache lock | Used in catblock.sql to build dba_kgllock. kgllkuse or kgllkses maps to v$session.saddr, kgllkpnc call pin, kgllkpns session pin, kgllkmod lock held (0: no lock; 1: null; 2: shared; 3: exclusive), kgllkflg (allegedly 8 for pre-10g or 2048 for 10g meaning SQL being run, Ref), kgllkspn savepoint. If you get library cache lock or pin wait, kgllkhdl matches v$session_wait.p1raw (handle address), and kglnaobj is the first 80 characters of the object name. Note:122793.1 has this SQL for our convenience: select * from x$kgllk lock_a where kgllkreq = 0 and exists (select lock_b. kgllkhdl from x$kgllk lock_b where kgllkses = '&saddr_from_v$session' /* blocked session */ and lock_a.kgllkhdl = lock_b.kgllkhdl and kgllkreq > 0). Kgllkadr column is shown in event 10270 trace files to find SQLs in session cursor cache (Ref) |
| x$kglob | kernel generic, librarycache object | To find library cache object for wait events library cache pin or lock and pipe get or put, match kglhdadr with v$session_wait.p1raw. kglhdflg is partially explained in Note:311689.1 (for permanent keeping). kglhddmk may be data object load mask; can be used to identify the number of the loaded heap, counted from 0 (see comment of 06/12/01 in Bug:1164709). Steve Adams' objects_on_hot_latches.sql finds the way Oracle links a library cache object (based on kglnahsh) to a specific library cache child latch. x$kglob, and in case of cursors x$kglcursor too, can be used to find library cache objects that are partially built therefore not visible in v$sql(XXX), v$open_cursor, v$object_dependency. (Try typing select *; and enter, then check these views!) |
| x$kglpn | kernel generic, librarycache pin | used in catblock.sql to build dba_kgllock. Some columns are simiarly explained for x$kgllk. |
| x$kglrd | kernel generic, librarycache readonly dependency | kglnacnm is PL/SQL program unit or anonymous block while kglnadnm is the individual SQLs inside the PL/SQL unit. Ref; this may be the way to differentiate between user recursive SQLs from system-generated recursive SQLs, which are not code rewritten from user supplied SQL text. (See also v$object_dependency, but that doesn't show relation between PL/SQL block and its contents.) In 11g, v$sql.program_id may be used to tie the constituent SQL to its containing PL/SQL stored object (not anonymous block). |
| x$kglst | kernel generic, librarycache status | |
| x$kqfco | kernel query, fixed table columns | x$kqfco.kqfcotab=x$kqfta.indx |
| x$kqfta | kernel query, fixed table | |
| x$kqfdt | kernel query, fixed derived table | acronym explained by Julian Dyke: it contains x$kglcursor, x$kgltable etc. which are based on x$kglob; effectively these are views of other x$ tables, but Oracle couldn't call them views because they already had x$kqfvi |
| x$kqfp | kernel query, fixed procedure | used in catprc.sql to build disk_and_fixed_objects view |
| x$kqfsz | kernel query, fixed size (size of fixed objects in current version of Oracle) | |
| x$kqfvi | kernel query, fixed view | |
| x$kqfvt | kernel query, fixed view table (how fixed view is built on fixed tables) | |
| x$ksled, x$kslei, x$ksles | kernel service, event definition, events for instance, events for session, respectively ("l" probably means "lock") | x$kslpo | kernel service, latch posting | Note:653299 says it "tracks which function is posting smon". Ksllwnam column (the part before semicolon if it exists) can match v$latch_misses.location to identify the latch that uses this function. |
| x$ksmfs | kernel service, memory fixed SGA | also contains db_block_buffers and log_buffer sizes for some reason |
| x$ksmfsv | kernel service, memory fixed SGA variables | detailing fixed SGA: select a.ksmfsnam, a.ksmfstyp, a.ksmfssiz, b.ksmmmval from x$ksmfsv a, x$ksmmem b where a.ksmfsadr = b.addr and a.ksmfsnam like... (Ref. p.82, Oracle Internal Services). For a latch, get ksmfsnam by matching x$ksmfsv.ksmfadr with x$kslld.kslldadr. You can see SGA parameters in ksmfsnam column and get their values with oradebug dumpvar varname or all values with oradebug dumpsga |
| x$ksmhp | kernel service, memory heap | S. Adams, "What it returns depends on which heap descriptor you join to it. It is effectively a function returning the contents of an arbitrary heap that takes the heap descriptor as its argument." |
| x$ksmjs | kernel service, memory java_pool summary | |
| x$ksmlru | kernel service, memory LRU | Refer to Metalink Notes 61623.1 and 43600.1 for details. Note that query on this table can only be done once; subsequent query returns no rows unless large chunk shared pool allocations happened in the interim. |
| x$ksmls | kernel service, memory large_pool summary | |
| x$ksmmem | kernel service, memory | Entire SGA memory map. You can find your database version by select ksmmmval from x$ksmmem where indx = 2 (if it's 64-bit Oracle, try 1), regardless machine architecture endian-ness. Note that the 4 bytes containing the version are delimited as XX.X.XX.X.XX so 09200300 is 9.2.0.3.0. Due to memory guard pages, you can only select from x$ksmmem specifying rownum < some number or indx = some value; otherwise the session may hang or throws ORA-3113 (Windows doesn't seem to have this problem). Indx is SGA index, i.e. SGA address minus sgabeg (which is x$ksmmem.addr where indx = 0), divided by architecture word size (4 for 32-bit, 8 for 64-bit machines); e.g., the value stored at address 0x9CD3F5D0 on a 64-bit machine (which is really address 0x000000009CD3F5B0) whose sgabeg is 0x60000000 can be calculated as: select (to_number('9CD3F5D0','xxxxxxxx') - to_number('60000000','xxxxxxxx')) /8 from dual; select ksmmmval from x$ksmmem where indx = 127565498; |
| x$ksmpp | kernel service, memory pga heap | PGA heap (variable area) |
| x$ksmsd | kernel service, memory sga definition | |
| x$ksmsp | kernel service, memory sga heap | The 3rd argument of ORA-4031 tells you which section of shared (or java or large) pool is short of memory. It matches x$ksmsp.ksmchcom (or v$sgastat.name). |
| x$ksmspr | kernel service, memory shared pool reserved | |
| x$ksmss | kernel service, memory shared_pool summary | The 3rd argument of ORA-4031 tells you which section of shared (or java or large) pool is short of memory. It matches x$ksmss.ksmssnam (or v$sgastat.name). |
| x$ksmup | kernel service, memory uga heap | UGA heap (variable area) |
| x$ksppcv | kernel service, parameter, current (session) value | Base table of v$parameter and v$parameter2. See comments on x$ksppi. |
| x$ksppi | kernel service, parameter, parameter info | Base table of v$parameter, v$system_parameter and v$system_parameter2. Often used to see undocumented parameters: 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 | kernel service, parameter, system value | Base table of v$system_parameter and v$system_parameter2. See comments on x$ksppi. |
| x$ksqeq | kernel service, enqueue en-queue | |
| x$ksqrs | kernel service, enqueue resource | "shows all outstanding enqueues with an additional flag. It basically shows the same information as the v$lock table." from Note1, which also gives the meanings of the flags. |
| x$ksqst | kernel service, enqueue_management statistics types | Acronym explained by K Gopalakrishnan. You can find how many times each type of enqueue lock has been taken since instance startup by select * from x$ksqst where ksqstget > 0 or in 9i select * from x$ksqst where ksqstsgt > 0 or ksqstfgt > 0. But v$enqueue_stat in 9i can also be used instead. |
| x$ksulv | kernel service, user locale value | |
| x$ksulop | kernel service, user long operation | |
| x$ksupr | kernel service, user process | |
| x$ksuse | kernel service, user session | |
| x$kswsastab | kernel service, workload [management] service, | Base table of v$services and a few other service-related views. v$services may need x$kswsastab.kswsastabpflg=0 restriction; otherwise stopped services linger in the view till instance bounce. |
| x$ktcxb | kernel transaction, control object | Base table of v$transaction. 4 bits of ktcxbflg column, exposed as v$transaction.flag, are explained in v$fixed_view_definition. Metalink 238763.996 explains the bit for isolation level. Since v$transaction is empty without a transaction, you can directly query x$ktcxb to find sessions with serializable isolation level: select * from v$session where taddr in (select ktcxbxba from x$ktcxb where bitand(ktcxbflg,268435456) <> 0). Other flags not shown in v$fixed_view_definition are: 1 read write and read committed, 4 read only. |
| x$ktfbfe | kernel transaction, file bitmap free extent | Free extent bitmap in file header for LMT (equivalent to fet$ in DMT); check dba_free_space view definition |
| x$ktfbhc | kernel transaction, file bitmap ? ? | Summarizes free space with one row per datafile (Ref); check dba_data_files or dba_temp_files view definition |
| x$ktfbue | kernel transaction, file bitmap used extent | Used extent bitmap in file header for LMT (equivalent to uet$ in DMT) |
| x$ktuxe | kernel transaction, undo transaction entry | Steve Adams says, you "get the SCN of the most recently committed (local) transaction" with select max(ktuxescnw * power(2, 32) + ktuxescnb) from x$ktuxe |
| x$kxfpsds | kernel execution, fast process slave dequeue statistics | Current list of reasons for parallel execution dequeuing, as explained for wait event "parallel query dequeue wait" in Anjo Kolk's paper. |
| x$kzsprv | kernel security, session privilege | Session-specific. Base table for v$enabledprivilege, which is base table of session_privs |
| x$kzsro | kernel security, session role | used in many SQL scripts in ?/rdbms/admin |
| x$le | lock element | To find the buffer header in the cache, select a.* from x$bh a, x$le b where a.le_addr = b.addr (from Anjo Kolk's paper) |
| x$le_stat | lock element status | |
| x$messages | (background process) messages | This records (possibly) all actions each background process can do. |
| x$targetrba | target RBA | Ref |
| x$trace | Beginning with 9i, x$trace records event tracing info. 10g RAC bdump/cdmp_time directory has trw files that contain the same info (the trace file seems to have columns TimeInMicroSec:?, OraclePid, SID, event, OpCode, TraceData). select event, count(*) from x$trace group by event shows what events are enabled internally (not shown in v$parameter). oerr ora eventID on UNIX shows the event name. RAC databases should have GES and GCS related events set. select pid, count(*) from x$trace group by pid shows how many events have been trapped by each oracle process (including those that exited). select sid, pid, count(*) from x$trace where (sid, pid) in (select sid, pid from v$session s, v$process p where s.paddr = p.addr) group by sid, pid order by 1, 2 shows the numbers for each currently existing session (I think without the where clause, exited sessions would be included). select event, op, time, seq#, data from x$trace where sid = &sid and pid = &pid order by time shows traced events for a session in question. | |
| x$uganco | user global area, network connection | Base table of v$dblink. Since it's about UGA, each session has different content. After you end your distributed transactions (which includes distributed queries) and close database links, v$dblink no longer shows the entries. But x$uganco still has them, with ncoflg set to 8320 and hstflg set to 0. Unfortunately this is not very useful because you can't see the UGA content from a different session. |
These columns are not exposed in v$sql based on comparison in v$fixed_view_definition in 9.2.0.1 (for column match between x$kglcursor and v$sql, view browser source): kglhdamk: always 0; kglhddmk: 0,1,65,253,...; some kind of masks (kglhdkmk, keep mask, is exposed as v$sql.kept_versions); kglhdexc: executions but not used any more; see documentation for v$db_object_cache.executions which is x$kglob.kglhdexc; 9i uses kglobt05 and 10g uses kglobt48; but kglobt05 and kglobt48 may be 0 perhaps on heap 6 flush(?) while kglhdexc keeps the old value? kglhdflg: (Ref) in my 9i DB: SQL> select to_char(kglhdflg,'xxxxxxxx'), count(*) from x$kglcursor group by to_char(kglhdflg,'xxxxxxxx'); TO_CHAR(K COUNT(*) --------- ---------- 10010000 193 10010001 39 12010000 49 50010000 95 kglhdnsp, kglhdobj, kglhdpmd: parent handle namespace, object, pin mode; kglnadlk: (DB link, Ref); kglnaown kglnaptm: previous time; probably deprecated, all null; Ref; kglobflg: Ref; kglobhd0: can be used "to perform the HEAPDUMP_ADDR dumps" (Bug:2247763) kglobhd1 kglobhd2 kglobhd3 kglobhd4 kglobhd5 kglobhd6 kglobhd7 kglobhs7 kglobpc0 kglobt22 kglobt23 kglobt24 kglobt25 kglobt26 kglobt27 kglobtl0 kglobtl1 kglobtn1 kglobtn2 kglobtn3 kglobtn4 kglobtn5 kglobtyp
Rama Velpuri
Julian Dyke
Frank Naude
To my Computer Page