[2023-04-18]增量收集统计信息

qxy0503

已于 2023-09-13 15:28:10 修改

阅读量616

点赞数 1

文章标签： oracle 数据库 sql

于 2023-04-18 16:50:51 首次发布

本文链接：https://blog.csdn.net/m15217321304/article/details/130225604

版权

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

1、创建分区表

SQL> create table p(id number, age date)
  2  partition by range(id)
  3  (partition p1 values less than(100),
  4  partition p2 values less than(200),
  5  partition p3 values less than(300),
  6  partition p4 values less than(400)
  7  );

Table created.

SQL> insert into p select level lv,sysdate from dual connect by level < 400;

399 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'p');

PL/SQL procedure successfully completed.

SQL>  select table_owner,table_name,partition_name from dba_tab_partitions where table_owner='SCOTT' and table_name='P';

TABLE_OWNER                    TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------ ------------------------------
SCOTT                          P                              P1
SCOTT                          P                              P2
SCOTT                          P                              P3
SCOTT                          P                              P4


SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

Session altered.

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

TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
P                                     399 2023-04-18 16:33:47

SQL> 
SQL>   select table_name,partition_name,num_rows,last_analyzed from dba_tab_partitions where table_name='P';

TABLE_NAME                     PARTITION_NAME                   NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -------------------
P                              P1                                     99 2023-04-18 16:33:46
P                              P2                                    100 2023-04-18 16:33:47
P                              P3                                    100 2023-04-18 16:33:47
P                              P4                                    100 2023-04-18 16:33:47

2、重新收集统计信息

SQL> exec dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'p');

PL/SQL procedure successfully completed.

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

TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
P                                     399 2023-04-18 16:35:12

SQL> select table_name,partition_name,num_rows,last_analyzed from dba_tab_partitions where table_name='P';

TABLE_NAME                     PARTITION_NAME                   NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -------------------
P                              P1                                     99 2023-04-18 16:35:12
P                              P2                                    100 2023-04-18 16:35:12
P                              P3                                    100 2023-04-18 16:35:12
P                              P4                                    100 2023-04-18 16:35:12

SQL>

对表收集统计信息，虽然分区中的数据没有变化，依然会收集全部统计信息。

3、开启增量统计信息的条件

SQL> set lin 300
SQL> col "publish" for a30
SQL> col "granularity" for a30
SQL> col "incremental" for a30
SQL> select 
  2  dbms_stats.get_prefs('publish','&owner','&tablename') "publish",
  3  dbms_stats.get_prefs('granularity','&&owner','&&tablename') "granularity",
  4  dbms_stats.get_prefs('INCREMENTAL','&&owner','&&tablename') "incremental" 
  5  from dual;
Enter value for owner: scott
Enter value for tablename: p
old   2: dbms_stats.get_prefs('publish','&owner','&tablename') "publish",
new   2: dbms_stats.get_prefs('publish','scott','p') "publish",
Enter value for owner: scott
Enter value for tablename: p
old   3: dbms_stats.get_prefs('granularity','&&owner','&&tablename') "granularity",
new   3: dbms_stats.get_prefs('granularity','scott','p') "granularity",
old   4: dbms_stats.get_prefs('INCREMENTAL','&&owner','&&tablename') "incremental"
new   4: dbms_stats.get_prefs('INCREMENTAL','scott','p') "incremental"

publish                        granularity                    incremental
------------------------------ ------------------------------ ------------------------------
TRUE                           AUTO                           FALSE

SQL> 
SQL> exec dbms_stats.set_table_prefs('SCOTT', 'P', 'INCREMENTAL', 'TRUE');

PL/SQL procedure successfully completed.

SQL> col "publish" for a30
SQL> col "granularity" for a30
SQL> col "incremental" for a30
SQL> select 
  2  dbms_stats.get_prefs('publish','&owner','&tablename') "publish",
  3  dbms_stats.get_prefs('granularity','&&owner','&&tablename') "granularity",
  4  dbms_stats.get_prefs('INCREMENTAL','&&owner','&&tablename') "incremental" 
  5  from dual;
old   2: dbms_stats.get_prefs('publish','&owner','&tablename') "publish",
new   2: dbms_stats.get_prefs('publish','scott','p') "publish",
old   3: dbms_stats.get_prefs('granularity','&&owner','&&tablename') "granularity",
new   3: dbms_stats.get_prefs('granularity','scott','p') "granularity",
old   4: dbms_stats.get_prefs('INCREMENTAL','&&owner','&&tablename') "incremental"
new   4: dbms_stats.get_prefs('INCREMENTAL','scott','p') "incremental"

publish                        granularity                    incremental
------------------------------ ------------------------------ ------------------------------
TRUE                           AUTO                           TRUE

SQL>

4、开启增量后第一次收集统计信息

SQL> exec dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'p');

PL/SQL procedure successfully completed.

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

TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
P                                     399 2023-04-18 16:39:26

SQL> select table_name,partition_name,num_rows,last_analyzed from dba_tab_partitions where table_name='P';

TABLE_NAME                     PARTITION_NAME                   NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -------------------
P                              P1                                     99 2023-04-18 16:39:26
P                              P2                                    100 2023-04-18 16:39:26
P                              P3                                    100 2023-04-18 16:39:26
P                              P4                                    100 2023-04-18 16:39:26

SQL>

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

5、开启增量后第二次收集统计信息

SQL> exec dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'p');

PL/SQL procedure successfully completed.

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

TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
P                                     399 2023-04-18 16:41:03

SQL> select table_name,partition_name,num_rows,last_analyzed from dba_tab_partitions where table_name='P';

TABLE_NAME                     PARTITION_NAME                   NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -------------------
P                              P1                                     99 2023-04-18 16:39:26
P                              P2                                    100 2023-04-18 16:39:26
P                              P3                                    100 2023-04-18 16:39:26
P                              P4                                    100 2023-04-18 16:39:26

SQL>

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

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

SQL> exec dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'p',PARTNAME=>'p1');

PL/SQL procedure successfully completed.

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

TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
P                                     399 2023-04-18 16:42:26

SQL> select table_name,partition_name,num_rows,last_analyzed from dba_tab_partitions where table_name='P';

TABLE_NAME                     PARTITION_NAME                   NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -------------------
P                              P1                                     99 2023-04-18 16:42:26
P                              P2                                    100 2023-04-18 16:39:26
P                              P3                                    100 2023-04-18 16:39:26
P                              P4                                    100 2023-04-18 16:39:26

SQL>