Oracle internal data dictionary oddity

fritshoogland's picture

This blogpost is about an inconsistency I found in the X$ tables X$KQFTA and X$KQFCO. This is very specific. If you don’t care about that, you can skip this post.

The Oracle database’s “dynamic performance views” are views that are prefixed with “GV$” and “V$”. The “G” with “GV$” stands for “global” and gets you the results from its “V$” equivalent for all instances. In most cases, but that’s not the purpose of this blog. In most cases, the “V$” version simply is the “GV$” view with inst_id (instance id, used in RAC to specify the instance id) set to the current instance. I’ll refer to both simply as “V$” for simplicity.

The “V$” views are mostly build on top of “X$” tables. The “X$” tables are Oracle internal tables, and not officially supported, therefore you should use the “V$” views.

One of the things that certain “X$” tables expose is information about the “X$” tables and their columns theirselves. These “X$” tables are visible in “X$KQFTA” (kernel query fixed tables tables) for the “X$” tables, and “X$KQFCO” (kernel query fixed tables columns) for the columns of the “X$” tables. I learned about these tables a long time ago from Kyle Hailey, Anjo Kolk and James Morle regarding direct memory attachment for wait events, the reason being the ability to find the memory address for certain “X$” tables to fetch the data these hold directly. We are talking about the 1990s here. In fact, Kyle later put the history of how he learned about it in the “Oracle insights: tales of the oaktable” book.

I gather information about every PSU of every Oracle database version on linux once it comes out. This is how I am able to produce the posts about the difference between a current and a previous PSU. One of the things that I gather is information about X$ tables and their columns. In order to obtain the columns, I use X$KQFTA and X$KQFCO and join these in the way that is publicly known:

select t.kqftanam, c.kqfconam
from x$kqfta t, x$kqfco c
where t.inst_id = 1
and c.inst_id = 1
and t.indx = c.kqfcotab;

And this worked well…

…until I produced a difference report of versions 12.1.0.2.191015 and 12.1.0.2.190716, where the report reported the following difference:

x$ tables columns unique to 12.1.0.2.190716 versus 12.1.0.2.191015

NAME                                                                                                 COLUMN_NAME
---------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
X$KQFTVRTTST0                                                                                        ADDR
X$KQFTVRTTST0                                                                                        CHGROWID
X$KQFTVRTTST0                                                                                        CHGROWOP
X$KQFTVRTTST0                                                                                        COL0_0
X$KQFTVRTTST0                                                                                        COL0_1
X$KQFTVRTTST0                                                                                        COL0_2
X$KQFTVRTTST0                                                                                        COL0_3
X$KQFTVRTTST0                                                                                        COL0_4
...and so on...

This could happen, these are columns that seem to have vanished with 12.1.0.2.191015. However, I decided to check it, although I haven’t got access to the 12.1.0.2.191015 PSU, because it’s a PSU version only available for paid extended support.

I looked at the columns of X$KQFTVRTST0 in my current database, which happens to be an ancient 11.2.0.2 database. However, much to my surprise, this gave me radically different columns:

SYS@o112 AS SYSDBA> desc X$KQFTVRTTST0
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 ADDR                                                                                                                       RAW(8)
 INDX                                                                                                                       NUMBER
 INST_ID                                                                                                                    NUMBER
 COLA                                                                                                                       VARCHAR2(64)
 COLB                                                                                                                       NUMBER

Okay, something weird is going on here.

First let’s look in which versions X$KQFTVRTTST0 is present:

ORAVER@o112 > @x_table_in_versions.sql

DATABASE_VERSION     NAME
-------------------- --------------------------------------------------------------------------------------------------------------------------------
11.2.0.1.0           X$KQFTVRTTST0
11.2.0.2.0           X$KQFTVRTTST0
11.2.0.2.12          X$KQFTVRTTST0
11.2.0.3.0           X$KQFTVRTTST0
11.2.0.3.15          X$KQFTVRTTST0
11.2.0.4.0           X$KQFTVRTTST0
11.2.0.4.1           X$KQFTVRTTST0
11.2.0.4.160119      X$KQFTVRTTST0
11.2.0.4.160419      X$KQFTVRTTST0
11.2.0.4.160719      X$KQFTVRTTST0
11.2.0.4.161018      X$KQFTVRTTST0
11.2.0.4.170418      X$KQFTVRTTST0
11.2.0.4.170718      X$KQFTVRTTST0
11.2.0.4.170814      X$KQFTVRTTST0
11.2.0.4.180116      X$KQFTVRTTST0
11.2.0.4.180417      X$KQFTVRTTST0
11.2.0.4.180717      X$KQFTVRTTST0
11.2.0.4.190115      X$KQFTVRTTST0
11.2.0.4.191015      X$KQFTVRTTST0
11.2.0.4.2           X$KQFTVRTTST0
11.2.0.4.3           X$KQFTVRTTST0
11.2.0.4.4           X$KQFTVRTTST0
11.2.0.4.5           X$KQFTVRTTST0
11.2.0.4.6           X$KQFTVRTTST0
11.2.0.4.7           X$KQFTVRTTST0
11.2.0.4.8           X$KQFTVRTTST0
12.1.0.2.0           X$KQFTVRTTST0
12.1.0.2.1           X$KQFTVRTTST0
12.1.0.2.160119      X$KQFTVRTTST0
12.1.0.2.160419      X$KQFTVRTTST0
12.1.0.2.160719      X$KQFTVRTTST0
12.1.0.2.161018      X$KQFTVRTTST0
12.1.0.2.170117      X$KQFTVRTTST0
12.1.0.2.170418      X$KQFTVRTTST0
12.1.0.2.170718      X$KQFTVRTTST0
12.1.0.2.170814      X$KQFTVRTTST0
12.1.0.2.171017      X$KQFTVRTTST0
12.1.0.2.180116      X$KQFTVRTTST0
12.1.0.2.180417      X$KQFTVRTTST0
12.1.0.2.181016      X$KQFTVRTTST0
12.1.0.2.190115      X$KQFTVRTTST0
12.1.0.2.190416      X$KQFTVRTTST0
12.1.0.2.190716      X$KQFTVRTTST0
12.1.0.2.191015      X$KQFTVRTTST0
12.1.0.2.3           X$KQFTVRTTST0
12.1.0.2.4           X$KQFTVRTTST0
12.1.0.2.5           X$KQFTVRTTST0
12.2.0.1.0           X$KQFTVRTTST0
12.2.0.1.170620      X$KQFTVRTTST0
12.2.0.1.170718      X$KQFTVRTTST0
12.2.0.1.170814      X$KQFTVRTTST0
12.2.0.1.171017      X$KQFTVRTTST0
12.2.0.1.180116      X$KQFTVRTTST0
12.2.0.1.180417      X$KQFTVRTTST0
12.2.0.1.180717      X$KQFTVRTTST0
12.2.0.1.181016      X$KQFTVRTTST0
12.2.0.1.190115      X$KQFTVRTTST0
12.2.0.1.190416      X$KQFTVRTTST0
12.2.0.1.190716      X$KQFTVRTTST0
12.2.0.1.191015      X$KQFTVRTTST0
18.3                 X$KQFTVRTTST0
18.4                 X$KQFTVRTTST0
18.5                 X$KQFTVRTTST0
18.6                 X$KQFTVRTTST0
18.7                 X$KQFTVRTTST0
18.8                 X$KQFTVRTTST0
19.2                 X$KQFTVRTTST0
19.3                 X$KQFTVRTTST0
19.4                 X$KQFTVRTTST0
19.5                 X$KQFTVRTTST0

So, X$KQFVRTTST0 appeared with 11.2.0.1. Now let’s take a look at how the columns look like in X$KQFVRTTST0 looks in version 11.2.0.1:

ORAVER@o112 > @x_table_cols_in_versions.sql

DATABASE_VERSION	       TABLE_NAME		      COLUMN_NAME
------------------------------ ------------------------------ ----------------------------------------
11.2.0.1.0		       X$KQFTVRTTST0		      COL3_9
11.2.0.1.0		       X$KQFTVRTTST0		      COL3_8
11.2.0.1.0		       X$KQFTVRTTST0		      COL3_7
11.2.0.1.0		       X$KQFTVRTTST0		      COL3_6
11.2.0.1.0		       X$KQFTVRTTST0		      COL3_5
11.2.0.1.0		       X$KQFTVRTTST0		      COL3_4
11.2.0.1.0		       X$KQFTVRTTST0		      COL3_3
11.2.0.1.0		       X$KQFTVRTTST0		      COL3_2
11.2.0.1.0		       X$KQFTVRTTST0		      COL3_1
11.2.0.1.0		       X$KQFTVRTTST0		      COL3_0
11.2.0.1.0		       X$KQFTVRTTST0		      COL2_9
11.2.0.1.0		       X$KQFTVRTTST0		      COL2_8
11.2.0.1.0		       X$KQFTVRTTST0		      COL2_7
11.2.0.1.0		       X$KQFTVRTTST0		      COL2_6
11.2.0.1.0		       X$KQFTVRTTST0		      COL2_5
11.2.0.1.0		       X$KQFTVRTTST0		      COL2_4
11.2.0.1.0		       X$KQFTVRTTST0		      COL2_3
11.2.0.1.0		       X$KQFTVRTTST0		      COL2_2
11.2.0.1.0		       X$KQFTVRTTST0		      COL2_1
11.2.0.1.0		       X$KQFTVRTTST0		      COL2_0
11.2.0.1.0		       X$KQFTVRTTST0		      COL1_9
11.2.0.1.0		       X$KQFTVRTTST0		      COL1_8
11.2.0.1.0		       X$KQFTVRTTST0		      COL1_7
11.2.0.1.0		       X$KQFTVRTTST0		      COL1_6
11.2.0.1.0		       X$KQFTVRTTST0		      COL1_5
11.2.0.1.0		       X$KQFTVRTTST0		      COL1_4
11.2.0.1.0		       X$KQFTVRTTST0		      COL1_3
11.2.0.1.0		       X$KQFTVRTTST0		      COL1_2
11.2.0.1.0		       X$KQFTVRTTST0		      COL1_1
11.2.0.1.0		       X$KQFTVRTTST0		      COL1_0
11.2.0.1.0		       X$KQFTVRTTST0		      COL0_9
11.2.0.1.0		       X$KQFTVRTTST0		      COL0_8
11.2.0.1.0		       X$KQFTVRTTST0		      COL0_7
11.2.0.1.0		       X$KQFTVRTTST0		      COL0_6
11.2.0.1.0		       X$KQFTVRTTST0		      COL0_5
11.2.0.1.0		       X$KQFTVRTTST0		      COL0_4
11.2.0.1.0		       X$KQFTVRTTST0		      COL0_3
11.2.0.1.0		       X$KQFTVRTTST0		      COL0_2
11.2.0.1.0		       X$KQFTVRTTST0		      COL0_1
11.2.0.1.0		       X$KQFTVRTTST0		      COL0_0
11.2.0.1.0		       X$KQFTVRTTST0		      CHGROWOP
11.2.0.1.0		       X$KQFTVRTTST0		      CHGROWID
11.2.0.1.0		       X$KQFTVRTTST0		      COL_OPERATION
11.2.0.1.0		       X$KQFTVRTTST0		      INST_ID
11.2.0.1.0		       X$KQFTVRTTST0		      INDX
11.2.0.1.0		       X$KQFTVRTTST0		      ADDR

These are the columns which were shown in the difference report that said they were removed. Now let’s look at the columns in version 19.5:

ORAVER@o112 > @x_table_cols_in_versions.sql

DATABASE_VERSION               TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------ ----------------------------------------
19.5                           X$KQFTVRTTST0                  COL3_9
19.5                           X$KQFTVRTTST0                  COL3_8
19.5                           X$KQFTVRTTST0                  COL3_7
19.5                           X$KQFTVRTTST0                  COL3_6
19.5                           X$KQFTVRTTST0                  COL3_5
19.5                           X$KQFTVRTTST0                  COL3_4
19.5                           X$KQFTVRTTST0                  COL3_3
19.5                           X$KQFTVRTTST0                  COL3_2
19.5                           X$KQFTVRTTST0                  COL3_1
19.5                           X$KQFTVRTTST0                  COL3_0
19.5                           X$KQFTVRTTST0                  COL2_9
19.5                           X$KQFTVRTTST0                  COL2_8
19.5                           X$KQFTVRTTST0                  COL2_7
19.5                           X$KQFTVRTTST0                  COL2_6
19.5                           X$KQFTVRTTST0                  COL2_5
19.5                           X$KQFTVRTTST0                  COL2_4
19.5                           X$KQFTVRTTST0                  COL2_3
19.5                           X$KQFTVRTTST0                  COL2_2
19.5                           X$KQFTVRTTST0                  COL2_1
19.5                           X$KQFTVRTTST0                  COL2_0
19.5                           X$KQFTVRTTST0                  COL1_9
19.5                           X$KQFTVRTTST0                  COL1_8
19.5                           X$KQFTVRTTST0                  COL1_7
19.5                           X$KQFTVRTTST0                  COL1_6
19.5                           X$KQFTVRTTST0                  COL1_5
19.5                           X$KQFTVRTTST0                  COL1_4
19.5                           X$KQFTVRTTST0                  COL1_3
19.5                           X$KQFTVRTTST0                  COL1_2
19.5                           X$KQFTVRTTST0                  COL1_1
19.5                           X$KQFTVRTTST0                  COL1_0
19.5                           X$KQFTVRTTST0                  COL0_9
19.5                           X$KQFTVRTTST0                  COL0_8
19.5                           X$KQFTVRTTST0                  COL0_7
19.5                           X$KQFTVRTTST0                  COL0_6
19.5                           X$KQFTVRTTST0                  COL0_5
19.5                           X$KQFTVRTTST0                  COL0_4
19.5                           X$KQFTVRTTST0                  COL0_3
19.5                           X$KQFTVRTTST0                  COL0_2
19.5                           X$KQFTVRTTST0                  COL0_1
19.5                           X$KQFTVRTTST0                  COL0_0
19.5                           X$KQFTVRTTST0                  CHGROWOP
19.5                           X$KQFTVRTTST0                  CHGROWID
19.5                           X$KQFTVRTTST0                  COL_OPERATION
19.5                           X$KQFTVRTTST0                  CON_ID
19.5                           X$KQFTVRTTST0                  INST_ID
19.5                           X$KQFTVRTTST0                  INDX
19.5                           X$KQFTVRTTST0                  ADDR

These are exactly the same! So the columns for X$KQFTVRTTST0 that I obtained from the database are the same for all versions in my data, but when I look in an actual database using ‘describe’ I see ADDR, INDX, INST_ID and COLA and COLB. So my data seems to be wrong!

My first reaction was to change the columns for X$KQFTVRTST0 for every version to correct this. But then I realised that I had to investigate this deeper to understand the actual problem, there might be more X$ tables involved.

The first thing to do, is to see if I can reproduce the issue in my database. This can be quite easily be done by describing X$KQFTVRTTST0 to see the columns, and then query these using the above query that demonstrates the join between X$KQFTA and X$KQFCO by adding a filter on ‘X$KQFTVRTST0’:

SYS@o112 AS SYSDBA> desc X$KQFTVRTTST0
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 ADDR                                                                                                                       RAW(8)
 INDX                                                                                                                       NUMBER
 INST_ID                                                                                                                    NUMBER
 COLA                                                                                                                       VARCHAR2(64)
 COLB                                                                                                                       NUMBER
SYS@o112 AS SYSDBA> l
  1  select t.kqftanam, c.kqfconam
  2  from x$kqfta t, x$kqfco c
  3  where t.inst_id = 1
  4  and c.inst_id = 1
  5  and t.indx = c.kqfcotab
  6* and kqftanam = 'X$KQFTVRTTST0'
SYS@o112 AS SYSDBA> /

KQFTANAM                       KQFCONAM
------------------------------ ------------------------------
X$KQFTVRTTST0                  ADDR
X$KQFTVRTTST0                  INDX
X$KQFTVRTTST0                  INST_ID
X$KQFTVRTTST0                  COL_OPERATION
X$KQFTVRTTST0                  CHGROWID
X$KQFTVRTTST0                  CHGROWOP
X$KQFTVRTTST0                  COL0_0
X$KQFTVRTTST0                  COL0_1
X$KQFTVRTTST0                  COL0_2
X$KQFTVRTTST0                  COL0_3
X$KQFTVRTTST0                  COL0_4
X$KQFTVRTTST0                  COL0_5
X$KQFTVRTTST0                  COL0_6
X$KQFTVRTTST0                  COL0_7
X$KQFTVRTTST0                  COL0_8
X$KQFTVRTTST0                  COL0_9
X$KQFTVRTTST0                  COL1_0
X$KQFTVRTTST0                  COL1_1
X$KQFTVRTTST0                  COL1_2
X$KQFTVRTTST0                  COL1_3
X$KQFTVRTTST0                  COL1_4
X$KQFTVRTTST0                  COL1_5
X$KQFTVRTTST0                  COL1_6
X$KQFTVRTTST0                  COL1_7
X$KQFTVRTTST0                  COL1_8
X$KQFTVRTTST0                  COL1_9
X$KQFTVRTTST0                  COL2_0
X$KQFTVRTTST0                  COL2_1
X$KQFTVRTTST0                  COL2_2
X$KQFTVRTTST0                  COL2_3
X$KQFTVRTTST0                  COL2_4
X$KQFTVRTTST0                  COL2_5
X$KQFTVRTTST0                  COL2_6
X$KQFTVRTTST0                  COL2_7
X$KQFTVRTTST0                  COL2_8
X$KQFTVRTTST0                  COL2_9
X$KQFTVRTTST0                  COL3_0
X$KQFTVRTTST0                  COL3_1
X$KQFTVRTTST0                  COL3_2
X$KQFTVRTTST0                  COL3_3
X$KQFTVRTTST0                  COL3_4
X$KQFTVRTTST0                  COL3_5
X$KQFTVRTTST0                  COL3_6
X$KQFTVRTTST0                  COL3_7
X$KQFTVRTTST0                  COL3_8
X$KQFTVRTTST0                  COL3_9

Bingo! Describe tells me about COLA and COLB, which is the actual situation, and querying X$KQFTA and X$KQFCO gives me different, wrong columns.

First let’s investigate if we can find the correct columns in X$KQFCO, and see what the difference is between X$KQFTA.INDX and X$KQFCO.KQFCOTAB:

SYS@o112 AS SYSDBA> select indx from x$kqfta where kqftanam = 'X$KQFTVRTTST0';

      INDX
----------
       841

SYS@o112 AS SYSDBA> select kqfcotab from x$kqfco where kqfconam = 'COLA';

  KQFCOTAB
----------
       842

Aha! So the join is off by one! So, that must mean that if I decrease KQFCOTAB by one, I do get the correct output:

SYS@o112 AS SYSDBA> ed
Wrote file afiedt.buf

  1  select t.kqftanam, c.kqfconam
  2  from x$kqfta t, x$kqfco c
  3  where t.inst_id = 1
  4  and c.inst_id = 1
  5  and t.indx = c.kqfcotab-1
  6* and kqftanam = 'X$KQFTVRTTST0'
  7  /

KQFTANAM                       KQFCONAM
------------------------------ ------------------------------
X$KQFTVRTTST0                  ADDR
X$KQFTVRTTST0                  INDX
X$KQFTVRTTST0                  INST_ID
X$KQFTVRTTST0                  COLA
X$KQFTVRTTST0                  COLB

Bingo! The next thing to do is see if this is only a problem for X$KQFTVRTTST0, or a bigger problem. Let’s take the next table based on X$KQFTA.INDX, and see if this shows the same problem:

SYS@o112 AS SYSDBA> select kqftanam from x$kqfta where indx = 842;

KQFTANAM
------------------------------
X$DIAG_ADR_CONTROL
SYS@o112 AS SYSDBA> desc X$DIAG_ADR_CONTROL
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 ADDR                                                                                                                       RAW(8)
 INDX                                                                                                                       NUMBER
 INST_ID                                                                                                                    NUMBER
 ADR_PATH_IDX                                                                                                               VARCHAR2(445)
 ADR_HOME                                                                                                                   VARCHAR2(445)
 ADRID                                                                                                                      NUMBER
 SHORTP_POLICY                                                                                                              NUMBER
 LONGP_POLICY                                                                                                               NUMBER
 LAST_MOD_TIME                                                                                                              TIMESTAMP(9) WITH TIME ZONE
 LAST_AUTOPRG_TIME                                                                                                          TIMESTAMP(9) WITH TIME ZONE
 LAST_MANUPRG_TIME                                                                                                          TIMESTAMP(9) WITH TIME ZONE
 ADRDIR_VERSION                                                                                                             NUMBER
 ADRSCHM_VERSION                                                                                                            NUMBER
 ADRSCHMV_SUMMARY                                                                                                           NUMBER
 ADRALERT_VERSION                                                                                                           NUMBER
 CREATE_TIME                                                                                                                TIMESTAMP(9) WITH TIME ZONE
SYS@o112 AS SYSDBA> ed
Wrote file afiedt.buf

  1   select t.kqftanam, c.kqfconam
  2   from x$kqfta t, x$kqfco c
  3   where t.inst_id = 1
  4   and c.inst_id = 1
  5   and t.indx = c.kqfcotab
  6*  and kqftanam = 'X$DIAG_ADR_CONTROL'
SYS@o112 AS SYSDBA> /

KQFTANAM                       KQFCONAM
------------------------------ ------------------------------
X$DIAG_ADR_CONTROL             ADDR
X$DIAG_ADR_CONTROL             INDX
X$DIAG_ADR_CONTROL             INST_ID
X$DIAG_ADR_CONTROL             COLA
X$DIAG_ADR_CONTROL             COLB

Clearly the columns are wrong, these are the columns of X$KQFTVRTTST0! Let’s subtract X$KQFCO.KQFCOTAB by one:

SYS@o112 AS SYSDBA> ed
Wrote file afiedt.buf

  1   select t.kqftanam, c.kqfconam
  2   from x$kqfta t, x$kqfco c
  3   where t.inst_id = 1
  4   and c.inst_id = 1
  5   and t.indx = c.kqfcotab-1
  6*  and kqftanam = 'X$DIAG_ADR_CONTROL'
SYS@o112 AS SYSDBA> /

KQFTANAM                       KQFCONAM
------------------------------ ------------------------------
X$DIAG_ADR_CONTROL             ADDR
X$DIAG_ADR_CONTROL             INDX
X$DIAG_ADR_CONTROL             INST_ID
X$DIAG_ADR_CONTROL             ADR_PATH_IDX
X$DIAG_ADR_CONTROL             ADR_HOME
X$DIAG_ADR_CONTROL             ADRID
X$DIAG_ADR_CONTROL             SHORTP_POLICY
X$DIAG_ADR_CONTROL             LONGP_POLICY
X$DIAG_ADR_CONTROL             LAST_MOD_TIME
X$DIAG_ADR_CONTROL             LAST_AUTOPRG_TIME
X$DIAG_ADR_CONTROL             LAST_MANUPRG_TIME
X$DIAG_ADR_CONTROL             ADRDIR_VERSION
X$DIAG_ADR_CONTROL             ADRSCHM_VERSION
X$DIAG_ADR_CONTROL             ADRSCHMV_SUMMARY
X$DIAG_ADR_CONTROL             ADRALERT_VERSION
X$DIAG_ADR_CONTROL             CREATE_TIME

And this is correct again!

Now let’s verify the highest X$KQFTA.INDX number in the version of this database:

SYS@o112 AS SYSDBA> select max(indx) from x$kqfta;

 MAX(INDX)
----------
       927

SYS@o112 AS SYSDBA> select kqftanam from x$kqfta where indx = 927;

KQFTANAM
------------------------------
X$DIAG_VTEST_EXISTS

SYS@o112 AS SYSDBA> desc X$DIAG_VTEST_EXISTS
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 ADDR                                                                                                                       RAW(8)
 INDX                                                                                                                       NUMBER
 INST_ID                                                                                                                    NUMBER
 ADR_PATH_IDX                                                                                                               VARCHAR2(445)
 ADR_HOME                                                                                                                   VARCHAR2(445)
 PROBLEM_ID                                                                                                                 NUMBER
 PROBLEM_KEY                                                                                                                VARCHAR2(552)
 FIRST_INCIDENT                                                                                                             NUMBER
 FIRSTINC_TIME                                                                                                              TIMESTAMP(9) WITH TIME ZONE
 LAST_INCIDENT                                                                                                              NUMBER
 LASTINC_TIME                                                                                                               TIMESTAMP(9) WITH TIME ZONE
SYS@o112 AS SYSDBA> ed
Wrote file afiedt.buf

  1  select t.kqftanam, c.kqfconam
  2  from x$kqfta t, x$kqfco c
  3  where t.inst_id = 1
  4  and c.inst_id = 1
  5  and t.indx = c.kqfcotab
  6* and kqftanam = 'X$DIAG_VTEST_EXISTS'
SYS@o112 AS SYSDBA> /

KQFTANAM                       KQFCONAM
------------------------------ ------------------------------
X$DIAG_VTEST_EXISTS            ADDR
X$DIAG_VTEST_EXISTS            INDX
X$DIAG_VTEST_EXISTS            INST_ID
X$DIAG_VTEST_EXISTS            ADR_PATH_IDX
X$DIAG_VTEST_EXISTS            ADR_HOME
X$DIAG_VTEST_EXISTS            INCIDENT_ID
X$DIAG_VTEST_EXISTS            PROBLEM_ID
X$DIAG_VTEST_EXISTS            CREATE_TIME
X$DIAG_VTEST_EXISTS            CLOSE_TIME
X$DIAG_VTEST_EXISTS            STATUS
X$DIAG_VTEST_EXISTS            FLAGS
X$DIAG_VTEST_EXISTS            FLOOD_CONTROLLED
X$DIAG_VTEST_EXISTS            ERROR_FACILITY
X$DIAG_VTEST_EXISTS            ERROR_NUMBER
X$DIAG_VTEST_EXISTS            ERROR_ARG1
X$DIAG_VTEST_EXISTS            ERROR_ARG2
X$DIAG_VTEST_EXISTS            ERROR_ARG3
X$DIAG_VTEST_EXISTS            ERROR_ARG4
X$DIAG_VTEST_EXISTS            ERROR_ARG5
X$DIAG_VTEST_EXISTS            ERROR_ARG6
X$DIAG_VTEST_EXISTS            ERROR_ARG7
X$DIAG_VTEST_EXISTS            ERROR_ARG8
X$DIAG_VTEST_EXISTS            SIGNALLING_COMPONENT
X$DIAG_VTEST_EXISTS            SIGNALLING_SUBCOMPONENT
X$DIAG_VTEST_EXISTS            SUSPECT_COMPONENT
X$DIAG_VTEST_EXISTS            SUSPECT_SUBCOMPONENT
X$DIAG_VTEST_EXISTS            ECID
X$DIAG_VTEST_EXISTS            IMPACT
X$DIAG_VTEST_EXISTS            IMPACT_STR
X$DIAG_VTEST_EXISTS            STATUS_STR
X$DIAG_VTEST_EXISTS            ERROR_ARG9
X$DIAG_VTEST_EXISTS            ERROR_ARG10
X$DIAG_VTEST_EXISTS            ERROR_ARG11
X$DIAG_VTEST_EXISTS            ERROR_ARG12

So that’s wrong, let’s try subtracting by one:

SYS@o112 AS SYSDBA> ed
Wrote file afiedt.buf

  1  select t.kqftanam, c.kqfconam
  2  from x$kqfta t, x$kqfco c
  3  where t.inst_id = 1
  4  and c.inst_id = 1
  5  and t.indx = c.kqfcotab-1
  6* and kqftanam = 'X$DIAG_VTEST_EXISTS'
SYS@o112 AS SYSDBA> /

KQFTANAM                       KQFCONAM
------------------------------ ------------------------------
X$DIAG_VTEST_EXISTS            ADDR
X$DIAG_VTEST_EXISTS            INDX
X$DIAG_VTEST_EXISTS            INST_ID
X$DIAG_VTEST_EXISTS            ADR_PATH_IDX
X$DIAG_VTEST_EXISTS            ADR_HOME
X$DIAG_VTEST_EXISTS            PROBLEM_ID
X$DIAG_VTEST_EXISTS            PROBLEM_KEY
X$DIAG_VTEST_EXISTS            FIRST_INCIDENT
X$DIAG_VTEST_EXISTS            FIRSTINC_TIME
X$DIAG_VTEST_EXISTS            LAST_INCIDENT
X$DIAG_VTEST_EXISTS            LASTINC_TIME

And correct again!

So at least starting from X$KQFTA.INDX = 841 up to the highest INDX number, 927, X$KQFCO.KQFCOTAB is off by one.

Starting from there, I decided to test INDX = 1, which was consistent with KQFCOTAB. Then I decided to go down in INDX number starting from 841 being the number of X$KQFTVRTTST0. In my database number 840 is X$KTCNQROW, which was off by one, then I found something weird:

SYS@o112 AS SYSDBA> select kqftanam from x$kqfta where indx = 839;

KQFTANAM
------------------------------
X$SKGXP_MISC

SYS@o112 AS SYSDBA> desc X$SKGXP_MISC
ERROR:
ORA-00942: table or view does not exist
SYS@o112 AS SYSDBA> ed
Wrote file afiedt.buf

  1      select t.kqftanam, c.kqfconam
  2      from x$kqfta t, x$kqfco c
  3      where t.inst_id = 1
  4      and c.inst_id = 1
  5      and t.indx = c.kqfcotab
  6*    and kqftanam = 'X$SKGXP_MISC'
SYS@o112 AS SYSDBA> /

no rows selected

So the X$ view X$SKGXP_MISC exists in X$KQFTA, but doesn’t exist in reality, nor in X$KQFCO. Please mind this is a separate, different issue from the X$KQFCO.KQFCOTAB inconsistency this blogpost is about. My current thinking is the issue of existing in X$KQFTA and not queryable and (rightfully because of that), not available in X$KQFCO, might be either just a screwup/bug, or the X$ tables being reserved in the X$ table array, but not actually created, maybe because of setting during compilation. Just a hunch, I don’t know.

The same issue of being visible in X$KQFTA and not existing is true for:
838, X$SKGXP_CONNECTION
837, X$SKGXP_PORT
836, X$KSXP_STATS

INDX number 835 is X$KSXPTESTTBL. This table has the X$KQFCO.KQFCOTAB off by one issue:

SYS@o112 AS SYSDBA> select kqftanam from x$kqfta where indx = 835;

KQFTANAM
------------------------------
X$KSXPTESTTBL
SYS@o112 AS SYSDBA> desc X$KSXPTESTTBL
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 ADDR                                                                                                                       RAW(8)
 INDX                                                                                                                       NUMBER
 INST_ID                                                                                                                    NUMBER
 KSXPTESTF1                                                                                                                 NUMBER
 KSXPTESTF2                                                                                                                 VARCHAR2(10)
 KSXPTESTF3                                                                                                                 NUMBER
 KSXPTESTF4                                                                                                                 NUMBER
 KSXPTESTF5                                                                                                                 RAW(8)
 KSXPTESTF6                                                                                                                 DATE
 KSXPTESTF7                                                                                                                 TIMESTAMP(3)
 KSXPTESTF8                                                                                                                 TIMESTAMP(3) WITH TIME ZONE
 KSXPTESTF9                                                                                                                 VARCHAR2(10)
 KSXPTESTF10                                                                                                                RAW(2)
 KSXPTESTF11                                                                                                                RAW(10)
SYS@o112 AS SYSDBA> ed
Wrote file afiedt.buf

  1  select t.kqftanam, c.kqfconam
  2  from x$kqfta t, x$kqfco c
  3  where t.inst_id = 1
  4  and c.inst_id = 1
  5  and t.indx = c.kqfcotab
  6* and t.kqftanam = 'X$KSXPTESTTBL'
SYS@o112 AS SYSDBA> /

no rows selected
SYS@o112 AS SYSDBA> ed
Wrote file afiedt.buf

  1  select t.kqftanam, c.kqfconam
  2  from x$kqfta t, x$kqfco c
  3  where t.inst_id = 1
  4  and c.inst_id = 1
  5  and t.indx = c.kqfcotab-1
  6* and t.kqftanam = 'X$KSXPTESTTBL'
SYS@o112 AS SYSDBA> /

KQFTANAM                       KQFCONAM
------------------------------ ------------------------------
X$KSXPTESTTBL                  ADDR
X$KSXPTESTTBL                  INDX
X$KSXPTESTTBL                  INST_ID
X$KSXPTESTTBL                  KSXPTESTF1
X$KSXPTESTTBL                  KSXPTESTF2
X$KSXPTESTTBL                  KSXPTESTF3
X$KSXPTESTTBL                  KSXPTESTF4
X$KSXPTESTTBL                  KSXPTESTF5
X$KSXPTESTTBL                  KSXPTESTF6
X$KSXPTESTTBL                  KSXPTESTF7
X$KSXPTESTTBL                  KSXPTESTF8
X$KSXPTESTTBL                  KSXPTESTF9
X$KSXPTESTTBL                  KSXPTESTF10
X$KSXPTESTTBL                  KSXPTESTF11

After X$KSXPTESTTBL, INDX number 834 is X$CELL_NAME:

SYS@o112 AS SYSDBA> select kqftanam from x$kqfta where indx = 834;

KQFTANAM
------------------------------
X$CELL_NAME
SYS@o112 AS SYSDBA> desc X$CELL_NAME
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 ADDR                                                                                                                       RAW(8)
 INDX                                                                                                                       NUMBER
 INST_ID                                                                                                                    NUMBER
 CELLPATH_CELLROW                                                                                                           VARCHAR2(400)
 CELLHASHVAL_CELLROW                                                                                                        NUMBER
SYS@o112 AS SYSDBA> ed
Wrote file afiedt.buf

  1  select t.kqftanam, c.kqfconam
  2  from x$kqfta t, x$kqfco c
  3  where t.inst_id = 1
  4  and c.inst_id = 1
  5  and t.indx = c.kqfcotab
  6* and t.kqftanam = 'X$CELL_NAME'
SYS@o112 AS SYSDBA> /

KQFTANAM                       KQFCONAM
------------------------------ ------------------------------
X$CELL_NAME                    ADDR
X$CELL_NAME                    INDX
X$CELL_NAME                    INST_ID
X$CELL_NAME                    CELLPATH_CELLROW
X$CELL_NAME                    CELLHASHVAL_CELLROW

And with X$CELL_NAME the X$KQFTA.INDX=X$KQFCO.KQFCOTAB link is restored.

So, this means that starting with X$KSXPTESTTBL the X$KQFCO.KQFCOTAB number is off by one. In fact, I tested version 18 (18.4), and found that the value X$KQFTA.INDX is 1217, so much higher, but still with X$KSXPTESTTBL the inconsistency of X$KQFCO.KQFCOTAB being one too high to correctly join with X$KQFTA.INDX.

Conclusion.
This means that based on my current investigations, it seems that starting from Oracle database version 11.2.0.1 up to version 19 and current PSU, starting with the X$KQFTA.INDX value of the table X$KSXPTESTTBL in X$KQFTA, the X$KQFCO.KQFCOTAB value needs to be subtracted by one to get the correct fields from X$KQFCO.KQFCOTAB to join to X$KQFTA.INDX.

A lot of X$ tables are not impacted, the majority of the tables have an X$KQFTA.INDX value lower than X$KSXPTESTTBL, but there are a reasonable amount of tables that are impacted, a lot of them related to ADR (starting with X$DIAG).

The reason for showing an additional oddity with X$SKGXP_MISC, X$SKGXP_CONNECTION, X$SKGXP_PORT and X$KSXP_STATS is that the X$ table where the join problem starts is X$KSXPTESTTBL. All these tables have something to do with cross instance IPC communication (skgxp/ksxp), and a lot of them have the weird property that they don’t exist whilst are visible in X$KQFTA, this might have something to do with X$KQFCO.KQFCOTAB being off by one. Of course this is something I can’t check, because it requires source code access, but I thought it would be good to mention.

It’s interesting to see that the join of X$KQFTA.INDX to X$KQFCO.KQFCOTAB is present in Oracle’s data dictionary with the view V$INDEXED_FIXED_COLUMN:

SQL> select view_definition from v$fixed_view_definition where view_name = 'GV$INDEXED_FIXED_COLUMN';

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

Which by now should be clear is not correct for X$ tables with an X$KQFTA.INDX number equal or higher than X$KSXPTESTTBL.

To prevent automated spam submissions leave this field empty.