常用的Oracle x$ Tables,本文章源自:http://www.adp-gmbh.ch[@more@]
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.
The meaning of state:
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
.
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 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.
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 |
x$kcbwbpd
Buffer pool descriptor, the base table for
v$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$kccle
Controlfile logfile entry. Use
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$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.
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$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.
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这一列。
2、KGLLKSNM:对应于v$session的sid
3、KGLLKUSE:对应于v$session的saddr这一列,即session的地址。
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的占有者,其它->锁的请求者。
2、KGLLKSNM:对应于v$session的sid
3、KGLLKUSE:对应于v$session的saddr这一列,即session的地址。
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的占有者,其它->锁的请求者。
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.
x$kqfta
It seems that all x$table names can be retrieved with the following query.
This table can be joined with
x$kqfco
which contains the columns for the tables:
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$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.)
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$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.
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 |
SM | SMON |
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 |
x$ksusd
Contains a record for all
statistics
.
x$ktcxb
The
SGA
transaction table.
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$ktuxe
Kernel transaction, undo transaction entry
x$kvis
Has (among others) a row containing the db block size:
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$xssinfo
A perlscript to find x$ tables
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/717880/viewspace-822126/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/717880/viewspace-822126/