Queries on user_cons_columns much slower in Oracle 12c

Question

We're in the process of upgrading from Oracle 11g to 12c, and have noticed that queries on user_cons_columns seem to be quite a bit slower.

For example this is about 4 times as slow, even on a smaller dataset:

select uc.search_condition 
from user_constraints uc inner join user_cons_columns ucc on ucc.CONSTRAINT_NAME = uc.CONSTRAINT_NAME  
where ucc.table_name = :upper_table_name
and ucc.column_name = :upper_column

Could it just be a matter of gathering statistics?

But where are you checking those values? Have you upgraded already? you might encounter some performance degradation after an in-place upgrade. Ps: you only have that query that you noticed to be slow? All the others queries to other tables are the same? — Renato Afonso
This is on a clean test 12c system that we have done a clean install of our application on, so not an upgrade. No other queries seem to have this problem. — J Dor
I'm sure there was a strong recommendation by Oracle to NOT gather stats on the data dictionary tables, so I would check before doing this. Best place to start with your issue is explain plan and compare to the previous version. — TenG
@TenG - that may have been true a long time ago but since 11G (if not earlier) Oracle have recommended that we can stats on the data dictionary. That's why DBMS_STATS has a GATHER_DICTIONARY_STATS() procedure — APC

Kjetil S. Kjetil S. · Accepted Answer · 2019-03-05T15:07:32

In my experience selects from user_constraints and user_cons_columns and other data dictionary views have been slow for several major Oracle versions. Not just 12c. Doing dbms_stats.gather_dictionary_stats; speeded up the first query below between 10-20%.

But what was really helpful was to rewrite the query to select from with clause "tables" using the /*+materialized*/ hint instead of selecting directly from the user_ tables.

This query is very slow on my setup, about 150 seconds: (it returns all foreign keys on a list of tables, including table and column names in both ends of the foreign keys)

select
  cc.table_name, cc.position, cc.constraint_name, cc.column_name,
  cr.table_name r_table_name, ccr.constraint_name r_constraint_name, ccr.column_name r_column_name
from user_constraints   c
join user_cons_columns  cc on cc.constraint_name=c.constraint_name and
                              cc.owner=c.owner and 
                              cc.table_name=c.table_name
join user_constraints   cr on cr.owner=c.r_owner and
                              cr.constraint_name=c.r_constraint_name and
                              cr.constraint_type in ('P','U')
join user_cons_columns ccr on ccr.constraint_name=cr.constraint_name and
                              ccr.owner=cr.owner and
                              ccr.table_name=cr.table_name and
                              ccr.position=cc.position
where c.constraint_type='R'
and c.table_name in ('TABLE_A', 'TABLE_B', ........a list of about 157 table names.......)
order by cc.table_name, cc.position, constraint_name, column_name, cc.position;

After rewriting to this, the query use just 1-8 seconds:

with
uc  as (select /*+materialize*/ owner,table_name,constraint_name,constraint_type,r_owner,r_constraint_name  from user_constraints),
ucc as (select /*+materialize*/ owner,table_name,constraint_name,position,column_name from user_cons_columns)
select
  cc.table_name, cc.position, cc.constraint_name, cc.column_name,
  cr.table_name r_table_name, ccr.constraint_name r_constraint_name, ccr.column_name r_column_name
from uc     c
join ucc   cc on cc.constraint_name=c.constraint_name and cc.owner=c.owner and cc.table_name=c.table_name
join uc    cr on cr.owner=c.r_owner and cr.constraint_name=c.r_constraint_name and cr.constraint_type in ('P','U')
join ucc  ccr on ccr.constraint_name=cr.constraint_name and ccr.owner=cr.owner and ccr.table_name=cr.table_name and ccr.position=cc.position
where c.constraint_type='R'
and c.table_name in ('TABLE_A', 'TABLE_B', ........a list of about 157 table names.......)
order by cc.table_name, cc.position, constraint_name, column_name, cc.position;

I also tried * instead of listing just the needed columns in the with tables, but that didn't help. I'm guessing it's because Oracle ignore /*+materialize*/ hints if too much data is to be remembered/cached.

Queries on user_cons_columns much slower in Oracle 12c

3 Answers