René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

Oracle's x$ Tables

x$ tables are the sql interface to viewing oracle's memory in the SGA.
The names for the x$ tables can be queried with
select kqftanam from x$kqfta;

x$activeckpt

x$bh

Information on buffer headers.
Contains a record (the buffer header) for each block in the buffer cache.
This select statement lists how many blocks are Available, Free and Being Used.
select count(*), State from (
    select decode (state, 
       0, 'Free',
       1, decode (lrba_seq,
          0, 'Available',
             'Being Used'),
       3, 'Being Used',
          state) State
     from x$bh )
    group by state
The meaning of state:
0FREEno valid block image
1XCURa current mode block, exclusive to this instance
2SCURa current mode block, shared with other instances
3CR a consistent read (stale) block image
4READbuffer is reserved for a block being read from disk
5MRECa block in media recovery mode
6IRECa block in instance (crash) recovery mode
The meaning of tch: tch is the touch count. A high touch count indicates that the buffer is used often. Therefore, it will probably be at the head of the MRU list. See also touch count.
The meaning of tim: touch time.
class represents a value designated for the use of the block.
lru_flag
set_ds maps to addr on x$kcbwds.
le_addr can be outer joined on x$le.le_addr.
flag is a bit array.
Bitif set 
0 Block is dirty 
4temporary block 
9 or 10 ping 
14 stale 
16 direct 
524288 (=0x80000) Block was read in a full table scanSee this link

x$bufqm

x$class_stat

x$context

x$globalcontext

x$hofp

x$hs_session

The x$kc... tables

x$kcbbhs

x$kcbmmav

x$kcbsc

x$kcbwait

x$kcbwbpd

Buffer pool descriptor, the base table for v$buffer_pool.
How is the buffer cache split between the default, the recycle and the keep buffer pool.

x$kcbwds

Set descriptor, see also x$kcbwbpd
The column id can be joined with v$buffer_pool.id.
The column bbwait corresponds to the buffer busy waits wait event.
Information on working set buffers
addr can be joined with x$bh.set_ds.
set_id will be between lo_setid and hi_setid in v$buffer_pool for the relevant buffer pool.

x$kccal

x$kccbf

x$kccbi

x$kccbl

x$kccbp

x$kccbs

x$kcccc

x$kcccf

x$kccdc

x$kccdi

x$kccdl

x$kccfc

x$kccfe

x$kccfn

x$kccic

x$kccle

Controlfile logfile entry. Use
select max(lebsz) from x$kccle
to find out the size of a log block. The log block size is the unit for the following init params: log_checkpoint_interval, _log_io_size, and max_dump_file_size.

x$kcclh

x$kccor

x$kcccp

Checkpoint Progress:
The column cpodr_bno displays the current redo block number. Multiplied with the OS Block Size (usually 512), it returns the amount of bytes of redo currently written to the redo logs. Hence, this number is reset at each log switch.
k$kcccp can (together with x$kccle) be used to monitor the progress of the writing of online redo logs. The following query does this.
select
  le.leseq                        "Current log sequence No",
  100*cp.cpodr_bno/le.lesiz       "Percent Full",
  cp.cpodr_bno                    "Current Block No",
  le.lesiz                        "Size of Log in Blocks"
from
  x$kcccp cp,
  x$kccle le
where
  LE.leseq =CP.cpodr_seq
  and bitand(le.leflg,24)=8;
bitand(le.leflg,24)=8 makes sure we get the current log group
How much redo is written by Oracle uses a variation of this SQL statement to track how much redo is written by different DML Statements.

x$kccrs

x$kccrt

x$kccsl

x$kcctf

x$kccts

x$kcfio

x$kcftio

x$kckce

x$kckty

x$kclcrst

x$kcrfx

x$kcrmf

x$kcrmx

x$kcrralg

x$kcrrarch

x$kcrrdest

x$kcrrdstat

x$kcrrms

x$kcvfh

x$kcvfhmrr

x$kcvfhonl

x$kcvfhtmp

x$kdnssf

The x$kg... tables

KG stands for kernel generic

x$kghlu

This view shows one row per shared pool area. If there's a java pool, an additional row is displayed.

x$kgicc

x$kgics

x$kglcursor

x$kgldp

x$kgllk

This table lists all held and requested library object locks for all sessions. It is more complete than v$lock.
The column kglnaobj displays the first 80 characters of the name of the object.
select 
  kglnaobj, kgllkreq
from 
  x$kgllk x join v$session s on 
    s.saddr = x.kgllkses;
kgllkreq = 0 means, the lock is held, while kgllkreq > 0 means that the lock is requested.

x$kglmem

x$kglna

x$kglna1

x$kglob

Library Cache Object

x$kglsim

x$kglst

x$kgskasp

x$kgskcft

x$kgskcp

x$kgskdopp

x$kgskpft

x$kgskpp

x$kgskquep

x$kjbl

x$kjbr

x$kjdrhv

x$kjdrpcmhv

x$kjdrpcmpf

x$kjicvt

x$kjilkft

x$kjirft

x$kjisft

x$kjitrft

x$kksbv

x$kkscs

x$kkssrd

x$klcie

x$klpt

x$kmcqs

x$kmcvc

x$kmmdi

x$kmmrd

x$kmmsg

x$kmmsi

x$knstacr

x$knstasl

x$knstcap

x$knstmvr

x$knstrpp

x$knstrqu

x$kocst

The x$kq... tables

x$kqfco

This table has an entry for each column of the x$tables and can be joined with x$kqfta.
The column kqfcosiz indicates the size (in bytes?) of the columns.
select
  t.kqftanam "Table Name",
  c.kqfconam "Column Name",
  c.kqfcosiz "Column Size"
from
  x$kqfta t,
  x$kqfco c
where
  t.indx = c.kqfcotab

x$kqfdt

x$kqfsz

x$kqfta

It seems that all x$table names can be retrieved with the following query.
select kqftanam from x$kqfta;
This table can be joined with x$kqfco which contains the columns for the tables:
select
  t.kqftanam "Table Name",
  c.kqfconam "Column Name"
from
  x$kqfta t,
  x$kqfco c
where
  t.indx = c.kqfcotab

x$kqfvi

x$kqfvt

x$kqlfxpl

x$kqlset

x$kqrfp

x$kqrfs

x$kqrst

x$krvslv

x$krvslvs

x$krvxsv

The x$ks... tables

KS stands for kernel services.

x$ksbdd

x$ksbdp

x$ksfhdvnt

x$ksfmcompl

x$ksfmelem

x$ksfmextelem

x$ksfmfile

x$ksfmfileext

x$ksfmiost

x$ksfmlib

x$ksfmsubelem

x$ksfqp

x$ksimsi

x$ksled

x$kslei

x$ksles

x$kslld

x$ksllt

x$ksllw

x$kslwsc

x$ksmfs

x$ksmfsv

This SGA map.

x$ksmge

x$ksmgop

x$ksmgsc

x$ksmgst

x$ksmgv

x$ksmhp

x$ksmjch

x$ksmjs

x$ksmlru

Memory least recently used
Whenever a select is performed on x$ksmlru, its content is reset!
This table show which memory allocations in the shared pool caused the throw out of the biggest memory chunks since it was last queried.

x$ksmls

x$ksmmem

This 'table' seems to allow to address (that is read (write????)) every byte in the SGA. Since the size of the SGA equals the size of select sum(value) from v$sga, the following query must return 0 (at least on a four byte architecture. Don't know about 8 bytes.)
select 
  (select sum(value)  from v$sga  ) -
  (select 4*count(*) from x$ksmmem) "Must be Zero!"
from 
  dual;

x$ksmsd

x$ksmsp

x$ksmsp_nwex

x$ksmspr

x$ksmss

x$ksolsfts

x$ksolsstat

x$ksppcv

x$ksppcv2

Contains the value kspftctxvl for each parameter found in x$ksppi. Determine if this value is the default value with the column kspftctxdf.

x$ksppi

This table contains a record for all documented and undocumented (starting with an underscore) parameters. select ksppinm from x$ksppi to show the names of all parameters. Join indx+1 with x$ksppcv2.kspftctxpn.

x$ksppo

x$ksppsv

x$ksppsv2

x$kspspfile

x$ksqeq

x$ksqrs

x$ksqst

Enqueue management statistics by type.
ksqstwat: The number of wait for the enqueue statistics class.
ksqstwtim: Cumulated waiting time. This column is selected when v$enqueue_stat.cum_wait_time is selected.
The types of classes are:
BLBuffer Cache Management
CFControlfile Transaction
CICross-instance call invocation
CUBind Enqueue
DFDatafile
DLDirect Loader index creation
DMDatabase mount
DP???
DRDistributed Recovery
DXDistributed TX
FBacquired when formatting a range of bitmap blocks far ASSM segments. id1=ts#, id2=relative dba
FSFile Set
INInstance number
IRInstance Recovery
ISInstance State
IVLibrary cache invalidation
JDSomething to do with dbms_job
JQJob queue
KKRedo log kick
LA..LPLibrary cache lock
MDenqueue for Change data capture materialized view log (gotten internally for DDL on a snapshot log) id1=object# of the snapshot log.
MRMedia recovery
NA..NZLibrary cache pin
PFPassword file
PIParallel slaves
PRProcess startup
PSParallel slave synchronization
SCSystem commit number
SMSMON
SQSequence number enqueue
SRSynchronized replication
SSSort segment
STSpace management transaction
SVSequence number value
SWSuspend writes enqueue gotten when someone issues alter system suspend|resume
TATransaction recovery
ULUser defined lock
UNUser name
USUndo segment, serialization
WLRedo log being written
XAInstance attribute lock
XIInstance registration lock
XRAcquired for alter system quiesce restricted

x$kstex

x$ksull

x$ksulop

x$ksulv

x$ksumysta

x$ksupr

x$ksuprlat

x$ksurlmt

x$ksusd

Contains a record for all statistics.

x$ksuse

x$ksusecon

x$ksusecst

x$ksusesta

x$ksusgif

x$ksusgsta

x$ksusio

x$ksutm

x$ksuxsinst

x$ktadm

x$targetrba

x$ktcxb

The SGA transaction table.

x$ktfbfe

x$ktfthc

x$ktftme

x$ktprxrs

x$ktprxrt

x$ktrso

x$ktsso

x$ktstfc

x$ktstssd

x$kttvs

Lists save undo for each tablespace: The column kttvstnm is the name of the tablespace that has saved undo. The column is null otherwise.

x$kturd

x$ktuxe

Kernel transaction, undo transaction entry

x$kvis

Has (among others) a row containing the db block size:
 select kvisval from x$kvis where kvistag = 'kcbbkl'

x$kvit

x$kwddef

x$kwqpd

x$kwqps

x$kxfpdp

x$kxfpns

x$kxfpsst

x$kxfpys

x$kxfqsrow

x$kxsbd

x$kxscc

x$kzrtpd

x$kzspr

x$kzsrt

x$le

Lock element: contains an entry for each PCM lock held for the buffer cache. x$le can be left outer joined to x$bh on le_addr.

x$le_stat

x$logmnr_callback

x$logmnr_contents

x$logmnr_dictionary

x$logmnr_logfile

x$logmnr_logs

x$logmnr_parameters

x$logmnr_process

x$logmnr_region

x$logmnr_session

x$logmnr_transaction

x$nls_parameters

x$option

x$prmsltyx

x$qesmmiwt

x$qesmmsga

x$quiesce

x$uganco

x$version

x$xsaggr

x$xsawso

x$xssinfo

A perlscript to find x$ tables

#!/usr/bin/perl -w

use strict;

open O, ("/appl/oracle/product/9.2.0.2/bin/oracle");
open F, (">x");

my $l;
my $p = ' ' x 40;
my %x;

while (read (O,$l,10000)) {
  $l = $p.$l;

  foreach ($l =~ /(x\$\w{3,})/g) {
   $x{$_}++;
  }

  $p = substr ($l,-40);
}

foreach (sort keys %x) {
  print F "$_\n";
}
Obviously, it is also possible to extract those names through x$kqfta