您现在的位置是：首页 >学无止境 >Oracle批量数据加载时的在线统计信息收集网站首页学无止境

Oracle批量数据加载时的在线统计信息收集

dingdingfish 2023-06-21 04:00:02
简介Oracle批量数据加载时的在线统计信息收集

这个特性的英文名叫Online Statistics Gathering for Bulk Loads，本文为对此特性的测试。

此特性描述为：

Starting in Oracle Database 12c, the database can gather table statistics automatically during the following types of bulk loads: INSERT INTO … SELECT into an empty table using a direct path insert, and CREATE TABLE AS SELECT .

此处有两个要点：

  • 表需为空
  • 需直接路径插入（是否nologging无所谓）

其实下面还有一句：

For example, if sales is an empty partitioned table, and if you run INSERT INTO sales SELECT, then the database gathers global statistics for sales. However, the database does not gather partition-level statistics.

也就是说，对于分区表，只收集全局统计信息，不收集分区级别的统计信息。

下面开始实验。

新建一表，即SSB Schema中的事实表。此时统计信息为空。

-- 新建表lineorder
alter session set nls_date_format = 'MM/DD/YYYY HH24:MI:SS';

select
num_rows,
sample_size,
last_analyzed,
tablespace_name
from dba_tables where table_name = 'LINEORDER';

  NUM_ROWS SAMPLE_SIZE LAST_ANALYZED       TABLESPACE_NAME
---------- ----------- ------------------- ------------------------------
                                           USERS

直接路径插入，统计信息有了，就不用再额外收集了。

alter session force parallel dml parallel 4;
set timing on
insert /*+ parallel(4) append */ into lineorder select /*+ parallel(4) */ * from lineorder_os;

384016850 rows created.

Elapsed: 00:03:35.29

select
num_rows,
sample_size,
last_analyzed,
tablespace_name
from dba_tables where table_name = 'LINEORDER';


  NUM_ROWS SAMPLE_SIZE LAST_ANALYZED       TABLESPACE_NAME
---------- ----------- ------------------- ------------------------------
 384016850   384016850 04/23/2023 09:52:14 USERS

基于分区的实验，删除表并重建，此时全局统计信息不存在：

drop table lineorder purge;
create table lineorder ....;
alter table lineorder modify partition by range(lo_orderdate)
(
    partition p1992 values less than ('19930101'),
    partition p1993 values less than ('19940101'),
    partition p1994 values less than ('19950101'),
    partition p1995 values less than ('19960101'),
    partition p1996 values less than ('19970101'),
    partition p1997 values less than ('19980101'),
    partition p1998 values less than ('19990101')
);

alter session set nls_date_format = 'MM/DD/YYYY HH24:MI:SS';

select
num_rows,
sample_size,
last_analyzed,
tablespace_name
from dba_tables where table_name = 'LINEORDER';

  NUM_ROWS SAMPLE_SIZE LAST_ANALYZED       TABLESPACE_NAME
---------- ----------- ------------------- ------------------------------

分区级的统计信息不存在：

select
num_rows,
sample_size,
last_analyzed,
tablespace_name
from dba_tab_partitions where table_name = 'LINEORDER';

  NUM_ROWS SAMPLE_SIZE LAST_ANALYZED       TABLESPACE_NAME
---------- ----------- ------------------- ------------------------------
                                           USERS
                                           USERS
                                           USERS
                                           USERS
                                           USERS
                                           USERS
                                           USERS

7 rows selected.

直接路径插入数据，全局统计信息有了，但分区级的统计信息仍不存在：

alter session force parallel dml parallel 4;
set timing on
insert /*+ parallel(4) append */ into lineorder select /*+ parallel(4) */ * from lineorder_os;

384016850 rows created.

Elapsed: 00:03:53.65

select
num_rows,
sample_size,
last_analyzed,
tablespace_name
from dba_tables where table_name = 'LINEORDER';

  NUM_ROWS SAMPLE_SIZE LAST_ANALYZED       TABLESPACE_NAME
---------- ----------- ------------------- ------------------------------
 384016850   384016850 04/23/2023 10:06:22

select
num_rows,
sample_size,
last_analyzed,
tablespace_name
from dba_tab_partitions where table_name = 'LINEORDER';

  NUM_ROWS SAMPLE_SIZE LAST_ANALYZED       TABLESPACE_NAME
---------- ----------- ------------------- ------------------------------
         0             04/23/2023 10:03:54 USERS
         0             04/23/2023 10:03:54 USERS
         0             04/23/2023 10:03:54 USERS
         0             04/23/2023 10:03:54 USERS
         0             04/23/2023 10:03:54 USERS
         0             04/23/2023 10:03:54 USERS
         0             04/23/2023 10:03:54 USERS

7 rows selected.

重新收集统计信息，此时分区级的统计信息也有了：

exec dbms_stats.gather_table_stats(null, 'LINEORDER', degree=>4);

PL/SQL procedure successfully completed.

select
num_rows,
sample_size,
last_analyzed,
tablespace_name
from dba_tables where table_name = 'LINEORDER';

  NUM_ROWS SAMPLE_SIZE LAST_ANALYZED       TABLESPACE_NAME
---------- ----------- ------------------- ------------------------------
 384016850   384016850 04/23/2023 10:10:51


select
num_rows,
sample_size,
last_analyzed,
tablespace_name
from dba_tab_partitions where table_name = 'LINEORDER';

  NUM_ROWS SAMPLE_SIZE LAST_ANALYZED       TABLESPACE_NAME
---------- ----------- ------------------- ------------------------------
  58399292    58399292 04/23/2023 10:09:21 USERS
  58237902    58237902 04/23/2023 10:09:34 USERS
  58268203    58268203 04/23/2023 10:09:48 USERS
  58258542    58258542 04/23/2023 10:10:02 USERS
  58427673    58427673 04/23/2023 10:10:15 USERS
  58267746    58267746 04/23/2023 10:10:29 USERS
  34157492    34157492 04/23/2023 10:10:43 USERS

7 rows selected.

参考

风语者！平时喜欢研究各种技术，目前在从事后端开发工作，热爱生活、热爱工作。

站长推荐