从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
暂时解决方案：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）有虚拟列的表
等都不支持的。
详细见SQL Tuning guide文档：10.3 How the Database Gathers Optimizer Statistics部分内容。
