ITPub博客

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

Oracle's x$ Tables(zz)

原创 Linux操作系统 作者:dragondb 时间:2018-12-10 12:00:04 0 删除 编辑

Oracle x$ "tables" are instance or session memory structures presented as tables and serve as base tables of most v$ views. Entries in the following list are added one at a time in the course of my study of Oracle internals by reading books and online materials (particularly at ixora), through my own lab test, by interpreting the text in v$fixed_view_definition if available, or because other nice folks email me. Obviously there're much more tables in x$kqfdt and x$kqfta than listed below. But generally I omit those whose usage is not interesting or its meaning is too obvious in v$fixed_view_definition.

Names of most x$ tables begin with x$k. The letter after "k" indicates what kernel layer this data structure belongs in. Check Chapter 1 of Steve Adams' book Oracle8i Internal Services to find out the functionality of each layer. Usually that knowledge can give you a hint at what an Oracle internal error is about. (If the letter "k" is preceded by "s", as in skgxp, then it's an OSD (operating system dependent) function or variable.) Letters after the first two represent sublayers, such as "l" in kgl means library cache. These are not easy to guess. Note:175982.1 has an extensive list of codes and is very informative.


Table NameAcronym ExpandedComments
x$bhbuffer header (buffer hash according to J. Morle)The most common use of this table is to find the object and the file# and block# of its header when there's high cache buffers chains latch contention: select obj, dbarfil, dbablk from x$bh a, v$latch_children b where a.hladdr = b.addr for the said latch (whose sleeps you think are too high). You can also use this table to see if a specific buffer has too many clones: select dbarfil, dbablk, count(*) from x$bh group by dbarfil, dbablk having count(*) > 2. Note that obj column matches dba_objects.data_object_id, not object_id. For performance reason, don't merge dba_extents with the query of x$bh that has a group by, unless you use in-line view and no_merge hint (see J. Lewis Practical Oracle8i, p.215) The tch column, touch count, records how many times a particular buffer has been accessed. Its flag column is explained by J. Lewis; explanation of state, mode and indx can be found in Anjo Kolk's paper.
x$k2gtekernel 2-phase commit, global transaction entryMark Bobak's query (originally in Metalink forum thread 524821.994, where he further attributed authorship) uses this table to find sessions coming from or going to a remote database; in short, x$k2gte.k2gtdses matches v$session.saddr, .k2gtdxcb matches v$transaction.addr. It's more robust than this query, and better than checking for DX locks for outgoing sessions (since a DX lock only shows up in v$lock for the current distributed transaction session).
x$kcbbfkernel cache, buffer ??Ref1 ("_db_handles")
x$kcbfwaitkernel cache, buffer file waitA commonly used query breaks down the contents of v$waitstat into per-datafile statistics: select count, time, name from v$datafile df, x$kcbfwait fw where fw.indx+1 = df.file#
x$kcbswkernel cache, buffer statistics whyNote:34405.1 (select kcbwhdes, why0+why1+why2 "Gets", "OTHER_WAIT" from x$kcbsw s, x$kcbwh w where s.indx=w.indx and s."OTHER_WAIT">0 order by 3), Ref1 ("statistics about the way these [x$kcbwh] functions have been used")
x$kcbwaitkernel cache, buffer wait
x$kcbwdskernel cache, buffer working descriptors
x$kcbwhkernel cache, buffer ??See x$kcbsw for SQL. Ref1 ("different functions that may be used to perform different types of logical I/O"), Ref2
x$kcccfkernel cache, controlfilemanagement control fileIn 10gR1, to find controlfile size as viewed at OS level but from inside Oracle, select cfnam, (cffsz+1)*cfbsz from x$kcccf. cfbsz is the controlfile log block size; should report the same as the command dbfsize controlfile ($ORACLE_HOME/bin/dbfsize is available on UNIX, regardless Oracle version.) In 10gR2, block size and file size are both in v$controlfile although Reference manual misses them.
x$kcccpkernel cache, controlfile checkpoint progressS. Adams and K Gopalakrishnan use this view to find how much the current redo log is filled.
x$kccdikernel cache, controlfilemanagement database information
x$kcclekernel cache, controlfile logfile entrylebsz may be used to show redo logfile block size, usually 512; should report the same as the command dbfsize redologfile ($ORACLE_HOME/bin/dbfsize is available on UNIX only)
x$kcfiokernel cache, file I/O
x$kclcrstkernel cache, lock, consistent read statisticsbase table of v$cr_block_server or v$bsp, used to troubleshoot global cache cr requests
x$kclfhkernel cache, lock file header
x$kclfikernel cache, lock file index
x$kcluhkernel cache, lock undo header
x$kcluikernel cache, lock undo index
x$kcrfxkernel cache, redo file context"columns bfs (buffer size) and bsz (block size). Dividing bfs by bsz gives mxr (the maximum number of blocks to read size)" (from Anjo Kolk's paper)
x$kdxstkernel data, index statusused in catalog.sql to create index_stats
x$kdxhskernel data, index histogramused in catalog.sql to create index_histogram
x$kghlukernel generic, heap LRUs
x$kglcursorkernel generic, librarycache cursorBase table for v$sql, v$sqlarea. Fixed view based on x$kglob according to x$kqfdt. See Note 1 or x$kglob for more details. One use of this table is for finding partially parsed SQLs because they cause parse failures (viewable in v$sysstat or v$sesstat). Their kglobt02 (command type) is 0, kglobt09 (child number) is 65535 for the child, SQL text length is cut to 20 chars, kglobt17 and kglobt18 (parsing and user schema) are 0 or 2147483644 (for 32-bit Oracle) depending on if it's parent or child, and obviously miss heap 6 (cursor body). Find them by select kglnaobj, kglnatim, kglobts0, kglnahsh from x$kglcursor where kglobt02 = 0 (kglobts0 is module; you can further restrict by kglnatim i.e. first_load_time).
x$kgllkkernel generic, librarycache lockUsed in catblock.sql to build dba_kgllock. If you get library cache lock or pin wait, kgllkhdl matches v$session_wait.p1raw (handle address), and kglnaobj is the first 80 characters of the object name. Note:122793.1 has this SQL for our convenience: select * from x$kgllk lock_a where kgllkreq = 0 and exists (select lock_b. kgllkhdl from x$kgllk lock_b where kgllkses = '&saddr_from_v$session' /* blocked session */ and lock_a.kgllkhdl = lock_b.kgllkhdl and kgllkreq > 0). Kgllkadr column is shown in event 10270 trace files to find SQLs in session cursor cache (Ref)
x$kglobkernel generic, librarycache objectTo find library cache object for wait events library cache pin or lock and pipe get or put, match kglhdadr with v$session_wait.p1raw. kglhdflg is partially explained in Note:311689.1 (for permanent keeping). kglhddmk may be data object load mask; can be used to identify the number of the loaded heap, counted from 0 (see comment of 06/12/01 in Bug:1164709). Steve Adams' objects_on_hot_latches.sql finds the way Oracle links a library cache object (based on kglnahsh) to a specific library cache child latch. x$kglob, and in case of cursors x$kglcursor too, can be used to find library cache objects that are partially built therefore not visible in v$sql(XXX), v$open_cursor, v$object_dependency. (Try typing select *; and enter, then check these views!)
x$kglpnkernel generic, librarycache pinused in catblock.sql to build dba_kgllock
x$kglrdkernel generic, librarycache readonly dependencykglnacnm is PL/SQL program unit or anonymous block while kglnadnm is the individual SQLs inside the PL/SQL unit. (see Ref; see also v$object_dependency, but that doesn't show relation between PL/SQL block and its contents)
x$kglstkernel generic, librarycache status
x$kqfcokernel query, fixed table columnsx$kqfco.kqfcotab=x$kqfta.indx
x$kqftakernel query, fixed table
x$kqfdtkernel query, fixed derived tableacronym explained by Julian Dyke: it contains x$kglcursor, x$kgltable etc. which are based on x$kglob; effectively these are views of other x$ tables, but Oracle couldn't call them views because they already had x$kqfvi
x$kqfpkernel query, fixed procedureused in catprc.sql to build disk_and_fixed_objects view
x$kqfszkernel query, fixed size (size of fixed objects in current version of Oracle)
x$kqfvikernel query, fixed view
x$kqfvtkernel query, fixed view table (how fixed view is built on fixed tables)
x$ksled, x$kslei, x$ksleskernel service, event definition, events for instance, events for session, respectively ("l" probably means "lock")
x$kslpokernel service, latch postingNote:653299 says it "tracks which function is posting smon". Ksllwnam column (the part before semicolon if it exists) can match v$latch_misses.location to identify the latch that uses this function.
x$ksmfskernel service, memory fixed SGAalso contains db_block_buffers and log_buffer sizes for some reason
x$ksmfsvkernel service, memory fixed SGA variablesdetailing fixed SGA: select a.ksmfsnam, a.ksmfstyp, a.ksmfssiz, b.ksmmmval from x$ksmfsv a, x$ksmmem b where a.ksmfsadr = b.addr and a.ksmfsnam like... (Ref. p.82, Oracle Internal Services). For a latch, get ksmfsnam by matching x$ksmfsv.ksmfadr with x$kslld.kslldadr. You can see SGA parameters in ksmfsnam column and get their values with oradebug dumpvar varname or all values with oradebug dumpsga
x$ksmhpkernel service, memory heapS. Adams, "What it returns depends on which heap descriptor you join to it. It is effectively a function returning the contents of an arbitrary heap that takes the heap descriptor as its argument."
x$ksmjskernel service, memory java_pool summary
x$ksmlrukernel service, memory LRURefer to Metalink Notes 61623.1 and 43600.1 for details. Note that query on this table can only be done once; subsequent query returns no rows unless large chunk shared pool allocations happened in the interim.
x$ksmlskernel service, memory large_pool summary
x$ksmmemkernel service, memoryEntire SGA memory map. You can find your database version by select ksmmmval from x$ksmmem where indx = 2 (if it's 64-bit Oracle, try 1), regardless machine architecture endian-ness. Note that the 4 bytes containing the version are delimited as XX.X.XX.X.XX so 09200300 is 9.2.0.3.0. select a.*, substr(utl_raw.cast_to_varchar2(ksmmmval),1,20) from x$ksmmem a where rownum < 30. If you see "sga" and "heap" in the last column, it's big-endian; if you see "ags" and "paeh", it's little-endian.-->Due to memory guard pages, you can only select from x$ksmmem specifying rownum < some number or indx = some value; otherwise the session may hang or throws ORA-3113 (Windows doesn't seem to have this problem). indx is SGA index, i.e. SGA address minus sgabeg (which is x$ksmmem.addr where indx = 0) divided by 4 (or whatever the gap is between two addr's), possibly plus some offset.
x$ksmppkernel service, memory pga heapPGA heap (variable area)
x$ksmsdkernel service, memory sga definition
x$ksmspkernel service, memory sga heapThe 3rd argument of ORA-4031 tells you which section of shared (or java or large) pool is short of memory. It matches x$ksmsp.ksmchcom (or v$sgastat.name).
x$ksmsprkernel service, memory shared pool reserved
x$ksmsskernel service, memory shared_pool summaryThe 3rd argument of ORA-4031 tells you which section of shared (or java or large) pool is short of memory. It matches x$ksmss.ksmssnam (or v$sgastat.name).
x$ksmupkernel service, memory uga heapUGA heap (variable area)
x$ksppcvkernel service, parameter, current (session) valueBase table of v$parameter and v$parameter2. See comments on x$ksppi.
x$ksppikernel service, parameter, parameter infoBase table of v$parameter, v$system_parameter and v$system_parameter2. Often used to see undocumented parameters: select a.ksppinm Parameter, a.ksppdesc Description, b.ksppstvl "Session Value", c.ksppstvl "Instance Value" from x$ksppi a, x$ksppcv b, x$ksppsv c where a.indx = b.indx and a.indx = c.indx and a.ksppinm like '_%' escape '' order by 1
x$ksppsvkernel service, parameter, system valueBase table of v$system_parameter and v$system_parameter2. See comments on x$ksppi.
x$ksqeqkernel service, enqueue en-queue
x$ksqrskernel service, enqueue resource"shows all outstanding enqueues with an additional flag. It basically shows the same information as the v$lock table." from Note1, which also gives the meanings of the flags.
x$ksqstkernel service, enqueue_management statistics typesAcronym explained by K Gopalakrishnan. You can find how many times each type of enqueue lock has been taken since instance startup by select * from x$ksqst where ksqstget > 0 or in 9i select * from x$ksqst where ksqstsgt > 0 or ksqstfgt > 0. But v$enqueue_stat in 9i can also be used instead.
x$ksulvkernel service, user locale value
x$ksulopkernel service, user long operation
x$ksuprkernel service, user process
x$ksusekernel service, user session
x$ktcxbkernel transaction, control objectBase table of v$transaction. 4 bits of ktcxbflg column, exposed as v$transaction.flag, are explained in v$fixed_view_definition. Metalink 238763.996 explains the bit for isolation level. Since v$transaction is empty without a transaction, you can directly query x$ktcxb to find sessions with serializable isolation level: select * from v$session where taddr in (select ktcxbxba from x$ktcxb where bitand(ktcxbflg,268435456) <> 0). Other flags not shown in v$fixed_view_definition are: 1 read write and read committed, 4 read only.
x$ktfbfekernel transaction, file bitmap free extentFree extent bitmap in file header for LMT (equivalent to fet$ in DMT); check dba_free_space view definition
x$ktfbhckernel transaction, file bitmap ? ?Summarizes free space with one row per datafile (Ref); check dba_data_files or dba_temp_files view definition
x$ktfbuekernel transaction, file bitmap used extentUsed extent bitmap in file header for LMT (equivalent to uet$ in DMT)
x$ktuxekernel transaction, undo transaction entrySteve Adams says, you "get the SCN of the most recently committed (local) transaction" with select max(ktuxescnw * power(2, 32) + ktuxescnb) from x$ktuxe
x$kxfpsdskernel execution, fast process slave dequeue statisticsCurrent list of reasons for parallel execution dequeuing, as explained for wait event "parallel query dequeue wait" in Anjo Kolk's paper.
x$kzsprvkernel security, session privilegeSession-specific. Base table for v$enabledprivilege, which is base table of session_privs
x$kzsrokernel security, session roleused in many SQL scripts in ?/rdbms/admin
x$lelock elementTo find the buffer header in the cache, select a.* from x$bh a, x$le b where a.le_addr = b.addr (from Anjo Kolk's paper)
x$le_statlock element status
x$message(background process) messagesThis records (possibly) all actions each background process can do.
x$trace Beginning with 9i, x$trace records event tracing info. select event, count(*) from x$trace group by event shows what events are enabled internally (not shown in v$parameter). oerr ora eventID on UNIX shows the event name. RAC databases should have GES and GCS related events set. select pid, count(*) from x$trace group by pid shows how many events have been trapped by each oracle process (including those that exited). select sid, pid, count(*) from x$trace where (sid, pid) in (select sid, pid from v$session s, v$process p where s.paddr = p.addr) group by sid, pid order by 1, 2 shows the numbers for each currently existing session (I think without the where clause, exited sessions would be included). select event, op, time, seq#, data from x$trace where sid = &sid and pid = &pid order by time shows traced events for a session in question.
x$ugancouser global area, network connectionBase table of v$dblink. Since it's about UGA, each session has different content. After you end your distributed transactions (which includes distributed queries) and close database links, v$dblink no longer shows the entries. But x$uganco still has them, with ncoflg set to 8320 and hstflg set to 0. These entries linger even after shared pool flush (or global context flush in 10g).


Note 1 x$kglcursor columns
These columns are not exposed in v$sql based on comparison in v$fixed_view_definition in 9.2.0.1 (for column match between x$kglcursor and v$sql, 
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
0Block is dirty
4temporary block
9 or 10ping
14stale
16direct
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
view browser source): kglhdamk: always 0; kglhddmk: 0,1,65,253,...; some kind of masks (kglhdkmk, keep mask, is exposed as v$sql.kept_versions); kglhdexc: executions but not used any more; see documentation for v$db_object_cache.executions which is x$kglob.kglhdexc; 9i uses kglobt05 and 10g uses kglobt48; but kglobt05 and kglobt48 may be 0 perhaps on heap 6 flush(?) while kglhdexc keeps the old value? kglhdflg: (Ref) in my 9i DB: SQL> select to_char(kglhdflg,'xxxxxxxx'), count(*) from x$kglcursor group by to_char(kglhdflg,'xxxxxxxx'); TO_CHAR(K COUNT(*) --------- ---------- 10010000 193 10010001 39 12010000 49 50010000 95 kglhdnsp, kglhdobj, kglhdpmd: parent handle namespace, object, pin mode; kglnadlk: (DB link, Ref); kglnaown kglnaptm: previous time; probably deprecated, all null; Ref; kglobflg: Ref; kglobhd0: can be used "to perform the HEAPDUMP_ADDR dumps" (Bug:2247763) kglobhd1 kglobhd2 kglobhd3 kglobhd4 kglobhd5 kglobhd6 kglobhd7 kglobhs7 kglobpc0 kglobt22 kglobt23 kglobt24 kglobt25 kglobt26 kglobt27 kglobtl0 kglobtl1 kglobtn1 kglobtn2 kglobtn3 kglobtn4 kglobtn5 kglobtyp *****************************************************************

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/45259/viewspace-134960/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2003-05-05

  • 博文量
    33
  • 访问量
    24044