DBMS_STATS GATHER_FIXED_OBJECTS_STATS hangs

A colleague was preparing an Oracle database upgrade from 12.1 to 19c. One of steps was to do a pre-upgrade check and it had been hanging somehow for more than 24 hours. We looked at the system together and it’s quite a small database with the size about 300G.

By tracing the OS process, then to the Oracle session, we saw the the pre-upgrade check was doing DBMS_STATS.GATHER_FIXED_OBJECTS_STATS and it was hanging with the following statement:

cvj6gmu8wggbz

/* SQL Analyze(0) */ select /*+  full(t)    no_parallel(t) no_parallel_index(t)
dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring 
xmlindex_sel_idx_tbl no_substrb_pad  */to_char(count("ADDR")),substrb(dump(
min("ADDR"),16,0,64),1,240),substrb(dump(max("ADDR"),16,0,64),1,240),to_char(count("INDX")),
substrb(dump(min("INDX"),16,0,64),1,240),substrb(dump(max("INDX"),16,0,64),1,240),
to_char(count("INST_ID")),substrb(dump(min("INST_ID"),16,0,64),1,240),
substrb(dump(max("INST_ID"),16,0,64),1,240),to_char(count("CON_ID")),
substrb(dump(min("CON_ID"),16,0,64),1,240),substrb(dump(max("CON_ID"),16,0,64),1,240),
to_char(count("KQLFSQCE_HADD")),substrb(dump(min("KQLFSQCE_HADD"),16,0,64),1,240),
substrb(dump(max("KQLFSQCE_HADD"),16,0,64),1,240),to_char(count("KQLFSQCE_PHAD"
)),substrb(dump(min("KQLFSQCE_PHAD"),16,0,64),1,240),substrb(dump(max("KQLFSQCE_PHAD"),16,0,64),1,240),
to_char(count("KQLFSQCE_HASH")),substrb(dump(min("KQLFSQCE_HASH"),16,0,64),1,240),
substrb(dump(max("KQLFSQCE_HASH"),16,0,64),1,240),to_char(count("KQLFSQCE_SQLID")),
substrb(dump(min("KQLFSQCE_SQLID"),16,0,64),1,240),substrb(dump(max("KQLFSQCE_SQLID"),16,0,64),1,240),
to_char(count("KQLFSQCE_CHNO")),substrb(dump(min("KQLFSQCE_CHNO"),16,0,64),1,240),
substrb(dump(max("KQLFSQCE_CHNO"),16,0,64),1,240),to_char(count("KQLFSQCE_PNAME")),
substrb(dump(min("KQLFSQCE_PNAME"),16,0,64),1,240),substrb(dump(max("KQLFSQCE_PNAME"),16,0,64),1,240),
to_char(count("KQLFSQCE_PVALUE")),substrb(dump(min("KQLFSQCE_PVALUE"),16,0,64),1,240),
substrb(dump(max("KQLFSQCE_PVALUE"),16,0,64),1,240),to_char(count("KQLFSQCE_FLAGS")),
substrb(dump(min("KQLFSQCE_FLAGS"),16,0,64),1,240),substrb(dump(max("KQLFSQCE_FLAGS"),16,0,64),1,240),
to_char(count("KQLFSQCE_KSPNUM")),substrb(dump(min("KQLFSQCE_KSPNUM"),16,0,64),1,240),
substrb(dump(max("KQLFSQCE_KSPNUM"),16,0,64),1,240),to_char(count("KQLFSQCE_PNUM")),
substrb(dump(min("KQLFSQCE_PNUM"),16,0,64),1,240),substrb(dump(max("KQLFSQCE_PNUM"),16,0,64),1,240) 
from "SYS"."X$KQLFSQCE" t  /* NDV,NIL,NIL,ACL,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,
NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL*/

I’ve seen gather fixed objects stats hanging on 11g (Doc ID 2044146.1) which made me wonder another bug in 12c? After a search in MOS, and yes it is a bug (Doc ID 2294247.1) again on different X$ object — X$KQLFSQCE in 12.1. The statement above was exactly hanging on X$KQLFSQCE.

The following statement can also be used to verify the bug — it also hangs.

select count(*) from X$KQLFSQCE;

The bug is fixed with the patch 22468781 and 12.2.0.1. If there is no performance impact noticed, you can just let it run and it will finish eventually. In our case, it took more than 48 hours to finish. The good thing is once you have it run through, the next run actually will finish very quickly (within a few minutes for us). It seems Oracle has had the updated statistics after the first run and is able to chose optimal execution plans.

When doing an upgrade, as a best practice, Oracle recommends to gather statistics for internal structures.

For Data Dictionary, Oracle recommands to gather dictionary statistics both before and after upgrading the database, because Data Dictionary tables are modified and created during the upgrade.

For Fixed objects, Oracle strongly recommends that you regather fixed object statistics after you have run representative workloads on Oracle Database after an upgrade, or after other database configuration changes — e.g., increasing SGA. That means database needs to be up and running for a while with the significant workload on the database. In other words, the database needs to be warm-up. That’s because Fixed objects are the X$ tables and their indexes. The X$ tables are not really tables at all, but in memory structures in the Oracle kernel. V$ performance views such as v$sql, v$sql_plan, v$session are defined through X$ tables. Gather statistics on Fixed objects help the optimizer generate good execution plans, which can improve database performance.

Update:

As the changelog (bugs_fixed.txt) at the bottom of the article: AutoUpgrade Tool (Doc ID 2485457.1) mentioned, gathering fixed objects stats right after the upgrade is removed and left as DBA’s responsibility to do it after the database is warmed up.

build.version 21.1.1
build.date 2020/12/14 14:41:34
AUPG-1863 Remove the Fixup for post fixed objects statistics

build.version 19.10.0
build.date 2020/10/23 10:36:46
AUPG-1863 Remove fixup for running fixed objects stats after upgrade

Reference: