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
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:
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
There is another bad case when you upgrade 126.96.36.199 to 19c:
SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
ORA-03114: not connected to ORACLE
In Note Doc ID 2224880.1 is written:
Apply Patch 20195148 if available for your platform and version
There is no workaround.
But there is a workaround i use (restore the data Dictionary):
BEGIN DBMS_STATS.GATHER_DICTIONARY_STATS; END;
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 4676
Session ID: 354 Serial number: 30565
Your $FIXED_TABLE loop is potentially dangerous:
It bombs out with
ORA-20011: Approximate NDV failed: ORA-04030: out of process memory when trying
and uses up all available memory on the server!
Hey, thanks for the hint.
This was a valid solution by the time I wrote the blog post.
Things may have changed since then as you can see already by my update of Dec 2020 added a while later.
Hence, thanks for the hint – and as I wrote, please open an SR and work with Support on it if you see similar problems.
This is somebody I never found a real solution or root cause analysis internally. So I can only guess.