Database Reference
In-Depth Information
Drilling Down from V$ Views to X$ Fixed Tables
The next few sections present an approach for drilling down from a V$ view—via its underlying
GV$ view—all the way to one or more X$ tables at the lowest level. This approach is suitable
whenever the information exposed by a V$ view is a limiting factor in a troubleshooting or
performance diagnosis effort. Although Oracle Corporation has exposed more and more infor-
mation in X$ tables over the last few releases, it may occasionally be necessary to glean additional
information from X$ tables. Of course, due to their undocumented nature, X$ tables are subject
to change without notice.
Some articles endorsing X$ tables found on the Internet overlook that, in many cases,
equally useful information can be pulled from V$ views. As an example, instead of turning to
X$BH
to detect contention and hot blocks, one might also access
V$SEGMENT_STATISTICS
, which
was introduced in Oracle9
i
. Statspack reports at level 7 or higher include data captured from
V$SEGMENT_STATISTICS
. Personally, I have never needed information from X$ tables to resolve a
performance problem. Resolving hanging issues is a different matter, since the documented
view
DBA_BLOCKERS
does not consider library cache pins. Under such circumstances, knowing
about
X$KGLLK
is truly advantageous.
Drilling Down from V$PARAMETER to the Underlying X$ Tables
The best known X$ tables are probably
X$KSPPI
and
X$KSPPCV
. This section shows how to uncover
both by drilling down from
V$PARAMETER
. Likely, any DBA has used or heard of one or the other
undocumented (or hidden) parameter. Undocumented parameters start with an underscore
character (
_
). Such parameters are not found in
V$PARAMETER
. In Oracle10
g
, you may have
noticed double underscore parameters in the server parameter files of instances with enabled
automatic shared memory management (e.g.,
__db_cache_size
). The columns of the view
V$PARAMETER
are as follows:
SQL> DESCRIBE v$parameter
Name Null? Type
----------------------------------------- -------- -------------
NUM NUMBER
NAME VARCHAR2(80)
TYPE NUMBER
VALUE VARCHAR2(512)
DISPLAY_VALUE VARCHAR2(512)
ISDEFAULT VARCHAR2(9)
ISSES_MODIFIABLE VARCHAR2(5)
ISSYS_MODIFIABLE VARCHAR2(9)
ISINSTANCE_MODIFIABLE VARCHAR2(5)
ISMODIFIED VARCHAR2(10)
ISADJUSTED VARCHAR2(5)
ISDEPRECATED VARCHAR2(5)
DESCRIPTION VARCHAR2(255)
UPDATE_COMMENT VARCHAR2(255)
HASH NUMBER
