DBMS_STATS With METHOD_OPT =>'..SIZE auto' May Not Collect Histograms (文档 ID 557594.1)


在收集统计信息时指定method_opt=>.. size auto 时，有可能不收集直方图。


APPLIES TO:


Oracle Server - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.




SYMPTOMS


After stats are first gathered using DBMS_STATS with  METHOD_OPT =>'FOR all COLUMNS SIZE auto', query runs poorly.


Histograms are not collected properly on the columns.  After re-gathering the stats with the same procedure, the query runs fine.


症状：
第一次用size auto收集统计信息，查询缓慢
原因直方图没有被收集，用同样的过程重新收集后，查询速度变快


CAUSE


When 'SIZE AUTO' is specified, histograms are considered by information of col_usage$. If a column (mycol in this case) has its column statistics and it is used in predicates of SQL statements, then col_usage$ is updated to show that the column has been used as predicates. When DBMS_STATS.GATHER_TABLE_STATS is executed with 'SIZE AUTO' specified, it checks col_usage$ to see whether the column has been used as predicates or not . If it has, histogram is considered for that column.  


size auto收集直方图的条件：
1.列的数据倾斜。
2.列在查询语句中做谓语


测试：


SQL> create table hurp (mycol number);


Table created.




--插入1000条-1
begin 
for i in 1 .. 100000 loop 
insert into hurp values (-1）;
commit;
end loop;


for i in 1 .. 1000 loop
insert into hurp values (0);
commit;
end loop;


for i in 1 .. 100 loop
insert into hurp values(7);
commit;
end loop;
end;
/


PL/SQL procedure successfully completed.


---创建索引对列mycol
SQL> create index indx_hurp on hurp(mycol);


Index created.


--对列索引列收集统计信息
begin 
dbms_stats.gather_table_stats(ownname =>'SYS',
tabname =>'HURP',
method_opt =>'for all indexed columns size auto',
cascade =>TRUE);
end;
/


select lpad(owner,5) owner,
       lpad(table_name，5）table_name，
       column_name,
       endpoint_value,
       endpoint_number 
  from dba_histograms 
 where owner='SYS' 
   and table_name='HURP';
   
OWNER      LPAD(TABLE COLUMN_NAME                    ENDPOINT_VALUE ENDPOINT_NUMBER
---------- ---------- ------------------------------ -------------- ---------------
  SYS       HURP      MYCOL                                      -1               0
  SYS       HURP      MYCOL                                       7               1
  
-- Update col_usage$ 
SQL> select count(*) from hurp where mycol = 1; 


  COUNT(*)
----------
         0


---重新收集统计信息
SQL> begin 
  2  dbms_stats.gather_table_stats(ownname =>'SYS',
  3  tabname =>'HURP',
  4  method_opt =>'for all indexed columns size auto',
  5  cascade =>TRUE);
  6  end;
  7  /


PL/SQL procedure successfully completed.


---查看发现已经有了直方图
SQL> select lpad(owner,5) owner,
  2         lpad(table_name，5）table_name，
  3         column_name,
  4         endpoint_value,
  5         endpoint_number 
  6    from dba_histograms 
  7   where owner='SYS' 
  8     and table_name='HURP';


OWNER      TABLE_NAME COLUMN_NAME                    ENDPOINT_VALUE ENDPOINT_NUMBER
---------- ---------- ------------------------------ -------------- ---------------
  SYS       HURP      MYCOL                                      -1            5419
  SYS       HURP      MYCOL                                       0            5472
  SYS       HURP      MYCOL                                       7            5480
  
select count(*）from hurp where mycol=-1;  












-----测试数据不倾斜
drop table hurp purge;
create table hurp(mycol number);


begin 
for i in 1 .. 10000 loop
insert into hurp values(-1);
commit;
end loop;


for i in 1 .. 10000 loop 
insert into hurp values(0);
commit;
end loop;


for i in 1 .. 10000 loop
insert into hurp values(7);
commit;
end loop;
end;
/


create index indx_hurp on hurp(mycol);






select count(*)　from hurp where mycol=1;


exec dbms_stats.flush_database_monitoring_info;


begin
dbms_stats.gather_table_stats(ownname=>'SYS',
tabname=>'HURP',
method_opt=>'for all indexed columns size auto',
cascade=>true);
end;
/


select lpad(owner,5) owner,lpad(table_name,5) table_name,column_name,endpoint_value,endpoint_number from dba_histograms where owner='SYS' and table_name='HURP';


  
  


begin 
dbms_stats.gather_table_stats(ownname =>'SYS',
tabname =>'TEST',
method_opt =>'for all columns size auto',
cascade =>TRUE);
end;
/




select lpad(owner,5) owner,lpad(table_name,5) table_name,column_name,endpoint_value,endpoint_number from dba_histograms where owner='SYS' and table_name='TEST';


SELECT OWNER,TABLE_NAME,BU