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