首页 > Linux操作系统 > Linux操作系统 > Oracle's x$ Tables

Oracle's x$ Tables

原创 Linux操作系统 作者:hrb_qiuyb 时间:2008-01-30 22:58:23 0 删除 编辑

常用的Oracle x$ Tables,本文章源自:


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:
0 FREE no valid block image
1 XCUR a current mode block, exclusive to this instance
2 SCUR a current mode block, shared with other instances
3 CR a consistent read (stale) block image
4 READ buffer is reserved for a block being read from disk
5 MREC a block in media recovery mode
6 IREC a 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.
set_ds maps to addr on x$kcbwds.
le_addr can be outer joined on x$le.le_addr.
flag is a bit array.
Bit if set
0 Block is dirty
4 temporary block
9 or 10 ping
14 stale
16 direct
524288 (=0x80000) Block was read in a full table scan See this link


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.


Set descriptor, see also x$kcbwbpd
The column id can be joined with v$
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.


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.


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.
  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"
  x$kcccp cp,
  x$kccle le
  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.


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.
  kglnaobj, kgllkreq
  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.
1、KGLLKHDL:对应于v$session_wait的p1raw列,表明library cache lock的锁地址。也对应于x$kglob的kglhdadr这一列。
4、KGLNAOBJ:包含了在librarky cache中的对象上执行命令的语句的前80个字符。
5、KGLLKMOD:代表的锁"占用"模式,0-> 'None', 1->'Null', 2-> 'Share', 3-> 'Exclusive',其它值->'Unknown'
6、KGLLKREP:代表"请求"模式,0-> 'None', 1->'Null', 2-> 'Share', 3-> 'Exclusive',其它值->'Unknown',另一个角度说明0->lock的占有者,其它->锁的请求者。


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.
  t.kqftanam "Table Name",
  c.kqfconam "Column Name",
  c.kqfcosiz "Column Size"
  x$kqfta t,
  x$kqfco c
  t.indx = c.kqfcotab


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:
  t.kqftanam "Table Name",
  c.kqfconam "Column Name"
  x$kqfta t,
  x$kqfco c
  t.indx = c.kqfcotab


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.


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 sum(value)  from v$sga  ) -
  (select 4*count(*) from x$ksmmem) "Must be Zero!"


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


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.


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:
BL Buffer Cache Management
CF Controlfile Transaction
CI Cross-instance call invocation
CU Bind Enqueue
DF Datafile
DL Direct Loader index creation
DM Database mount
DP ???
DR Distributed Recovery
DX Distributed TX
FB acquired when formatting a range of bitmap blocks far ASSM segments. id1=ts#, id2=relative dba
FS File Set
IN Instance number
IR Instance Recovery
IS Instance State
IV Library cache invalidation
JD Something to do with dbms_job
JQ Job queue
KK Redo log kick
LA..LP Library cache lock
MD enqueue for Change data capture materialized view log (gotten internally for DDL on a snapshot log) id1=object# of the snapshot log.
MR Media recovery
NA..NZ Library cache pin
PF Password file
PI Parallel slaves
PR Process startup
PS Parallel slave synchronization
SC System commit number
SQ Sequence number enqueue
SR Synchronized replication
SS Sort segment
ST Space management transaction
SV Sequence number value
SW Suspend writes enqueue gotten when someone issues alter system suspend|resume
TA Transaction recovery
UL User defined lock
UN User name
US Undo segment, serialization
WL Redo log being written
XA Instance attribute lock
XI Instance registration lock
XR Acquired for alter system quiesce restricted


Contains a record for all statistics.


The SGA transaction table.


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


Kernel transaction, undo transaction entry


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


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.


A perlscript. to find x$ tables

#!/usr/bin/perl -w

use strict;

open O, ("/appl/oracle/product/");
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) {

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

foreach (sort keys %x) {
  print F "$_n";

来自 “ ITPUB博客 ” ,链接:,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录


  • 博文量
  • 访问量