| 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. |
| 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$kcbwds | kernel cache, buffer working descriptors | |
|
| 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$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. 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 |
| 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. (see Ref; see also v$object_dependency, but that doesn't show relation between PL/SQL block and its contents) |
| 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. select a.*, substr(utl_raw.cast_to_varchar2(ksmmmval),1,20) from x$ksmmem a where rownum < 30. If you see "sga" and "heap" in the last column, it's big-endian; if you see "ags" and "paeh", it's little-endian.-->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 4 (or whatever the gap is between two addr's), possibly plus some offset. |
| 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$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$message | (background process) messages | This records (possibly) all actions each background process can do. |
| x$trace | | Beginning with 9i, x$trace records event tracing info. 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. These entries linger even after shared pool flush (or global context flush in 10g). |