Note: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")
Mark 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).
A 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#
See x$kcbsw for SQL. Ref1 ("different functions that may be used to perform. different types of logical I/O"), Ref2
In 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.
S. Adams and K Gopalakrishnan use this view to find how much the current redo log is filled.
lebsz 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)
base table of v$cr_block_server or v$bsp, used to troubleshoot global cache cr requests
Base 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).
Used 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)
To 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!)
used in catblock.sql to build dba_kgllock
kglnacnm 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)
acronym explained by Julian Dyke: it contains x$kglcursor, x$kgltable etc. which are based on
Note: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.
also contains db_block_buffers and log_buffer sizes for some reason
detailing 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
S. 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."
Refer 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.
Entire 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 184.108.40.206.0. 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.
PGA heap (variable area)
The 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).
The 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).
UGA heap (variable area)
Base table of v$parameter and v$parameter2. See comments on x$ksppi.
Base 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
Base table of v$system_parameter and v$system_parameter2. See comments on x$ksppi.
"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.
Acronym 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.
Base 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.
Free extent bitmap in file header for LMT (equivalent to fet$ in DMT); check dba_free_space view definition
Summarizes free space with one row per datafile (Ref); check dba_data_files or dba_temp_files view definition
Used extent bitmap in file header for LMT (equivalent to uet$ in DMT)
Steve Adams says, you "get the SCN of the most recently committed (local) transaction" with select max(ktuxescnw * power(2, 32) + ktuxescnb) from x$ktuxe
Current list of reasons for parallel execution dequeuing, as explained for wait event "parallel query dequeue wait" in Anjo Kolk's paper.
Session-specific. Base table for v$enabledprivilege, which is base table of session_privs
used in many SQL scripts in ?/rdbms/admin
This records (possibly) all actions each background process can do.
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.
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).
The format is shown as following:
Table Name Acronym Expanded
Creation Date: 14-MAR-1995
Last Revision Date: 23-SEP-1999
This is a summary list of X$ Table Definitions - Last revision was 7.3.2
The main purpose of this note is to show the naming conventions.
[K]ernel Layer -Phase Commit [G]lobal [T]ransaction [E]ntry X$K2GTE - Current 2PC tx X$K2GTE2 - Current 2PC tx [C]ache Layer [B]uffer Management Buffer [H]ash X$BH - Hash Table Buffer LRU Statistics X$KCBCBH - [C]urrent [B]uffers (buckets) - lru_statistics X$KCBRBH - [R]ecent [B]uffers (buckets) - lru_extended Buffer [WAIT]s X$KCBWAIT - Waits by block class X$KCBFWAIT - Waits by File [W]orking Sets - 7.3 or higher X$KCBWDS - Set [D]escriptors [C]ontrol File Management [C]ontrol [F]ile List - 7.0.16 or higher X$KCCCF - Control File Names & status [D]atabase [I]nformation X$KCCDI - Database Information Data [F]iles X$KCCFE - File [E]ntries ( from control file ) X$KCCFN - [F]ile [N]ames [L]og Files X$KCCLE - Log File [E]ntries X$KCCLH - Log [H]istory ( archive entries ) Thread Information X$KCCRT - [R]edo [T]hread Information [F]ile Management X$KCFIO - File [IO] Statistics [L]ock Manager Component ( LCK ) [H]ash and Bucket Tables - 7.0.15 to 7.1.1, and 7.2.0 or higher X$KCLFH - File [H]ash Table X$KCLFI - File Bucket Table X$LE - Lock [E]lements X$LE_STAT - Lock Conversion [STAT]istics X$KCLFX - Lock Element [F]ree list statistics - 7.3 or higher X$KCLLS - Per LCK free list statistics - 7.3 or higher X$KCLQN - [N]ame (hash) table statistics - 7.3 or higher [R]edo Component [M]edia recovery - kcra.h - 7.3 or higher X$KCRMF - [F]ile context X$KCRMT - [T]hread context X$KCRMX - Recovery Conte[X]t [F]ile read X$KCRFX - File Read Conte[X]t - 7.3 or higher Reco[V]ery Component [F]ile [H]eaders X$KCVFH - All file headers X$KCVFHMRR - Files with [M]edia [R]ecovery [R]equired X$KCVFHONL - [ONL]ine File headers [K]ompatibility Management - 7.1.1 or higher X$KCKCE - [C]ompatibility Segment [E]ntries X$KCKTY - Compatibility [TY]pes X$KCKFM - Compatibility [F]or[M]ats ( index into X$KCKCE ) [D]ata Layer Sequence [N]umber Component X$KDNCE - Sequence [C]ache [E]ntries - 7.2 or lower [S]equence Enqueues - common area for enqueue objects X$KDNSSC - [C]ache Enqueue Objects - 7.2 or lower X$KDNSSF - [F]lush Enqueue Objects - 7.2 or lower X$KDNST - Cache [ST]atistics - 7.2 or lower Inde[X] Block Component X$KDXHS - Index [H]i[S]togram X$KDXST - Index [ST]atistics [G]eneric Layer [H]eap Manager X$KGHLU - State (summary) of [L]R[U] heap(s) - defined in ksmh.h [I]nstantiation Manager [C]ursor [C]ache X$KGICC - Session statistics - defined in kqlf.h X$KGICS - System wide statistics - defined in kqlf.h [L]ibrary Cache Manager ( defined and mapped from kqlf ) Bind Variables X$KKSBV - Library Object [B]ind [V]ariables Object Cache (KGL = Kernel General Library Cache) X$KGLOB - All [OB]jects X$KGLTABLE - Filter for [TABLE]s X$KGLBODY - Filter for [BODY] ( packages ) X$KGLTRIGGER - Filter for [TRIGGER]s X$KGLINDEX - Filter for [INDEX]es X$KGLCLUSTER - Filter for [CLUSTER]s X$KGLCURSOR - Filter for [CURSOR]s Cache Dependency X$KGLDP - Object [D]e[P]endency table X$KGLRD - [R]ead only [D]ependency table - 7.3 or higher Object Locks X$KGLLK - Object [L]oc[K]s Object Names X$KGLNA - Object [NA]mes (sql text) X$KGLNA1 - Object [NA]mes (sql text) with newlines - 7.2.0 or higher Object Pins X$KGLPN - Object [P]i[N]s Cache Statistics X$KGLST - Library cache [ST]atistics Translation Table X$KGLTR - Address [TR]anslation Access Table X$KGLXS - Object Access Table Authorization Table - 7.1.5 or higher X$KGLAU - Object Authorization table Latch Cleanup - 7.0.15 or higher X$KGLLC - [L]atch [C]leanup for Cache/Pin Latches [K]ompile Layer [S]hared Objects X$KKSAI - Cursor [A]llocation [I]nformation - 7.3.2 or higher [L]oader [L]ibrary X$KLLCNT - [C]o[NT]rol Statistics X$KLLTAB - [TAB]le Statistics [M]ulti-Threaded Layer [C]ircuit component X$KMCQS - Current [Q]ueue [S]tate X$KMCVC - [V]irtual [C]ircuit state [M]onitor Server/dispatcher [D]ispatcher X$KMMDI - [D]ispatcher [I]nfo (status) X$KMMDP - [D]ispatcher Config ( [P]rotocol info ) [S]erver X$KMMSI - [S]erver [I]nfo ( status ) X$KMMSG - [SG]a info ( global statistics) X$KMMRD - [R]equest timing [D]istributions s[Q]l Version and Option Layer Kernel [V]ersions X$VERSION - Library versions Kernel [O]ptions - 7.1.3 or higher X$OPTION - Server Options [Q]uery Layer [D]ictionary Cache Management X$KQDPG - [PG]a row cache cursor statistics [F]ixed Tables/views Management X$KQFCO - Table [CO]lumn definitions X$KQFDT - [D]erived [T]ables X$KQFSZ - Kernel Data structure type [S]i[Z]es X$KQFTA - Fixed [TA]bles X$KQFVI - Fixed [VI]ews X$KQFVT - [V]iew [T]ext definition - 7.2.0 or higher [R]ow Cache Management X$KQRST - Cache [ST]atistics X$KQRPD - [P]arent Cache [D]efinition - 7.1.5 or higher X$KQRSD - [S]ubordinate Cache [D]efinition - 7.1.5 or higher [S]ervice Layer [B]ackground Management [D]etached Process X$KSBDD - Detached Process [D]efinition (info) X$KSBDP - Detached [P]rocess Descriptor (name) X$MESSAGES - Background Message table [I]nstance [M]anagement - 7.3 or higher X$KSIMAT - Instance [AT]tributes X$KSIMAV - [A]ttribute [V]alues for all instances X$KSIMSI - [S]erial and [I]nstance numbers [L]ock Management [E]vent Waits X$KSLED - Event [D]escriptors X$KSLEI - [I]nstance wide statistics since startup X$KSLES - Current [S]ession statistics [L]atches X$KSLLD - Latch [D]escriptor (name) X$KSLLT - Latch statistics [ + Child latches @ 7.3 or higher ] X$KSLLW - Latch context ( [W]here ) descriptors - 7.3 or higher X$KSLPO - Latch [PO]st statistics - 7.3 or higher X$KSLWSC- No[W]ait and [S]leep [C]ount stats by Context -7.3 or higher [M]emory Management [C]ontext areas X$KSMCX - E[X]tended statistics on usage - 7.3.1 or lower Heap Areas X$KSMSP - SGA Hea[P] X$KSMPP - [P]GA Hea[P] - 7.3.2 and above X$KSMUP - [U]GA Hea[P] - 7.3.2 and above X$KSMHP - Any [H]ea[P] - 7.3.2 and above X$KSMSPR- [S]hared [P]ool [R]eserved List - 7.1.5 or higher [L]east recently used shared pool chunks X$KSMLRU - LR[U] flushes from the shared pool [S]GA Objects X$KSMSD - Size [D]efinition for Fixed/Variable summary X$KSMSS - Statistics (lengths) of SGA objects SGA [MEM]ory X$KSMMEM - map of the entire SGA - 7.2.0 or higher X$KSMFSV - Addresses of [F]ixed [S]GA [V]ariables - 7.2.1 or higher [P]arameter Component X$KSPPI - [P]arameter [I]nfo ( Names ) X$KSPPCV - [C]urrent Session [V]alues - 7.3.2 or above X$KSPPSV - [S]ystem [V]alues - 7.3.2 or above En[Q]ueue Management X$KSQDN - Global [D]atabase [N]ame X$KSQEQ - [E]n[Q]ueue Object X$KSQRS - Enqueue [R]e[S]ource X$KSQST - Enqueue [S]tatistics by [T]ype [U]ser Management [C]ost X$KSUCF - Cost [F]unction (resource limit) [L]icence X$KSULL - Licence [L]imits [L]anguage Manager X$NLS_PARAMETERS - NLS parameters X$KSULV - NLS [V]alid Values - 7.1.2 or higher [MY] [ST]atistics X$KSUMYSTA - [MY] [ST]atisics (current session) [P]rocess Info X$KSUPL - Process (resource) [L]imits X$KSUPRLAT - [LAT]ch Holder X$KSUPR - Process object [R]esource X$KSURU - Resource [U]sage [S]tatistics X$KSUSD - [D]escriptors (statistic names) X$KSUSGSTA - [G]lobal [ST]atistics [SE]ssions X$KSUSECST - Session status for events X$KSUSESTA - Session [STA]tistics X$KSUSECON - [CON]nection Authentication - 7.2.1 or higher X$KSUSE - [SE]ssion Info X$KSUSIO - [S]ystem [IO] statistics per session [T]imer X$KSUTM - Ti[M]e in 1/100th seconds Instance [X] X$KSUXSINST - [INST]ance state [T]race management X$TRACE - Current traced events X$TRACES - All possible traces X$KSTEX - Code [EX]ecution - 7.2.1 or higher E[X]ecution Management Device/Node [A]ffinity - 7.3.2 and above X$KSXAFA - Current File/Node Affinity [T]ransaction Layer Table [A]ccess [D]efinition X$KTADM - D[M]L lock [C]ontrol Component X$KTCXB - Transaction O[B]ject [S]or[T] Segments - 7.3 or higher X$KTSTSSD - [S]ort [S]egment [D]escriptor - per tablespace statistics [T]ablespace X$KTTVS - [V]alid [S]aveundo [U]ndo X$KTURD - Inuse [D]escriptors X$KTUXE - Transaction [E]ntry (table) - 7.3.2 or above Performance Layer [V] - 7.0.16 or higher [I]nformation tables X$KVII - [I]nitialisation Instance parameters X$KVIS - [S]izes of structure elements X$KVIT - [T]ransitory Instance parameters Security Layer [Z] [D]ictionary Component X$KZDOS - [OS] roles [S]ecurity State X$KZSPR - Enabled [PR]ivileges X$KZSRO - Enabled [RO]les [R]emote Logins - 7.1.1 or higher X$KZSRT - [R]emote Password File [T]able entries E[X]ecution Layer Parallel Query (Execute [F]ast) - 7.1.1 or higher [P]rocess and Queue Manager Statistics - 7.1.3 or higher X$KXFPYS - S[YS]tem Statistics X$KXFPDP - [D]etached [P]rocess (slave) statistics X$KXFQSROW - Table [Q]ueue Statistics - 7.3.2 or higher [C]oordinator Component X$KXFPCST - Query [ST]atistics X$KXFPCMS - [M]essage [S]tatistics X$KXFPCDS - [D]equeue [S]tatistics [S]lave Component X$KXFPSST - Query [ST]atistics X$KXFPSMS - [M]essage [S]tatistics X$KXFPCDS - [D]equeue [S]tatistics [S]hared Cursor X$KXSBD - [B]ind [D]ata - 7.3.2 and above X$KXSCC - SQL [C]ursor [C]ache Data - 7.3.2 and above [N]etwork Layer - 7.0.15 or higher Network [CO]nnections X$UGANCO - Current [N]etwork [CO]nnections
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25788467/viewspace-697652/，如需转载，请注明出处，否则将追究法律责任。