[20120507]视图all_tab_columns的定义问题.txt
生产系统我发现一个问题,开发人员在使用pb9.0维护时要调用如下类似的语句:
SELECT synonym_name
FROM SYS.all_synonyms s, SYS.all_tab_columns t
WHERE s.owner IN ('SCOTT', 'PUBLIC')
AND s.synonym_name = 'DEPT'
AND s.table_owner = t.owner
AND s.table_name = t.table_name
AND t.column_name = 'DEPTNO';
我使用oracle版本如下:
SQL> select * from v$version ;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
而这个语句的逻辑读异常的高,consistent gets=34XXXX,执行时间大约4XXms。
而当我看执行计划的时候发现,执行计划如下:
....太长...
Note
-----
- rule based optimizer used (consider using cbo)
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
居然执行计划使用的基于rule的优化模式,奇怪!
当我查询ALL_TAB_COLUMNS的定义时发现:
column text format a80select owner,view_name,text from dba_views where view_name='ALL_TAB_COLUMNS' and wner='SYS';OWNER VIEW_NAME TEXT------------------------------ ------------------------------ -------------------------------------