{"id":205,"date":"2012-11-28T10:43:33","date_gmt":"2012-11-28T10:43:33","guid":{"rendered":"http:\/\/tomas.papp.me.uk\/?p=205"},"modified":"2012-11-28T10:43:33","modified_gmt":"2012-11-28T10:43:33","slug":"oracle-x-tabulky","status":"publish","type":"post","link":"https:\/\/tomas.papp.me.uk\/?p=205","title":{"rendered":"Oracle X$ tabulky"},"content":{"rendered":"<ul>\n<li><a href=\"http:\/\/oracle-abc.wikidot.com\/x-bh\">x$bh<\/a> Buffer Hash<\/li>\n<\/ul>\n<ul>\n<li>x$kcbsw kernel cache, buffer statistics why<\/li>\n<\/ul>\n<p>Note:34405.1 (select kcbwhdes, why0+why1+why2 &#8220;Gets&#8221;, &#8220;OTHER_WAIT&#8221; from x$kcbsw s, x$kcbwh w where s.indx=w.indx and s.&#8221;OTHER_WAIT&#8221;&gt;0 order by 3), Ref1 (&#8220;statistics about the way these [x$kcbwh] functions have been used&#8221;)<\/p>\n<ul>\n<li>x$k2gte Kernel 2-phase commit, Global Transaction Entry<\/li>\n<\/ul>\n<p>Mark Bobak&#8217;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&#8217;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).<\/p>\n<ul>\n<li>x$kcbbf Kernel Cache, Buffer ?? Ref1 (&#8220;_db_handles&#8221;)<\/li>\n<\/ul>\n<ul>\n<li>x$kcbfwait kernel cache, buffer file wait<\/li>\n<\/ul>\n<p>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#<\/p>\n<ul>\n<li>x$kcbwait kernel cache, buffer wait<\/li>\n<\/ul>\n<ul>\n<li>x$kcbwds kernel cache, buffer working descriptors<\/li>\n<\/ul>\n<ul>\n<li>x$kcbwh kernel cache, buffer ??<\/li>\n<\/ul>\n<p>See x$kcbsw for SQL. Ref1 (&#8220;different functions that may be used to perform different types of logical I\/O&#8221;), Ref2<\/p>\n<ul>\n<li>x$kcccf kernel cache, controlfilemanagement control file<\/li>\n<\/ul>\n<p>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.<\/p>\n<ul>\n<li>x$kcccp kernel cache, controlfile checkpoint progress<\/li>\n<\/ul>\n<p>S. Adams and K Gopalakrishnan use this view to find how much the current redo log is filled.<\/p>\n<ul>\n<li>x$kccdi kernel cache, controlfilemanagement database information<\/li>\n<\/ul>\n<ul>\n<li>x$kccle kernel cache, controlfile logfile entry<\/li>\n<\/ul>\n<p>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)<\/p>\n<ul>\n<li>x$kcfio kernel cache, file I\/O<\/li>\n<\/ul>\n<ul>\n<li>x$kclcrst kernel cache, lock, consistent read statistics<\/li>\n<\/ul>\n<p>base table of v$cr_block_server or v$bsp, used to troubleshoot global cache cr requests<\/p>\n<ul>\n<li>x$kclfh kernel cache, lock file header<\/li>\n<\/ul>\n<ul>\n<li>x$kclfi kernel cache, lock file index<\/li>\n<\/ul>\n<ul>\n<li>x$kcluh kernel cache, lock undo header<\/li>\n<\/ul>\n<ul>\n<li>x$kclui kernel cache, lock undo index<\/li>\n<\/ul>\n<ul>\n<li>x$kcrfx kernel cache, redo file context &#8220;columns bfs (buffer size) and bsz (block size). Dividing bfs by bsz gives mxr (the maximum number of blocks to read size)&#8221; (from Anjo Kolk&#8217;s paper)<\/li>\n<\/ul>\n<ul>\n<li>x$kdxst kernel data, index status used in catalog.sql to create index_stats<\/li>\n<\/ul>\n<ul>\n<li>x$kdxhs kernel data, index histogram used in catalog.sql to create index_histogram<\/li>\n<\/ul>\n<ul>\n<li>x$kghlu kernel generic, heap LRUs<\/li>\n<\/ul>\n<ul>\n<li>x$kglcursor kernel generic, librarycache cursor<\/li>\n<\/ul>\n<p>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&#8217;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).<\/p>\n<ul>\n<li>x$kgllk kernel generic, librarycache lock<\/li>\n<\/ul>\n<p>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 = &#8216;&amp;saddr_from_v$session&#8217; \/* blocked session *\/ and lock_a.kgllkhdl = lock_b.kgllkhdl and kgllkreq &gt; 0). Kgllkadr column is shown in event 10270 trace files to find SQLs in session cursor cache (Ref)<\/p>\n<ul>\n<li>x$kglob kernel generic, librarycache object<\/li>\n<\/ul>\n<p>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&#8217; 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!)<\/p>\n<ul>\n<li>x$kglpn kernel generic, librarycache pin<\/li>\n<\/ul>\n<p>used in catblock.sql to build dba_kgllock<\/p>\n<ul>\n<li>x$kglrd kernel generic, librarycache readonly dependency<\/li>\n<\/ul>\n<p>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&#8217;t show relation between PL\/SQL block and its contents)<\/p>\n<ul>\n<li>x$kglst kernel generic, librarycache status<\/li>\n<\/ul>\n<ul>\n<li>x$kqfco kernel query, fixed table columns x$kqfco.kqfcotab=x$kqfta.indx<\/li>\n<\/ul>\n<ul>\n<li>x$kqfta kernel query, fixed table<\/li>\n<\/ul>\n<ul>\n<li>x$kqfdt kernel query, fixed derived table<\/li>\n<\/ul>\n<p>acronym explained by Julian Dyke: it contains x$kglcursor, x$kgltable etc. which are based on<\/p>\n<ul>\n<li>x$kglob; effectively these are views of other x$ tables, but Oracle couldn&#8217;t call them views because they already had x$kqfvi<\/li>\n<\/ul>\n<ul>\n<li>x$kqfp kernel query, fixed procedure used in catprc.sql to build disk_and_fixed_objects view<\/li>\n<\/ul>\n<ul>\n<li>x$kqfsz kernel query, fixed size (size of fixed objects in current version of Oracle)<\/li>\n<\/ul>\n<ul>\n<li>x$kqfvi kernel query, fixed view<\/li>\n<\/ul>\n<ul>\n<li>x$kqfvt kernel query, fixed view table (how fixed view is built on fixed tables)<\/li>\n<\/ul>\n<ul>\n<li>x$ksled, x$kslei, x$ksles kernel service, event definition, events for instance, events for session, respectively (&#8220;l&#8221; probably means &#8220;lock&#8221;)<\/li>\n<\/ul>\n<ul>\n<li>x$kslpo kernel service, latch posting<\/li>\n<\/ul>\n<p>Note:653299 says it &#8220;tracks which function is posting smon&#8221;. Ksllwnam column (the part before semicolon if it exists) can match v$latch_misses.location to identify the latch that uses this function.<\/p>\n<ul>\n<li>x$ksmfs kernel service, memory fixed SGA<\/li>\n<\/ul>\n<p>also contains db_block_buffers and log_buffer sizes for some reason<\/p>\n<ul>\n<li>x$ksmfsv kernel service, memory fixed SGA variables<\/li>\n<\/ul>\n<p>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\u2026 (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<\/p>\n<ul>\n<li>x$ksmhp kernel service, memory heap<\/li>\n<\/ul>\n<p>S. Adams, &#8220;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.&#8221;<\/p>\n<ul>\n<li>x$ksmjs kernel service, memory java_pool summary<\/li>\n<\/ul>\n<ul>\n<li>x$ksmlru kernel service, memory LRU<\/li>\n<\/ul>\n<p>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.<\/p>\n<ul>\n<li>x$ksmls kernel service, memory large_pool summary<\/li>\n<li>x$ksmmem kernel service, memory<\/li>\n<\/ul>\n<p>Entire SGA memory map. You can find your database version by select ksmmmval from x$ksmmem where indx = 2 (if it&#8217;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. Due to memory guard pages, you can only select from x$ksmmem specifying rownum &lt; some number or indx = some value; otherwise the session may hang or throws ORA-3113 (Windows doesn&#8217;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&#8217;s), possibly plus some offset.<\/p>\n<ul>\n<li>x$ksmpp kernel service, memory pga heap<\/li>\n<\/ul>\n<p>PGA heap (variable area)<\/p>\n<ul>\n<li>x$ksmsd kernel service, memory sga definition<\/li>\n<\/ul>\n<ul>\n<li>x$ksmsp kernel service, memory sga heap<\/li>\n<\/ul>\n<p>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).<\/p>\n<ul>\n<li>x$ksmspr kernel service, memory shared pool reserved<\/li>\n<\/ul>\n<ul>\n<li>x$ksmss kernel service, memory shared_pool summary<\/li>\n<\/ul>\n<p>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).<\/p>\n<ul>\n<li>x$ksmup kernel service, memory uga heap<\/li>\n<\/ul>\n<p>UGA heap (variable area)<\/p>\n<ul>\n<li>x$ksppcv kernel service, parameter, current (session) value<\/li>\n<\/ul>\n<p>Base table of v$parameter and v$parameter2. See comments on x$ksppi.<\/p>\n<ul>\n<li>x$ksppi kernel service, parameter, parameter info<\/li>\n<\/ul>\n<p>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 &#8220;Session Value&#8221;, c.ksppstvl &#8220;Instance Value&#8221; from x$ksppi a, x$ksppcv b, x$ksppsv c where a.indx = b.indx and a.indx = c.indx and a.ksppinm like &#8216;\\_%&#8217; escape &#8216;\\&#8217; order by 1<\/p>\n<ul>\n<li>x$ksppsv kernel service, parameter, system value<\/li>\n<\/ul>\n<p>Base table of v$system_parameter and v$system_parameter2. See comments on x$ksppi.<\/p>\n<ul>\n<li>x$ksqeq kernel service, enqueue en-queue<\/li>\n<\/ul>\n<ul>\n<li>x$ksqrs kernel service, enqueue resource<\/li>\n<\/ul>\n<p>&#8220;shows all outstanding enqueues with an additional flag. It basically shows the same information as the v$lock table.&#8221; from Note1, which also gives the meanings of the flags.<\/p>\n<ul>\n<li>x$ksqst kernel service, enqueue_management statistics types<\/li>\n<\/ul>\n<p>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 &gt; 0 or in 9i select * from x$ksqst where ksqstsgt &gt; 0 or ksqstfgt &gt; 0. But v$enqueue_stat in 9i can also be used instead.<\/p>\n<ul>\n<li>x$ksulv kernel service, user locale value<\/li>\n<\/ul>\n<ul>\n<li>x$ksulop kernel service, user long operation<\/li>\n<\/ul>\n<ul>\n<li>x$ksupr kernel service, user process<\/li>\n<\/ul>\n<ul>\n<li>x$ksuse kernel service, user session<\/li>\n<\/ul>\n<ul>\n<li>x$ktcxb kernel transaction, control object<\/li>\n<\/ul>\n<p>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) &lt;&gt; 0). Other flags not shown in v$fixed_view_definition are: 1 read write and read committed, 4 read only.<\/p>\n<ul>\n<li>x$ktfbfe kernel transaction, file bitmap free extent<\/li>\n<\/ul>\n<p>Free extent bitmap in file header for LMT (equivalent to fet$ in DMT); check dba_free_space view definition<\/p>\n<ul>\n<li>x$ktfbhc kernel transaction, file bitmap ? ?<\/li>\n<\/ul>\n<p>Summarizes free space with one row per datafile (Ref); check dba_data_files or dba_temp_files view definition<\/p>\n<ul>\n<li>x$ktfbue kernel transaction, file bitmap used extent<\/li>\n<\/ul>\n<p>Used extent bitmap in file header for LMT (equivalent to uet$ in DMT)<\/p>\n<ul>\n<li>x$ktuxe kernel transaction, undo transaction entry<\/li>\n<\/ul>\n<p>Steve Adams says, you &#8220;get the SCN of the most recently committed (local) transaction&#8221; with select max(ktuxescnw * power(2, 32) + ktuxescnb) from x$ktuxe<\/p>\n<ul>\n<li>x$kxfpsds kernel execution, fast process slave dequeue statistics<\/li>\n<\/ul>\n<p>Current list of reasons for parallel execution dequeuing, as explained for wait event &#8220;parallel query dequeue wait&#8221; in Anjo Kolk&#8217;s paper.<\/p>\n<ul>\n<li>x$kzsprv kernel security, session privilege<\/li>\n<\/ul>\n<p>Session-specific. Base table for v$enabledprivilege, which is base table of session_privs<\/p>\n<ul>\n<li>x$kzsro kernel security, session role<\/li>\n<\/ul>\n<p>used in many SQL scripts in ?\/rdbms\/admin<\/p>\n<ul>\n<li>x$le lock element To 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&#8217;s paper)<\/li>\n<\/ul>\n<ul>\n<li>x$le_stat lock element status<\/li>\n<\/ul>\n<ul>\n<li>x$message (background process) messages<\/li>\n<\/ul>\n<p>This records (possibly) all actions each background process can do.<\/p>\n<ul>\n<li>x$trace Beginning with 9i, x$trace records event tracing info.<\/li>\n<\/ul>\n<p>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 = &amp;sid and pid = &amp;pid order by time shows traced events for a session in question.<\/p>\n<ul>\n<li>x$uganco user global area, network connection Base table of v$dblink.<\/li>\n<\/ul>\n<p>Since it&#8217;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).<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>x$bh Buffer Hash x$kcbsw kernel cache, buffer statistics why Note:34405.1 (select kcbwhdes, why0+why1+why2 &#8220;Gets&#8221;, &#8220;OTHER_WAIT&#8221; from x$kcbsw s, x$kcbwh w where s.indx=w.indx and s.&#8221;OTHER_WAIT&#8221;&gt;0 order by 3), Ref1 (&#8220;statistics about the way these [x$kcbwh] functions have been used&#8221;) x$k2gte Kernel 2-phase commit, Global Transaction Entry Mark Bobak&#8217;s query (originally in Metalink forum thread 524821.994, where [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-205","post","type-post","status-publish","format-standard","hentry","category-oracle"],"_links":{"self":[{"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=\/wp\/v2\/posts\/205","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=205"}],"version-history":[{"count":2,"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=\/wp\/v2\/posts\/205\/revisions"}],"predecessor-version":[{"id":207,"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=\/wp\/v2\/posts\/205\/revisions\/207"}],"wp:attachment":[{"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=205"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=205"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tomas.papp.me.uk\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=205"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}