Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

Donald K. Burleson

Oracle Tips

 

Querying the X$ structures

One of the most hidden areas of the Oracle database is the X$ structures. As we may know, the X$ structures are the innermost tables that are used to build the common v$ views that we use to query the data dictionary.

Here is an idea of the naming of these X$ structures. Of course, these internal structures change between Oracle releases, and this list is from Oracle 8.1.5.

 

 

 

X$ks – Kernel Services

 

x$ksmfs          kernel services, memory fixed SGA          

x$ksmfsv        kernel services, memory fixed SGA vectors         

x$ksmjs          kernel services, memory java_pool summary         

x$ksmlru         kernel services, memory LRU          

x$ksmls          kernel services, memory large_pool summary       

x$ksmmem    kernel services, memory     

x$ksmpp        kernel services, memory process pool      

x$ksmsd         kernel services, memory SGA definition     

x$ksmsp         kernel services, memory shared pool          

x$ksmspr       kernel services, memory shared pool reserved     

x$ksmss         kernel services, memory shared_pool summary    

x$ksmup         kernel services, memory user pool

x$ksqst           kernel services, enqueue status       

x$ksulop         kernel services, user long operation            

x$ksulv            kernel services, user locale value    

x$ksupr           kernel services, user process           

 

 

X$kc – kernel cache

 

x$kcbfwait      kernel cache, block file wait

x$kcbwait       kernel cache, block wait        

x$kcccp          kernel cache, controlfile checkpoint progress        

x$kcfio            kernel cache, file I/O  

x$kclfh            kernel cache, lock file header           

x$kclfi kernel cache, lock file index  

x$kcluh           kernel cache, lock undo header       

x$kclui            kernel cache, lock undo index          

 

 

X$kq – kernel query

 

x$kqfco           kernel query, fixed table columns     

x$kqfdt            kernel query, fixed table        

x$kqfp kernel query, fixed procedure         

x$kqfsz           kernel query, fixed size          

x$kqfta            kernel query, fixed table        

x$kqfvi            kernel query, fixed view         

x$kqfvt            kernel query, fixed view table            

 

 

 

X$kg – kernel generic

 

x$kghlu           kernel generic, heap LRUs   

x$kgllk            kernel generic, library cache lock   

x$kglob           kernel generic, library cache object             

x$kglpn           kernel generic, library cache pin    

x$kglst            kernel generic, library cache status             

 

 

X$kz – Kernel security

 

 

x$kzsro           kernel security, system role 

 

X$le – lock element

 

x$le                 lock element   

x$le_stat         lock element status  

 

 

 

The X$ views are completely undocumented, and Oracle does not provide any assistance for deriving the meaning of the contents of the X$ structures. 

 

However, there are some queries that you can run to see internal operations within Oracle. Let’s begin by describing the X$ksqst structure, the structure that show kernel enqueue status:

 

SQL> desc x$ksqst;
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------
 ADDR                                               RAW(8)
 INDX                                               NUMBER
 INST_ID                                            NUMBER
 KSQSTTYP                                           VARCHAR2(2)
 KSQSTGET                                           NUMBER
 KSQSTWAT                                           NUMBER

 

As can see, the X$ structures are quite cryptic, but there are hints about their contents.

 

We can also further explore the X$ structures by viewing the X$ksqst table. This table show the kernel status for all enqueues within your Oracle database:

 

-- See the gets and waits for different types of enqueues:
select
   *
from
   x$ksqst
where 
   ksqstget > 0;

 

Here is an actual listing from this script, showing the enqueue data from a real Oracle instance.

 

SQL> @x2
ADDR                   INDX    INST_ID KS   KSQSTGET   KSQSTWAT
---------------- ---------- ---------- -- ---------- ----------
00000004269DBB10         49          1 BR       1045          0
00000004269DBBB0         69          1 CF     245954          0
00000004269DBBC8         72          1 CI       5780          0
00000004269DBC28         84          1 CU       1886          0
00000004269DBCE0        107          1 DL         34          0
00000004269DBD10        113          1 DR       3053          0
00000004269DBD30        117          1 DV        389          0
00000004269DBD40        119          1 DX    1255971          0
00000004269DC138        246          1 HW      11377          0
00000004269DC218        274          1 IS       2305          0
00000004269DC308        304          1 JQ       1514          0
00000004269DC610        401          1 MR       1083          0
00000004269DC8A8        484          1 PE     212238          0
00000004269DC8B0        485          1 PF          1          0
00000004269DCB20        563          1 RT          1          0
00000004269DCC08        592          1 SQ       1868          0
00000004269DCC20        595          1 ST      26909          0
00000004269DCC98        610          1 TC        220          0
00000004269DCCE8        620          1 TM     378751          0
00000004269DCD18        626          1 TS       6605          0
00000004269DCD20        627          1 TT      64173          0
00000004269DCD40        631          1 TX     392834          0
00000004269DCE18        658          1 US     134438          0
00000004269DCFE0        715          1 WL        192          0

 

It is great fun to attempt queries against the X$ structures and see if you can guess the contents of the structures. Below is a query that will display the largest number of blocks that can be written by DBWR at any time:

 

select
   kviival write_batch_size
from  
   x$kvii
where
   kviitag = 'kcbswc';

 

Here is the output from this query. This displays the write batch size for the Oracle database.

 

SQL> connect internal as sysdba;
Connected.
SQL> @x1
WRITE_BATCH_SIZE
----------------
            4096

 

In sum, there is a wealth of interesting information in the X$ structures, and those DBAs with a propensity to learn Oracle Internals, these queries can be both fun and informative.

 


 

 

 

 

 

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 
 
email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.