Oracle 12c – Slow query to tables ALL_CONS_COLUMNS and ALL_CONSTRAINTS

database-tuningoracleoracle-12cperformanceperformance-tuningquery-performance

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.

Best Answer

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.

Related Solutions

Sql-server – Why is an aggregate query significantly faster with a GROUP BY clause than without one

It looks like it is probably following an index on CreatedDate in order from lowest to highest and doing lookups to evaluate the SomeIndexedValue = 1 predicate.

When it finds the first matching row it is done, but it may well be doing many more lookups than it expects before it finds such a row (it assumes the rows matching the predicate are randomly distributed according to date.)

See my answer here for a similar issue

The ideal index for this query would be one on SomeIndexedValue, CreatedDate. Assuming that you can't add that or at least make your existing index on SomeIndexedValue cover CreatedDate as an included column then you could try rewriting the query as follows

SELECT MIN(DATEADD(DAY, 0, CreatedDate)) AS CreatedDate
FROM MyTable
WHERE SomeIndexedValue = 1

to prevent it from using that particular plan.

Query gets slow when bind peeking is turned on

Bind peeking is done in stages. First the query is be flagged as "bind sensitive" based on histograms, and then it may become "bind aware". The IS_BIND_SENSITIVE and IS_BIND_AWARE columns in V$SQL will show the state of the query. If IS_BIND_AWARE is not on, the query is not adapting.

This post helps to explains the process.

Best Answer

Related Solutions

Sql-server – Why is an aggregate query significantly faster with a GROUP BY clause than without one

Query gets slow when bind peeking is turned on

Related Question