[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的定義時發現：
--居然發現all_tab_cols的定義使用hint=rule,這個就是執行計劃選擇rule的原因！
--如果我修改如下：
SELECT /*+ first_rows */ synonym_name
統計資訊
---------------------------------------------------------
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
![Windows下配置Apache的SSL服務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)