If you ever feel the need to dig down into the “dynamic performance views (v$)”, and then go a little deeper to get to the “internal structures (x$)” there are 5 objects in the list of x$ that you will eventually want to know about.
- x$kqfta – headline information about the x$ structures – name, size, column count.
- x$kqfvi – a list of the names of the dynamic performance views (mostly pairs of v$/gv$)
- x$kqfvt – a list of the select statements that define the views in x$kqfvi
- x$kqfdt – a list of “derived tables”: a cross between synonyms and views of a few of the structures in x$kqfta
- x$kqfco – a list of the “columns” in each of the tables in x$kqfta (but not x$kqfvi and x$kqfdt)
I’ve listed the tables in this order because that’s the way they’re indexed (0 – 4) in x$kqfta, but your journey is likely to start from the view v$fixed_table, or the view v$fixed_view_definition. From a session connected as SYS to the cdb$root in 19.11.0.0:
SQL> describe v$fixed_view_definition
Name Null? Type
----------------------------------------- -------- ----------------------------
VIEW_NAME VARCHAR2(128)
VIEW_DEFINITION VARCHAR2(4000)
CON_ID NUMBER
SQL> select view_definition
2 from v$fixed_view_definition
3 where view_name = 'V$FIXED_VIEW_DEFINITION'
4 /
VIEW_DEFINITION
--------------------------------------------------------------------------------
select VIEW_NAME , VIEW_DEFINITION, CON_ID from GV$FIXED_VIEW_DEFINITION where
inst_id = USERENV('Instance')
1 row selected.
SQL> select view_definition
2 from v$fixed_view_definition
3 where view_name = 'GV$FIXED_VIEW_DEFINITION'
4 /
VIEW_DEFINITION
--------------------------------------------------------------------------------
select i.inst_id,kqfvinam,kqftpsel, i.con_id from x$kqfvi i, x$kqfvt t where i
.indx = t.indx
There’s a fairly common pattern in the fixed view definitions – they often come in pairs, one starting GV$ the other starting V$, and the V$ is usually just “select most of the columns from the gv$ for this instance”. The GV$ views are the “Global” views relevant to RAC, the V$ views are local to the current instance. (Just pretend you haven’t noticed that Oracle uses the deprecated userenv() function to find the instance id rather than the sys_context(‘userenv’,{variable}) function.)
If you are running RAC and query one of the GV$ views you’ll find that every other instance starts a Parallel Execution process to create it’s local results for the calling instance. This PX process doesn’t come from (and will ignore the limit on) the normal pool of PX processes and will have a name like PZnn rather than the traditional Pnnn.
You’ll note in the result from the last query that gv$fixed_view_definition is a simple join of x$kqfvi and x$kqfvt on the indx column. There is a problem with this view – hidden in plain sight – but I’ll have to come back to that later.
We can go through a similar set of queries to find the definition of v$fixed_table:
SQL> select view_definition
2 from v$fixed_view_definition
3 where view_name = 'V$FIXED_TABLE'
4 /
VIEW_DEFINITION
--------------------------------------------------------------------------------
select NAME , OBJECT_ID , TYPE , TABLE_NUM, CON_ID from GV$FIXED_TABLE where in
st_id = USERENV('Instance')
1 row selected.
SQL> select view_definition
2 from v$fixed_view_definition
3 where view_name = 'GV$FIXED_TABLE'
4 /
VIEW_DEFINITION
--------------------------------------------------------------------------------
select inst_id,kqftanam, kqftaobj, 'TABLE', indx, con_id from x$kqfta union all
select inst_id,kqfvinam, kqfviobj, 'VIEW', 65537, con_id from x$kqfvi union all
select inst_id,kqfdtnam, kqfdtobj, 'TABLE', 65537, con_id from x$kqfdt
1 row selected.
So the “fixed tables” are really the combination of the basic structures, views on the structures, and the “derived tables”. The objects in the UNION ALL each have a unique object_id in a range close to power(2,32) and (for the tables in x$kqfta) if you make a call to dbms_stats.gather_table_stats() that object_id will be used to store the statistics in the data dictionary.
So what are the “derived tables”. There are two ways to address this question – query it, or find a place where one is used. Here’s a full listing of x$kqfdt (still 19.11.0.0:
SQL> select * from x$kqfdt;
ADDR INDX INST_ID CON_ID KQFDTOBJ KQFDTNAM KQFDTEQU
---------------- ---------- ---------- ---------- ---------- -------------------------------- ---------------------------
000000001454E6C0 0 1 0 4294952735 X$KSLLTR_CHILDREN X$KSLLTR
000000001454E6E8 1 1 0 4294952736 X$KSLLTR_PARENT X$KSLLTR
000000001454E710 2 1 0 4294956013 X$KSLLTR_OSP X$KSLLTR
000000001454E738 3 1 0 4294956014 X$KSLWSC_OSP X$KSLWSC
000000001454E760 4 1 0 4294951073 X$KCVFHONL X$KCVFH
000000001454E788 5 1 0 4294951074 X$KCVFHMRR X$KCVFH
000000001454E7B0 6 1 0 4294951440 X$KCVFHALL X$KCVFH
000000001454E7D8 7 1 0 4294951056 X$KGLTABLE X$KGLOB
000000001454E800 8 1 0 4294951057 X$KGLBODY X$KGLOB
000000001454E828 9 1 0 4294951058 X$KGLTRIGGER X$KGLOB
000000001454E850 10 1 0 4294951059 X$KGLINDEX X$KGLOB
000000001454E878 11 1 0 4294951060 X$KGLCLUSTER X$KGLOB
000000001454E8A0 12 1 0 4294951061 X$KGLCURSOR X$KGLOB
000000001454E8C8 13 1 0 4294952684 X$KGLCURSOR_CHILD_SQLID X$KGLOB
000000001454E8F0 14 1 0 4294952680 X$KGLCURSOR_CHILD_SQLIDPH X$KGLOB
000000001454E918 15 1 0 4294952683 X$KGLCURSOR_CHILD X$KGLOB
000000001454E940 16 1 0 4294953372 X$KGLCURSOR_PARENT X$KGLOB
000000001454E968 17 1 0 4294953759 X$KGLSQLTXL X$KGLOB
000000001454E990 18 1 0 4294956135 X$ALL_KQLFXPL X$KQLFXPL
000000001454E9B8 19 1 0 4294953124 X$KKSSQLSTAT_PLAN_HASH X$KKSSQLSTAT
000000001454E9E0 20 1 0 4294953231 X$ZASAXTD1 X$ZASAXTAB
000000001454EA08 21 1 0 4294953232 X$ZASAXTD2 X$ZASAXTAB
000000001454EA30 22 1 0 4294953233 X$ZASAXTD3 X$ZASAXTAB
000000001454EA58 23 1 0 4294951597 X$JOXFS X$JOXFT
000000001454EA80 24 1 0 4294951598 X$JOXFC X$JOXFT
000000001454EAA8 25 1 0 4294951599 X$JOXFR X$JOXFT
000000001454EAD0 26 1 0 4294951621 X$JOXFD X$JOXFT
000000001454EAF8 27 1 0 4294952364 X$JOXOBJ X$JOXFT
000000001454EB20 28 1 0 4294952365 X$JOXSCD X$JOXFT
000000001454EB48 29 1 0 4294952366 X$JOXRSV X$JOXFT
000000001454EB70 30 1 0 4294952367 X$JOXREF X$JOXFT
000000001454EB98 31 1 0 4294952368 X$JOXDRC X$JOXFT
000000001454EBC0 32 1 0 4294952369 X$JOXDRR X$JOXFT
000000001454EBE8 33 1 0 4294952370 X$JOXMOB X$JOXFM
000000001454EC10 34 1 0 4294952371 X$JOXMIF X$JOXFM
000000001454EC38 35 1 0 4294952372 X$JOXMIC X$JOXFM
000000001454EC60 36 1 0 4294952373 X$JOXMFD X$JOXFM
000000001454EC88 37 1 0 4294952374 X$JOXMMD X$JOXFM
000000001454ECB0 38 1 0 4294952375 X$JOXMAG X$JOXFM
000000001454ECD8 39 1 0 4294952376 X$JOXMEX X$JOXFM
000000001454ED00 40 1 0 4294956138 X$ALL_ASH X$ASH
000000001454ED28 41 1 0 4294956123 X$ALL_KESWXMON X$KESWXMON
000000001454ED50 42 1 0 4294956126 X$ALL_KESWXMON_PLAN X$KESWXMON_PLAN
43 rows selected.
What it comes down to is that there are several different ways of addressing some of the base objects. x$kglob is the nicest example of this with 11 “equivalences”. I don’t know why Oracle has taken this approach – but it may reflect the fact that some of the underlying structures don’t look anything like “rows” and different derived tables expose different components of the same underlying in mutually incomaptible ways. Here’s an example showing one of these derived tables making an appearance:
SQL> explain plan for
2 select child_number, plan_hash_value, sql_text
3 from V$sql where sql_id = '7m239n32gftgh';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
Plan hash value: 1346707802
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 549 | 0 (0)| 00:00:01 |
|* 1 | FIXED TABLE FIXED INDEX| X$KGLCURSOR_CHILD (ind:2) | 1 | 549 | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("KGLOBT03"='7m239n32gftgh' AND "INST_ID"=USERENV('INSTANCE'))
13 rows selected.
When you try to find x$kglcursor_child in the fixed tables (x$kqfta) it’s not there – but a check against x$kqfdt tells us that it’s just another way of addressing x$kglob.
This plan also shows an interesting feature in the table access Operation, which is reported as “Fixed table fixed index” with “ind:2” appended when we check the Name column of the plan.
In this context “index” has a non-standard meaning – x$kglob is a memory structure helping to describe the contents of the Library Cache, so we shouldn’t expect to see anything that looks like a traditional index. In this context this just means that Oracle has a particularly efficient access path if your predicate is of the right type. We could guess in this case that it’s the predicate “kglobt03 =’7m239n32gftgh'” that Oracle is using, but it would be nice to check, and nice to know what other “indexed” access paths there are into the structure (this is, after all, “ind:2”, so there ought to be an “ind:1”). Conveniently Oracle has a dynamic performance view called v$index_fixed_columns that we can query:
SQL> select * from v$indexed_fixed_column where table_name = 'X$KGLOB';
TABLE_NAME INDEX_NUMBER COLUMN_NAME COLUMN_POSITION CON_ID
------------------------- ------------ -------------------- --------------- ----------
X$KGLOB 1 KGLNAHSH 0 0
X$KGLOB 2 KGLOBT03 0 0
2 rows selected.
Note that the Index_Number column tells use that x$kglobt03 is defined as the 2nd index on the table – which is consistent with the “ind:2” in the plan. Of course, it’s not immediately obvious, and can take a bit of effort to work out, that kgnahsh corresponds to v$sql.hash_value and kglobt03 corresponds to v$sql.sql_id; but it’s nice to know that we can find all the efficent access paths if we need to tune a bit of “expert” SQL that we’ve discovered on the internet.
Even more exciting – we can go back to v$fixed_view_definition and find out what structure(s) are sitting underneath v$indexed_fixed_column:
SQL> select view_definition
2 from v$fixed_view_definition
3 where view_name = 'GV$INDEXED_FIXED_COLUMN'
4 /
VIEW_DEFINITION
--------------------------------------------------------------------------------
select c.inst_id,kqftanam, kqfcoidx, kqfconam, kqfcoipo, c.con_id from x$kqfco
c, x$kqfta t where t.indx = c.kqfcotab and kqfcoidx != 0
It’s a join between x$kqfta and x$kqfco – the fifth item on our list: the complete list of x$ columns. And you just can’t help wondering what results you would get if you removed the filter on column kqfcoidx. So here’s a little script I wrote a long time ago to do just that:
rem
rem Script: x_desc.sql
rem Author: Jonathan Lewis
rem Dated: September 2002
rem Purpose: Describe the X$ objects
rem
rem Notes:
rem This script can only be run by SYS
rem
set newpage 0
set pagesize 60
set linesize 132
set trimspoon on
set tab off
break on kqftanam skip page
column kqftanam new_value m_table noprint
column kqfconam heading "Column" format a34
column kqfcodty heading "Ext Type"
column kqfcotyp heading "Int Type"
column kqfcomax heading "Array Max"
column kqfcolsz heading "Len Size"
column kqfcolof heading "Len Offset"
column kqfcosiz heading "Col Size"
column kqfcooff heading "Offset"
column kqfcoidx heading "Index"
column kqfcoipo heading "Idx Col"
ttitle -
skip 1 -
center m_table -
skip 2
spool x_desc
select
ta.kqftanam,
co.kqfconam,
co.kqfcodty,
co.kqfcotyp,
co.kqfcomax,
co.kqfcolsz,
co.kqfcolof,
co.kqfcosiz,
co.kqfcooff,
co.kqfcoidx,
co.kqfcoipo
from
x$kqfta ta,
x$kqfco co
where
co.kqfcotab = ta.indx
order by
ta.kqftanam,
co.kqfcooff,
co.indx
;
spool off
And here’s the result for just one “table” in 19.11.0.0 – x$ksled (corresponding to v$event_name) together with a query to report its column count and row size from x$kqfta.
Column Ext Type Int Type Array Max Len Size Len Offset Col Size Offset Index Idx Col
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
ADDR 23 9 0 0 0 8 0 1 0
INDX 2 11 0 0 0 4 0 2 0
INST_ID 2 11 0 0 0 4 0 0 0
CON_ID 2 11 0 0 0 2 0 0 0
KSLEDNAM 1 6 0 0 0 64 0 0 0
KSLEDCLASSID 2 7 0 0 0 4 0 0 0
KSLEDCLASS 1 7 0 0 0 64 0 0 0
KSLEDP1 1 6 0 0 0 64 8 0 0
KSLEDP2 1 6 0 0 0 64 16 0 0
KSLEDP3 1 6 0 0 0 64 24 0 0
KSLEDFLG 2 11 0 0 0 4 32 0 0
KSLEDCLASS# 2 0 0 0 0 2 36 0 0
KSLEDDSP 1 6 0 0 0 64 40 0 0
KSLEDHASH 2 11 0 0 0 4 52 0 0
SQL> select kqftarsz, kqftacoc from X$kqfta where kqftanam = 'X$KSLED';
KQFTARSZ KQFTACOC
---------- ----------
56 14
In this particular case you can see that the column count (14) matches – I haven’t checked if this is always true, but I suspect it is – and in this case we can see from a quick check of the highest offset (52) plus its column size (4) that the row size (56) matches as well, but that’s not always the case as we shall see in other examples.
There are, inevitably, some puzzles in this output.
- How can we have 7 columns all starting at the same zero offset?
- How can you have a column with an offset of 16 (ksledp2) when the previous column has an offset of 8 and a length of 64?
- Might there be an interesting 2 bytes of information between ksledclass# (offset 36 length 2) and ksleddsp (offset 40)?
I won’t attempt to unravel the mystery any further at this point, but I will just point out that there are two “indexes” on this “table”. To access a row efficiently you can either query by addr (its 64-bit address) or by indx (which suggests it’s stored in memory as a simple non-segmented, array – which is another detail I’ll explain at a later date).
Coming soon
- The problem with v$fixed_view_definition – and how to circumvent it: now published
- Three ways of checking row sizes – which may give different results: now published
- Notes on interpreting the columns in x$kqfco.
I will leave you with one last thought – the X$ “tables” fall into 4 different categories and you can identify which category that is by checking the address (addr) of the first row of each table (provided the table has some data in it, of course). And here’s a silly little detail – I think every x$ table starts at indx = 1, except x$ksupr which starts at indx = 0.
[…] Digging into x$ (Mar 2022): preliminary investigation of x$ information […]
Pingback by Infrastructure Catalogue | Oracle Scratchpad — March 16, 2022 @ 11:16 pm GMT Mar 16,2022 |
Hi Jonathan,
In general, there is nothing wrong with the zero offset.
In this specific example, ADDR, INDX, INST_ID, CON_ID are just “special” columns that are present in all X$ tables – Oracle invokes certain functions to resolve them.
KSLEDNAM starts at offset 0 and it is an address of a string (the same as KSLEDP2 which explains why its length is 64).
KSLEDCLASSID, KSLEDCLASS are not stored within the other X$KSLED columns but are resolved via C functions based on X$KSLED.ADDR.
Some other similar functions (in particular, functions resolving some X$KSUSE columns) refer to the “gaps” within the respective X$ structure. In this case, it can be KSLEDCLASS# but I have not checked it.
Here is an output of a tool that I wrote for obtaining X$ structures from the oracle binary:
The important part here is the func column: when one of KSLEDCLASSID or KSLEDCLASS is referenced, Oracle calls the kslgclsid or kslgclsname C functions correspondingly.
I am unaware as to how to obtain those C functions without working with the Oracle binaries (that is what I do inside my xinfo tool).
I think it depends on how we classify them. Classifying by the memory area is just one criteria.
Each X$KQFTA row itself has an address which made me think that there are so called ‘Virtual X$ tables’ which I briefly described here: https://mvelikikh.blogspot.com/2022/02/virtual-x-tables.html
Thanks,
Mikhail
Comment by Mikhail Velikikh — March 17, 2022 @ 10:44 am GMT Mar 17,2022 |
Mikhail,
Thanks for the comments – my questions were rhetorical so I wasn’t expecting such an extensive response, and I’ve learned a couple of things from it.
For one – I’ve always reported the columns by offset and never thought of reporting column id, and I wonder if this has made me miss some interesting clues in some of the other x$ I’ve looked at.
I’ve also never tried to track down the functions called for type 7 – it’s interesting that you say it may be based on the x$ksled.addr, I’d assumed it was passing in the ksledclass#. Is your observation based on checking the call stack for the function? If so maybe the function uses the address to find the class number.
I’d not noticed that the rows in x$kqfta itself were split across two memory ranges, I wonder if this “virtualx$ tables” arrangement is similar to the idea of the bootstrap data dictionary – i.e. is there a minimum set of x$tables that have to exist before the rest of the instance can be created and not only do those tables got into low memory, the list of of those tables also goes into low memory.
Regards
Jonathan Lewis
Comment by Jonathan Lewis — March 18, 2022 @ 3:35 pm GMT Mar 18,2022 |
Jonathan,
I investigated several function called for type 20, such as ksugetapp_cb (X$KSUSE.KSUSEAPP) and ksugetact_cb (X$KSUSE.KSUSEACT) – everywhere there was an X$KSUSE.ADDR passed as the second parameter.
However, after looking at it more closely, I see that the index (X$KSLED.INDX) is passed to kslgclsid and kslgclsname – there is no address passed.
Internally, Oracle calculates the X$KSLED row based on that index and takes KSLEDCLASS# to get actual KSLEDCLASSID and KSLEDCLASS values.
I have to apologize for misleading you. At the end of the day, INDX and ADDR are pretty much similar in their function – both can be used to get the actual X$ row.
Here is not an extensive list of KQFCOTYP that seem to be calculated on the fly: 7, 8, 19, 20, 33, 43.
Here are some examples of X$ tables that have columns of those types and the corresponding functions:
Thanks,
Mikhail
Comment by Mikhail Velikikh — March 18, 2022 @ 9:46 pm GMT Mar 18,2022 |
There are x$ views, like x$ktcxb (which underpins v$transaction) that are completely populated on the fly by an Oracle C function (ktcxbc in this case) on every select on x$. The helper function gathers data from SGA, process it and puts it in UGA. Tanel Poder described the concept of the x$ helper functions: https://tanelpoder.com/2014/01/10/oracle-x-tables-part-1-where-do-they-get-their-data-from/
offset=0 seems to mean that data isn’t stored in an array, but generated on the fly instead.
Mikhail, have you published xinfo?
Does the tool show which of the following Oracle C functions populates x$kglcursor_child.kglobcce (v$sql.optimizer_env)?:
Best regards,
Nenad
Comment by Nenad Noveljic — March 18, 2022 @ 3:20 pm GMT Mar 18,2022 |
Hi Nenad,
Table level callbacks are stored within the kqftap structure per my understanding. I use it in that xinfo tool (‘list’ is a command that gets its data from kqftab and kqftap):
Basically, I reviewed the kqftap structure and found that some X$ tables have functions there, which I called cb1 and cb2 descriptively. I assume they have a special meaning (like cb1 is called to populate the X$ structure, and cb2 is called when a cleanup is needed), but I have not investigated it specifically.
X$KSLED.KSLEDNAM has offset 0, and we can see that there is an address of a string at this memory location:
Nenad, yes, I wrote this tool in Python. It also uses some Linux utilities to complicate things because I am pretty much useless at Python:
https://github.com/mvelikikh/oracle/tree/master/tools/xinfo
I put short notes to introduce the tool:
https://mvelikikh.blogspot.com/2022/01/x-tables-reading-xkqfta-from-oracle.html
https://mvelikikh.blogspot.com/2022/02/x-tables-table-level-callbacks-and.html
No, the tool can show what functions populate an X$ table if it is populated on the fly (xinfo desc with the ‘–with-kqftap’ option, at least, that is how I think of those callbacks in kqftap), or what functions are called to resolve the X$ columns when they are referenced in a query if those columns are calculated on the fly.
For example, kslgclsid is called when we refer to X$KSLED.KSLEDCLASSID, and kslgclsname is called when we refer to X$KSLED.KSLEDCLASS.
As I see, KGLOBCCE is a static column. It is stored within X$KGLOB at its offset (2453 in 21.5):
If we take a more complex example, such as X$KSUSE, here are the columns that are generated on the fly based on my understanding (the output is edited manually and the column names are mostly taken from X$KQFCO after removing the KQFCO prefix):
Despite the fact that they all have offset 0, having an offset 0 by itself does not mean that the column is generated on the fly as the example with X$KSLED.KSLEDNAM demonstrates.
Thanks,
Mikhail
Comment by Mikhail Velikikh — March 18, 2022 @ 9:51 pm GMT Mar 18,2022 |
Prompted by the “classification” observation, I ran a query to break the list down by kqftatyp. Interesting to note the split:
Yet another little detail to explore some time.
Regards
Jonathan Lewis
Comment by Jonathan Lewis — March 18, 2022 @ 5:46 pm GMT Mar 18,2022 |
[…] one of my notes about excavating the x$ tables I said that there was a problem “hidden in plain sight” with the dynamic performance […]
Pingback by v$_fixed_view_definition | Oracle Scratchpad — March 21, 2022 @ 7:08 pm GMT Mar 21,2022 |