[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 a80
select owner,view_name,text from dba_views where view_name='ALL_TAB_COLUMNS' and wner='SYS';
OWNER VIEW_NAME TEXT
------------------------------ ------------------------------ --------------------------------------------------------------------------------
SYS ALL_TAB_COLUMNS select /*+ rule */ OWNER, TABLE_NAME,
COLUMN_NAME, DATA_TYPE, DATA_TYPE_MOD, DATA_TYPE_OWNER,
DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID,
DEFAULT_LENGTH, DATA_DEFAULT, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE,
DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED, SAMPLE_SIZE,
CHARACTER_SET_NAME, CHAR_COL_DECL_LENGTH,
GLOBAL_STATS, USER_STATS, AVG_COL_LEN, CHAR_LENGTH, CHAR_USED,
V80_FMT_IMAGE, DATA_UPGRADED, HISTOGRAM
from ALL_TAB_COLS
where HIDDEN_COLUMN = 'NO'
--居然发现all_tab_cols的定义使用hint=rule,这个就是执行计划选择rule的原因！
--如果我修改如下：
SELECT /*+ first_rows */ 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';
统计信息
---------------------------------------------------------
0 recursive calls
0 db block gets
18 consistent gets
0 physical reads
0 redo size
247 bytes sent via SQL*Net to client
335 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
-- 仅仅18个逻辑读！我也检查了另外一套windows的系统（版本也是10.2.0.4），SYS.all_tab_columns的定义也是使用rule。
--其他的版本例如9.2.08都没有这样定义。
2.理论讲修改定义应该没有问题，抽取定义，修改如下：（如果害怕，最好还是别动）。
CREATE OR REPLACE FORCE VIEW SYS.all_tab_columns (owner,
table_name,
column_name,
data_type,
data_type_mod,
data_type_owner,
data_length,
data_precision,
data_scale,
nullable,
column_id,
default_length,
data_default,
num_distinct,
low_value,
high_value,
density,
num_nulls,
num_buckets,
last_analyzed,
sample_size,
character_set_name,
char_col_decl_length,
global_stats,
user_stats,
avg_col_len,
char_length,
char_used,
v80_fmt_image,
data_upgraded,
histogram
)
AS
SELECT
owner, table_name, column_name, data_type, data_type_mod, data_type_owner, data_length, data_precision, data_scale,
nullable, column_id, default_length, data_default, num_distinct, low_value, high_value, density, num_nulls,
num_buckets, last_analyzed, sample_size, character_set_name, char_col_decl_length, global_stats, user_stats,
avg_col_len, char_length, char_used, v80_fmt_image, data_upgraded, histogram
FROM all_tab_cols
WHERE hidden_column = 'NO';
COMMENT ON TABLE SYS.ALL_TAB_COLUMNS IS 'Columns of user''s tables, views and clusters';
COMMENT ON COLUMN SYS.ALL_TAB_COLUMNS.TABLE_NAME IS 'Table, view or cluster name';
COMMENT ON COLUMN SYS.ALL_TAB_COLUMNS.COLUMN_NAME IS 'Column name';
COMMENT ON COLUMN SYS.ALL_TAB_COLUMNS.DATA_TYPE IS 'Datatype of the column';
COMMENT ON COLUMN SYS.ALL_TAB_COLUMNS.DATA_TYPE_MOD IS 'Datatype modifier of the column';
COMMENT ON COLUMN SYS.ALL_TAB_COLUMNS.DATA_TYPE_OWNER IS 'Owner of the datatype of the column';
COMMENT ON COLUMN SYS.ALL_TAB_COLUMNS.DATA_LENGTH IS 'Length of the column in bytes';
COMMENT ON COLUMN SYS.ALL_TAB_COLUMNS.DATA_PRECISION IS 'Length: decimal digits (NUMBER) or binary digits (FLOAT)';
COMMENT ON COLUMN SYS.ALL_TAB_COLUMNS.DATA_SCALE IS 'Digits to right of decimal point in a number';
COMMENT ON COLUMN SYS.ALL_TAB_COLUMNS.NULLABLE IS 'Does column allow NULL values?';
COMMENT ON COLUMN SYS.ALL_TAB_COLUMNS.COLUMN_ID IS 'Sequence number of the column as created';
COMMENT ON COLUMN SYS.ALL_TAB_COLUMNS.DEFAULT_LENGTH IS 'Length of default value for the column';
COMMENT ON COLUMN SYS.ALL_TAB_COLUMNS.DATA_DEFAULT IS 'Default value for the column';
COMMENT ON COLUMN SYS.ALL_TAB_COLUMNS.NUM_DISTINCT IS 'The number of distinct values in the column';
COMMENT ON COLUMN SYS.ALL_TAB_COLUMNS.LOW_VALUE IS 'The low value in the column';
COMMENT ON COLUMN SYS.ALL_TAB_COLUMNS.HIGH_VALUE IS 'The high value in the column';
COMMENT ON COLUMN SYS.ALL_TAB_COLUMNS.DENSITY IS 'The density of the column';
COMMENT ON COLUMN SYS.ALL_TAB_COLUMNS.NUM_NULLS IS 'The number of nulls in the column';
COMMENT ON COLUMN SYS.ALL_TAB_COLUMNS.NUM_BUCKETS IS 'The number of buckets in histogram for the column';
COMMENT ON COLUMN SYS.ALL_TAB_COLUMNS.LAST_ANALYZED IS 'The date of the most recent time this column was analyzed';
COMMENT ON COLUMN SYS.ALL_TAB_COLUMNS.SAMPLE_SIZE IS 'The sample size used in analyzing this column';
COMMENT ON COLUMN SYS.ALL_TAB_COLUMNS.CHARACTER_SET_NAME IS 'Character set name';
COMMENT ON COLUMN SYS.ALL_TAB_COLUMNS.CHAR_COL_DECL_LENGTH IS 'Declaration length of character type column';
COMMENT ON COLUMN SYS.ALL_TAB_COLUMNS.GLOBAL_STATS IS 'Are the statistics calculated without merging underlying partitions?';
COMMENT ON COLUMN SYS.ALL_TAB_COLUMNS.USER_STATS IS 'Were the statistics entered directly by the user?';
COMMENT ON COLUMN SYS.ALL_TAB_COLUMNS.AVG_COL_LEN IS 'The average length of the column in bytes';
COMMENT ON COLUMN SYS.ALL_TAB_COLUMNS.CHAR_LENGTH IS 'The maximum length of the column in characters';
COMMENT ON COLUMN SYS.ALL_TAB_COLUMNS.CHAR_USED IS 'C if maximum length is specified in characters, B if in bytes';
COMMENT ON COLUMN SYS.ALL_TAB_COLUMNS.V80_FMT_IMAGE IS 'Is column data in 8.0 image format?';
COMMENT ON COLUMN SYS.ALL_TAB_COLUMNS.DATA_UPGRADED IS 'Has column data been upgraded to the latest type version format?';
--CREATE PUBLIC SYNONYM ALL_TAB_COLUMNS FOR SYS.ALL_TAB_COLUMNS;
GRANT SELECT ON SYS.ALL_TAB_COLUMNS TO PUBLIC WITH GRANT OPTION;
3.在测试，结果如下，正常！
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
18 consistent gets
0 physical reads
0 redo size
247 bytes sent via SQL*Net to client
335 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed