The X$ tables are intriguing to mischievously curious DBAs. There are 945 X$ tables in 11gR2 (22.214.171.124) compared to only 613 in Oracle 10gR2 (10.2.0.1) and just 394 in Oracle 9i Release 2 (126.96.36.199.0). There are also 628 indexes on the X$ tables. The Oracle dynamic tables are designed just as many robust Oracle application data models are. A set of tables is available to users (DBAs) via a set of synonyms on a set of views based on these tables. The synonym names start with V$ and are the object names published in the reference manual of the Oracle documentation set. These synonyms on the V$ views are used as the primary method of querying data from these tables. Interested DBAs, however, keep and use a toolkit of practical X$ table queries that supplement their V$ view queries.
The X$ tables contain instance-specific information spanning a variety of areas. They contain information about the current configuration of the instance, information about the sessions connected to the instance, and a gold mine of performance information. The X$ tables are platform-specific. The documented column definitions of the V$ views may be consistent from platform to platform, but the underlying SQL statements referencing the X$ tables may differ. The Oracle kernel consists of layers. The X$ table names contain an abbreviation for the particular kernel layer to which they pertain.
The X$ tables are not permanent or even temporary tables that reside in database datafiles. The X$ tables reside only in memory. When you start up your instance, they are created. They exist even before you create your control file. When you shut down your instance, they are destroyed. All 945 X$ tables are defined right after the instance is started (before mount). They are defined, but they cannot all be queried. Many of them require at least a mounted, if not open, database. To observe this, query the X$KQFTA and X$KQFDT table after starting your instance with the nomount option.
The X$ tables are owned by the SYS database user and are read-only, which is why they are referred to as fixed tables and the V$ views are referred to as fixed views. This statement might be a juicy invitation for you to try to verify this read-only property. Any attempt to alter these tables with a DDL or DML statement is met with an ORA-02030 error.
Oracle has extensively used the decode function in the underlying SQL statements of the data dictionary views. If you compare the V$ view underlying SQL statements from version to version, you will likely find differences in the implementation of some V$ views. The columns of the V$ views may stay more constant in terms of their name and meaning, which allows Oracle RDBMS engineers to change the X$ tables from version to version while not disrupting too much of the Oracle user community’s use of the V$ views. The fact that the V$ views are accessed through synonyms gives Oracle engineers another level of flexibility to alter the underlying structures, also with little or no impact on the user community’s use of the V$ views. Oracle’s extensive use of the decode function in the underlying V$ view SQL statements also facilitates the platform-specific implementation of a query, returning the generic data that a user of a particular V$ view expects from platform to platform. Consequently, running the correct scripts when upgrading a database is important to ensure the dictionary views are created in a way that matches the underlying X$ tables.
Application designers and developers may save themselves some development and maintenance pain by adopting a similar strategy. They can employ views and synonyms for application software access to an application’s underlying tables and stored programmatic objects (Java and PL/SQL). DBAs should investigate whether designers and developers can benefit from using this strategy. In some cases, the costs are higher than the benefits. Although this section is by no means a complete treatment of useful X$ table queries, it introduces some of the commonly used X$ table queries, grouped by the major tuning areas to which they pertain. Because X$ table queries are a supplement to queries of fixed views rather than a replacement for them, this section includes queries of both X$ tables and related fixed views.