Database Reference
In-Depth Information
Relationships Between X$ Tables and V$ Views
Wouldn't it be convenient to consult a document that contains the underlying X$ fixed table
for any V$ view and vice versa? Such a document would facilitate the drill-down process presented
in the previous section. Such a document may be generated automatically. The following four
facts are paramount to coding the generation of such a document:
The column
PREV_HASH_VALUE
of the dynamic performance view
V$SESSION
holds the
hash value of the previous SQL statement executed by a database session.
The execution plan for a SQL statement, which is identified by its hash value, is available
in
V$SQL_PLAN
.
A cached execution plan contains the names of the objects accessed in
V$SQL_PLAN.
OBJECT_NAME
.
All row sources pertaining to X$ tables contain the string “FIXED TABLE”.
Based on this information, an algorithm that executes
SELECT
statements on V$ views and
pulls the names of underlying X$ tables from
V$SQL_PLAN
may be devised. The final task is to
store the associations between V$ views and X$ tables found in a table, such as this:
CREATE TABLE x_v_assoc (
x_id number,
v_id number);
Instead of storing the fixed table or view names, the table
X_V_ASSOC
saves their object
identifiers. The names may be retrieved by joining either column with
V$FIXED_TABLE.
OBJECT_ID
.
Following is the pseudo-code of the aforementioned algorithm:
LOOP over all V$ view names and their OBJECT_ID in V$FIXED_TABLE
Parse and execute SELECT * FROM
view_name
using dynamic SQL (DBMS_SQL)
Get the value of V$SESSION.PREV_HASH_VALUE for the current session
LOOP over all object names from the execution plan for the previous SQL
statement in V$SQL_PLAN,
considering only row sources that contain the string "FIXED TABLE"
Translate the object name to the fixed table number
V$FIXED_TABLE.OBJECT_ID
Insert the object identifier of the X$ table and the object identifier
of the associated V$ view
into the table X_V_ASSOC
END LOOP
END LOOP
The full source code is too long to reproduce here, but it is available in the file
x_v_assoc.sql
in
the source code depot. Once the table
X_V_ASSOC
is populated, the following query retrieves X$
fixed tables and the V$ views that are based on them:
