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