11g下使用參數skewonly收集長條圖的缺陷已修複,11gskewonly
之前在10g下驗證過使用參數skewonly收集長條圖的缺陷,11g下已經修複。長條圖在大資料量上顯的越來越重要了,如果不準,執行計畫很可能不準。
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> drop table test purge;
SQL> create table test
(
id varchar2(60) primary key,
name varchar2(100)
);
SQL> insert into test
select rpad('a', 40, 'a') || to_char(rownum), object_name
from dba_objects;
已建立69617行。
SQL> commit;
SQL> select count(1),count(distinct id),count(distinct name) from test;
COUNT(1) COUNT(DISTINCTID) COUNT(DISTINCTNAME)
---------- ----------------- -------------------
69617 69617 41622
SQL> exec dbms_stats.gather_table_stats(user,'test',METHOD_OPT=>'for all columns size skewonly');
SQL> select COLUMN_NAME, NUM_DISTINCT from user_tab_columns where TABLE_NAME = 'TEST';
COLUMN_NAME NUM_DISTINCT
------------------------------ ------------
ID 69617
NAME 40904
SQL> exec dbms_stats.gather_table_stats(user,'TEST',METHOD_OPT=>'for all columns size auto');
SQL> select COLUMN_NAME, NUM_DISTINCT from user_tab_columns where TABLE_NAME = 'TEST';
COLUMN_NAME NUM_DISTINCT
------------------------------ ------------
ID 69617
NAME 40904
可以看到兩次收集的都是準的。