Queries on user_cons_columns much slower in Oracle 12c

user_6853810 0
Viewed 2347

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?

oracle12c oracle
3 Answers

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.

user_8499670

I'm a bit late to this party but as suggested by Burleson, use the /*+ RULE */ hint with your queries on the Oracle data dictionary. This effectively turns off the optimizer.

Many have said not to use hints and that the RULE hint has been deprecated but it makes a huge difference in my case. One of my DBA_IND_COLUMNS queries which took 18 MINUTES to run now takes less than a second (Oracle 12cR1). At a loss to say why this works...

user_1407700
Related