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) …
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 22.214.171.124 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:
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)
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.
- 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
126.96.36.199, 188.8.131.52, 184.108.40.206 or220.127.116.11 to 18.104.22.168 Grid Infrastructure and Database Upgrade on Exadata Database Machine running Oracle Linux