oracle 增量统计信息搜集

jiawang

--在11g之前，收集global的统计信息时，oracle需要扫描每个分区的统计信息，才能得出 global的统计信息。
----在11g以后，Oracle会简单记录分区的概要信息，（synopsis，记录在 WRI$_OPTSTAT_SYNOPSIS_HEAD$ 和 WRI$_OPTSTAT_SYNOPSIS$中），
--------对于global的统计信息，可以不再扫描每个分区的，只需通过计算概要信息，即可得出global 的统计信息

--1、创建分区表
create table pdba partition by range(created)
(partition p1 values less than (to_date('2011-1-1','yyyy-mm-dd')),
partition p2 values less than (to_date('2012-1-1','yyyy-mm-dd')),
partition p3 values less than (to_date('2013-1-1','yyyy-mm-dd')),
partition p4 values less than (maxvalue))
as select * from sys.dba_objects;

--2、重新收集统计信息
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'PDBA');

SET LINESIZE  1000
col table_name for a20
col PARTITION_NAME for a20
select table_name,
       PARTITION_NAME,
       PARTITION_POSITION,
       NUM_ROWS,
       blocks,
       LAST_ANALYZED
  from dba_tab_partitions
   where table_name='PDBA';


TABLE_NAME           PARTITION_NAME       PARTITION_POSITION   NUM_ROWS     BLOCKS LAST_ANALYZED
-------------------- -------------------- ------------------ ---------- ---------- -------------------
PDBA                 P1                                    1          0          0 2024-12-31 23:45:33
PDBA                 P2                                    2          0          0 2024-12-31 23:45:33
PDBA                 P3                                    3          0          0 2024-12-31 23:45:33
PDBA                 P4                                    4      86299       1267 2024-12-31 23:45:34


--


select table_name,num_rows,last_analyzed from dba_tables where table_name='PDBA';


TABLE_NAME             NUM_ROWS LAST_ANALYZED
-------------------- ---------- -------------------
PDBA                      86299 2024-12-31 23:45:34


--3、查看并开启指定表的增量统计信息开关


set lin 300
col "publish" for a30
col "granularity" for a30
col "incremental" for a30
select
dbms_stats.get_prefs('publish','&owner','&tablename') "publish",
dbms_stats.get_prefs('granularity','&&owner','&&tablename') "granularity",
dbms_stats.get_prefs('INCREMENTAL','&&owner','&&tablename') "incremental"
from dual;
publish                        granularity                    incremental
------------------------------ ------------------------------ ------------------------------
TRUE                           AUTO                           FALSE




exec dbms_stats.set_table_prefs('SCOTT', 'PDBA', 'INCREMENTAL', 'TRUE');
--重新执行上面的语句：
publish                        granularity                    incremental
------------------------------ ------------------------------ ------------------------------
TRUE                           AUTO                           TRUE


--4、开启增量后第一次收集统计信息
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'PDBA');




SET LINESIZE  1000
col table_name for a20
col PARTITION_NAME for a20
select table_name,
       PARTITION_NAME,
       PARTITION_POSITION,
       NUM_ROWS,
       blocks,
       LAST_ANALYZED
  from dba_tab_partitions
   where table_name='PDBA';


TABLE_NAME           PARTITION_NAME       PARTITION_POSITION   NUM_ROWS     BLOCKS LAST_ANALYZED
-------------------- -------------------- ------------------ ---------- ---------- -------------------
PDBA                 P1                                    1          0          0 2024-12-31 23:53:12
PDBA                 P2                                    2          0          0 2024-12-31 23:53:12
PDBA                 P3                                    3          0          0 2024-12-31 23:53:12
PDBA                 P4                                    4      86299       1267 2024-12-31 23:53:13




select table_name,num_rows,last_analyzed from dba_tables where table_name='PDBA';
TABLE_NAME             NUM_ROWS LAST_ANALYZED
-------------------- ---------- -------------------
PDBA                      86299 2024-12-31 23:53:13




----设置增量收集后，第一次收集统计信息会将全部历史分区重新收集，以生成历史分区的大纲信息，分区数越多，耗时越长


  


--5、开启增量后第二次收集统计信息
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'PDBA');


SET LINESIZE  1000
col table_name for a20
col PARTITION_NAME for a20
select table_name,
       PARTITION_NAME,
       PARTITION_POSITION,
       NUM_ROWS,
       blocks,
       LAST_ANALYZED
  from dba_tab_partitions
   where table_name='PDBA';


TABLE_NAME           PARTITION_NAME       PARTITION_POSITION   NUM_ROWS     BLOCKS LAST_ANALYZED
-------------------- -------------------- ------------------ ---------- ---------- -------------------
PDBA                 P1                                    1          0          0 2024-12-31 23:53:12
PDBA                 P2                                    2          0          0 2024-12-31 23:53:12
PDBA                 P3                                    3          0          0 2024-12-31 23:53:12
PDBA                 P4                                    4      86299       1267 2024-12-31 23:53:13


SQL> select table_name,num_rows,last_analyzed from dba_tables where table_name='PDBA';


TABLE_NAME             NUM_ROWS LAST_ANALYZED
-------------------- ---------- -------------------
PDBA                      86299 2024-12-31 23:55:12


SQL>


--全局统计信息时间更新了，但是分区统计信息还是旧的。


--对某个分区收集统计信息，全局统计信息也会更新


exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'PDBA',PARTNAME=>'P1');


SQL>  SET LINESIZE  1000
SQL>  col table_name for a20
SQL>  col PARTITION_NAME for a20
SQL>  select table_name,
  2         PARTITION_NAME,
  3         PARTITION_POSITION,
  4         NUM_ROWS,
  5         blocks,
  6         LAST_ANALYZED
  7    from dba_tab_partitions
  8     where table_name='PDBA';


TABLE_NAME           PARTITION_NAME       PARTITION_POSITION   NUM_ROWS     BLOCKS LAST_ANALYZED
-------------------- -------------------- ------------------ ---------- ---------- -------------------
PDBA                 P1                                    1          0          0 2024-12-31 23:57:05
PDBA                 P2                                    2          0          0 2024-12-31 23:53:12
PDBA                 P3                                    3          0          0 2024-12-31 23:53:12
PDBA                 P4                                    4      86299       1267 2024-12-31 23:53:13


SQL>  select table_name,num_rows,last_analyzed from dba_tables where table_name='PDBA';


TABLE_NAME             NUM_ROWS LAST_ANALYZED
-------------------- ---------- -------------------
PDBA                      86299 2024-12-31 23:57:05


SQL>