12c批量加载自动收集统计信息特性和相关bug、限制-凯发app官方网站

从12c开始，oracle对批量加载的表可以在线收集统计信息，解决了因统计信息不准导致不能走正确执行计划的问题。当然，在线统计信息收集也有一些限制和问题，后面会详细说明。

批量加载统计信息自动收集主要是针对空表或空的分区灌入数据，且是direct path的语句，而且用户是非sys用户，也即：
create table as select （ctas）
insert /* append*/ into select ...
insert /* parallel*/ into select...
这几种形式，而且必须原先的表或分区是空的，不是空的不会在线收集统计信息（这是大前提）。


下面分别演示。
1.create table as select （ctas）批量加载统计信息收集


create table autostats1 as select * from dba_tables;


查看统计信息已经自动收集：
select num_rows,sample_size,last_analyzed from dba_tab_statistics where table_name=upper('autostats1');
  num_rows sample_size last_analyzed
---------- ----------- -----------------
      2240        2240 20240424 16:45:23
      
select column_name,sample_size,histogram from dba_tab_col_statistics where table_name=upper('autostats1');
column_name          sample_size histogram
-------------------- ----------- ------------------------------
temporary                   2240 none
iot_type                      99 none
partitioned                 2240 none
。。。


隐患：1）ctas自动收集统计信息，因为表还没有使用，在sys.col_usage$里还没有记录，所以不收集列直方图信息。
      2）如果ctas先建表使用where 1=0，则触发自动收集num_rows=0，如果后续灌入数据不是使用insert/* append或parallel select ...,
         则不会立马自动更新，导致统计信息错误。


2.insert into select direct path(append或parallel hints）批量加载统计信息收集


普通的insert不会触发自动收集统计信息：
insert into autostats1
select * from dba_tables;
2241 rows created.


elapsed: 00:00:00.27
commit;


可以看到统计信息未更新：
select num_rows,sample_size,last_analyzed from dba_tab_statistics where table_name=upper('autostats1');
  num_rows sample_size last_analyzed
---------- ----------- -----------------
      2240        2240 20240424 16:45:23


表有数据使用direct path也不会自动收集：
insert/* append */ into autostats1
select * from dba_tables;
commit;


select num_rows,sample_size,last_analyzed from dba_tab_statistics where table_name=upper('autostats1');


  num_rows sample_size last_analyzed
---------- ----------- -----------------
      2240        2240 20240424 16:45:23


truncate后，再使用direct path会收集：
truncate table  autostats1;
insert/* append */ into autostats1
select * from dba_tables;
commit;


空表使用insert append会收集统计信息，可以看到last_analyzed和sample_size,num_rows都变了。
 select num_rows,sample_size,last_analyzed from dba_tab_statistics where table_name=upper('autostats1');


  num_rows sample_size last_analyzed
---------- ----------- -----------------
      2241        2241 20240424 16:46:48


parallel是direct path,对于空表也会触发自动统计信息收集：
truncate table  autostats1;
insert/* parallel */ into autostats1
select * from dba_tables;
commit;     


select num_rows,sample_size,last_analyzed from dba_tab_statistics where table_name=upper('autostats1');


  num_rows sample_size last_analyzed
---------- ----------- -----------------
      2241        2241 20240424 16:51:41


隐患：1）如果create table as where 1=0先建表，则会触发统计信息收集，这时候num_rows=0,可能导致问题，
后续必须使用append或parallel insert select才会触发更新统计信息：
drop table autostats1;
create table autostats1 as select * from dba_tables where 1=0;
select num_rows,sample_size,last_analyzed from dba_tab_statistics where table_name=upper('autostats1');


  num_rows sample_size last_analyzed
---------- ----------- -----------------
         0           0 20240424 16:51:30


insert/* append */ into autostats1
select * from dba_tables;
commit;


select num_rows,sample_size,last_analyzed from dba_tab_statistics where table_name=upper('autostats1');


  num_rows sample_size last_analyzed
---------- ----------- -----------------
      2241        2241 20240424 16:51:41
      


3.分区表批量插入数据的统计信息自动收集
这个主要是insert select，必须使用append或parallel,而且对应的表或分区是空的，才会触发收集
3.1）使用insert into tab select ...方式，只更新全局统计信息，不更新分区统计信息。
3.2）使用insert into tab partition(part...) select方式，只更新分区统计信息，不更新全局统计信息。
如下所示：
drop table part_stats;
create table part_stats
(
id number,
insert_date date
)
partition by range(insert_date)
(
partition p_20401 values less than (to_date('2024-2-1','yyyy-mm-dd')),
partition p_20402 values less than (to_date('2024-3-1','yyyy-mm-dd'))
)
;


1.使用insert into table方式，只有空表才会触发收集，只更新表不更新partition统计信息。
insert/* append*/ into part_stats
select level,date'2024-1-1' level
from dual
connect by level<20;
commit;


col table_name for a20;
col partition_name for a20;
可以看到，收集了表统计信息，但是没有收集分区统计信息。
select table_name,partition_name,num_rows,sample_size,last_analyzed from dba_tab_statistics where table_name=upper('part_stats');
table_name           partition_name         num_rows sample_size last_analyzed
-------------------- -------------------- ---------- ----------- -----------------
part_stats                                        19          19 20240424 17:01:29
part_stats           p_20401
part_stats           p_20402


再次插入p_20402分区，因为表不是空的了，所以不会触发收集：
insert/* append*/ into part_stats
select level,date'2024-2-1' level
from dual
connect by level<20;
commit;


select table_name,partition_name,num_rows,sample_size,last_analyzed from dba_tab_statistics where table_name=upper('part_stats');


table_name           partition_name         num_rows sample_size last_analyzed
-------------------- -------------------- ---------- ----------- -----------------
part_stats                                        19          19 20240424 17:01:29
part_stats           p_20401
part_stats           p_20402




2）使用insert into tab partition(part...) select方式，只有对应partition是空的才会触发收集
   只更新partition统计信息，不更新表统计信息。


删除统计信息：
exec dbms_stats.delete_table_stats(ownname=>user,tabname=>'part_stats',no_invalidate=>false);
清空表
truncate table part_stats;
按照partition方式插入：
insert/* append*/ into part_stats partition(p_20401)
select level,date'2024-1-1' level
from dual
connect by level<20;
commit;


可以看到更新了partition统计信息没有更新表统计信息：
select table_name,partition_name,num_rows,sample_size,last_analyzed from dba_tab_statistics where table_name=upper('part_stats');


table_name           partition_name         num_rows sample_size last_analyzed
-------------------- -------------------- ---------- ----------- -----------------
part_stats
part_stats           p_20402
part_stats           p_20401                      19          19 20240424 17:09:19




同样更新了p_20402统计信息，没有更新表统计信息：
insert/* append*/ into part_stats partition(p_20402)
select level,date'2024-2-1' level
from dual
connect by level<20;
commit;


select table_name,partition_name,num_rows,sample_size,last_analyzed from dba_tab_statistics where table_name=upper('part_stats');


table_name           partition_name         num_rows sample_size last_analyzed
-------------------- -------------------- ---------- ----------- -----------------
part_stats
part_stats           p_20401                      19          19 20240424 17:09:19
part_stats           p_20402                      19          19 20240424 17:09:47




4.新增列会触发统计信息bug导致列的num_distinct=0，可能导致笛卡尔积
主要场景是ctas或insert append等灌入数据后，然后add column再更新数据，其中12c开始add column有个bug会触发统计信息收集，
因为新增列没有default值的话，则num_distinct=0,后续更新这个值不会触发收集，导致和其他表关联走笛卡尔积。


drop table tab1;
create table tab1
as
select level id
from dual connect by level<100;
select table_name,partition_name,num_rows,sample_size,last_analyzed from dba_tab_statistics where table_name=upper('tab1');


ctas触发自动统计信息收集：
table_name           partition_name         num_rows sample_size last_analyzed
-------------------- -------------------- ---------- ----------- -----------------
tab1                                              99          99 20240424 17:13:14


新增列,发现触发列统计信息收集，对应num_distinct=0，last_analyzed是新的：
alter table tab1 add newcol number;
select column_name,num_distinct,histogram,last_analyzed from dba_tab_col_statistics where table_name=upper('tab1');


column_name          num_distinct histogram                      last_analyzed
-------------------- ------------ ------------------------------ -----------------
id                             99 none                           20240424 17:13:14
newcol                          0 none                           20240424 17:14:10


更新newcol数据,不会触发列统计信息更新，还是num_distinct=0,这时候如果和其他表关联，可能导致笛卡尔积：
update tab1 set newcol=id;
commit;


column_name          num_distinct histogram                      last_analyzed
-------------------- ------------ ------------------------------ -----------------
id                             99 none                           20240424 17:13:14
newcol                          0 none                           20240424 17:14:10




对应bug如下：bug 33815940  alter table add column adds non-expected column statistics
暂时凯发app官方网站的解决方案：set "_fix_control" = "20424684:off";


5.批量加载统计信息自动收集限制
1）不能完全依赖于批量加载收集，从前面也可以看出，批量加载收集只是针对空表或分区空的时候收集，不收集索引或直方图信息，要收集必须手动收集。
2）如果有alter table add newcol需求，因为有bug，可能导致num_distinct=0,这时候需要更新或用set "_fix_control" = "20424684:off"修复这个bug
3）不能是oracle内置schema下的表，比如sys用户无效，必须是用户schema下的表
4）表的统计信息不能是lock的
5）有虚拟列的表
等都不支持的。