DBMS_STATS. GATHER_DICTIONARY_STATS fails with ORA-20001 ORA-6502 ORA-6512 – Concurrent Stats

I really have to say “Thank you very much” to the people out there alerting me about issues I haven’t seen before. Just in the past week I’ve got to learn about three issues which are related to the upgrade – and I haven’t seen before. Please don’t expect me always to follow up with the progress of your SRs or the related bugs. But your experiences are such an important source for me – so thanks again!

The most recent issue (thanks to Bernd Tuba from MM Warburg) …

Symptom

execute dbms_stats.gather_dictionary_stats

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.DBMS_STATS", line 24268
ORA-20001: Unexpected configuration:
sofar: 41
objList.count: 41
sofar_part: 0
partObjList.count: 0
sofar_ind: 0
indObjList.count: 21
concurrent: TRUE
runPartTable: TRUE
jstats.Running: -19
ORA-06512: at "SYS.DBMS_STATS", line 27383
ORA-06512: at "SYS.DBMS_STATS", line 27402
ORA-06512: at line 1

I see the word “CONCURRENT” – and it immediately rings a bell. Wasn’t there an issue with the change of setting for default stats gathering in Oracle Database 12.1.0.2 being now CONCURRENT=TRUE?

Yes … the reason for this blog’s existence is not only to give you some interesting useful information to read about but also to dump of details from my limited brain capacity 🙂 I have written something about a similar issue in October 2014:

Analysis

This is interesting. Whereas in my other blog post the concurrent stats gathering conflicted with the resource manager settings and is fixed by a patch (Patch 19664340: ORA-20000: UNABLE TO GATHER STATISTICS CONCURRENTLY: RESOURCE MANAGER IS NOT ENABLED) this one is a different topic.

Bug:18406728  DBMS_STATS.GATHER_DICTIONARY_STATS GOES INTO SPIN CONSUMING 100% CPU
(unpublished bug – therefore no link included here)

Solution

Very simple to solve – switch CONCURRENT stats gathering to FALSE.

SQL> exec dbms_stats.set_global_prefs('CONCURRENT', 'FALSE');

In the Exadata Upgrade Note (MOS Note: 1681467.1) you’ll find the same recommendation.

More Information? 

• MOS Note: 2037154.1
  DBMS_STATS.GATHER_DICTIONARY_STATS Fails with “ORA-06502: PL/SQL: numeric or value error: character string buffer too small
• MOS Note: 1681467.1
  11.2.0.2, 11.2.0.3, 11.2.0.4 or12.1.0.1 to 12.1.0.2 Grid Infrastructure and Database Upgrade on Exadata Database Machine running Oracle Linux

–Mike