分类： Oracle
2013-04-17 13:48:44
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:
SQL> describe x$ksmsp
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
0 2
0 5
COUNT FILE# NAME
------------------------------------------------
0 4
33 1
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
2009
Copyright (c) 1982, 2007, 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.
SQL> desc x$kzspr
Name Null? Type
----------------------------------------- --------
----------------
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
KZSPRPRV NUMBER
SQL> desc x$kzsro
Name Null? Type
----------------------------------------- --------
----------------
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
KZSROROL NUMBER
SQL> desc x$kzsrt
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.
