Oracle internal data dictionary oddity
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.
Frits Hoogland Weblog 