Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 


x$ kernel service & cache table tips

Expert Oracle Database Tips by Donald BurlesonMarch 22, 2015

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:

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   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.

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.

 

 

 
 
 
Frustrated by the Hidden or Undocumented Features of Oracle? 

The landmark book "Exploring Oracle Internals: Tips and Tricks for the Oracle DBA"  will take you far beyond the standard features of the Oracle database into the hidden and undocumented realms formerly reserved for Oracle insiders.  You will discover valuable tips and tricks for mastering your Oracle database, and you can buy it for 30% off directly from the publisher.
 


 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster