Querying the X$ structures
One of the most hidden areas of the Oracle database is the X$ structures. As we may know, the X$ structures are the innermost tables that are used to build the common v$ views that we use to query the data dictionary.
Here is an idea of the naming of these X$ structures. Of course, these internal structures change between Oracle releases, and this list is from Oracle 8.1.5.
X$ks – Kernel Services
x$ksmfs kernel services, memory fixed SGA
x$ksmfsv kernel services, memory fixed SGA vectors
x$ksmjs kernel services, memory java_pool summary
x$ksmlru kernel services, memory LRU
x$ksmls kernel services, memory large_pool summary
x$ksmmem kernel services, memory
x$ksmpp kernel services, memory process pool
x$ksmsd kernel services, memory SGA definition
x$ksmsp kernel services, memory shared pool
x$ksmspr kernel services, memory shared pool reserved
x$ksmss kernel services, memory shared_pool summary
x$ksmup kernel services, memory user pool
x$ksqst kernel services, enqueue status
x$ksulop kernel services, user long operation
x$ksulv kernel services, user locale value
x$ksupr kernel services, user process
X$kc – kernel cache
x$kcbfwait kernel cache, block file wait
x$kcbwait kernel cache, block wait
x$kcccp kernel cache, controlfile checkpoint progress
x$kcfio kernel cache, file I/O
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$kq – kernel query
x$kqfco kernel query, fixed table columns
x$kqfdt kernel query, fixed table
x$kqfp kernel query, fixed procedure
x$kqfsz kernel query, fixed size
x$kqfta kernel query, fixed table
x$kqfvi kernel query, fixed view
x$kqfvt kernel query, fixed view table
X$kg – kernel generic
x$kghlu kernel generic, heap LRUs
x$kgllk kernel generic, library cache lock
x$kglob kernel generic, library cache object
x$kglpn kernel generic, library cache pin
x$kglst kernel generic, library cache status
X$kz – Kernel security
x$kzsro kernel security, system role
X$le – lock element
x$le lock element
x$le_stat lock element status
The X$ views are completely undocumented, and Oracle does not provide any assistance for deriving the meaning of the contents of the X$ structures.
However, there are some queries that you can run to see internal operations within Oracle. Let’s begin by describing the X$ksqst structure, the structure that show kernel enqueue status:
As can see, the X$ structures are quite cryptic, but there are hints about their contents.
We can also further explore the X$ structures by viewing the X$ksqst table. This table show the kernel status for all enqueues within your Oracle database:
Here is an actual listing from this script, showing the enqueue data from a real Oracle instance.
It is great fun to attempt queries against the X$ structures and see if you can guess the contents of the structures. Below is a query that will display the largest number of blocks that can be written by DBWR at any time:
Here is the output from this query. This displays the write batch size for the Oracle database.
In sum, there is a wealth of interesting information in the X$ structures, and those DBAs with a propensity to learn Oracle Internals, these queries can be both fun and informative.