重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛

标题: oracle 增量统计信息搜集 [打印本页]

作者: jiawang 时间: 2025-6-29 20:24
标题: oracle 增量统计信息搜集
--在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>

欢迎光临重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛 (http://bbs.cqsztech.com/)