Exploring Oracle 11g Database
Internals with x$ Tables
In order to query the x$ tables, one must have SYS level
privilegeswithin the Oracle
11g database environment. Due to the hundreds of x$ tables in each
major release of Oracle, it would take a huge amount of material
to cover each and every one in great detail. Therefore, this
chapter will provide a listing of the key x$ tables centered
around the kernel layers with some example scripts to explain how
to access the Oracle 11g database kernel internal information. The
following family listing of x$ tables allows one to view the
status for kernel services (KS) within Oracle 11g.
X$KS- Kernel Services
|
x$ksmfs |
Memory fixed SGA |
|
x$ksmfsv |
Memory fixed SGA vectors |
|
x$ksmjs |
Java Pool memory |
|
x$ksmlru |
Memory LRU (least recently used) |
|
x$ksmls |
Large pool memory |
|
x$ksmmem |
Memory |
|
x$ksmpp |
Memory Process Pool |
|
x$ksmsd |
Memory SGA definitions |
|
x$ksmsp |
Shared pool Memory |
|
x$ksmspr |
Shared pool reserved memory |
|
x$ksmss |
Shared pool summary |
|
x$ksmup |
User pool memory |
|
x$ksqst
|
Enqueue status |
|
x$ksulop |
User long operation |
|
x$ksulv |
User locale value |
|
x$ksupr |
User process |
It is demonstrated here how to understand shared pool memory by
performing a describe on the x$ksmsp table:
Name Null? Type
----------------------------------------- --------
----------------
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
KSMCHIDX NUMBER
KSMCHDUR NUMBER
KSMCHCOM VARCHAR2(16)
KSMCHPTR RAW(4)
KSMCHSIZ NUMBER
KSMCHCLS VARCHAR2(8)
KSMCHTYP NUMBER
KSMCHPAR RAW(4)
The following example query against the x$ksmsp table allows
one to understand how shared pool memory is currently being used
by the Oracle 11g database.
SQL> select ksmchcom AComment,
2 ksmchcls Status,
3 sum(ksmchsiz) Bytes
4 from x$ksmsp
5 group by ksmchcom, ksmchcls;
ACOMMENT STATUS
BYTES
---------------- -------- ----------
sql area recr 3354624
PL/SQL DIANA freeabl 8753152
trigger defini recr 94404
joxlod exec hp recr 269792
partitioning d recr 18052
sql area:KOKA recr 40960
policy hash tab freeabl 164
Label Cache Hea freeabl 104
qtree_kwqspqctx freeabl 40
dbgefgHtAddSK-1 freeabl 450996
Session Page freeabl 6776
Now review an example of how to understand x$ tables by
querying against one of the key x$kc tables for the Kernel Cache
(KC) layer with Oracle 11g.
X$KC - Kernel Cache
|
x$kcbfwai |
Block file wait |
|
x$kcbwait |
Block wait |
|
x$kcccp |
Checkpoint progress controlfile |
|
x$kcfio |
File I/O |
|
x$kclfh |
Lock file header |
|
x$kclfi |
Lock file index |
|
x$kcluh |
Lock undo header |
|
x$kclui |
Lock undo index |
For this case study of the Kernel Cache (KC) family of x$
tables, give a code example with the x$kcbfwaittable to examine buffer busy wait issues.
The following script shows the datafiles that
have data blocks wait conditions present waited on within the test
Oracle 11g database.
SQL> select count, file#, name
2 from x$kcbfwait, v$datafile
3 where indx+1=file#
4 order by count;
COUNT FILE# NAME
------------------------------------------------
0 3
C:\WIN11G1\ORADATA\WIN11G\UNDOTBS01.DBF
0 2
C:\WIN11G1\ORADATA\WIN11G\SYSAUX01.DBF
0 5
C:\WIN11G1\ORADATA\WIN11G\EXAMPLE01.DBF
COUNT FILE# NAME
------------------------------------------------
0 4
C:\WIN11G1\ORADATA\WIN11G\USERS01.DBF
33 1
C:\WIN11G1\ORADATA\WIN11G\SYSTEM01.DBF
SQL>
Next to be used is an example to understand the kernel query
layer of the 11g database kernel from the x$kq family.
X$KQ - Kernel Query
|
x$kqfco |
Fixed table
columns |
|
x$kqfdt |
Fixed table |
|
x$kqfp |
Fixed procedure |
|
x$kqfsz |
Fixed size |
|
x$kqfta |
Fixed table |
|
x$kqfvi |
Fixed view |
|
x$kqfvt |
Fixed view table |
Now one can examine the 11g database internal structures for
fixed tables and views by usage of the x$kqkernel query tables. For instance, issue a query
against the x$kqfvitable which
will provide the complete listing for all v$ and gv$ views based
on the x$ tables for Oracle 11g.
SQL> select kqfvinam from x$kqfvi;
KQFVINAM
------------------------------
GV$WAITSTAT
V$WAITSTAT
GV$BH
V$BH
GV$GC_ELEMENT
V$GC_ELEMENT
GV$CR_BLOCK_SERVER
V$CR_BLOCK_SERVER
GV$CURRENT_BLOCK_SERVER
V$CURRENT_BLOCK_SERVER
GV$ENCRYPTED_TABLESPACES
V$ENCRYPTED_TABLESPACES
GV$GC_ELEMENTS_WITH_COLLISIONS
V$GC_ELEMENTS_WITH_COLLISIONS
GV$FILE_CACHE_TRANSFER
V$FILE_CACHE_TRANSFER
GV$TEMP_CACHE_TRANSFER
V$TEMP_CACHE_TRANSFER
GV$CLASS_CACHE_TRANSFER
V$CLASS_CACHE_TRANSFER
GV$INSTANCE_CACHE_TRANSFER
V$INSTANCE_CACHE_TRANSFER
GV$LOCK_ELEMENT
V$LOCK_ELEMENT
GV$BSP
V$BSP
GV$LOCKS_WITH_COLLISIONS
V$LOCKS_WITH_COLLISIONS
GV$FILE_PING
V$FILE_PING
GV$TEMP_PING
V$TEMP_PING
GV$CLASS_PING
The listing from x$kqfvi is
now available, so proceed to examine other Oracle 11g database
structures within the database kernel by accessing the x$kq layer
of the generic kernel database structures. The following table
lists the most frequently used x$kq kernel generic x$ tables.
X$KG - Kernel Generic
|
x$kghlu |
Heap LRU (Least
Recently Used) |
|
x$kgllk |
Library cache lock
|
|
x$kglob |
Library cache
object |
|
x$kglpn |
Library cache pin
|
|
x$kglst |
Library cache
status |
If a DBA is experiencing performance degradation issues due to
library cache issues, then query the x$kgllktable to investigate further.
C:\>sqlplus "/as sysdba"
SQL*Plus: Release 11.1.0.6.0 - Production on Thu May 14 16:43:01 2015
Copyright (c) 1982, 2015, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 -
Production
With the Partitioning, OLAP, Data Mining and Real Application
Testing
options
SQL> select
2 kglnaobj, kgllkreq
3 from
4 x$kgllk x join v$session s on
5 s.saddr = x.kgllkses;
KGLNAOBJ
KGLLKREQ
------------------------------------------------------------
----------
table_4_9_1322_0_0_0
0
table_4_9_1322_0_0_0
0
STANDARD
0
DBMS_PRVT_TRACE
0
table_1_ff_20b_0_0_0
0
table_1_ff_20b_0_0_0
0
select 1 from sys.aq$_subscriber_table where rownum < 2
and 0
select 1 from sys.aq$_subscriber_table where rownum < 2
and 0
select decode(bitand(a.flags, 16384), 0, a.next_run_date,
0
select decode(bitand(a.flags, 16384), 0, a.next_run_date,
0
select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$
where 0
select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$
where 0
insert into smon_scn_time (thread, time_mp, time_dp, scn,
sc 0
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oi
0
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oi
0
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oi
0
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oi
0
table_1_ff_207_0_0_0
0
table_1_ff_207_0_0_0
0
select con#,type#,condlength,intcols,robj#,rcon#,match#,refa
0
select con#,type#,condlength,intcols,robj#,rcon#,match#,refa
0
AQ$_ALERT_QT_E
0
select name,intcol#,segcol#,type#,length,nvl(precision#,0),d
0
select name,intcol#,segcol#,type#,length,nvl(precision#,0),d
0
select name,intcol#,segcol#,type#,length,nvl(precision#,0),d
0
select name,intcol#,segcol#,type#,length,nvl(precision#,0),d
0
DELETE FROM RECENT_RESOURCE_INCARNATIONS$ WHERE RESOURCE_TYP
0
DELETE FROM RECENT_RESOURCE_INCARNATIONS$ WHERE RESOURCE_TYP
0
select decode(bitand(a.flags, 16384), 0, a.next_run_date,
0
select decode(bitand(a.flags, 16384), 0, a.next_run_date,
0
DBMS_HA_ALERTS_PRVT
0
BEGIN dbms_ha_alerts_prvt.clear_instance_resources( :dbdo
0
BEGIN dbms_ha_alerts_prvt.clear_instance_resources( :dbdo
0
select kglnaobj, kgllkreq from
x$kgllk x join v$session 0
select kglnaobj, kgllkreq from x$kgllk x join v$session
0
select tab.rowid, tab.msgid, tab.corrid, tab.priority,
tab. 0
KGLNAOBJ KGLLKREQ
------------------------------------------------------------
----------
select tab.rowid, tab.msgid, tab.corrid, tab.priority,
tab. 0
select subscriber_id, name, address, protocol, subscriber_ty
0
select subscriber_id, name, address, protocol, subscriber_ty
0
STANDARD
0
table_1_ff_213_0_0_0
0
table_1_ff_213_0_0_0
0
ALERT_QUE_R
0
table_1_ff_20f_0_0_0
0
table_1_ff_20f_0_0_0
0
select a.next_start_date, a.objid, a.w_open from (select
b. 0
select a.next_start_date, a.objid, a.w_open from (select
b. 0
PLITBLM
0
ALERT_QUE
0
select /*+ FIRST_ROWS(1) */ x.C1, x.C2, x.C3 from (select
a 0
select /*+ FIRST_ROWS(1) */ x.C1, x.C2, x.C3 from (select
a 0
select OBJOID, CLSOID, RUNTIME, PRI, JOBTYPE, SCHLIM,
WT, 0
select OBJOID, CLSOID, RUNTIME, PRI, JOBTYPE, SCHLIM,
WT, 0
insert into "SYS"."ALERT_QT" (q_name, msgid, corrid,
priori 0
insert into "SYS"."ALERT_QT" (q_name, msgid, corrid,
priori 0
DBMS_HA_ALERTS_PRVT
0
SELECT INSTANCE_NAME, HOST_NAME, NVL(GVI_STARTUP_TIME, SYSTI
0
SELECT INSTANCE_NAME, HOST_NAME, NVL(GVI_STARTUP_TIME, SYSTI
0
table_1_ff_203_0_0_0
0
table_1_ff_203_0_0_0
0
table_1_ff_203_0_0_0
0
table_1_ff_203_0_0_0
0
select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ whe
0
select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ whe
0
select CONNECTION_POOL_NAME, STATUS, MINSIZE, MAXSIZE,
0
select CONNECTION_POOL_NAME, STATUS, MINSIZE, MAXSIZE,
0
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.fla
0
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.fla
0
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.fla
0
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.fla
0
select max(RETENTION) from SYS_FBA_FA
0
select max(RETENTION) from SYS_FBA_FA
0
DATABASE
0
select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t
0
select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t
0
select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t
0
select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t
0
update sys.mon_mods$ set inserts =
inserts + :ins, updates = 0
ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY=
'A 0
select value, flags, modified_inst, additional_info,
0
select value, flags, modified_inst, additional_info,
0
SELECT OBJOID, CLSOID, DECODE(BITAND(FLAGS, 16384), 0,
RU 0
SELECT OBJOID, CLSOID, DECODE(BITAND(FLAGS, 16384), 0,
RU 0
select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$
t 0
DBMS_PRVT_TRACE
0
COMMIT
0
select 1 from obj$ where name='DBA_QUEUE_SCHEDULES'
0
select 1 from obj$ where name='DBA_QUEUE_SCHEDULES'
0
88 rows selected.
SQL>
The advantage of using the above x$ table query against the
x$kgllk table is that more information is provided by using this
x$ table than the v$lockquery.
Recalling from earlier on, it was mentioned that all of the v$
dynamic performance views are based upon x$ tables. In the above
query code listing, the x$kgllk table lists all held and
requested library object locks for all sessions within Oracle 11g.
Now decipher some of the columns in the x$kgllk table from the
query that was just ran against the Oracle 11g database. In the
x$kgllk table, the column kglnaobj
displays the name of the object in terms of the first 80
characters of the object name. If one examines the kgllkreqcolumn, it can be determined that a value of zero
indicates that a lock is being held, whereas a value greater than
zero for the kgllkreq column indicates that a lock has been
requested within the Oracle database. As can be seen, using x$
tables will expand the realm of performance and database analysis
for complex Oracle issues. Now examine the x$ tables for the
kernel security (KZ) layer within Oracle 11g.
X$KZ - Kernel Security (KZ) Layer
|
x$kzspr
|
Enabled Privileges |
|
x$kzsro
|
Enabled Roles |
|
x$kzsrt |
Remote Password
File Table Entries |
As these x$ tables are undocumented, next to be reviewed is an
example of the security kernel layer for Oracle 11g roles and
privileges by querying against the x$kzspr
and x$kzsrttables. First,
obtain the column definitions for the three security x$ tables.
Name Null? Type
----------------------------------------- --------
----------------
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
KZSPRPRV NUMBER
Name Null? Type
----------------------------------------- --------
----------------
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
KZSROROL NUMBER
Name Null? Type
----------------------------------------- --------
-----------------
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
USERNAME VARCHAR2(30)
SYSDBA NUMBER
SYSOPER NUMBER
SYSASM NUMBER
VALID NUMBER
Now that the table definitions for the above three x$ tables
for the kernel security (KZ) layer are defined, drill down with
the following code example to obtain details for Oracle 11g
security roles and privileges.
SQL> select username, sysdba,
sysoper, sysasm, valid
2 from x$kzsrt;
USERNAME SYSDBA
SYSOPER SYSASM VALID
------------------------------ ---------- ---------- ----------
----
INTERNAL 1 1 0
1
SYS 1 1 0
1
The above query against the x$kzsrt
table yields details for the Oracle 11g remote password entries
for 11g roles including that for SYSDBA, SYSOPER and SYSASM along
with a status value for whether the elevated privilege has been
enabled or not within the Oracle 11g database. A value of 1
indicates that an account has been enabled for these privileges
while a value of 0 would indicate that no user has these
privileges enabled in the Oracle database.
A cursory walkthrough of the x$ tables for Oracle 11g has now
been provided, so the survey will conclude with how x$ tables can
be used by the experienced Oracle professional to investigate and
understand Oracle 11g new features.
