Database Reference
In-Depth Information
CHAPTER 9
■ ■ ■
Introduction to X$ Fixed Tables
A few X$ tables are mentioned in the documentation, but the vast majority are undocumented.
For example X$BH is mentioned in the Oracle9i Performance Tuning Guide as well as the Oracle10g
Performance Tuning Guide . The Oracle10g Warehouse Builder Installation and Administration
Guide contains a procedure for resolving locking issues pertaining to the library cache by
accessing X$KGLLK . 1
Many X$ tables hold much more information than the GV$ views built on top of them. In
instances where information provided by GV$ or V$ views is insufficient, the underlying X$
table may be scrutinized. Numerous X$ tables do not serve as the basis for GV$ views at all.
X$ Fixed Tables and C Programming
At least a significant part, if not all of the code for the ORACLE DBMS kernel, is written in the C
programming language. A lot of data is maintained in two-dimensional arrays. On UNIX systems,
C arrays located in the SGA may be read by commercial third-party performance diagnostic
utilities that attach one or more shared memory segments that hold the SGA. 2 Personally,
although I have great respect for the developers who reverse engineer internal data structures
in the SGA, I am not an advocate of such SGA sampling tools. After all they are mere sampling
tools and may miss relevant data. In my view, extended SQL trace data combined with Statspack
(or AWR) snapshots provide sufficient database performance diagnostic data that is not collected
by sampling.
The basic idea behind V$ views is to expose information in C data structures to database
administrators. This is done by mapping V$ views to C data structures through some interme-
diate layers. X$ tables are one of the intermediate layers. They are the layer closest to C, to be
precise. Of course the word table in X$ table has a meaning that is almost entirely different
from the meaning in a SQL context. It goes without saying that none of the X$ tables have a
segment in DBA_SEGMENTS associated with them. Additional evidence for the uniqueness of X$
tables comes from the fact that the row source for accessing them is FIXED TABLE FULL. To
speed up access, indexes on X$ tables are maintained. The row source associated with index
access to a fixed table is called FIXED TABLE FIXED INDEX. Along the same lines, there are no
The manual shows how to access X$KGLLK for resolving the error “ORA-04021 timeout occurred while
waiting to lock object.”
1.
2.
See Oracle Wait Interface: A Practical Guide to Performance Diagnostics and Tuning by Richmond Shee
et al. ([ShDe 2004]).
93
 
Search WWH ::




Custom Search