Oracle Scratchpad

March 16, 2022

Excavating x$

Filed under: Infrastructure,Oracle — Jonathan Lewis @ 11:13 pm GMT Mar 16,2022

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 a 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.

8 Comments »

  1. […] 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 | Reply

  2. Hi Jonathan,

    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?

    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:

    [oracle@db-21 bin]$ xinfo desc 'X$KSLED'
    +-----+------------+--------------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-------------+-------------+
    | cno |    nam_ptr | nam          | siz | dty | typ | max | lsz | lof | off | idx | ipo | kqfcop_indx | func        |
    +-----+------------+--------------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-------------+-------------+
    |   1 | 0x16e6959c | ADDR         |   8 |  23 |   9 |   0 |   0 |   0 |   0 |   1 |   0 |           0 |             |
    |   2 | 0x15d7d660 | INDX         |   4 |   2 |  11 |   0 |   0 |   0 |   0 |   2 |   0 |           0 |             |
    |   3 | 0x160f96b0 | INST_ID      |   4 |   2 |  11 |   0 |   0 |   0 |   0 |   0 |   0 |           0 |             |
    |   4 | 0x16e78d5c | CON_ID       |   2 |   2 |  11 |   0 |   0 |   0 |   0 |   0 |   0 |           0 |             |
    |   5 | 0x16e8b1e4 | KSLEDNAM     |  64 |   1 |   6 |   0 |   0 |   0 |   0 |   0 |   0 |           0 |             |
    |   6 | 0x16e8b1f0 | KSLEDP1      |  64 |   1 |   6 |   0 |   0 |   0 |   8 |   0 |   0 |           0 |             |
    |   7 | 0x16e8b1f8 | KSLEDP2      |  64 |   1 |   6 |   0 |   0 |   0 |  16 |   0 |   0 |           0 |             |
    |   8 | 0x16e8b200 | KSLEDP3      |  64 |   1 |   6 |   0 |   0 |   0 |  24 |   0 |   0 |           0 |             |
    |   9 | 0x16e8b208 | KSLEDCLASSID |   4 |   2 |   7 |   0 |   0 |   0 |   0 |   0 |   0 |           2 | kslgclsid   |
    |  10 | 0x16e8b218 | KSLEDCLASS#  |   2 |   2 |   0 |   0 |   0 |   0 |  36 |   0 |   0 |           0 |             |
    |  11 | 0x16e8b224 | KSLEDCLASS   |  64 |   1 |   7 |   0 |   0 |   0 |   0 |   0 |   0 |           3 | kslgclsname |
    |  12 | 0x16e8b230 | KSLEDHASH    |   4 |   2 |  11 |   0 |   0 |   0 |  52 |   0 |   0 |           0 |             |
    |  13 | 0x16e8b23c | KSLEDDSP     |  64 |   1 |   6 |   0 |   0 |   0 |  40 |   0 |   0 |           0 |             |
    |  14 | 0x16e8b248 | KSLEDFLG     |   4 |   2 |  11 |   0 |   0 |   0 |  32 |   0 |   0 |           0 |             |
    +-----+------------+--------------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-------------+-------------+
    

    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 will leave you with one last thought – the X$ “tables” fall into 4 different categories and you can identify which category that is by check the address (addr) of the first row of each table (provided the table has some data in it, of course).

    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 | Reply

    • 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 | Reply

      • Jonathan,

        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 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:

        # types 7 and 8
        (venv) [oracle@db-21 ~]$ xinfo desc 'X$KMMRD'
        +-----+------------+----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-------------+--------+
        | cno |    nam_ptr | nam      | siz | dty | typ | max | lsz | lof | off | idx | ipo | kqfcop_indx | func   |
        +-----+------------+----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-------------+--------+
        ...
        |   5 | 0x16e9f088 | KMMRDPRO |   8 |  23 |   7 |   0 |   0 |   0 |   0 |   0 |   0 |         329 | kmmgrs |
        ...
        |   7 | 0x16e9f0a0 | KMMRDCNT |   4 |   2 |   8 |  12 |   0 |   0 |   0 |   0 |   0 |         330 | kmmgrd |
        +-----+------------+----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-------------+--------+
        # type 19
        (venv) [oracle@db-21 ~]$ xinfo desc 'X$KEWAM'
        +-----+------------+--------------------------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-------------+---------------------------+
        | cno |    nam_ptr | nam                      | siz | dty | typ | max | lsz | lof | off | idx | ipo | kqfcop_indx | func                      |
        +-----+------------+--------------------------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-------------+---------------------------+
        ....
        |  13 | 0x16e7c4dc | LATEST_SAMPLE_ID         |   4 |   2 |  19 |   0 |   0 |   0 |   0 |   0 |   0 |         426 | kewaGetLatestSampleId     |
        |  15 | 0x16e7c4b4 | OLDEST_SAMPLE_ID         |   4 |   2 |  19 |   0 |   0 |   0 |   0 |   0 |   0 |         427 | kewaGetOldestSampleId     |
        |  17 | 0x16eac664 | START_FLUSHING_SAMPLE_ID |   4 |   2 |  19 |   0 |   0 |   0 |   0 |   0 |   0 |         428 | kewaGetStartFlushSampleId |
        ...
        +-----+------------+--------------------------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-------------+---------------------------+
        # types 20, 33, and 43
        (venv) [oracle@db-21 ~]$ xinfo desc 'X$QERFXTST'
        +-----+------------+---------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-------------+-------------+
        | cno |    nam_ptr | nam     | siz | dty | typ | max | lsz | lof | off | idx | ipo | kqfcop_indx | func        |
        +-----+------------+---------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-------------+-------------+
        ...
        |   5 | 0x16ea6080 | QIN     |   4 |   2 |  20 |   0 |   0 |   0 |   0 |   0 |   0 |         362 | qerfxtstin  |
        |   6 | 0x16e88954 | QSI     |   8 |   2 |  33 |   0 |   0 |   0 |   0 |   0 |   0 |         363 | qerfxtstsb8 |
        |   7 | 0x16ea6084 | QDL     |   8 |   2 |  43 |   0 |   0 |   0 |   0 |   0 |   0 |         364 | qerfxtstub8 |
        +-----+------------+---------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-------------+-------------+
        

        Thanks,
        Mikhail

        Comment by Mikhail Velikikh — March 18, 2022 @ 9:46 pm GMT Mar 18,2022 | Reply

  3. 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)?:

    nm oracle | grep kglob
    
    [280207]        |           112891104|                1190|FUNC |GLOB |0    |20     |kglobal
    [280440]        |           458066416|                 730|FUNC |GLOB |0    |20     |kglobcl
    [280425]        |           112014800|                1701|FUNC |GLOB |0    |20     |kglobf0
    [280343]        |           112016528|                3262|FUNC |GLOB |0    |20     |kglobfr
    [280547]        |           111127584|                6671|FUNC |GLOB |0    |20     |kglobld
    [280482]        |           458067168|                2152|FUNC |GLOB |0    |20     |kglobpc
    [280489]        |           458065632|                 757|FUNC |GLOB |0    |20     |kglobpg
    [280495]        |           111123552|                4016|FUNC |GLOB |0    |20     |kglobpn
    [223724]        |           457890176|                 634|FUNC |GLOB |0    |20     |kglobr
    [223725]        |           457890848|                 388|FUNC |GLOB |0    |20     |kglobs
    

    Best regards,
    Nenad

    Comment by Nenad Noveljic — March 18, 2022 @ 3:20 pm GMT Mar 18,2022 | Reply

    • Hi Nenad,

      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/

      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):

      [oracle@db-21 bin]$ xinfo list 'X$KTCXB' -o json --with-kqftap
      {
        "560": {
          "obj": 4294951014,
          "ver": 16,
          "nam_ptr": 384330392,
          "nam": "X$KTCXB",
          "xstruct_nam_ptr": 384330400,
          "xstruct": "ktcxv",
          "typ": 4,
          "flg": 0,
          "rsz": 2056,
          "coc": 61,
          "kqftap": {
            "xstruct_ptr": 383357760,
            "cb1_ptr": 151869360,
            "xstruct": "ktcxv_c",
            "cb1": "ktcxbc"
          }
        }
      }
      

      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.

      offset=0 seems to mean that data isn’t stored in an array, but generated on the fly instead.

      X$KSLED.KSLEDNAM has offset 0, and we can see that there is an address of a string at this memory location:

      SQL> select addr, kslednam from x$ksled where rownum<=10;
      
      ADDR             KSLEDNAM
      ---------------- ----------------------------------------------------------------
      000000008519AAC0 null event
      000000008519AAF8 logout restrictor
      000000008519AB30 VKTM Logical Idle Wait
      000000008519AB68 VKTM Init Wait for GSGA
      000000008519ABA0 IORM Scheduler Slave Idle Wait
      000000008519ABD8 near PGA limit throttle
      000000008519AC10 Parameter File I/O
      000000008519AC48 rdbms ipc message
      000000008519AC80 remote db operation
      000000008519ACB8 remote db file read
      
      10 rows selected.
      
      SQL> oradebug peek 0x000000008519AAC0 4
      [08519AAC0, 08519AAC4) = 1570A598
      SQL> oradebug peek 0x1570A598 48
      [01570A598, 01570A5C8) = 6C6C756E 65766520 0000746E 6F676F6C 72207475 72747365 6F746369 00000072 4D544B56 676F4C20 6C616369 6C644920
      SQL> select utl_raw.cast_to_varchar2(hextoraw(replace('6C6C756E 65766520 0000746E', ' '))) from dual;
      
      UTL_RAW.CAST_TO_VARCHAR2(HEXTORAW(REPLACE('6C6C756E657665200000746E','')))
      -------------------------------------------------------------------------------------------------------------------------------------
      lluneve   tn
      

      Mikhail, have you published xinfo?

      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

      Does the tool show which of the following Oracle C functions populates x$kglcursor_child.kglobcce (v$sql.optimizer_env)?:

      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):

      SQL> select addr, '0x'||to_char(to_number(addr, 'xxxxxxxxxxxxxxxx')+2453, 'fmxxxxxxxxxxxxxxxx') kglobcce_off, kglobcce from x$kglob where rownum<=10 and kglobcce is not null;
      
      ADDR             KGLOBCCE_OFF        KGLOBCCE
      ---------------- ------------------- ------------------------------------------------------------------------------------------------------------------------
      00007F325EE41F10 0x7f325ee428a5      E289FB8950964001890210006EF5C3E2CFFA332056414555519521105545551545545558591555449665851D5511058555559155515122555415A0EA
      00007F325E657A68 0x7f325e6583fd      E289FB8950964001890210006EF5C3E2CFFA332056414555519521105545551545545558591555449665851D55110585555591555151225554D5A0EA
      00007F325E659560 0x7f325e659ef5      E289FB8950964001890210006EF5C3E2CFFA332056414555519521105545551545545558591555449665851D55110585555591555151225554D5A0EA
      00007F325E62BDA0 0x7f325e62c735      E289FB8950964001890210006EF5C3E2CFFA332056414555519521105545551545545558591555449665851D5511058555559155515122555415A0EA
      00007F325E62D898 0x7f325e62e22d      E289FB8950964001890210006EF5C3E2CFFA332056414555519521105545551545545558591555449665851D5511058555559155515122555415A0EA
      00007F325E62F390 0x7f325e62fd25      E289FB8950964001890210006EF5C3E2CFFA332056414555519521105545551545545558591555449665851D55110585555591555151225554D5A0EA
      00007F325E630E88 0x7f325e63181d      E289FB8950964001890210006EF5C3E2CFFA332056414555519521105545551545545558591555449665851D5511058555559155515122555415A0EA
      00007F325E632980 0x7f325e633315      E289FB8950964001890210006EF5C3E2CFFA332056414555519521105545551545545558591555449665851D55110585555591555151225554D5A0EA
      00007F325E634478 0x7f325e634e0d      E289FB8950964001890210006EF5C3E2CFFA332056414555519521105545551545545558591555449665851D5511058555559155515122555415A0EA
      00007F325E635F70 0x7f325e636905      E289FB8950964001890210006EF5C3E2CFFA332056414555519521105545551545545558591555449665851D55110585555591555151225554D5A0EA
      
      10 rows selected.
      
      SQL> oradebug peek 0x7f325e633315 48
      [7F325E633314, 7F325E633348) = FB89E220 40965089 10028901 C3F56E00 33FACFE2 45415620 21955155 55455510 55544515 55155958 85659644 0511551D
      SQL> oradebug peek 0x7f325e634e0d 48
      [7F325E634E0C, 7F325E634E40) = FB89E221 40965089 10028901 C3F56E00 33FACFE2 45415620 21955155 55455510 55544515 55155958 85659644 0511551D
      

      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):

      (venv) [oracle@db-21 bin]$ xinfo desc 'X$KSUSE'
      +-----+------------+-------------------------------+------+-----+-----+-----+-----+------+-------+-----+-----+-------------+---------------------------------+
      | cno |    nam_ptr | nam                           |  siz | dty | typ | max | lsz |  lof |   off | idx | ipo | kqfcop_indx | func                            |
      +-----+------------+-------------------------------+------+-----+-----+-----+-----+------+-------+-----+-----+-------------+---------------------------------+
      |   5 | 0x16e8b9d4 | KSSPAFLG                      |    4 |   2 |  20 |   0 |   0 |    0 |     0 |   0 |   0 |           9 | kss_view_flags                  |
      |   6 | 0x16e8b9e0 | KSSPAOWN                      |    8 |  23 |  20 |   0 |   0 |    0 |     0 |   0 |   0 |          10 | kss_view_owner                  |
      |   7 | 0x16e8b9ec | KSSPATYP                      |    4 |   2 |   7 |   0 |   0 |    0 |     0 |   0 |   0 |          11 | ksugpa                          |
      |  25 | 0x16e8bac4 | KSUSEPRO                      |    8 |  23 |   7 |   0 |   0 |    0 |     0 |   0 |   0 |          12 | ksugsp                          |
      |  41 | 0x16e8bb78 | KSUSESQI                      |   13 |   1 |  20 |   0 |   0 |    0 |     0 |   0 |   0 |          13 | ksu_getsid_cb                   |
      |  45 | 0x16e8bbac | KSUSEPSI                      |   13 |   1 |  20 |   0 |   0 |    0 |     0 |   0 |   0 |          14 | ksu_getpsid_cb                  |
      |  60 | 0x16e8bc60 | KSUSEAPP                      |   64 |   1 |  20 |   0 |   0 |    0 |     0 |   0 |   0 |          15 | ksugetapp_cb                    |
      |  61 | 0x16e8bc6c | KSUSEAPH                      |    4 |   2 |  20 |   0 |   0 |    0 |     0 |   0 |   0 |          16 | ksugetapph_cb                   |
      |  62 | 0x16e8bc78 | KSUSEACT                      |   64 |   1 |  20 |   0 |   0 |    0 |     0 |   0 |   0 |          17 | ksugetact_cb                    |
      |  63 | 0x16e8bc84 | KSUSEACH                      |    4 |   2 |  20 |   0 |   0 |    0 |     0 |   0 |   0 |          18 | ksugetacth_cb                   |
      |  65 | 0x16e8bc9c | KSUSECTM                      |    4 |   2 |   7 |   0 |   0 |    0 |     0 |   0 |   0 |          19 | ksugeltm                        |
      |  71 | 0x16e8bcd8 | KSUSEGRP                      |   32 |   1 |   7 |   0 |   0 |    0 |     0 |   0 |   0 |          20 | ksugetccg                       |
      |  73 | 0x16e8bcf0 | KSUSEPXOPT                    |    4 |   2 |  20 |   0 |   0 |    0 |     0 |   0 |   0 |          21 | ksugetpxopt                     |
      |  75 | 0x16e8bd08 | KSUSECQD                      |    4 |   2 |   7 |   0 |   0 |    0 |     0 |   0 |   0 |          22 | ksugqd                          |
      |  79 | 0x16e8bd3c | KSUSEBLOCKER                  |    4 |   2 |   7 |   0 |   0 |    0 |     0 |   0 |   0 |          23 | ksugetblocker                   |
      |  80 | 0x16e8bd4c | KSUSEFBLOCKER                 |    4 |   2 |   7 |   0 |   0 |    0 |     0 |   0 |   0 |          24 | ksugetfinalblocker              |
      |  90 | 0x16e8bdbc | KSUSEWTM                      |    4 |   2 |   7 |   0 |   0 |    0 |     0 |   0 |   0 |          25 | ksugwtm                         |
      |  91 | 0x16e8bdc8 | KSUSESVC                      |   64 |   1 |   7 |   0 |   0 |    0 |     0 |   0 |   0 |          26 | ksugsvc                         |
      | 103 | 0x16e8be5c | KSUSEAPPCONT_MODE             |    4 |   2 |  20 |   0 |   0 |    0 |     0 |   0 |   0 |          27 | kpoxcAppContMode_cb             |
      | 104 | 0x16e8be70 | KSUSEAPPCONT_STATESTATIC      |    4 |   2 |  20 |   0 |   0 |    0 |     0 |   0 |   0 |          28 | kpoxcAppContStateStatic_cb      |
      | 105 | 0x16e8be8c | KSUSEAPPCONT_INREQUEST        |    4 |   2 |  20 |   0 |   0 |    0 |     0 |   0 |   0 |          29 | kpoxcAppContInRequest_cb        |
      | 106 | 0x16e8bea4 | KSUSEAPPCONT_ERRPENDINGREPLAY |    4 |   2 |  20 |   0 |   0 |    0 |     0 |   0 |   0 |          30 | kpoxcAppContErrPendingRepl_cb   |
      | 107 | 0x16e8bec4 | KSUSEAPPCONT_REQROUNDTRIPNUM  |    4 |   2 |  20 |   0 |   0 |    0 |     0 |   0 |   0 |          31 | kpoxcAppContReqRoundtripNum_cb  |
      | 108 | 0x16e8bee4 | KSUSEAPPCONT_PENDINGERRORCODE |    4 |   2 |  20 |   0 |   0 |    0 |     0 |   0 |   0 |          32 | kpoxcAppContPendingErrorCode_cb |
      | 109 | 0x16e8bf04 | KSUSEAPPCONT_CTLFLAGS         |    4 |   2 |  20 |   0 |   0 |    0 |     0 |   0 |   0 |          33 | kpoxcAppContCtlFlags_cb         |
      | 110 | 0x16e8bf1c | KSUSEAPPCONT_ENDOFCALLSTATUS  |    4 |   2 |  20 |   0 |   0 |    0 |     0 |   0 |   0 |          34 | kpoxcAppContEndOfCallStatus_cb  |
      | 112 | 0x16e8bf48 | KSUSETAG                      |   16 |   1 |   7 |   0 |   0 |    0 |     0 |   0 |   0 |          35 | ksugtag                         |
      +-----+------------+-------------------------------+------+-----+-----+-----+-----+------+-------+-----+-----+-------------+---------------------------------+
      

      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 | Reply

  4. Prompted by the “classification” observation, I ran a query to break the list down by kqftatyp. Interesting to note the split:

    select 
            substr(addr,9,1) adr, kqftatyp, count(*) 
    from x$kqfta 
    group by 
            substr(addr,9,1), kqftatyp
    order by 1,2
    /
    
    ADR    KQFTATYP   COUNT(*)
    ---- ---------- ----------
    1             1        164
                  2         18
                  4        729
                  5        302
                  6          8
                  7          8
                  8          1
    
    8             9          8
                 10         97
    
    
    

    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 | Reply

  5. […] 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 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.

%d bloggers like this: