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

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

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