The X$ tables that are too obvious, too obscure, or too uninteresting are not included here.
| Table Name |
Guessed Acronym |
Comments |
| x$activeckpt |
active checkpoint |
Ckpt_type 2 for MR checkpoint (Ref), 3 for interval (Ref) or thread checkpoint (Ref), 7 for incremental checkpoint, 10 for object reuse/truncate checkpoint, 11 for object checkpoint (Ref). |
| x$bh |
buffer header |
This table is commonly used 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 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 (some unused columns are later used; e.g. bit 29 means plugged_from_foreign_db in 12c); explanation of state, mode and indx can be found in Anjo Kolk's paper. Tim is time the buffer touch happened (Note 1). Lru_flag is about the buffer's position on LRU lists (Ref and 136312.1); 2 moved_to_tail, 4 on_auxiliary_list (auxliary LRU), 8 hot_buffer (on hot end of main LRU), and numbers can be added e.g. 6=2+4. |
| x$ckptbuf |
checkpoint buffer (queue) |
Lists the buffers on the checkpoint queue. Immediately after a full checkpoint, the buffers with non-zero buf_ptr and buf_dbablk should go down. |
| x$con |
container |
Base table of v$containers and v$pdbs. Some bits in column dict_flags are not exposed, e.g. bit 11 for force logging of PDBs: select decode(bitand(dict_flags,1024),0,'NO',1024,'YES') force_logging from x$con; |
| x$dbgalertext |
debug alert extented |
One use is to find old alert.log text long after you recycled the physical file: select originating_timestamp, message_text from x$dbgalertext. The message_id and message_group columns are also interesting and are not available in alert.log. |
| x$dbglogext |
debug log extended |
12c |
| x$dbgricx, x$dbgrifx, x$dbgrikx, x$dbgripx |
debug repository (Automatic Diagnostic Repository) incident code?|f?|key|problem ? |
You can quickly summarize what kind of errors the database has had: select error_facility||'-'||error_number, count(*) from x$dbgricx group by error_facility||'-'||error_number order by 2, and optionally restrict to a certain time range. You can summarize on a more granular level, such as shared pool vs large pool on error_arg2 in case of ORA-4031, and find records of these errors in v$diag_incident or (undocumented) v$diag_diagv_incident. In any case, you may find this easier than grep alert.log. For each incident, its session info is in x$dbgrikx. |
| x$dbkece |
debug kernel error, critical error |
Base table of undocumented v$diag_critical_error but includes facility dbge (Diagnostic Data Extractor or dde) |
| x$dbkefefc |
debug kernel error, fatal error flood control |
Rules for flood control on too many fatal errors. |
| x$dglparam |
data guard logical parameters |
Base table of dba_logstdby_parameters but includes invisible parameters. |
| x$diag_alert_ext |
diagnostics alert extended |
Base table of v$diag_alert_ext. Same as x$dbgalertext but has more lines, slower to query |
| x$diag_hm_run, x$diag_vhm_run |
diagnostics health monitor runs |
Base table of undocumented v$diag_(v)hm_run. Health monitor job records. Maybe complementary to v$hm_run? |
| x$diag_ips_configuration |
diagnostics incident packaging service configuration |
Base table of v$diag_ips_configuration. Some ADR IPS related config info. Like a few other v$diag* (or x$diag*) tables, some columns such as adr_home, name, can't be exactly matched as if there're trailing characters. CTAS to create a regular table against which you query, or use subquery factoring with /*+materialize*/ hint. |
| x$dnfs_meta |
dNFS metadata |
Some metadata related to dNFS, SGA memory, message timeout, ping timeout, etc. |
| x$dra_failure |
data recovery advisor failures |
DRA failure names and descriptions. |
| x$drm_history, x$drm_history_stats |
dynamic remastering history, stats |
History of RAC DRM and stats. Parent_key is object_id. If an object is remastered to another node (new_master) too frequently, consider partitioning the app sessions. In 12.1.0.2, there's also x$drm_wait_stats. |
| x$ipcor_topo_domain, x$ipcor_topo_ndev |
ipc core topology domain|name device |
12cR2 RAC. Interconnect interface protocol (e.g. ethernet), names, MAC. No IP addresses, which are in v$cluster_interconnects and v$configured_interconnects, x$ksipcip_kggpnp and x$ksipcip. |
| x$jskjobq |
job scheduling, job queue |
Internal job queue. Job_oid is object_id in dba_objects. If you must query this table, exit the session as soon as you're done with your work because your session after the query holds an exclusive JS lock, which will block CJQ process! Rollback or commit won't release the lock. |
x$k2gte,
x$k2gte2 |
kernel 2-phase commit, global transaction entry |
See Note:104420.1. Find sessions coming from or going to a remote database; in short, x$k2gte.k2gtdses matches v$session.saddr, .k2gtdxcb matches v$transaction.addr.
select /*+ ordered */
substr(s.ksusemnm,1,10)||'-'|| substr(s.ksusepid,1,10) origin,
substr(g.k2gtitid_ora,1,35) gtxid,
substr(s.indx,1,4)||'.'|| substr(s.ksuseser,1,5) lsession,
s.ksuudlna username,
substr(decode(bitand(ksuseidl,11), 1,'ACTIVE', 0, decode( bitand(ksuseflg,4096) , 0,'INACTIVE','CACHED'),
2,'SNIPED', 3,'SNIPED', 'KILLED'),1,1) status,
e.kslednam waiting
from x$k2gte g, x$ktcxb t, x$ksuse s, x$ksled e
where g.k2gtdxcb=t.ktcxbxba
and g.k2gtdses=t.ktcxbses
and s.addr=g.k2gtdses
and e.indx=s.ksuseopc;
It's 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$k2gte2 is the same as x$k2gte except on k2gtetyp which may show 2 for 'TIGHTLY COUPLED' instead of 0 for 'FREE'. One use of x$k2gte[2] is the clearly translated global transaction ID in k2gtitid_ora as opposed to the hex numbers in v$global_transaction.globalid. |
| x$kbrpstat |
kernel backup recovery process(?) statistics |
12c. RMAN related |
| x$kcbbes |
kernel cache, buffer ? |
Check incremental checkpoints (259586.1) |
| x$kcbbf |
kernel cache, buffer buffer_handles |
Jonathan Lewis ("_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 name, count, time from v$datafile df, x$kcbfwait fw where fw.indx+1 = df.file# |
| x$kcbkpfs |
kernel cache, buffer ckpt prefetch statistics |
Tanel Poder |
| x$kcbkwrl |
kernel cache, buffer write list |
each row for the write list of one DBWR |
| x$kcbldrhist |
kernel cache, buffer load direct read history |
|
| x$kcbobh |
kernel cache, buffer, objectqueue buffer header |
10g and up. Tanel Poder |
| x$kcboqh |
kernel cache, buffer, object queue header |
See above |
| x$kcbpdbrm |
kernel cache, buffer, PDB resource metric |
12c. Column value is actual buffer cache usage in db_block_size, the same (except for unit) as buffer_cache_bytes of v$rsrc_pdb or bc_size_kgskmeminfo of x$kgskmeminfo. This table also gives lower (column minimum, bound by db_cache_size if defined) and upper (column maximum) limits. |
| 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 (That works for 10g only; for newer versions, see x$kcbuwhy); Ref1 ("statistics about the way these [x$kcbwh] functions have been used") |
| x$kcbuwhy |
kernel cache, buffer why |
For 11g and up, select kcbwhdes, why0+why1+why2 "Gets", "OTHER_WAIT" from x$kcbsw s, x$kcbwh w, x$kcbuwhy w2 where s.indx=w.indx and w.indx=w2.indx and s."OTHER_WAIT">0 |
| 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 workingset descriptors |
See above. Also see Ref1, Ref2, Ref3. Total row count in this table is _db_block_lru_latches, although only db_writer_processes rows have real numbers. Full list and descriptions of the columns are in the note section of Ref4 (search for "Xuan Bui of Oracle France") or in Ref5. |
| x$kcbwh |
kernel cache, buffer where/why |
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. Eygle studied instance heartbeat, column cphbt. |
| 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$kccnrs, x$kccrsp |
kernel cache, controlfile non-guaranteed restorepoint; kernel cache, controlfile restore point |
Base tables of v$restore_point, for non-guaranteed and guaranteed restore points. Retain records of them after they were dropped |
| x$kcfis* |
kernel cache, file intelligent scan |
Exadata smart scan related. Note that views for cell servers per se are x$kxdcm_* (kernel Exadata cell module) plus x$cell_name |
| x$kclcrst |
kernel cache, (RAC) 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, (RAC) lock file hashtable |
|
| x$kclfi |
kernel cache, (RAC) lock file index |
|
| x$kclfx |
kernel cache, (RAC) lock (element) freelist statistics |
See Ref1, Ref2, 1492990.1. If lwm is too low, you may see 'gc freelist' wait. |
| x$kcluh |
kernel cache, (RAC) lock undo header |
|
| x$kclui |
kernel cache, (RAC) lock undo index |
|
| x$kcmscn |
kernel cache, maximum SCN |
Cur_scn is the same as v$database.current_scn. Cur_max_scn should be 16384*seconds since 1988 (1376995.1). This view may be related to the SCN headroom problem. |
| x$kcnt |
kernel cache, nologging transaction |
19c. Base table of v$nologging_standby_txn. Nologging standby is a feature new in 18c. |
| x$kcrfstrand |
kernel cache, redo file strand |
10g and up. Info about redo strands. Non-zero pvt_strand_state_kcrfa_cln (and strand_num_ordinal_kcrfa_cln=3735928559 or DEADBEEF in hex) means a transaction is using this private strand. (Private strands may be disabled in RAC or if supplemental logging is on, but multistrand redo is still used.) Strand_size_kcrfa is the strand size (meaningful only if last_buf_kcrfa!='00'; Ref). Also see 18164614, Ref. |
| 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$kcrrlns |
kernel cache, recovery process LNS |
Related to LNS (redo transport) processes, NSSn (sync) and NSAn (async). |
| x$kd_column_statistics |
kernel data, column statistics |
12cR2. Set _column_level_stats to on to get the stats. Exposed to the undocumented v$column_statistics. |
| 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 server (manageability), workload repository tables |
See Note:555124.1 |
| x$kfdat |
kernel file, disk allocation table? |
Only populated in ASM instance. See Note:351117.1 and Steve Shaw and Julian Dyke Pro Oracle Database 10g RAC on Linux , pp.232-3. Column v_kfdat is 'V' for allocated and 'F' for free. For most ASM-related x$ tables, read Luca Canali. |
| x$kffxp |
kernel file, file extent map |
Only populated in ASM instance. You can check how many extents are allocated for each datafile on which disk, e.g. select a.name, d.path, d.group_number, d.disk_number, count(*)
from v$asm_alias a, v$asm_disk d, v$asm_file f, x$kffxp x
where a.group_number = x.group_kffxp and a.file_number = x.number_kffxp
and d.group_number = x.group_kffxp and d.disk_number = x.disk_kffxp
and f.group_number = a.group_number and f.file_number = a.file_number
and f.type = 'DATAFILE'
group by a.name, d.path, d.group_number, d.disk_number, f.bytes
order by 1; |
| x$kfklib |
kernel file, ? library |
You can tell from inside ASM instance whether you're using ASMLib and its version. |
| x$kghlu |
kernel generic, heap LRUs |
Some columns are explained here. |
| x$kglcursor |
kernel generic, librarycache cursor |
Base table for v$sql, v$sqlarea. Fixed view based on x$kglob according to x$kqfdt. One use of this table is to find 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; 256 for broken kgl lock in 10g or 1 in 9i, 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). X$kgllk.kglhdpar matches x$kglob.kglhdpar if there's a KGL lock on the object. Also see LibraryCachePinLockHowToFindBlocker.txt. If the blocker is in an instance other than that of the blocked session in a RAC database, use object name (kglnaobj and first half of v$session.p3raw) to find the blocker. |
| 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.p1raw. Kglobt03 is sql_id. 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. Kglobhd[0-7] is heap descriptor address and kglobhs[0-7] is its size; can join to x$ksmhp.ksmchds to see heap components. |
| x$kglpn |
kernel generic, librarycache pin |
Used in catblock.sql to build dba_kgllock. Some columns are simiarly explained for x$kgllk. Newer versions of Oracle use mutex in place of pin and this table won't have an entry for it although x$kglob.kglhdpmd will still have a non-zero value. |
| x$kglrd |
kernel generic, librarycache readonly dependency |
kglnacnm (container name?) is PL/SQL program unit or anonymous block while kglnadnm (dependent name?) is the individual SQLs inside the PL/SQL unit. Ref; this may be the way to differentiate between user recursive SQLs (code in PL/SQL, trigger, etc.) from system-generated recursive SQLs (data dictionary check etc.). (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 statistics |
Base table of v$librarycache. Unexposed column kglstidn can be used as namespace in dbms_shared_pool.purge. (Ref) |
| x$kgltr |
kernel generic, librarycache translation |
Maps synonym translation from original (kgltrorg) to final (kgltrfnl) address, All 3 address columns map to x$kglob.kglhdadr. Example. |
| x$kgskvft |
kernel generic, service, ?(resource manager) ? fixed table |
Base table of v$blocking_quiesce. If the blocking session is not in SYS_GROUP consumer group according to v$rsrc_session_info, v$blocking_quiesce ignores it. Workaround is to directly query x$kgskvft. (Ref; Bug 7832504) |
| x$kjxm |
kernel RAC cross-instance messaging |
Stats about messages sent and received on RAC instances (not about data buffers which would be in x$kclcrst i.e. v$cr_block_server a.k.a v$bsp). The kjxmname column is interesting as well as the stats. |
| x$kjznhangs, x$kjznhangses |
kernel RAC diag node hang session |
Base tables of v$hang_info and v$hang_session_info so column names can be deciphered. Retain info after the hang. |
| x$kkspcib |
kernel compilation shared (cursor) parse cursor ?? |
New in 12.1.0.2. Cursor to session mapping, after v$open_cursor no longer has this info? KKSCSPHD: parent addr, KKSCSHDL: child addr |
| x$kmgsct |
kernel memory, sga space ?? |
Base table of v$sga_dynamic_components, v$sga_current_resize_ops etc., probably used to be named x$ksmgst and x$ksmgsc in 9i. |
| x$kmgstfr |
kernel memory, sga space transfer ?? |
Maybe another way of representing SGA and memory components resizing operations. Ts: time; startaddr and end: addresses before and after resizing; donor and receiver: x$kmgsct.grantype |
| x$knstmvr |
kernel replication, statistics materialized view refresh |
Base table of v$mvrefresh. Stores MV refresh history info, such as session SID and serial#. Un-exposed columns reftype_knstmvr, groupstate_knstmvr and total_* are useful; see the query in Note:258021.1. |
| x$kpoxft |
kernel programmatic oracle, ? fixed table |
19c. Column kpoxftcldrv shows client driver and library versions and character sets for the clients that recently logged onto the server. In case of RAC, this info is instance-specific. |
| x$kqdpg |
kernel query, dictionary PGA |
Row cache cursor statistics, columns explained in "How can you tune it?" section of Tuning the _row_cache_cursors Parameter. Note this is PGA. Need to dump another process's PGA to view it. |
| x$kqfco |
kernel query, fixed table columns |
One use is to find all fixed tables given a column name, e.g. select kqftanam, kqfconam, kqfcoidx from x$kqfco c, x$kqfta t where t.indx=c.kqfcotab and kqfconam='KGLHDADR', or like part of the column name. If kqfcoidx is 0, the column is not indexed. |
| x$kqfdt |
kernel query, fixed derived table |
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 package |
Used in catprc.sql to build disk_and_fixed_objects view. Each object has two rows, one package and one package body. |
| x$kqfsz |
kernel query, fixed size (size of fixed objects in current version of Oracle) |
|
| x$kqfta |
kernel query, fixed table |
Base table of v$fixed_table, whose object_id (indx of x$kqfta) matches obj# of tab_stats$, the table dbms_stats.gather_fixed_objects_stats inserts stats into. |
| x$kqfvi |
kernel query, fixed view |
|
| x$kqlfsqce |
kernel query, librarycache fixedtable sql cursor environment |
Base table of v$sql_optimizer_env. One use is to find all parameters including underscore ones in the environment of a SQL cursor by not restricting on column kqlfsqce_flags as v$sql_optimizer_env does. |
| x$kqrpd |
kernel query, rowcache parent definition |
Column kqrpdosz is size of this parent rowcache object, not exposed in v$rowcache_parent although shown in rowcache dump. |
| x$kqrsd |
kernel query, rowcache subordinate definition |
Column kqrsdosz is size of this subordinate rowcache object, not exposed in v$rowcache_subordinate although shown in rowcache dump. |
| x$krbmsft |
kernel recovery backuprecovery, miscellaneous search file ? |
Commonly used with dbms_backup_restore.searchfiles to read the file list of a directory at OS. Ref |
| x$krcfh, x$krcfde, x$krcfbh, x$krcbit |
kernel recovery, changetracking file, header, descriptor, bitmap header, bitmap block |
Alex Gorbachev |
| x$krcstat |
kernel recovery, changetracking statistics |
Value dba_buffer_count_public*dba_entry_count_public*dba_entry_size is the memory that is currently allocated to the change tracking buffers. (Ref: 2160776.1) |
| x$ksbdd |
kernel service, background detached (process) definition |
Base table of v$bgprocess. Column ksbddfile in 12c associates the process with a header file, which hints at the kernel layer. But you may wish to see the actual internal names for the processes, in 10.2.0.1 and 11.2.0.1, which are more detailed and available in earlier versions as well. |
| x$ksbsrvdt |
kernel service, background server detached (process) |
Probably background process slaves. |
| x$ksbtabact |
kernel service, background ? action |
Actions performed by certain background processes and their timeout values |
| x$ksbucpuwtm |
kernel service, background ? CPU wait time |
18c. In root container. Background process CPU wait time. |
| x$ksfdstll |
kernel service, file disk (I/O) statistics, ? outlier |
Base table of v$io_outlier (for all I/O's except for LGWR) and v$lgwrio_outlier (for LGWR I/O). Timestamp, not exposed, is seconds since epoch. |
| x$ksimsi |
kernel service, instance management serial (and) instance (numbers) |
Base table of v$active_instances. The un-exposed ksimisum column is instance incarnation number, matching "Reconfiguration started ... new inc ..." in alert.log. |
| x$ksipc_info and x$ksipc_proc_stats |
kernel service IPC info and process stats |
These two 12c tables don't exist although v$fixed_table has their names. They probably would contain stats for the new IPC0 background process. Also related to the new _ksipc* parameters. |
| x$ksi_reuse_stats |
kernel service, instance, reuse stats |
12c only. Not sure why it's called this name. Apparently it's about enqueue resources. The name column is a wondeful alternative brief description for each enqueue or lock, compared with v$lock_type.description: select a.type, a.description, b.name from v$lock_type a, x$ksi_reuse_stats b where a.type = b.resname order by 1 |
| x$ksled, x$kslei, x$ksles |
kernel service, lock, event descriptors, events for instance, events for session |
Base tables for v$event_name, v$system_event, and v$session_event, respectively. Benefit of querying x$ksles: (1) When ksleswts (wait count) is 0, v$session_event won't have the row but x$ksles still has them with non-zero kslestim (time waited micro) or kslesmxt (max wait time in micro); (2) Since kslesmxt is in microsec, it could be non-zero even if v$session_event.max_wait is 0. x$kslei has benefit (2) over v$system_event. In 12c, the new column ksleddsp of x$ksled provides a better event name, such as "db single block read" for the perpetually confusing "db file sequential read": select kslednam, ksleddsp from x$ksled where kslednam != ksleddsp |
| x$kslemap |
kernel service, lock, event map |
"Indx = event number...Basically map events to a small number of useful classes like I/O waits" (Ref) |
| x$kslhot |
kernel service, lock, hot (blocks) |
Set _db_hot_block_tracking to true and track hot blocks in buffer cache. It's an alternative and probably better way than checking touch count. (Ref) |
| x$ksllclass |
kernel service, lock,, latch class |
"describes the 8 classes", "Specify which latch belongs to which class" with _latch_class_ (Ref) |
| x$kslltr_osp, x$kslwsc_osp |
kernel service, lock,, latch ? |
Records a few latches "missing" from v$latch* views, such as "DGA (domain global area) heap latch". |
| x$ksllw |
kernel service, lock, latch where |
Base table of v$latch_misses. But column ksllwlbl is not exposed in any view. It's said to record "the 'Why' meaning for some 'Where'" (Ref) or "Unit to guard" (Ref). |
| x$kslpo |
kernel service, latch posting |
Bug: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. Column ksllwlbl is explained in the entry for x$ksllw. |
| x$kslsesout |
kernel service, |
12cR2. Base table of undocumented v$event_outliers. |
| x$ksmdd |
kernel service, memory segmented (array) definition |
Ref |
| x$ksmfs |
kernel service, memory fixed SGA |
One of the base tables of v$sgastat. Shows sizes of fixed SGA, buffer cache, log buffer, shared I/O pool (for SecureFile LOBs), and in 12c, data transfer cache. Even though some of these can be dynamically resized in modern versions of Oracle, any component in shared memory not in some kind of pool (v$sgastat where pool is null) is left in this "fixed" SGA table. |
| 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 sga varname or all values with oradebug dumpsga. In 12.1.0.2, to find which database parameter (as in v$parameter and x$ksppi) roughly maps to which SGA variable, read the "Dump of Compile-Time Constants" section "Namespace ksppar_const_ns" of the trace file (note indx is off by 1). |
| 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." You need to join this table to another one on heap descriptor ksmchds, such as in v$sql_shared_memory (joining to x$kglcursor), or to x$ksmsp (on column ksmchpar), or kglobhd[0-6] of x$kglob or x$kglcursor_child, and possibly need to use use_nl hint. Example, example. |
| x$ksmjch, x$ksmjs |
kernel service, memory, java chunks, java (pool) statistics |
X$ksmjch shows each chunk of java pool except for free area. (Nor interesting to me though) |
| 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) statistics |
Check the sizes of the large pool components by select ksmssnam, count(*), sum(ksmsslen) from x$ksmls group by ksmssnam order by 1 |
| x$ksmmem |
kernel service, memory |
Entire SGA memory map. Each row shows memory content for 8 bytes (on 64-bit Oracle).
Due to memory guard pages, you can only select from x$ksmmem specifying a specific indx or addr (addr=hextoraw('...')), or by joining to another table on addr column; otherwise the session may hang or throws ORA-3113 (Windows doesn't seem to have this problem). One usage is to find the value for an SGA variable, e.g. select ksmmmval from x$ksmfsv a, x$ksmmem b where a.ksmfsadr=b.addr and ksmfsnam='kzaflg_' to see if audit is enabled (what kzaflg_ means), which is equivalent to oradebug peek command, or for this particular purpose, even more simply, oradebug dumpvar sga kzaflg_.
Indx is SGA index, i.e. the difference of SGA address and 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 0000000060001F40 on a 64-bit machine whose sgabeg is 0x60000000 can be calculated as:
select (to_number('0000000060001F40','xxxxxxxx') - to_number('60000000','xxxxxxxx')) /8 from dual;
select ksmmmval from x$ksmmem where indx = 1000;
|
| x$ksmns |
kernel service, memory numa (pool) statistics |
|
| x$ksmpgdst |
kernel service, memory PGA detailed statistics |
Base table of v$process_memory_detail, populated by alter session set events 'immediate trace name pga_detail_get level pid and cleaned by ...pga_detail_cancel.... |
| x$ksmpp |
kernel service, memory pga heap |
PGA heap (variable area). PGA subheaps:
select /*+use_nl(h,p)*/ h.ksmchds,p.ksmchcom,
h.ksmchcom ksmchnam,h.ksmchsiz, h.ksmchcls,h.ksmchpar
from x$ksmhp h,x$ksmpp p where h.ksmchds = p.ksmchpar
and p.ksmchcls like '%recr'
and p.ksmchpar != hextoraw('00');
|
| x$ksmsgmap |
kernel service, memory SGA map |
12cR2. Shows the beginning and ending addresses and sizes of fixed and variable areas of SGA. |
| 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). SGA heaps:
select /*+use_nl(h,s)*/ sess.sid, sess.username, h.ksmchds,
h.ksmchcom ksmchnam, h.ksmchsiz, h.ksmchcls,h.ksmchpar
from x$ksmhp h,x$ksmsp s,v$session sess
where h.ksmchds = s.ksmchpar and s.ksmchcls like '%recr'
and s.ksmchpar != hextoraw('00')
and h.ksmchown = sess.saddr;
SGA subheaps:
select /*+use_nl(h,s)*/ h.ksmchds,s.ksmchcom,h.ksmchcom
ksmchnam, h.ksmchsiz,h.ksmchcls,h.ksmchpar from
x$ksmhp h,x$ksmsp s where h.ksmchds = s.ksmchpar and
s.ksmchcls like '%recr'and s.ksmchpar != hextoraw('00');
You can sort on ksmchptr to get a map of memory pieces. In ksmchcom, the hex number after SQLA^ is the SQL hash value. |
| x$ksmspr |
kernel service, memory shared pool reserved |
|
| x$ksmsp_dsnew |
kernel service, memory shared pool, ? statistics new |
One row summarizes subpools and durations. Dscnt_kghdsnew is subpool count (distinct dsidx_ksmnwex in x$ksmsp_nwex). Cursiz_kghdsnew is total duration count (row count of x$ksmsp_nwex). |
| x$ksmsp_nwex |
kernel service, memory shared pool ? |
A new efficient fixed table shows subpools and durations. See 396940.1. |
| x$ksmss |
kernel service, memory sga statistics |
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$ksmsst, x$ksmstrs |
kernel service, memory, sga streams (pool), streams (pool) statistics |
|
| x$ksmssinfo |
kernel service, memory sga OS (level) info |
This 12c table shows how OS level shared memory segments are used (which segment is used by what component of SGA), in effect matching the rows of `ipcs -m' with those of v$sga. It also tells you whether and which segments are using HugePages, so you don't have to check /proc/pid/smaps to see that and is of course more detailed than just seeing the brief message in alert.log. See more. |
| x$ksmup |
kernel service, memory uga heap |
UGA heap (variable area). UGA subheaps:
select /*+use_nl(h,s)*/ h.ksmchds,u.ksmchcom,h.ksmchcom
ksmchnam,h.ksmchsiz,h.ksmchcls,h.ksmchpar
from x$ksmhp h,x$ksmup u where h.ksmchds = u.ksmchpar
and u.ksmchcls like '%recr'
and u.ksmchpar != hextoraw('00');
|
| x$ksolsfts |
kernel service, object level statistics, fts? |
Base table of v$segstat and v$segment_statistics. Fts_stmp records the last time fts_staval was updated, fts_preval the previously recorded value. Fts_inte greater than 0 reveals some less known types of statistics. Note that value in v$segstat or v$segment_statistics is cumulative; e.g., if "row lock waits" is non-zero, the waits may not be happening right now. |
| 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. You can also see if a specific parameter, underscore or not, is dynamically changeable etc.:
select
ksppinm name, ksppity "TYPE", ksppstvl value, ksppstdvl display_value, ksppstdf isdefault,
decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE') isses_modifiable,
decode(bitand(ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED', 3,'IMMEDIATE','FALSE') issys_modifiable,
decode(bitand(ksppiflg,4),4,'FALSE', decode(bitand(ksppiflg/65536,3), 0, 'FALSE', 'TRUE')) isinstance_modifiable ,
decode(bitand(ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismodified,
decode(bitand(ksppstvf,2),2,'TRUE','FALSE') isadjusted,
decode(bitand(ksppilrmflg/64, 1), 1, 'TRUE', 'FALSE') isdeprecated,
decode(bitand(ksppilrmflg/268435456, 1), 1, 'TRUE', 'FALSE') isbasic,
ksppdesc description,
ksppstcmnt update_comment
from x$ksppi x, x$ksppcv y
where x.indx=y.indx and ksppinm = '¶m';
Column ksppiflg has 30 bits in 11g and 32 bits in 12cR2, most of which are unexposed. But for a static parameter, whether it's instance-modifiable is not one of them. It would be very useful to have this bit. |
| x$ksppsv |
kernel service, parameter, system value |
Base table of v$system_parameter and v$system_parameter2. See comments on x$ksppi. |
| x$ksprstv |
kernel service, parameter, reset value |
12cR2. Base table of undocumented v$system_reset_parameter and v$system_reset_parameter2. Shows the value of a parameter if you run alter system reset to clear it from spfile. Also see comments on x$ksppi. |
| x$kspspfh |
kernel service, parameter spfile header |
Column kspspfhmodtime stores the time when you made the last change to the spfile. Use Note 1 to convert it to time but set epoch to 19870630. I don't know what's special about that time. |
| x$ksrcctx, x$ksrcdes |
kernel service, (intra-instance) broadcast, channel context, channel description |
Base tables of the undocumented v$channel_waits, which is used to find the big contributors to "reliable message" wait event. You can find the last message publishing time by select a.name_ksrcdes, b.totpub_ksrcctx, b.waitcount_ksrcctx, b.waittime_ksrcctx, new_time(to_date(to_char(lastpub_ksrcctx/86400+to_date('19700101','yyyymmdd'),'yyyymmdd hh24:mi:ss'),'yyyymmdd hh24:mi:ss'), 'GMT', 'CDT') from x$ksrcdes a, x$ksrcctx b where b.name_ksrcctx=a.indx and b.waitcount_ksrcctx>0. Column id_ksrcdes of x$ksrcdes provides alternative keywords you can use to search for. |
| x$ksrchdl |
kernel service, (intra-instance) broadcast, channel ? |
Column ctxp_ksrchdl matches p1 of 'reliable message' (context) and owner_ksrchdl matches ksspaown of x$ksuse (base table of v$session). Last message time is lastmsg_ksrchdl seconds since epoch (Note 1). |
| x$kstex |
kernel service, trace execution |
Base table of v$execution, a table documented poorly and probably wrong for a long time. The definition in v$fixed_view_definition probably should restrict on id instead of op (where id=10), official documentation should call FUNCTION function, PID pid (as v$process.pid), and the view should expose sid as session ID. This table together with x$trace provides info about KST trace. Unfortunately it seems to have stopped working in 11g and up. |
| x$ksugblnetstat, x$ksugblnetstatawr |
kernel service, user, global network stats (AWR) |
19c. Some TCP parameter settings and statistics. |
| x$ksulop |
kernel service, user long operation |
Base table of v$session_longops. Column ksulotgt, probably for total gets?, is not exposed. |
| x$ksulv |
kernel service, user locale value |
Base table of v$nls_valid_values. |
| x$ksupgp, x$ksupgs |
kernel service, user, process group, process group sniped |
X$ksupgp.ksupgpnm!='DEFAULT' may suggest session leaking (Ref). X$ksupgs is the base table of undocumented v$detached_session showing sessions killed (without immediate option) but not cleaned. |
| x$ksupl, x$ksuru |
kernel service, user, process (resource) limit, resource usage |
X$ksupl.ksuplstn=x$ksuru.ksurind. Ksuplres is the limit and ksuruse is the current usage. Not sure what resource (or kernel profile) exactly. (Ref) |
| x$ksuprlat |
kernel service, user process latch |
Base table of v$latchholder. Unexposed columns are ksuprllv (level), ksuprlty (type), ksuprlmd (mode), ksulawhy (why or reason), and ksulawhr (where or location). |
| x$ksuse |
kernel service, user session |
Base table of v$session. To_char(ksuseflg,'xxxxxxxx') can be checked against session state object flag. In fact, this applies to all tables with this column, i.e. x$ksuse, x$ksusesta, x$ksumysta, x$ksusio, x$ksusecst, x$ksusecon, x$kewssesv. Common bits are x1 (user session), x2 (recursive session), x4 (audit logon/logoff by cleanup), x40 (user session logs on), x10 (user session created by system processes), x8000000 (called NLS alter session). You can e.g. use it to find hidden recursive sessions. |
| x$ksusecon |
kernel service, user session connection |
In 11g, check client version with
with x as (select distinct to_char(ksuseclvsn,'xxxxxxx') v
from x$ksusecon where ksusenum = &sid)
select decode(v, ' 0', 'no version provided: 10g or lower, or background process?',
to_number(substr(v,1,2),'xx') || '.' || --maj_rel
to_number(substr(v,3,1),'x') || '.' || --mnt_rel
to_number(substr(v,4,2),'xx') || '.' || --ias_rel
to_number(substr(v,6,1),'x') || '.' || --pat_set
to_number(substr(v,7,2),'xx')) client_version -- port_mnt
from x;
(Ref)
Not needed in 12c because v$session_connect_info.client_version works fine. |
| x$ksusm |
kernel service, user session migration |
Base table of undocumented v$tsm_sessions and v$sscr_sessions. Related to migratable sessions, sessions with OCI_MIGRATE set during creation. |
| x$ksuvmstat |
kernel service, user virtual memory statistics |
In 10g and up, base table providing physical_memory_bytes to v$osstat (and VM paging stats on Windows). But on Linux up to Oracle 10.2.0.3, this number is system free memory in kilobytes (grep MemFree /proc/meminfo); on other OSes or 10.2.0.4 or up on Linux, it is "Total number of bytes of physical memory". |
| x$kswsastab |
kernel service, workgroup services, service table |
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$ksxafa |
kernel service, execution, (parallel) affinity ? |
Shows datafile - node (host) affinity. To test, make sure _affinity_on is true (default), set _enable_default_affinity to a number and bounce instance. Ksxafnum is file# in v$datafile or v$tempfile plus db_files. Not sure how to test though. Probably limited use with modern storage technology. |
| x$ksxm_dft |
kernel service, execution, modification dml frequency tracking |
Base table of undocumented v$object_dml_frequencies. Set _dml_frequency_tracking to true to see data. |
| x$ksxmme |
kernel service, execution, modification dml ? |
12cR2. Base table of undocumented v$dml_stats. Counts recent DML operations independent of commit or rollback. Controlled by _dml_monitoring_enabled. Lastused is seconds from epoch (Note 1). |
| x$ksxpclient |
kernel service, ipc, client |
On RAC, shows IPC client stats, cache for global cache (cache fusion traffic), dlm for distributed lock manager (GCS+GES), etc. Source for dba_hist_ic_client_stats. (Ref) |
| x$ksxpif |
kernel service, ipc, interface |
On RAC, lists all network interfaces and their stats. The same info as given by `ifconfig' or `ip -s link' except for hardware addresses. But one nice feature of this table is that the stats for virtual interfaces (e.g. ethX:X actually used by Oracle RAC) are separated out of those of their physical ones. |
| x$ksxpping |
kernel service, ipc, ping |
For RAC. Base table of 12c v$instance_ping. Can be used before 12c. |
| x$ksxp_stats |
kernel service, ipc, stats |
On RAC, IPC stats for each server process. Same info as in the `oradebug ipc' trace but less detailed (to compare, set pid to a specific process before dump). It shows summary stats for the five queues, IPC regions, bids prepared, etc. |
| x$ktcn* |
kernel transaction, change notification * |
all related to database change notification |
| x$ktcxb |
kernel transaction, control, transaction object |
Base table of v$transaction. Four bits of ktcxbflg column, exposed as v$transaction.flag, are explained in v$fixed_view_definition. Since v$transaction is empty without a transaction, you can directly query x$ktcxb to find sessions with certain attributes, e.g. serializable isolation level: select * from v$session where taddr in (select ktcxbxba from x$ktcxb where bitand(ktcxbflg,268435456)!=0). Other bits of ktcxbflg not shown in v$fixed_view_definition are: bit 1 read write and read committed, 4(?) read only, 13 using private strand (Ref), and there's one for distributed transaction. Experiment to find more. Inside Oracle, symbolic names such as KTCXBALC, KTCXBTRN, KTCXBINV, KTCXBCMT, KTCXBROL represent them. |
| x$ktfbfe |
kernel tablespace, file bitmap free extent |
Free extent bitmap in file header for LMT (equivalent to fet$ in DMT). Check dba_free_space view definition (Ref). |
| x$ktfbhc |
kernel tablespace, file bitmap header control |
Summarizes free space with one row per datafile. Check dba_data_files or dba_temp_files view definition |
| x$ktfbnstat |
kernel tablespace, file bigfile ? stat |
In 12c. Base table of undocumented v$bts_stat. Stats populated for bigfile tablespaces. |
| x$ktfbue |
kernel tablespace, file bitmap used extent |
Used extent bitmap in file header for LMT (equivalent to uet$ in DMT) |
| x$ktifb, x$ktiff, x$ktifp, x$ktifv |
kernel transaction, in-memory flush, ? |
Related to in-memory undo flushing. X$ktifp shows IMU pools and x$ktiff shows the events (functions) that trigger IMU flushing and the counts. (Ref) |
| x$ktprhist |
kernel transaction, parallel (transaction) recovery history |
Retains history for some time after a parallel transaction rollback. Columns usn, slt and seq are what were in v$transaction. Columns stime and etime can be converted (Note 1). See also the documented views v$fast_start_transactions, v$fast_start_servers. |
| x$ktrso |
kernel transaction, resumable session ? |
Base table of v$resumable, gv$_resumable2 (which has objid i.e. tablespace$.ts#, and type). The only unexposed columns are kssobflg and kssobown (which is v$session.saddr). |
| x$ktsimapool |
kernel transaction, ? in-memory pool |
In 12cR2. In-memory pools. Since the sizes (length column) are larger than alloc_bytes of v$inmemory_area (or x$ktsimau), maybe an IM area is an allocated part of IM pool. Pool != area. |
| x$ktsj* |
kernel transaction, space job(?) |
New in 12.1.0.2. Related to space pre-allocation (controlled by _enable_space_preallocation). Event 60051. |
| x$ktskstat |
kernel transaction/tablespace, segment shrink statistics |
Stats for alter table shrink space. Begin and end times are seconds from epoch (Note 1). |
| x$ktslchunk |
kernel transaction/tablespace, space LOB chunk |
Probably records transactions on LOB columns. FSB is LOB Free Space Block, HBB Hash Bucket Block, etc. |
| x$ktspstat |
kernel tablespace, space statistics |
Records some ASSM tablespace space management stats. Column ktspstatfsf "records how many times L1-BMBs are selected then rejected because they are owned by a different instance" (Bug 407495) |
| x$ktsso |
kernel transaction, sort segment |
Base table of v$sort_usage (or v$tempseg_usage). From 11.2.0.2, ktssosqlid provides SQL ID for the SQL associated with this temp segment usage, not exposed in the v$ views until 12.1.0.2. See Bug 17834663 and description. |
| x$ktugd |
kernel transaction, undo global data |
|
| x$ktumascn |
kernel transaction, undo minimum active SCN |
Used on Exadata only. Ref. |
| x$ktuqqry |
kernel transaction, undo ? query |
Base table of flashback_transaction_query. |
| x$kturhist |
kernel transaction, undo recovery history |
Base table of v$fast_start_transactions. The unexposed dtime column stores the transaction recovery time as seconds since epoch (Note 1). |
| x$ktusmst |
kernel transaction, undo system managed, statistics |
Base table of v$undostat. Unexposed column ktusmstrqsid may be recursive SQL ID. It's not always equal to ktusmstrmqi (maxqueryid or "the longest running SQL statement in the period". |
| x$ktuxe |
kernel transaction, undo transaction entry |
"get the SCN of the most recently committed (local) transaction" with select max(ktuxescnw * power(2, 32) + ktuxescnb) from x$ktuxe (Ref); select * from x$ktuxe where ktuxecfl = 'DEAD' and ktuxesta = 'ACTIVE' "shows transaction dead waiting for cleanup" (1561125) |
| x$kvii, x$kvit |
kernel (performance) view, instance, initialization, transitory (parameters) |
Various database and instance parameters related to buffer cache working mechanisms, CPUs, etc. In pre-10g, there's also x$kvis for sizes of internal structures. |
| x$kwqbpmt |
kernel OLTP queue ? |
Streams memory percentage used (frused_kwqbpmt), _buffered_publisher_flow_control_threshold (flbp_kwqbpmt, default 5000), _capture_publisher_flow_control_threshold (flcp_kwqbpmt, default 15000). |
| x$kxdbio_stats, x$kxdcm*, x$kxdrs |
kernel Exadata, block (level) intelligent operations stats, callback for metrics, resilvering |
Some are 12cR2 only. Exadata only. See oradebug doc component (Ref). |
| x$kxfbbox |
kernel execution, fast (parallel process) black box |
12c. Base table of undocumented v$px_process_trace. |
| x$kxfpbs |
kernel execution, fast (parallel) process batch size |
Settings related to parallel processes, e.g. whether to use large pool (true if using ASMM, parallel_automatic_tuning is true, or _PX_use_large_pool is true), _parallel_min_message_pool, etc. |
| x$kxfpcds, x$kxfpcms, x$kxfpcst |
kernel execution, fast (parallel) process, coordinator, dequeue stats, message stats, (query) stats |
Coordinator stats. See below for slave stats. |
| x$kxfpinstload |
kernel execution, fast (parallel) process instance load |
Trace file after setting _px_trace has the same info, and is more complete. See notes 444164.1, 1508338.1, 1630039.1. |
| x$kxfpsds, x$kxfpsms, x$kxfpsst |
kernel execution, fast (parallel) process, slave, dequeue stats, message stats, (query) stats |
Current list of reasons for parallel execution slave and stats. For dequeuing, see wait event "parallel query dequeue wait" in Anjo Kolk's paper. X$kxfpsst is the base table of v$pq_sesstat. |
| x$kxsbd |
kernel execution, SQL bind data |
Base table of v$sql_bind_data. Column kxsbdof2 (or shared_flag2 of v$sql_bind_data) is oacfl2 (not oacflg2 as in Note:39817.1) in SQL trace. "System-generated binds have a value of 256 in the SHARED_FLAG2 column". According to Bug 4359367, when it's 0x300, the bind variable is marked as unsafe (affecting cursor_sharing=similar). Note:296377.1 has more on its value. |
| x$kxttstecs, x$kxttstehs, x$kxttsteis, x$kxttstets |
kernel execution, temporary table stats, column stats, histograms, index stats, table stats |
12c. Gather stats for a global temporary table and you'll see stats in here (not in dba_tables, dba_indexes etc). |
| x$kywm* |
kernel ? workload management |
Related to work load management (WLM), event 10739, dbms_wlm package, undocumented v$calltag, v$wlm*. |
| x$kzspr, x$kzsro |
kernel security, session, privilege, role |
Session-specific. X$kzspr is the base table for v$enabledprivilege, which is base table of session_privs. X$kzsro is the base table of session_roles, and is used by many SQL scripts in ?/rdbms/admin. |
| x$le |
lock element |
Base table of v$gc_element. See the definition of gv$bh for its relationship with x$bh. |
| x$lobsegstat, x$lobstat, x$logstathist |
LOB (segment) stats, history |
X$lobsegstat seems to allow one query only and becomes empty (until populated later)? Ktslbegtime may be seconds from epoch (Note 1). X$lobstat is more persistent and is the base table of undocumented v$lobstat (space allocation/deallocation columns not exposed). Lobcurrenttime is seconds since epoch (Note 1). KTSJ Slave process W000 may query v$lobstat in doing space management. For basic LOB stats, v$sesstat and v$sysstat have 'lob reads', 'lob writes' etc., and v$segstat has segment-specific stats. |
| x$logbuf_readhist |
Log buffer read histogram |
951152.1 |
| x$messages |
(background process) messages |
May be the place where background processes (dest) store and fetch messages about what they do. Related to _messages parameter (should be much higher than row count of this table; 2*processes by default), messages latch, and "rdbms ipc (message|reply)" wait events. |
| x$modact_length |
(sql) module action length (limit) |
Not sure why necessary. The lengths stored in here are used to truncate long module and action strings in views such as dba_hist_sqlstat, sqltune related views, etc (find all by select view_name, text from user_views where lower(text_vc) like '%x$modact_length%' as sys in 12c). |
| x$qesmmiwt |
query execution, sql memory management ? workarea ? |
Base table of v$sql_workarea_active, but columns sqlsig, siblings (for parallel slaves), cap_size (mem size limit), min_mem, onepass_mem, optimal_mem, ds_flags, isize (for input?), osize (output?), ktssosize are not exposed. Note that 223730.1 says "Small active sorts (under 64 KB) are excluded from the view" v$sql_workarea_active, which is built on x$qesmmiwt without a where-clause. |
| x$qesmmsga |
query execution, sql memory management sga |
Base table of v$pgastat, which does not show invisible stats (where qesmmsgavs=0). |
x$qksbgses, x$qksbgsys |
query compilation, sql bug session or system |
Base tables of v$session_fix_control and v$system_fix_control. Unexposed columns are bits_qksbgs[ey]row (number of bits used), flag_qksbgs[ey]row, id_qksbgs[ey]row. |
| x$qksceses, x$qkscesys |
query compilation, sql compilation environment, session / system |
Base tables for v$ses_optimizer_env and v$sys_optimizer_env, respectively. There're so many optimizer parameters the two documented views are missing that sometimes you need to query these base tables directly. For unexposed session CBO params, select pname_qksceserow from x$qksceses minus select name from v$ses_optimizer_env. For sys params, select pname_qkscesyrow from x$qkscesys minus select name from v$sys_optimizer_env. |
| x$qksfgi_cursor |
query compilation, sql fine-grained invalidation(?) cursor |
Objn_qksfgi points to objects whose changes may invalidate the SQL (sqlid_qksfgi). |
| x$qksxa_reason |
query compilation, sql explain annotations |
12c. If qksxareasons is 'DOP downgrade %', indx can be matched with otherstat_2_value of v$sql_plan_monitor to explain why parallel degree is downgraded (2888303.1, 2512427.1) |
| x$shadow_datafile |
shadow datafile |
12cR2. For shadow lost write protection. 2159248.1. |
| x$skgxp_connection, x$skgxp_port |
OS kernel generic interface IPC, connections, ports |
RAC only. Show network connections on the interconnect. If you find significant 'gc blocks lost' in gv$sysstat, check the highest retrans of x$skgxp_connection and narrow down to the specific socket connections based on remote IP's and PID's (rem_ip* and rem_pid). Or check the highest lost_msgs in x$skgxp_port. |
| x$targetrba |
target RBA |
Ref |
| x$trace |
trace |
KST tracing (Ref). From 11g, time is microseconds since 2000-01-01 (assumes CDT local timezone here): select new_time(to_date(to_char(time/86400000000+to_date('20000101','yyyymmdd'),'yyyymmdd hh24:mi:ss'),'yyyymmdd hh24:mi:ss'), 'GMT', 'CDT') from x$trace. Below 11g, op column indicates various operations, such as 7 for wait, 11 for latch post (896098). 10g RAC bdump/cdmp_time directory has trw files that contain the same info (the trace file has columns TimeInMicroSec:?, OraclePid, SID, event, OpCode, TraceData). In 11g and up, the files are named *_bucket.trc. |
| 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 (distributed queries included) and close database links, v$dblink no longer shows the entries. But x$uganco still has them, which unfortunately are not visible from another session. |
| x$xplton, x$xpltoo |
explain plan sql trace(?) operation name, option |
Ref |
| x$xs_sessions |
? sessions |
Probably "lightweight user sessions", or Fusion Security sessions. May be created by a specially constructed OCI or Java program. Also said to be proxy sessions, but apparently not sessions created by proxy logon. |
| x$zasa* |
? |
Maybe related to Audit Vault. |