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:



SQL> desc x$ksqst;
Name Null? Type
----------------------------------------- -------- ------------------
ADDR RAW(8)
INDX NUMBER
INST_ID NUMBER
KSQSTTYP VARCHAR2(2)
KSQSTGET NUMBER
KSQSTWAT NUMBER


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:



-- See the gets and waits for different types of enqueues:
select
*
from
x$ksqst
where
ksqstget > 0;


Here is an actual listing from this script, showing the enqueue data from a real Oracle instance.



SQL> @x2
ADDR INDX INST_ID KS KSQSTGET KSQSTWAT
---------------- ---------- ---------- -- ---------- ----------
00000004269DBB10 49 1 BR 1045 0
00000004269DBBB0 69 1 CF 245954 0
00000004269DBBC8 72 1 CI 5780 0
00000004269DBC28 84 1 CU 1886 0
00000004269DBCE0 107 1 DL 34 0
00000004269DBD10 113 1 DR 3053 0
00000004269DBD30 117 1 DV 389 0
00000004269DBD40 119 1 DX 1255971 0
00000004269DC138 246 1 HW 11377 0
00000004269DC218 274 1 IS 2305 0
00000004269DC308 304 1 JQ 1514 0
00000004269DC610 401 1 MR 1083 0
00000004269DC8A8 484 1 PE 212238 0
00000004269DC8B0 485 1 PF 1 0
00000004269DCB20 563 1 RT 1 0
00000004269DCC08 592 1 SQ 1868 0
00000004269DCC20 595 1 ST 26909 0
00000004269DCC98 610 1 TC 220 0
00000004269DCCE8 620 1 TM 378751 0
00000004269DCD18 626 1 TS 6605 0
00000004269DCD20 627 1 TT 64173 0
00000004269DCD40 631 1 TX 392834 0
00000004269DCE18 658 1 US 134438 0
00000004269DCFE0 715 1 WL 192 0


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:



select
kviival write_batch_size
from
x$kvii
where
kviitag = 'kcbswc';


Here is the output from this query. This displays the write batch size for the Oracle database.



SQL> connect internal as sysdba;
Connected.
SQL> @x1
WRITE_BATCH_SIZE
----------------
4096


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