Oracle 12c - Slow query to tables ALL_CONS_COLUMNS and ALL_CONSTRAINTS

Question

We have successfully upgraded Oracle 11g to Oracle 12c. So far it's been going well. But We noticed one problem, queries made for Oracle's constraint tables (ALL_CONS_COLUMNS and ALL_CONSTRAINTS) are really slow compared to 11g.

When We used 11g, there was no problem at all. The query finished in less than one second but in 12c it is at least 4 times slower. It is affecting all applications We have and even though there's a workaround (application side), ideally, You'd expect the same performance between 11g and 12c.

We have also tried to measure the query cost using Toad. On 12c, the cost is much smaller than 11g. Could this be affecting the query time? Is there anything I can try to solve this problem?

PS : Any other queries made for another tables are okay. Performance wise, there's little to no difference between 11g and 12c.

Any feedback is welcomed. Thank you very much.

• eckes: Yes it got worse (and was not good before). I guess caching that is your best option if you can influence the application.
• eckes: And if it bothers production, of course also open a SR with Oracle Support.
• Lee: Yes, We have already used cache in application. It's weird how only Oracle default tables have such slow query performance, the other tables are as quick as They were in 11g. Thanks for your advice.
• eckes: I don’t know why this is the case I just know those are not simple tables, they are very complex views. It does help a lot if you not query for each column but to request the constraints of a whole Schema once.
• Mark D Powell: Did you try to re-collect the CBO statistics for the RDBMS Dictionary?

Answer 1

Sounds like statistics on those tables are out of date. I would invoke the following statements:

purge dba_recyclebin;
exec dbms_stats.gather_dictionary_stats
exec dbms_stats.gather_fixed_objects_stats;

If your application creates and or drops tables frequently, you may need to put the above statements in a scheduled job.

And as @MarkDPowell suggested, you should also consider gathering system stats via DBMS_STATS.GATHER_SYSTEM_STATS and DBMS_STATS.GATHER_PROCESSING_RATE (new in 12c) -- that is important in 11g and more important in 12c.