oracle 9i 查詢資料字典檢視慢案例分析

           環境oracle 9.2.0.8+window2003

sql查詢語句如下:

Select t.Column_Name, t.Data_Type, t.Data_Length, t.Nullable,t.Data_Precision,t.Data_Scale,d.comments
 From User_Tab_Columns t,User_Col_Comments d Where t.TABLE_NAME=d.table_name AND t.COLUMN_NAME=d.column_name AND t.Table_Name ='T_ABC';

查詢要二三十秒,才得到結果,並且有全表掃描

執行計劃如下：

SELECT STATEMENT, GOAL = CHOOSE   2279 1 225
 TABLE ACCESS BY INDEX ROWID SYS OBJ$ 2 1 23
  INDEX UNIQUE SCAN SYS I_OBJ1 1 1
 TABLE ACCESS BY INDEX ROWID SYS OBJ$ 2 1 23
  INDEX UNIQUE SCAN SYS I_OBJ1 1 1
 TABLE ACCESS BY INDEX ROWID SYS OBJ$ 2 1 23
  INDEX UNIQUE SCAN SYS I_OBJ1 1 1
 TABLE ACCESS BY INDEX ROWID SYS OBJ$ 2 1 23
  INDEX UNIQUE SCAN SYS I_OBJ1 1 1
 TABLE ACCESS BY INDEX ROWID SYS OBJ$ 2 1 23
  INDEX UNIQUE SCAN SYS I_OBJ1 1 1
 FILTER    
  NESTED LOOPS OUTER   2279 1 225
   NESTED LOOPS OUTER   2278 1 222
    NESTED LOOPS OUTER   2276 1 196
     NESTED LOOPS OUTER   2275 1 146
      NESTED LOOPS OUTER   2273 1 139
       NESTED LOOPS   2272 1 103
        HASH JOIN   2271 1 75
         NESTED LOOPS   5 1 45
          TABLE ACCESS BY INDEX ROWID SYS OBJ$ 4 1 28
           INDEX RANGE SCAN SYS I_OBJ2 3 1
          TABLE ACCESS CLUSTER SYS COL$ 1 1 17
           INDEX UNIQUE SCAN SYS I_OBJ#  1
         TABLE ACCESS FULL SYS COL$ 2265 27280 818400
        TABLE ACCESS BY INDEX ROWID SYS OBJ$ 1 1 28
         INDEX UNIQUE SCAN SYS I_OBJ1  1
       TABLE ACCESS CLUSTER SYS COLTYPE$ 1 1 36
      INDEX RANGE SCAN SYS I_HH_OBJ#_INTCOL# 2 1 7
     TABLE ACCESS BY INDEX ROWID SYS COM$ 1 1 50
      INDEX UNIQUE SCAN SYS I_COM1  1
    TABLE ACCESS BY INDEX ROWID SYS OBJ$ 2 1 26
     INDEX RANGE SCAN SYS I_OBJ3 1 188
   TABLE ACCESS CLUSTER SYS USER$ 1 1 3
    INDEX UNIQUE SCAN SYS I_USER#  1
  TABLE ACCESS CLUSTER SYS TAB$ 2 1 10
   INDEX UNIQUE SCAN SYS I_OBJ# 1 1 

加上基於規則的hint提示時，查詢正常

 Select  /*+rule */  t.Column_Name,
           2         t.Data_Type,
           3         t.Data_Length,
           4         t.Nullable,
           5         t.Data_Precision,
           6         t.Data_Scale,
           7         d.comments
           8    From User_Tab_Columns t, User_Col_Comments d
           9   Where t.TABLE_NAME = d.table_name
          10     AND t.COLUMN_NAME = d.column_name
          11     AND t.Table_Name = 'T_ABC'

Executed in 0.157 seconds

執行計劃為索引掃描

SELECT STATEMENT, GOAL = CHOOSE    
 TABLE ACCESS BY INDEX ROWID SYS OBJ$  
  INDEX UNIQUE SCAN SYS I_OBJ1  
 TABLE ACCESS BY INDEX ROWID SYS OBJ$  
  INDEX UNIQUE SCAN SYS I_OBJ1  
 TABLE ACCESS BY INDEX ROWID SYS OBJ$  
  INDEX UNIQUE SCAN SYS I_OBJ1  
 TABLE ACCESS BY INDEX ROWID SYS OBJ$  
  INDEX UNIQUE SCAN SYS I_OBJ1  
 TABLE ACCESS BY INDEX ROWID SYS OBJ$  
  INDEX UNIQUE SCAN SYS I_OBJ1  
 FILTER    
  NESTED LOOPS OUTER    
   NESTED LOOPS    
    NESTED LOOPS    
     NESTED LOOPS OUTER    
      NESTED LOOPS OUTER    
       NESTED LOOPS OUTER    
        NESTED LOOPS OUTER    
         NESTED LOOPS    
          TABLE ACCESS BY INDEX ROWID SYS OBJ$  
           INDEX RANGE SCAN SYS I_OBJ2  
          TABLE ACCESS CLUSTER SYS COL$  
           INDEX UNIQUE SCAN SYS I_OBJ#  
         TABLE ACCESS CLUSTER SYS COLTYPE$  
        INDEX RANGE SCAN SYS I_HH_OBJ#_INTCOL#  
       TABLE ACCESS BY INDEX ROWID SYS OBJ$  
        INDEX RANGE SCAN SYS I_OBJ3  
      TABLE ACCESS CLUSTER SYS USER$  
       INDEX UNIQUE SCAN SYS I_USER#  
     TABLE ACCESS BY INDEX ROWID SYS OBJ$  
      INDEX RANGE SCAN SYS I_OBJ2  
    TABLE ACCESS BY INDEX ROWID SYS COL$  
     INDEX UNIQUE SCAN SYS I_COL1  
   TABLE ACCESS BY INDEX ROWID SYS COM$  
    INDEX UNIQUE SCAN SYS I_COM1  
  TABLE ACCESS CLUSTER SYS TAB$  
   INDEX UNIQUE SCAN SYS I_OBJ# 

透過檢查dba_tables表，發現對sys使用者下的系統表做過分析，

 然後刪除sys使用者下的統計資料

   BEGIN
    DBMS_STATS.delete_SCHEMA_stats(ownname => 'SYS');
    END;
    /
 查詢資料字典檢視恢復正常

18:27:58 SQL> Select t.Column_Name,
           2         t.Data_Type,
           3         t.Data_Length,
           4         t.Nullable,
           5         t.Data_Precision,
           6         t.Data_Scale,
           7         d.comments
           8    From User_Tab_Columns t, User_Col_Comments d
           9   Where t.TABLE_NAME = d.table_name
          10     AND t.COLUMN_NAME = d.column_name
          11     AND t.Table_Name = 'T_XZSP_TASK_DAY_REMIND'
          12  /
 

Executed in 0.158 seconds

建議：對oracle 系統的資料字典表千萬別做分析,否則會有效能影響

            因為oracle 9i系統資料字典表執行計劃預設最佳化器為RBO