Slow

Speed up Upgrade Phase 65 with a new catuposb.sql

Credits to Chris Smids from Proximus in Belgium 🙂 Thanks, Chris!!!


Upgrade to Oracle 12.1.0.2 is slow in phase: #65 ?

You are wondering why phase: #65 of the database upgrade to Oracle Database 12.1.0.2 takes quite a while. You dig down into the catupgrd0.log and recognized this statement taking a while:

dbms_output.put_line('catuposb, update 4 - rows updated ' || rows_updated);
END; -- end of update for system internally generated objs
/

The cause for this issue is buried in the script catuposb.sql hitting stale histograms which did not get refreshed even if you gathered dictionary stats before the upgrade …

Continue reading...

Query on ALL_SYNONYMS is slow in Oracle Database 12c

A customer (Thanks Stefano!) alerted me on this issue during a workshop and I did some further investigation. Basic headline is:

Query on ALL_SYNONYMS is very slow in Oracle Database 12.1.0.2 compared to 11g.

The workaround for this  21324443: SLOW QUERY IN 12C ON ALL_SYNONYMS. is:

dbms_stats.gather_table_stats('SYS','OBJ$',estimate_percent
=>100, method_opt => 'for columns flags size 1, spare3 size 254, type# size 254');

and I see that at least 8 other customers opened SRs hitting the same issue.
Does the workaround suit you in any way?

The bug had no progress since it was opened. If you are seeking progress …

Continue reading...