Oracle x$ tables Oracle Database Tips by Burleson Consulting

Question:  Why are the Oracle x$ tables hidden?

Answer:  Oracle builds v$ views upon the Oracle x$ tables (which are actually mapped in-memory C program structures) and the x$ tables and columns, while interesting are undocumented and change with each release.

Here is a description of some of the Oracle x$ tables and sample queries against the x$ table structures.  You can also grep your $ORACLE_HOME/rdbms/admin directory and see how the x$ structures are mapped to v$ views.

Also see the x$ kernel service & cache table.

For a map of the important data on the v$ views that are built from the Oracle x$ tables, see this Oracle Poster.

Exploring Oracle Internals - Inside the x$ Tables of Oracle 11g

The power inherent in the dynamic performance v$ views available with Oracle for the serious Oracle database professional was explored. This chapter will provide further coverage of the inner sanctum of Oracle 11g which is contained in the x$ tables and which form the basis for the dynamic performance v$ views. This chapter will examine:

• Exploring Oracle 12c new features and internals with the x$ tables

• Using the x$ tables for Oracle 12c database analysis

• Scripts using x$ tables with Oracle 12c

Classification of x$ Tables

As one can expect, the structure of the x$ tables is quite cryptic and poorly documented, if at all, within the Oracle literature. What is key is to establish a naming convention for the categories of x$ tables so that the Oracle expert can better understand their meaning and usage. Due to the nature of Oracle database internals, the x$ tables are not documented and their secrets remain internal to Oracle development.

However, there is a useful method to categorize the x$ tables into various functions based on how they operate. Fortunately, in most cases it is quite safe to query the x$ tables without causing any harm. They provide insight into the Oracle database internals for the advanced DBA.

This chapter will provide a useful classification system that can allow the DBA to better understand the nature of x$ tables. At first glance they appear cryptic but are quite easy to decipher once a system has been provided. Yet one needs to review the layers that make up the Oracle 11g database kernel first. Following are the Oracle 11g kernel database layers:

Oracle Kernel Database Layers

KS: Kernel Services

KX: Kernel Execution

K2: Kernel Distributed Transactions

KK: Kernel Compilation

KZ: Kernel Security

KQ: Kernel Query

KA: Kernel Access

KD: Kernel Data

KT: Kernel Transactions

KC: Kernel Cache

KJ: Kernel Locking

KG: Kernel Generic

A short explanation of these kernel layers follows so that one can better understand the relationship between x$ tables and the database kernel for Oracle 11g. The Kernel Services layer gives one the required database services for all other layers in the database kernel stack. For instance, it regulates the initialization parameters in the database for the session and instance levels as well as manages database concurrency operations for locking, latching operations and wait event management for database and instance level statistics. Next to be covered is the Kernel Execution layer.

The Kernel Execution layer performs code executions from the Kernel Compilation layer (KK) and handles bind operations for PL/SQL code as well as recursive calls within the shared pool area for the Oracle 11g SGA.

The Kernel Distributed Transaction(K2) layer manages operations involved within distributed transactions including two-phase commit tasks within the Oracle 11g database.

The Kernel Compilation (KK) layer is responsible for managing the compilation of PL/SQL objects along with tasks performed by the Oracle optimizer.

Next is the Kernel Security layer (KZ) which manages role and system privileges within the Oracle 11g database for security operations.

The Kernel Query (KQ) layer handles row caching operations from the data dictionary. It provides critical tasks so that the Kernel Security (KZ) and Kernel Compilation (KK) layers are able to receive data from the query results performed at this Kernel Query layer.

Kernel Access (KA) provides operations that permit access to database segments as well as routing information to other kernel layers within the Oracle 11g database kernel stack.

Kernel Data(KD) manages the storage layer for segments and data retrieval as well as formatting operations of database segments for storage of table data and index data.

Kernel Transactions (KT) manages freelist operations along with rollback segments including interested transaction list (ITL) allocation for operations that occur within data blocks, undo tasks, and transaction consistency based on the ACID model for relational databases.

Kernel Cache (KC) handles operations around the database buffer cache for Oracle 11g. It works hand-in-hand with system functions to manage shared memory resources including the buffer cache and redo log memory operations.

Kernel Locking (KJ) provides for lock management for RAC environments. It does not manage single instance non-RAC lock operations.

The Kernel Generic layer performs basic database kernel operations.

Following is a brief summary of the different kernel services available for review via the x$ tables. First to be examined are the x$ tables around the Kernel Services (KS) layer for Oracle 11g.

Here is a list of the "important" x$ tables in Oracle 12c:

x$activeckpt	active checkpoint
x$bh	buffer header
x$ckptbuf	checkpoint buffer (queue)
x$dbgalertext	debug alert extented
x$dbglogext	debug log extended
x$dbgricx, x$dbgrifx, x$dbgrikx, x$dbgripx	debug tables
x$dbkece	debug kernel error, critical error
x$dbkefefc	debug kernel error, fatal error flood control
x$dglparam	data guard logical parameters
x$diag_alert_ext	diagnostics alert extended
x$diag_hm_run, x$diag_vhm_run	diagnostics health monitor runs
x$diag_ips_configuration	diagnostics incident packaging service configuration
x$dnfs_meta	dNFS metadata
x$dra_failure	data recovery advisor failures
x$drm_history, x$drm_history_stats	dynamic remastering history, stats
x$jskjobq	job scheduling ?, job queue
x$k2gte, x$k2gte2	kernel 2-phase commit, global transaction entry
x$kbrpstat	kernel backup recovery process(?) statistics
x$kcbbes	kernel cache, buffer ?
x$kcbbf	kernel cache, buffer buffer_handles
x$kcbfwait	kernel cache, buffer file wait
x$kcbkpfs	kernel cache, buffer ckpt prefetch statistics
x$kcbkwrl	kernel cache, buffer write list
x$kcbldrhist	kernel cache, buffer load direct read history
x$kcbobh	kernel cache, buffer, objectqueue buffer header
x$kcboqh	kernel cache, buffer, object queue header
x$kcbsw	kernel cache, buffer statistics why
x$kcbuwhy	kernel cache, buffer why
x$kcbwbpd	kernel cache, buffer workingset buffer pool descriptor
x$kcbwds	kernel cache, buffer workingset descriptors
x$kcbwh	kernel cache, buffer where/why
x$kcccf	kernel cache, controlfilemanagement control file
x$kcccp	kernel cache, controlfile checkpoint progress
x$kccdi	kernel cache, controlfilemanagement database information
x$kccle	kernel cache, controlfile logfile entry
x$kccnrs, x$kccrsp	kernel cache, controlfile non-guaranteed restorepoint; kernel cache, controlfile restore point
x$kcfis*	kernel cache, file intelligent scan
x$kclcrst	kernel cache, (RAC) lock, consistent read statistics
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
x$kcluh	kernel cache, (RAC) lock undo header
x$kclui	kernel cache, (RAC) lock undo index
x$kcmscn	kernel cache, maximum SCN
x$kcrfstrand	kernel cache, redo file strand
x$kcrfx	kernel cache, redo file context
x$kcrrlns	kernel cache, recovery process LNS
x$kdxst	kernel data, index status
x$kdxhs	kernel data, index histogram
x$kewrtb	kernel server (manageability), workload repository tables
x$kfdat	kernel file, disk allocation table?
x$kffxp	kernel file, file extent map
x$kfklib	kernel file, - library
x$kghlu	kernel generic, heap LRUs
x$kglcursor	kernel generic, librarycache cursor
x$kgllk	kernel generic, librarycache lock
x$kglob	kernel generic, librarycache object
x$kglpn	kernel generic, librarycache pin
x$kglrd	kernel generic, librarycache readonly dependency
x$kglst	kernel generic, librarycache statistics
x$kgltr	kernel generic, librarycache translation
x$kgskvft	kernel generic, service, ?? fixed table
x$kjxm	kernel RAC cross-instance (?) messaging
x$kjznhangs, x$kjznhangses	kernel RAC diag node hang session
x$kmgsct	kernel memory, granule scoreboard ?
x$kmgstfr	kernel memory, granule - transfer
x$knstmvr	kernel replication, statistics materialized view refresh
x$kqdpg	kernel query, dictionary PGA
x$kqfco	kernel query, fixed table columns
x$kqfdt	kernel query, fixed derived table
x$kqfp	kernel query, fixed package
x$kqfsz	kernel query, fixed size (size of fixed objects in current version of Oracle)
x$kqfta	kernel query, fixed table
x$kqfvi	kernel query, fixed view
x$kqlfsqce	kernel query, librarycache fixedtable sql cursor environment
x$kqrpd	kernel query, rowcache parent definition
x$kqrsd	kernel query, rowcache subordinate definition
x$krbmsft	kernel recovery, - search file ?
x$krcfh, x$krcfde, x$krcfbh, x$krcbit	kernel recovery, changetracking file, header, descriptor, bitmap header, bitmap block
x$ksbdd	kernel service, background detached (process) definition
x$ksbsrvdt	kernel service, background server detached (process)
x$ksbtabact	kernel service, background - action
x$ksimsi	kernel service, instance management serial (and) instance (numbers)
x$ksipc_info and x$ksipc_proc_stats	kernel service IPC info and process stats
x$ksi_reuse_stats	kernel service, instance, reuse stats
x$ksled, x$kslei, x$ksles	kernel service, lock, event descriptors, events for instance, events for session
x$kslemap	kernel service, lock, event map
x$kslhot	kernel service, lock, hot (blocks)
x$ksllclass	kernel service, lock,, latch class
x$ksllw	kernel service, lock, latch where
x$kslpo	kernel service, latch posting
x$ksmdd	kernel service, memory segmented (array) definition
x$ksmfs	kernel service, memory fixed SGA
x$ksmfsv	kernel service, memory fixed SGA variables
x$ksmhp	kernel service, memory heap
x$ksmjch, x$ksmjs	kernel service, memory, java chunks, java (pool) statistics
x$ksmlru	kernel service, memory LRU
x$ksmls	kernel service, memory large (pool) statistics
x$ksmmem	kernel service, memory
x$ksmns	kernel service, memory numa (pool) statistics
x$ksmpgdst	kernel service, memory PGA detailed statistics
x$ksmpp	kernel service, memory pga heap
x$ksmsp	kernel service, memory sga heap
x$ksmspr	kernel service, memory shared pool reserved
x$ksmsp_dsnew	kernel service, memory shared pool, - statistics new
x$ksmsp_nwex	kernel service, memory shared pool ?
x$ksmss	kernel service, memory sga statistics
x$ksmsst, x$ksmstrs	kernel service, memory, sga streams (pool), streams (pool) statistics
x$ksmssinfo	kernel service, memory sga OS (level) info
x$ksmup	kernel service, memory uga heap
x$ksolsfts	kernel service, object level statistics, fts?
x$ksppcv	kernel service, parameter, current (session) value
x$ksppi	kernel service, parameter, parameter info
x$ksppsv	kernel service, parameter, system value
x$kspspfh	kernel service, parameter spfile header
x$ksrcctx, x$ksrcdes	kernel service, (intra-instance) broadcast, channel context, channel description
x$ksrchdl	kernel service, (intra-instance) broadcast, channel ?
x$kstex	kernel service, trace execution
x$ksulop	kernel service, user long operation
x$ksulv	kernel service, user locale value
x$ksupgp, x$ksupgs	kernel service, user, process group, process group sniped
x$ksupl, x$ksuru	kernel service, user, process (resource) limit, resource usage
x$ksuprlat	kernel service, user process latch
x$ksuse	kernel service, user session
x$ksusecon	kernel service, user session connection
x$ksusm	kernel service, user session migration
x$ksuvmstat	kernel service, user virtual memory statistics
x$kswsastab	kernel service, workgroup services, service table
x$ksxafa	kernel service, execution, - file affinity
x$ksxm_dft	kernel service, execution, modification dml frequency tracking
x$ksxpclient	kernel service, ipc, client
x$ksxpif	kernel service, ipc, interface
x$ksxpping	kernel service, ipc, ping
x$ksxp_stats	kernel service, ipc, stats
x$ktcn*	kernel transaction, change notification *
x$ktcxb	kernel transaction, control, transaction object
x$ktfbfe	kernel tablespace, file bitmap free extent
x$ktfbhc	kernel tablespace, file bitmap header control
x$ktfbnstat	kernel tablespace, file bigfile - stat
x$ktfbue	kernel tablespace, file bitmap used extent
x$ktifb, x$ktiff, x$ktifp, x$ktifv	kernel transaction, in-memory flush, ?
x$ktprhist	kernel transaction, parallel (transaction) recovery history
x$ktsimapool	kernel transaction, - in-memory pool
x$ktsj*	kernel transaction, space job(?)
x$ktskstat	kernel transaction/tablespace, segment shrink statistics
x$ktslchunk	kernel transaction/tablespace, space LOB chunk
x$ktspstat	kernel tablespace, space statistics
x$ktsso	kernel transaction, sort segment
x$ktugd	kernel transaction, undo global data
x$ktuqqry	kernel transaction, undo - query
x$kturhist	kernel transaction, undo recovery history
x$ktusmst	kernel transaction, undo system managed, statistics
x$ktuxe	kernel transaction, undo transaction entry
x$kvii, x$kvit	kernel (performance) view, instance, initialization, transitory (parameters)
x$kwqbpmt	kernel OLTP queue ?
x$kxdbio_stats, x$kxdcm*, x$kxdrs	kernel Exadata, block (level) intelligent operations stats, callback for metrics, resilvering
x$kxfbbox	kernel execution, fast (parallel process) black box
x$kxfpbs	kernel execution, fast (parallel) process batch size
x$kxfpcds, x$kxfpcms, x$kxfpcst	kernel execution, fast (parallel) process, coordinator, dequeue stats, message stats, (query) stats
x$kxfpinstload	kernel execution, fast (parallel) process instance load
x$kxfpsds, x$kxfpsms, x$kxfpsst	kernel execution, fast (parallel) process, slave, dequeue stats, message stats, (query) stats
x$kxsbd	kernel execution, SQL bind data
x$kxttstecs, x$kxttstehs, x$kxttsteis, x$kxttstets	kernel execution, temporary table stats, column stats, histograms, index stats, table stats
x$kywm*	kernel - workload management
x$kzspr, x$kzsro	kernel security, session, privilege, role
x$le	lock element
x$lobsegstat, x$lobstat, x$logstathist	LOB (segment) stats, history
x$logbuf_readhist	Log buffer read histogram
x$messages	(background process) messages
x$modact_length	(sql) module action length (limit)
x$qesmmiwt	query execution, sql memory management ?
x$qesmmsga	query execution, sql memory management ?
x$qksbgses, x$qksbgsys	query compilation service, bug session or system
x$qksceses, x$qkscesys	query compilation service, compilation environment, session or system
x$skgxp_connection, x$skgxp_port	OS kernel generic interface IPC, connections, ports
x$targetrba	target RBA
x$trace	trace
x$uganco	user global area, network connection
x$xplton, x$xpltoo	explain plan sql trace(?) operation name, option
x$xs_sessions	? sessions
x$zasa*	?

Also see my notes on Oracle x$ tables:

If you like Oracle tuning, you may enjoy my new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.