Oracle 11g:元数据查询非常慢

Question

我有一个视图，它应该显示模式中某些表的列的注释和约束（包括适用的检查条件）。

从本质上讲，我（左）加入了ALL_COL_COMMENTS到ALL_CONS_COLUMNS再到ALL_CONSTRAINTS

然而，由于某种原因，这真的很慢（大约需要10秒），尽管我有非常少的表（只有7个），非常少的列（总共58列）。因此查询返回的结果很少。而且它仍然很慢。我能做什么？

  CREATE OR REPLACE FORCE VIEW "MYDB"."COMMENTS_VIEW" ("TABLE_NAME", "COLUMN_NAME", "COMMENTS", "CONSTRAINT_TYPE", "CHECK_CONDITION") AS 
  SELECT r.TABLE_NAME, r.COLUMN_NAME, r.COMMENTS, DECODE(q.CONSTRAINT_TYPE,'P', 'Primary Key', 'C', 'Check Constraint', 'R', 'Referential Integrity Constraint' ), q.SEARCH_CONDITION AS CHECK_CONDITION
      FROM  ALL_COL_COMMENTS r -- ALL_COL_COMMENTS has the COMMENTS
          LEFT JOIN ALL_CONS_COLUMNS p ON (p.TABLE_NAME = r.TABLE_NAME AND p.OWNER = 'MYDB' AND p.COLUMN_NAME = r.COLUMN_NAME) -- ALL_CONS_COLUMNS links COLUMNS to CONSTRAINTS
          LEFT JOIN ALL_CONSTRAINTS q ON (q.OWNER = 'MYDB' AND q.CONSTRAINT_NAME = p.CONSTRAINT_NAME AND q.TABLE_NAME = p.TABLE_NAME AND (q.CONSTRAINT_TYPE = 'C' OR q.CONSTRAINT_TYPE = 'P' OR q.CONSTRAINT_TYPE = 'R' ) ) -- this gives us INFO on CONSTRAINTS
    WHERE r.OWNER = 'MYDB' 
        AND 
    r.TABLE_NAME IN ('TABLE1', 'TABLE2', 'TABLE3', 'TABLE4', 'TABLE5', 'TABLE6', 'TABLE7')
        AND
    r.COLUMN_NAME NOT IN ('CREATED', 'MODIFIED', 'CREATED_BY', 'MODIFIED_BY') 
      ORDER BY r.TABLE_NAME, r.COLUMN_NAME, r.COMMENTS;

Answer 1

确保字典和固定对象统计信息是最新的。对于几乎任何SQL性能问题，检查最新的统计信息都是很好的第一步。字典和固定对象是不寻常的，很可能以前没有人考虑过收集它们的统计数据。

begin
    dbms_stats.gather_fixed_objects_stats;
    dbms_stats.gather_dictionary_stats;
end;
/

Answer 2

尽可能在表和列ID而不是名称上进行联接。即使是业主，如果可以的话。示例：

    ON p.TABLE_ID = r.TABLE_ID

此外，您正在从已经是谁知道有多少基础表的视图的对象中进行选择。查询优化器可能遇到了困难（在某些方面可能会放弃）。尝试将查询转换为使用基表。

Answer 3

我要么使用查询探查器，要么（更简单）只删除部分查询，直到它变得超级快。例如，删除DECODE（）调用，也许就是这样做的。