|
 |
|
Oracle x$ tables
Oracle 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
In the last chapter, the power inherent in the
dynamic performance v$ views available with Oracle 11g 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 11g new features and
internals with the x$ tables
-
Using the x$ tables for Oracle 11g
database analysis
-
Scripts using x$ tables with Oracle 11g
Classification of x$ Tables
As one can expect, the structure of the x$
tablesis 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:
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 Serviceslayer 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 Compilationlayer (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 Securitylayer (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.
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. |
|
