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 I can only ask you to let support know
through the SR that you’d like to have the bug escalated.
Thanks for letting me know!
And please see also Bug 22113854 which is fixed now as the above one got closed partially as a duplicate of Bug 22113854. Fix is available on top of several Proactive BPs. Please check with Oracle Support.
This issue is supposed to be fixed with Oracle Database 12.2.0.1.
Kind regards
–Mike
Hi Mike,
I solved similar issues following Doc ID 22113854.8:
Bug 22113854 – In 12c slow query on ALL_SYNONYMS
comparing to yours:
Bug 21324443 : SLOW QUERY IN 12C ON ALL_SYNONYMS
the difference is essentially in the lower case and in mixing the words 😉
Joking apart, for the first one there is a patch that simply adds a hint /*+ NO_PUSH_SUBQ */ to “_ALL_SYNONYMS_TREE” .
I will try the workaround you suggest as well.
THX
Thanks for sharing, Ludovico 🙂
Cheers
Mike
Hi guys,
I’m facing same issue on 12cR2 – “Database Jan 2019 Release Update : 12.2.0.1.190115 (28822515)”
I opened an SR but I don’t see any progress yet.
Do you know is there is a patch in 12.2 ?
Regards.
Hi Martin,
bug 22113854 is supposed to be fixed in 12.2. If you encounter a similar issue again in 12.2, then please force the support guy to open a new bug and collect all the necessary information together with you. If there’s no progress in the SR, increase the severity to 1 (but not 24.x7 as the SR will then sail around the world) and request an escalation if that doesn’t bring progress. Call the HUB (your Oracle Support telephone number) and initiate a callback from a manager. Define until when you expect the callback.
Hope this helps – cheers, Mike
Thanks Mike,
I will try follow it via MOS.
Regards.
MartinZ