[20120507]视图all_tab_columns的定义问题.txt

[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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-722867/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-722867/