oracle - 在 Oracle 12c 中对 user_cons_columns 的查询要慢得多

Question

我们正在从 Oracle 11g 升级到 12c，并且注意到对 user_cons_columns 的查询似乎要慢一些。

例如，即使在较小的数据集上，速度也会慢 4 倍:

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

这只是收集统计数据的问题吗？

Answer 1

根据我的经验，从 user_constraints 和 user_cons_columns 和其他数据字典 View 中选择几个主要 Oracle 版本的速度很慢。不仅仅是12c。执行 dbms_stats.gather_dictionary_stats; 将下面的第一个查询速度提高了 10-20%。

但真正有帮助的是使用 /*+materialized*/ 重写查询以从 with 子句“tables”中选择提示而不是直接从 user_ 表中选择。

这个查询在我的设置中非常慢，大约 150 秒:(它返回表列表中的所有外键，包括外键两端的表名和列名)

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;

重写后，查询只使用 1-8 秒:

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;

我还尝试了 * 而不是在 with 表中仅列出所需的列，但这没有帮助。我猜这是因为如果要记住/缓存太多数据，Oracle 会忽略 /*+materialize*/ 提示。