Introduction to X Views

Introduction to X$ Views

The SQL-less SGA access has two prerequisites: knowledge of C programming language and X$ views. Here, we will help you to better understand X$ memory structures, but C programming is beyond the scope of this book.

X$ data structures are the heart of the Oracle RDBMS kernel. They are rapidly changing memory structures in the RDBMS kernel, and they keep track of various statistics throughout the life of the instance. So if your instance has been running for several months, there is a chance that some statistic values grew too large and wrapped around. Those suspicious values can generally be ignored. The contents of X$ views always reside in memory and cannot be exported to any other database because they have no information about them in the dictionary. They do not have storage settings like those that are associated with normal tables, but they do have indexes on fixed columns .

X$ views generally start with the letter K, which stands for kernel . At first glance, they appear cryptic, but once you get used to them, they are easy to understand and decrypt. There is no external documentation available for the details of X$ views and their distribution across various kernel layers .

Oracle kernel comprises various layers, each of which works independently, and the control is passed from one layer to another layer. Each layer has a set of X$ memory structures that show the statuses and statistics of the functions within the layer. Most of the information is exposed in the V$ views, which are built on the X$ memory structures. Following are some of the layers in the Oracle kernel.

• Compilation layer (KK)

• Execution layer (KX)

• Distributed Transaction layer (K2)

• Security layer (KZ)

• Query layer (KQ)

• Access layer (KA)

• Data layer (KD)

• Transaction layer (KT)

• Cache layer (KC)

• Service layer (KS)

• Lock Management layer (KJ)

• Generic layer (KG)

The Compilation layer (KK) is responsible for compiling PL/SQL objects and generating explain plans based on the statistics available from the dictionary. The major component of the compilation layer is Oracle optimizer.

The Execution layer (KX) executes the compiled code from the top layer and binds the SQL and PL/SQL objects. This layer is also responsible for recursive calls to the dictionary and cursor management inside the shared pool.

The Distributed Transaction layer (K2) handles two-phase commits in the distributed transactions. A two-phase commit (prepare and commit) is the protocol used in the distributed database to ensure data integrity.

The Security layer (KZ) helps the upper two layers during compilation and execution. This layer manages roles and system privileges.

The Query layer (KQ) caches the rows from the data dictionary in the dictionary cache. The Compilation (KK) and Security (KZ) layers get the data from the query layer during compilation.

The Access layer (KA) is responsible for access to the database segments and provides information to the upper layers.

The Data layer (KD) controls the physical data storage and retrieval in the segments. It controls the formatting of data segments for storing table data and index trees.

The major component in the Transaction layer (KT) is rollback segments. This layer controls the freelist management, interested transaction list (ITL) allocations inside data blocks, row-level locking during the transaction, and undo generation. It also controls the rollback segment allocation and manages the consistency during a transaction.

The Cache layer (KC) manages the database buffer cache. This layer works closely with operating system facilities to manage the buffer cache and shared memory. It is also responsible for the redo generation and redo write to the redo log files.

The Service layer (KS) provides the required services to the other layers. It enforces initialization parameters in sessions and the instance. It also controls latch allocations and lock management in single-instance Oracle, and manages the wait events and statistics instance-wide.

The Lock Management layer (KJ) manages the locks and resources in a RAC environment. This layer manages the buffer locks (for global caches) which are specific to RAC and not to be confused with table or row level locks.

Before you look at the X$ views that will give you the information to access the SGA directly, let us dispel some of the myths that frequently surround them:

Myth: You should not query X$ views because they put a heavy load on the database.

Fact: It is absolutely safe to query X$ views. Almost every V$ view is based on one or more X$ views. If anything, it is cheaper to bypass the V$ views and query the X$ views directly.

Myth: You should not frequently query the X$ views because the contents will be erased once queried.

Fact: This applies only to the X$KSMLRU (kernel service layer memory-component least recently used) fixed table. The contents of this view will be erased once queried. As for the other X$ views, their contents remain .

Myth: You should not perform DML against any X$ views, as doing so will crash the instance.

Fact: You cannot perform DML on X$ views even if you want to. Oracle doesn ‚ t allow that. However, there are some undocumented ways to clear or reset the contents of a few X$ views. But those are not considered DML.

Myth: In practice, you never need to access X$ views because almost all of the information from X$ views is available through V$ views.

Fact: Only a portion of the information in X$ views is exposed in V$ views. For advanced statistics and internal information, you still need to query X$ views. The shared pool chunks sizes and block touch count information, just to name a couple, are only available in X$ views.

Myth: The SYS user is the only one that has access to X$ views. So, to get information from the views, one must log in as SYS.

Fact: This is partly true. You can create views based on the X$ views (as SYS) and grant SELECT on those views to any user. In this case, the users need not be a DBA to query those views.