Oracle X$ 表
5墨值下载
The X$ tables that are too obvious, too obscure, or too uninteresting are not included here.
Table Name Guessed Acronym Comments
x$activeckpt active checkpoint
Ckpt_type 2 for MR checkpoint (Ref), 3 for interval (Ref) or thread checkpoint (Ref), 7 for incremental checkpoint, 1
(Ref).
x$bh buffer header
This table is commonly used to find the object and the file# and block# of its header when there's high cache buffe
v$latch_children b where a.hladdr = b.addr for the said latch (whose sleeps you think are too high). You can also us
dbarfil, dbablk, count(*) from x$bh group by dbarfil, dbablk having count(*) > 2. Note obj column matches dba_ob
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
records how many times a particular buffer has been accessed. Its flag column is explained by J. Lewis (some unuse
plugged_from_foreign_db in 12c); explanation of state, mode and indx can be found in Anjo Kolk's paper. Tim is tim
position on LRU lists (Ref and 136312.1); 2 moved_to_tail, 4 on_auxiliary_list (auxliary LRU), 8 hot_buffer (on hot
x$ckptbuf
checkpoint buffer
(queue)
Lists the buffers on the checkpoint queue. Immediately after a full checkpoint, the buffers with non-zero buf_ptr an
x$dbgalertext debug alert extented
One use is to find old alert.log text long after you recycled the physical file: select originating_timestamp, message
columns are also interesting and are not available in alert.log.
x$dbglogext debug log extended 12c
x$dbgricx,
x$dbgrifx,
x$dbgrikx,
x$dbgripx
debug ?
You can quickly summarize what kind of errors the database has had: select error_facility||'-'||error_number, count
by 2, and optionally restrict to a certain time range. You can summarize on a more granular level, such as shared po
records of these errors in (undocumented) v$diag_incident or v$diag_diagv_incident. In any case, you may find this
x$dbgrikx.
x$dbkece
debug kernel error,
critical error
Base table of undocumented v$diag_critical_error but includes facility dbge (Diagnostic Data Extractor or dde)
x$dbkefefc
debug kernel error,
fatal error flood
control
Rules for flood control on too many fatal errors.
x$dglparam
data guard logical
parameters
Base table of dba_logstdby_parameters but includes invisible parameters.
x$diag_alert_ext
diagnostics alert
extended
Base table of v$diag_alert_ext. Same as x$dbgalertext but has more lines, slower to query
x$diag_hm_run,
x$diag_vhm_run
diagnostics health
monitor runs
Base table of undocumented v$diag_(v)hm_run. Health monitor job records. Maybe complementary to v$hm_run?
x$diag_ips_confi
guration
diagnostics incident
packaging service
configuration
Base table of v$diag_ips_configuration. Some ADR IPS related config info. Like a few other v$diag* (or x$diag*) tab
matched as if there're trailing characters. CTAS to create a regular table against which you query, or use subquery f
x$dnfs_meta dNFS metadata Some metadata related to dNFS, SGA memory, message timeout, ping timeout, etc.
x$dra_failure
data recovery advisor
failures
DRA failure names and descriptions.
x$drm_history,
x$drm_history_s
tats
dynamic remastering
history, stats
History of RAC DRM and stats. Parent_key is object_id. If an object is remastered to another node (new_master) to
there's also x$drm_wait_stats.
x$ipcor_topo_do
main,
x$ipcor_topo_nd
ev
ipc ? domain / name
of device
12cR2 RAC. Interconnect interface protocol (e.g. ethernet), names, MAC. No IP addresses, which are in v$cluster_in
x$ksipcip.
x$jskjobq
job scheduling, job
queue
Internal job queue. Job_oid is object_id in dba_objects. If you must query this table, exit the session as soon as yo
an exclusive JS lock, which will block CJQ process! Rollback or commit won't release the lock.
x$k2gte,
x$k2gte2
kernel 2-phase
commit, global
transaction entry
See Note:104420.1. Find sessions coming from or going to a remote database; in short, x$k2gte.k2gtdses matches
ordered */ substr(s.ksusemnm,1,10)||'-'|| substr(s.ksusepid,1,10) origin, substr(g.k2gtitid_ora,1,35) gtxid, substr(
username, substr(decode(bitand(ksuseidl,11), 1,'ACTIVE', 0, decode( bitand(ksuseflg,4096) , 0,'INACTIVE','CACHED
waiting from x$k2gte g, x$ktcxb t, x$ksuse s, x$ksled e where g.k2gtdxcb=t.ktcxbxba and g.k2gtdses=t.ktcxbses a
checking for DX locks for outgoing sessions (since a DX lock only shows up in v$lock for the current distributed tran
k2gtetyp which may show 2 for 'TIGHTLY COUPLED' instead of 0 for 'FREE'. One use of x$k2gte[2] is the clearly tran
numbers in v$global_transaction.globalid.
x$kbrpstat
kernel backup
recovery process(?)
statistics
12c. RMAN related
x$kcbbes kernel cache, buffer ? Check incremental checkpoints (259586.1)
x$kcbbf kernel cache, buffer Jonathan Lewis ("_db_handles")
buffer_handles
x$kcbfwait
kernel cache, buffer
file wait
A commonly used query breaks down the contents of v$waitstat into per-datafile statistics: select name, count, tim
x$kcbkpfs
kernel cache, buffer
ckpt prefetch
statistics
Tanel Poder
x$kcbkwrl
kernel cache, buffer
write list
each row for the write list of one DBWR
x$kcbldrhist
kernel cache, buffer
load direct read
history
x$kcbobh
kernel cache, buffer,
objectqueue buffer
header
10g and up. Tanel Poder
x$kcboqh
kernel cache, buffer,
object queue header
See above
x$kcbpdbrm
kernel cache, buffer,
PDB resource metric
12c. Column value is actual buffer cache usage in db_block_size, the same (except for unit) as buffer_cache_bytes
also gives lower (column minimum, bound by db_cache_size if defined) and upper (column maximum) limits.
x$kcbsw
kernel cache, buffer
statistics why
Note:34405.1: select kcbwhdes, why0+why1+why2 "Gets", "OTHER_WAIT" from x$kcbsw s, x$kcbwh w where s.in
10g only; for newer versions, see x$kcbuwhy); Ref1 ("statistics about the way these [x$kcbwh] functions have been
x$kcbuwhy
kernel cache, buffer
why
For 11g and up, select kcbwhdes, why0+why1+why2 "Gets", "OTHER_WAIT" from x$kcbsw s, x$kcbwh w, x$kcbuw
s."OTHER_WAIT">0
x$kcbwbpd
kernel cache, buffer
workingset buffer pool
descriptor
See 183770.999 for relationship to x$bh and x$kcbwds. Some people use this query to find how many blocks of a s
decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',7,'16K SU
bh.object_name,bh.blocks from x$kcbwds ds, x$kcbwbpd pd, (select /*+ use_hash(x) */ set_ds, o.name object_na
and x.state!=0 and o.owner#!=0 and o.name='&mytable' group by set_ds, o.name) bh where ds.set_id>=pd.bp_lo
ds.addr=bh.set_ds
x$kcbwds
kernel cache, buffer
workingset
descriptors
See above. Also see Ref1, Ref2, Ref3. Total row count in this table is _db_block_lru_latches, although only db_write
columns are in the note section of Ref4 (search for "Xuan Bui of Oracle France") or in Ref5.
x$kcbwh
kernel cache, buffer
where/why
See x$kcbsw for SQL. Ref1 ("different functions that may be used to perform different types of logical I/O"), Ref2
x$kcccf
kernel cache,
controlfilemanagemen
t control file
In 10gR1, to find controlfile size as viewed at OS level but from inside Oracle, select cfnam, (cffsz+1)*cfbsz from x$
as the command dbfsize ($ORACLE_HOME/bin/dbfsize is available on UNIX, regardless Oracle version.) I
although Reference manual misses them.
x$kcccp
kernel cache,
controlfile checkpoint
progress
S. Adams and K Gopalakrishnan use this view to find how much the current redo log is filled. Eygle studied instance
x$kccdi
kernel cache,
controlfilemanagemen
t database
information
x$kccle
kernel cache,
controlfile logfile entry
lebsz may be used to show redo logfile block size, usually 512; should report the same as the command dbfsize
x$kccnrs,
x$kccrsp
kernel cache,
controlfile non-
guaranteed
restorepoint; kernel
cache, controlfile
restore point
Base tables of v$restore_point, for non-guaranteed and guaranteed restore points. Retain records of them after the
x$kcfis*
kernel cache, file
intelligent scan
Exadata smart scan related. Note that views for cell servers per se are x$kxdcm_* (kernel Exadata cell module) plu
x$kclcrst
kernel cache, (RAC)
lock, consistent read
statistics
base table of v$cr_block_server or v$bsp, used to troubleshoot global cache cr requests
x$kclfh
kernel cache, (RAC)
lock file hashtable
x$kclfi
kernel cache, (RAC)
lock file index
x$kclfx
kernel cache, (RAC)
lock (element) freelist
statistics
See Ref1, Ref2, 1492990.1. If lwm is too low, you may see 'gc freelist' wait.
of 10
5墨值下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
下载排行榜
评论