oracle数据字典查询慢,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