Fixed Objects Stats Gathering Fails – what do you do now?

A few days ago I blogged about what you could do when Fixed Objects Stats Gathering is slow in an upgrade process. You can skip the preupgrade gathering, and the postupgrade gathering does not happen anymore since the October 2020 version of AutoUpgrade. Still, I came across another issue the other day: Fixed Objects Stats Gathering Fails – what do you do now?

Photo by Spencer Imbrock on Unsplash

What happened?

Actually this is connected to the Fixed Objects Stats Gathering is slow in an upgrade case. The customer encountered an incredible long runtime. And the support engineer after a while pointed to the non-public BUG 30686131 – IMPLEMENT SOFT ASSERT (ORA-700) FOR DBMS_STATS ON X$ FIXED OBJECT TABLES.

I didn’t know this bug yet – but when I read it, I was a bit scared as there seems to be a potential chance for missing entries in FIXED_OBJ$ after a database upgrade. And when an existing X$ table is missing in FIXED_OBJ$, then the call:

exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

will abort with either and ORA-600 or an ORA-700

BUG 30362844 – PSRPERF: ORA-600 KQLDTSTU3 GATHERING X$ STATS ON 12C PDB PLUGGED INTO 19.3 covers the initial problem of X$ tables not being in FIXED_OBJ$. And the consequence is that some X$ tables may not be analyzed as they have no entries in this table. But even worse, the procedure will error out with ORA-600 [KQLDTSTU3] in case this happens. So the fix for BUG 30686131 prevents the procedure of signaling this ORA-600.

But you may ask yourself now – what has happened to the customer? And how is this connected?

The potential solution?

My customer got asked by Support to gather stats now manually with a procedure on all X$ tables which have an entry in FIXED_OBJ$. And this is what we tried.

But the procedure failed quickly with:

BEGIN
*
ERROR at line 1:
ORA-20000: Unable to analyze TABLE "SYS"."X$KSMMEM", analyzing the table is not
supported
ORA-06512: at "SYS.DBMS_STATS", line 40753
ORA-06512: at "SYS.DBMS_STATS", line 40037
ORA-06512: at "SYS.DBMS_STATS", line 39450
ORA-06512: at "SYS.DBMS_STATS", line 40185
ORA-06512: at "SYS.DBMS_STATS", line 40734
ORA-06512: at line 4
ORA-06512: at line 4

Ups. So what should we do now?

The Manual Fixed Objects Stats Gathering Solution

I did a search on the bug database and found a very old bug which explained that some X$ tables are protected from stats gathering. This may have to do with issues found in the past. And now I had to run my procedure several times to find those you can’t be analyzed. The flag is in the code, hence I couldn’t query it.

And this is the procedure which works fine in an 19.8 and 19.9 database to manually gather fixed objects stats in case the standard procedure will fail due to missing entries in FIXED_OBJ$:

BEGIN
for i in (select V$FIXED_TABLE.name,type from   fixed_obj$,V$FIXED_TABLE
          where fixed_obj$.obj#=V$FIXED_TABLE.object_id
            and V$FIXED_TABLE.name not in ('X$KSMMEM', 'X$LOGMNR_CONTENTS','X$KZEKMFVW','X$JOXFT','X$JOXFM','X$CDBVW$','X$COMVW$','X$OBLNK$')
         ) loop
  SYS.DBMS_STATS.GATHER_TABLE_STATS (
     ownname          => ''
    ,TabName           => i.name
    ,Estimate_Percent  => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE
    ,Method_Opt        => 'FOR ALL COLUMNS SIZE AUTO '
    ,Degree            => NULL
    ,Cascade           => DBMS_STATS.AUTO_CASCADE
    ,No_Invalidate     => DBMS_STATS.AUTO_INVALIDATE
    ,Force             => FALSE);
end loop;
END;
/

This way we could complete the stats gathering finally.

Further investigation lead me to the conclusion that 8 X$ tables have no entries in FIXED_OBJ$ in 19c.

When is this fixed?

Actually the error does not get thrown anymore when you are on Oracle 19.8.0 according to MOS Note: 2523220.1 (Database 19 Release Updates and Revisions Bugs Fixed Lists. But does this really solve my problem?

Actually, in this case it doesn’t.

But as you can read above, the procedure works fine, and we could analyze all X$ tables except those 14:

select count(*) FROM dba_tab_statistics where object_type = 'FIXED TABLE' and last_analyzed is null;   

  COUNT(*)
----------
	14

Or:

select table_name FROM dba_tab_statistics where object_type = 'FIXED TABLE' and last_analyzed is null

TABLE_NAME
--------------------------------------------------------------------------------
X$KSMMEM
X$LOGMNR_CONTENTS
X$KZEKMFVW
X$JOXFT
X$JOXFM
X$CDBVW$
X$COMVW$
X$OBLNK$
X$KSIPC_PROC_STATS
X$KSIPC_INFO
X$KSXP_STATS
X$SKGXP_PORT
X$SKGXP_CONNECTION
X$SKGXP_MISC

Annotation – Dec 16, 2020

Several customers reported similar issues to me. And I have to confess that I shared everything with you I know about the issue. Hence, you please need to open SRs and try to get bugs assigned to it. I have the strong feeling that something is not working correctly or behaving strangely. But I can’t nail this down.

From feedback from some of you I know that you excluded as well:

• X$KSIPC_PROC_STATS
  
• X$KTUQQRY
• X$KSIPC_INFO
• X$KJAC_ID

in above manual stats gathering query to make it run without hangs or fails.

I really can just repeat: Plus open SRs if you encounter such issues.

Further Links and Information

• Fixed Objects Stats Gathering is slow in an upgrade
• October 2020 version of AutoUpgrade
• BUG 30686131 – IMPLEMENT SOFT ASSERT (ORA-700) FOR DBMS_STATS ON X$ FIXED OBJECT TABLES
• MOS Note: 2523220.1 (Database 19 Release Updates and Revisions Bugs Fixed Lists.
• Jonathan Lewis on Gathering Fixed Objects Stats

–Mike