首页 » ORACLE 9i-23ai » Oracle Incremental Statistics 忽略ESTIMATE_PERCENT ?

Oracle Incremental Statistics 忽略ESTIMATE_PERCENT ?

Oracle Database 11g 引入了增量统计信息维护(Incremental Statistics Maintenance),以提高大型分区表统计信息收集的性能。当为分区表启用增量统计信息维护时,Oracle 会通过聚合分区级别统计信息来准确生成全局级别统计信息。除了增量统计信息外,配置较小的采样率也是加快大表统计信息收集的另一种方法。今天有位同事问,为什么在同时配置增量统计信息和小采样率时,小采样率没有起作用。下面我们来测试一下。

统计信息配置项常用SQL

--For table level
 select dbms_stats.get_prefs('CASCADE','HR','EMP') from dual;
 select dbms_stats.get_prefs('DEGREE','HR','EMP') from dual;

select dbms_stats.get_prefs('ESTIMATE_PERCENT') from dual;
 select dbms_stats.get_prefs('METHOD_OPT') from dual;
 select dbms_stats.get_prefs('NO_INVALIDATE') from dual;
 select dbms_stats.get_prefs('GRANULARITY') from dual;
 select dbms_stats.get_prefs('PUBLISH') from dual;
 select dbms_stats.get_prefs('INCREMENTAL') from dual;
 select dbms_stats.get_prefs('INCREMENTAL_LEVEL') from dual;
 select dbms_stats.get_prefs('STALE_PERCENT') from dual;

---New introduced in 12C/18C/19C
 select dbms_stats.get_prefs('AUTOSTATS_TARGET') from dual;
 select dbms_stats.get_prefs('CONCURRENT') from dual;
 select dbms_stats.get_prefs('INCREMENTAL_STALENESS') from dual;
 select dbms_stats.get_prefs('GLOBAL_TEMP_TABLE_STATS') from dual;
 select dbms_stats.get_prefs('TABLE_CACHED_BLOCKS') from dual;
 select dbms_stats.get_prefs('OPTIONS') from dual;
 select dbms_stats.get_prefs('STAT_CATEGORY') from dual;
 select dbms_stats.get_prefs('PREFERENCE_OVERRIDES_PARAMETER') from dual;
 select dbms_stats.get_prefs('APPROXIMATE_NDV_ALGORITHM') from dual;
 select dbms_stats.get_prefs('AUTO_STAT_EXTENSIONS') from dual;
 select dbms_stats.get_prefs('WAIT_TIME_TO_UPDATE_STATS') from dual;
 select dbms_stats.get_prefs('ROOT_TRIGGER_PDB') from dual;
 select dbms_stats.get_prefs('COORDINATOR_TRIGGER_SHARD') from dual;
 select dbms_stats.get_prefs('AUTO_TASK_STATUS') from dual;
 select dbms_stats.get_prefs('AUTO_TASK_MAX_RUN_TIME') from dual;
 select dbms_stats.get_prefs('AUTO_TASK_INTERVAL') from dual;

Change the value of these parameters

SET_GLOBAL_PREFS : Change the default value of the parameters used by gather stats for any object in database.
SET_TABLE_PREFS : Procedure is used to change the default values for the tables only.
SET_SCHEMA_PREFS : Procedure is used to change default value for schemas objects only.
SET_DATABASE_PREFS : Procedures used to change default value for all user defined schemas.

DEMO

 CREATE TABLE "OE"."ORDERS2"
 (id int,name varchar2(10),order_date date)
 PARTITION BY RANGE ("ORDER_DATE")
 (PARTITION "ORDERS_FEB_2007"  VALUES LESS THAN (TO_DATE('2007-03-01', 'YYYY-MM-DD')),
  PARTITION "ORDERS_MAR_2007"  VALUES LESS THAN (TO_DATE('2007-04-01','YYYY-MM-DD')),
  PARTITION "ORDERS_APR_2007"  VALUES LESS THAN (TO_DATE('2007-05-01','YYYY-MM-DD')),
  PARTITION "ORDERS_MAY_2007"  VALUES LESS THAN (TO_DATE('2007-06-01','YYYY-MM-DD')),
  PARTITION "ORDERS_JUN_2007"  VALUES LESS THAN (TO_DATE('2007-07-01','YYYY-MM-DD')),
  PARTITION "ORDERS_JUL_2007"  VALUES LESS THAN (TO_DATE('2007-08-01','YYYY-MM-DD')),
  PARTITION "ORDERS_AUG_2007"  VALUES LESS THAN (TO_DATE('2007-09-01','YYYY-MM-DD')),
  PARTITION "ORDERS_SEP_2007"  VALUES LESS THAN (TO_DATE('2007-10-01','YYYY-MM-DD')),
  PARTITION "ORDERS_OCT_2007"  VALUES LESS THAN (TO_DATE('2007-11-01','YYYY-MM-DD')),
  PARTITION "ORDERS_NOV_2007"  VALUES LESS THAN (TO_DATE('2007-12-01','YYYY-MM-DD')),
  PARTITION "ORDERS_DEC_2007"  VALUES LESS THAN (TO_DATE('2008-01-01','YYYY-MM-DD')),
  PARTITION "ORDERS_JAN_2008"  VALUES LESS THAN (TO_DATE('2008-02-01','YYYY-MM-DD')),
  PARTITION "ORDERS_FEB_2008"  VALUES LESS THAN (TO_DATE('2008-03-01','YYYY-MM-DD')),
  PARTITION "ORDERS_MAR_2008"  VALUES LESS THAN (TO_DATE('2008-04-01','YYYY-MM-DD' )),
  PARTITION "ORDERS_APR_2008"  VALUES LESS THAN (TO_DATE('2008-05-01','YYYY-MM-DD')),
  PARTITION "ORDERS_MAY_2008"  VALUES LESS THAN (TO_DATE('2008-06-01','YYYY-MM-DD')),
  PARTITION "ORDERS_JUN_2008"  VALUES LESS THAN (TO_DATE('2008-07-01','YYYY-MM-DD')),
  PARTITION "ORDERS_JUL_2008"  VALUES LESS THAN (TO_DATE('2008-08-01','YYYY-MM-DD')),
  PARTITION "ORDERS_AUG_2008"  VALUES LESS THAN (TO_DATE('2008-09-01','YYYY-MM-DD')),
  PARTITION "ORDERS_SEP_2008"  VALUES LESS THAN (TO_DATE('2008-10-01','YYYY-MM-DD')),
  PARTITION "ORDERS_OCT_2008"  VALUES LESS THAN (TO_DATE('2008-11-01','YYYY-MM-DD')),
  PARTITION "ORDERS_NOV_2008"  VALUES LESS THAN (TO_DATE('2008-12-01','YYYY-MM-DD')),
  PARTITION "ORDERS_DEC_2008"  VALUES LESS THAN (TO_DATE('2009-01-01','YYYY-MM-DD')),
  PARTITION "ORDERS_JAN_2009"  VALUES LESS THAN (TO_DATE('2009-02-01','YYYY-MM-DD')),
  PARTITION "ORDERS_FEB_2009"  VALUES LESS THAN (TO_DATE('2009-03-01','YYYY-MM-DD')),
  PARTITION "ORDERS_MAR_2009"  VALUES LESS THAN (TO_DATE('2009-04-01','YYYY-MM-DD' )),
  PARTITION "ORDERS_APR_2009"  VALUES LESS THAN (TO_DATE('2009-05-01','YYYY-MM-DD')),
  PARTITION "ORDERS_MAY_2009"  VALUES LESS THAN (TO_DATE('2009-06-01','YYYY-MM-DD')),
  PARTITION "ORDERS_JUN_2009"  VALUES LESS THAN (TO_DATE('2009-07-01','YYYY-MM-DD')),
  PARTITION "ORDERS_JUL_2009"  VALUES LESS THAN (TO_DATE('2009-08-01','YYYY-MM-DD')),
  PARTITION "ORDERS_AUG_2009"  VALUES LESS THAN (TO_DATE('2009-09-01','YYYY-MM-DD')),
  PARTITION "ORDERS_SEP_2009"  VALUES LESS THAN (TO_DATE('2009-10-01','YYYY-MM-DD')),
  PARTITION "ORDERS_OCT_2009"  VALUES LESS THAN (TO_DATE('2009-11-01','YYYY-MM-DD')),
  PARTITION "ORDERS_NOV_2009"  VALUES LESS THAN (TO_DATE('2009-12-01','YYYY-MM-DD')),
  PARTITION "ORDERS_DEC_2009"  VALUES LESS THAN (TO_DATE('2010-01-01','YYYY-MM-DD')),
  PARTITION "ORDERS_JAN_2010"  VALUES LESS THAN (TO_DATE('2010-02-01','YYYY-MM-DD')),
  PARTITION "ORDERS_FEB_2010"  VALUES LESS THAN (TO_DATE('2010-03-01','YYYY-MM-DD')),
  PARTITION "ORDERS_MAR_2010"  VALUES LESS THAN (TO_DATE('2010-04-01','YYYY-MM-DD' )),
  PARTITION "ORDERS_APR_2010"  VALUES LESS THAN (TO_DATE('2010-05-01','YYYY-MM-DD')),
  PARTITION "ORDERS_MAY_2010"  VALUES LESS THAN (TO_DATE('2010-06-01','YYYY-MM-DD')),
  PARTITION "ORDERS_JUN_2010"  VALUES LESS THAN (TO_DATE('2010-07-01','YYYY-MM-DD')),
  PARTITION "ORDERS_JUL_2010"  VALUES LESS THAN (TO_DATE('2010-08-01','YYYY-MM-DD')),
  PARTITION "ORDERS_AUG_2010"  VALUES LESS THAN (TO_DATE('2010-09-01','YYYY-MM-DD')),
  PARTITION "ORDERS_SEP_2010"  VALUES LESS THAN (TO_DATE('2010-10-01','YYYY-MM-DD')),
  PARTITION "ORDERS_OCT_2010"  VALUES LESS THAN (TO_DATE('2010-11-01','YYYY-MM-DD')),
  PARTITION "ORDERS_NOV_2010"  VALUES LESS THAN (TO_DATE('2010-12-01','YYYY-MM-DD')),
  PARTITION "ORDERS_DEC_2010"  VALUES LESS THAN (TO_DATE('2011-01-01','YYYY-MM-DD')),
  PARTITION "ORDERS_JAN_2011"  VALUES LESS THAN (TO_DATE('2011-02-01','YYYY-MM-DD')),
  PARTITION "ORDERS_FEB_2011"  VALUES LESS THAN (TO_DATE('2011-03-01','YYYY-MM-DD')),
  PARTITION "ORDERS_MAR_2011"  VALUES LESS THAN (TO_DATE('2011-04-01','YYYY-MM-DD' )),
  PARTITION "ORDERS_APR_2011"  VALUES LESS THAN (TO_DATE('2011-05-01','YYYY-MM-DD')),
  PARTITION "ORDERS_MAY_2011"  VALUES LESS THAN (TO_DATE('2011-06-01','YYYY-MM-DD')),
  PARTITION "ORDERS_JUN_2011"  VALUES LESS THAN (TO_DATE('2011-07-01','YYYY-MM-DD')),
  PARTITION "ORDERS_JUL_2011"  VALUES LESS THAN (TO_DATE('2011-08-01','YYYY-MM-DD')),
  PARTITION "ORDERS_AUG_2011"  VALUES LESS THAN (TO_DATE('2011-09-01','YYYY-MM-DD')),
  PARTITION "ORDERS_SEP_2011"  VALUES LESS THAN (TO_DATE('2011-10-01','YYYY-MM-DD')),
  PARTITION "ORDERS_OCT_2011"  VALUES LESS THAN (TO_DATE('2011-11-01','YYYY-MM-DD')),
  PARTITION "ORDERS_NOV_2011"  VALUES LESS THAN (TO_DATE('2011-12-01','YYYY-MM-DD')),
  PARTITION "ORDERS_DEC_2011"  VALUES LESS THAN (TO_DATE('2012-01-01','YYYY-MM-DD')),
  PARTITION "ORDERS_JAN_2012"  VALUES LESS THAN (TO_DATE('2012-02-01','YYYY-MM-DD')),
  PARTITION "ORDERS_FEB_2012"  VALUES LESS THAN (TO_DATE('2012-03-01','YYYY-MM-DD')),
  PARTITION "ORDERS_MAR_2012"  VALUES LESS THAN (TO_DATE('2012-04-01','YYYY-MM-DD')),
  PARTITION "ORDERS_APR_2012"  VALUES LESS THAN (TO_DATE('2012-05-01','YYYY-MM-DD')),
  PARTITION "ORDERS_MAY_2012"  VALUES LESS THAN (TO_DATE('2012-06-01','YYYY-MM-DD')),
  PARTITION "ORDERS_JUN_2012"  VALUES LESS THAN (TO_DATE('2012-07-01','YYYY-MM-DD')),
  PARTITION "ORDERS_JUL_2012"  VALUES LESS THAN (TO_DATE('2012-08-01','YYYY-MM-DD')),
  PARTITION "ORDERS_AUG_2012"  VALUES LESS THAN (TO_DATE('2012-09-01','YYYY-MM-DD')),
  PARTITION "ORDERS_SEP_2012"  VALUES LESS THAN (TO_DATE('2012-10-01','YYYY-MM-DD')),
  PARTITION "ORDERS_OCT_2012"  VALUES LESS THAN (TO_DATE('2012-11-01','YYYY-MM-DD')),
  PARTITION "ORDERS_NOV_2012"  VALUES LESS THAN (TO_DATE('2012-12-01','YYYY-MM-DD')),
  PARTITION "ORDERS_DEC_2012"  VALUES LESS THAN (TO_DATE('2013-01-01','YYYY-MM-DD'))
  )
 /

SQL> insert into oe.ORDERS2
select rownum,'anbob'||rownum,to_date('2010/1/1','yyyy/mm/dd')  from dual connect by rownum<=40; 
40 rows created. 

SQL> BEGIN
 dbms_stats.gather_table_stats('OE','ORDERS2');
END;
/  
PL/SQL procedure successfully completed.

SQL> SELECT table_name, partition_name,LAST_ANALYZED, num_rows, sample_size
FROM dba_tab_statistics
WHERE table_name = 'ORDERS2'
and owner='OE'; 

TABLE_NAME                     PARTITION_NAME                 LAST_ANALYZED         NUM_ROWS SAMPLE_SIZE
------------------------------ ------------------------------ ------------------- ---------- -----------
ORDERS2                                                       2024-07-16 17:28:06         40          40
ORDERS2                        ORDERS_APR_2007                2024-07-16 17:28:06          0
ORDERS2                        ORDERS_APR_2008                2024-07-16 17:28:06          0
...
ORDERS2                        ORDERS_JAN_2008                2024-07-16 17:28:06          0
ORDERS2                        ORDERS_JAN_2009                2024-07-16 17:28:06          0
ORDERS2                        ORDERS_JAN_2010                2024-07-16 17:28:06         40          40
ORDERS2                        ORDERS_JAN_2011                2024-07-16 17:28:06          0
ORDERS2                        ORDERS_JAN_2012                2024-07-16 17:28:06          0
ORDERS2                        ORDERS_JUL_2007                2024-07-16 17:28:06          0
...

72 rows selected.

SQL>  insert into oe.ORDERS2
select rownum,'anbob'||rownum,to_date('2010/1/1','yyyy/mm/dd')  from dual connect by rownum<=40;
 40 rows created. 

SQL> commit;
Commit complete.

SQL>  BEGIN
 dbms_stats.gather_table_stats('OE','ORDERS2');
END;
/   

PL/SQL procedure successfully completed.

SQL> SELECT table_name, partition_name,LAST_ANALYZED, num_rows, sample_size
FROM dba_tab_statistics
WHERE table_name = 'ORDERS2'
and owner='OE';   

TABLE_NAME                     PARTITION_NAME                 LAST_ANALYZED         NUM_ROWS SAMPLE_SIZE
------------------------------ ------------------------------ ------------------- ---------- -----------
ORDERS2                                                       2024-07-16 17:29:59         80          80
ORDERS2                        ORDERS_APR_2007                2024-07-16 17:29:59          0
ORDERS2                        ORDERS_APR_2008                2024-07-16 17:29:59          0
...
ORDERS2                        ORDERS_JAN_2008                2024-07-16 17:29:59          0
ORDERS2                        ORDERS_JAN_2009                2024-07-16 17:29:59          0
ORDERS2                        ORDERS_JAN_2010                2024-07-16 17:29:59         80          80
ORDERS2                        ORDERS_JAN_2011                2024-07-16 17:29:59          0
ORDERS2                        ORDERS_JAN_2012                2024-07-16 17:29:59          0
ORDERS2                        ORDERS_JUL_2007                2024-07-16 17:29:59          0

...


# 1,启用表级增量统计信息
SQL>  exec dbms_stats.set_table_prefs('OE','ORDERS2','INCREMENTAL','TRUE');
PL/SQL procedure successfully completed.

SQL> insert into oe.ORDERS2
select rownum,'anbob'||rownum,to_date('2010/1/1','yyyy/mm/dd')  from dual connect by rownum<=40; 

SQL> commit;
Commit complete.

SQL> BEGIN
 dbms_stats.gather_table_stats('OE','ORDERS2');
END;
/     
SQL> SELECT table_name, partition_name,LAST_ANALYZED, num_rows, sample_size
FROM dba_tab_statistics
WHERE table_name = 'ORDERS2'
and owner='OE';      

TABLE_NAME                     PARTITION_NAME                 LAST_ANALYZED         NUM_ROWS SAMPLE_SIZE
------------------------------ ------------------------------ ------------------- ---------- -----------
ORDERS2                                                       2024-07-16 17:31:35        120         120
ORDERS2                        ORDERS_APR_2007                2024-07-16 17:31:35          0
ORDERS2                        ORDERS_APR_2008                2024-07-16 17:31:35          0
...
ORDERS2                        ORDERS_JAN_2009                2024-07-16 17:31:35          0
ORDERS2                        ORDERS_JAN_2010                2024-07-16 17:31:35        120         120
ORDERS2                        ORDERS_JAN_2011                2024-07-16 17:31:35          0
ORDERS2                        ORDERS_JAN_2012                2024-07-16 17:31:35          0
ORDERS2                        ORDERS_JUL_2007                2024-07-16 17:31:35          0
...


2, 启用全局统计信息增量收集
EXEC DBMS_STATS.SET_GLOBAL_PREFS('INCREMENTAL', 'TRUE');

SQL>  insert into oe.ORDERS2
select rownum,'anbob'||rownum,to_date('2010/1/1','yyyy/mm/dd')  from dual connect by rownum<=40; 
40 rows created. 

SQL>  BEGIN
 dbms_stats.gather_table_stats('OE','ORDERS2');
END;
/
PL/SQL procedure successfully completed.

SQL>
SQL> SELECT table_name, partition_name,LAST_ANALYZED, num_rows, sample_size
FROM dba_tab_statistics
WHERE table_name = 'ORDERS2'
and owner='OE';   

TABLE_NAME                                                   PARTITION_NAME                 LAST_ANALYZED         NUM_ROWS SAMPLE_SIZE
------------------------------------------------------------ ------------------------------ ------------------- ---------- -----------
ORDERS2                                                                                     2024-07-16 21:38:14        160         160
ORDERS2                                                      ORDERS_APR_2007                2024-07-16 17:31:35          0
...
ORDERS2                                                      ORDERS_JAN_2008                2024-07-16 17:31:35          0
ORDERS2                                                      ORDERS_JAN_2009                2024-07-16 17:31:35          0
ORDERS2                                                      ORDERS_JAN_2010                2024-07-16 21:38:14        160         160
ORDERS2                                                      ORDERS_JAN_2011                2024-07-16 17:31:35          0


# 确保使用增量统计信息的合适算法(19c)
在 Oracle 12c 及更高版本中,不需要配置SET_GLOBAL_PREFS

exec dbms_stats.set_database_prefs('incremental_staleness','USE_STALE_PERCENT');
exec dbms_stats.set_database_prefs('STALE_PERCENT','15'); 

BEGIN 
dbms_stats.gather_table_stats('OE','ORDERS2'); 
END; 
/ 

SQL> update oe.orders2 partition (ORDERS_FEB_2010) set name='weejar111' WHERE ID=40;
1 row updated.

SQL> COMMIT;

BEGIN
dbms_stats.gather_table_stats('OE','ORDERS2');
END;
/

SELECT table_name, to_char(last_analyzed, 'DD-MON-YYYY, HH24:MI:SS') last_analyze
FROM all_tables
WHERE table_name='ORDERS2';

TABLE_NAME LAST_ANALYZE
----------------------------- ------------------------
ORDERS2 12-JUL-2024, 12:41:09

SELECT partition_name, to_char(last_analyzed, 'DD-MON-YYYY, HH24:MI:SS') last_analyze
FROM all_tab_partitions
WHERE table_name='ORDERS2'
ORDER BY partition_position;

PARTITION_NAME LAST_ANALYZE
------------------------------ ------------------------
...
ORDERS_SEP_2009 12-JUL-2024, 12:36:55
ORDERS_OCT_2009 12-JUL-2024, 12:36:55
ORDERS_NOV_2009 12-JUL-2024, 12:36:55
ORDERS_DEC_2009 12-JUL-2024, 12:36:55
ORDERS_JAN_2010 12-JUL-2024, 12:38:21
ORDERS_FEB_2010 12-JUL-2024, 12:41:09
ORDERS_MAR_2010 12-JUL-2024, 12:36:55
ORDERS_APR_2010 12-JUL-2024, 12:36:55
ORDERS_MAY_2010 12-JUL-2024, 12:36:55
ORDERS_JUN_2010 12-JUL-2024, 12:36:55
...

Note:
仅更新了数据变更的分区级统计信息和全局表级统计信息。

配置增量统计信息与采样 –11g

SQL> exec dbms_stats.set_table_prefs('OE','ORDERS2','estimate_percent','1');

PL/SQL procedure successfully completed.

SQL>  BEGIN
 dbms_stats.gather_table_stats('OE','ORDERS2');
END;
/       2    3    4

PL/SQL procedure successfully completed.

SQL>  SELECT table_name, partition_name,LAST_ANALYZED, num_rows, sample_size
FROM dba_tab_statistics
WHERE table_name = 'ORDERS2'
and owner='OE';      2    3    4

TABLE_NAME                                                   PARTITION_NAME                 LAST_ANALYZED         NUM_ROWS SAMPLE_SIZE
------------------------------------------------------------ ------------------------------ ------------------- ---------- -----------
ORDERS2                                                                                     2024-07-16 21:44:37        160         160
ORDERS2                                                      ORDERS_APR_2007                2024-07-16 21:44:37          0
ORDERS2                                                      ORDERS_APR_2008                2024-07-16 21:44:37          0
...
ORDERS2                                                      ORDERS_FEB_2011                2024-07-16 21:44:37          0
ORDERS2                                                      ORDERS_FEB_2012                2024-07-16 21:44:37          0
ORDERS2                                                      ORDERS_JAN_2008                2024-07-16 21:44:37          0
ORDERS2                                                      ORDERS_JAN_2009                2024-07-16 21:44:37          0
ORDERS2                                                      ORDERS_JAN_2010                2024-07-16 21:44:37        160         160
ORDERS2                                                      ORDERS_JAN_2011                2024-07-16 21:44:37          0
...


SQL> alter table oe.ORDERS2 modify name varchar2(50);
Table altered.

SQL> insert into oe.ORDERS2  SELECT rownum,'anbob'||rownum,to_date('2010/1/1','yyyy/mm/dd')  from xmltable('1 to 4000000');
4000000 rows created.

SQL> commit;
Commit complete.

SQL> BEGIN
 dbms_stats.gather_table_stats('OE','ORDERS2');
END;
/       2    3    4

PL/SQL procedure successfully completed.

SQL> SELECT table_name, partition_name,LAST_ANALYZED, num_rows, sample_size
FROM dba_tab_statistics
WHERE table_name = 'ORDERS2'
and owner='OE';   2    3    4

TABLE_NAME                                                   PARTITION_NAME                 LAST_ANALYZED         NUM_ROWS SAMPLE_SIZE
------------------------------------------------------------ ------------------------------ ------------------- ---------- -----------
ORDERS2                                                                                     2024-07-16 21:48:40    3979800       39798
ORDERS2                                                      ORDERS_APR_2007                2024-07-16 21:48:39          0
ORDERS2                                                      ORDERS_APR_2008                2024-07-16 21:48:39          0
ORDERS2                                                      ORDERS_APR_2009                2024-07-16 21:48:39          0
ORDERS2                                                      ORDERS_APR_2010                2024-07-16 21:48:39          0
ORDERS2                                                      ORDERS_APR_2011                2024-07-16 21:48:39          0
ORDERS2                                                      ORDERS_APR_2012                2024-07-16 21:48:39          0
ORDERS2                                                      ORDERS_AUG_2007                2024-07-16 21:48:39          0
ORDERS2                                                      ORDERS_AUG_2008                2024-07-16 21:48:39          0
ORDERS2                                                      ORDERS_AUG_2009                2024-07-16 21:48:39          0
...
...
ORDERS2                                                      ORDERS_JAN_2008                2024-07-16 21:48:39          0
ORDERS2                                                      ORDERS_JAN_2009                2024-07-16 21:48:39          0
ORDERS2                                                      ORDERS_JAN_2010                2024-07-16 21:48:39    4017500       40175
ORDERS2                                                      ORDERS_JAN_2011                2024-07-16 21:48:39          0
ORDERS2                                                      ORDERS_JAN_2012                2024-07-16 21:48:39          0
ORDERS2                                                      ORDERS_JUL_2007                2024-07-16 21:48:39          0


SQL>  update oe.orders2 partition (ORDERS_JAN_2010) set name='weejar111' WHERE ID=40;
5 rows updated.

SQL>  BEGIN
 dbms_stats.gather_table_stats('OE','ORDERS2');
END;
/    2    3    4

PL/SQL procedure successfully completed.

SQL>  SELECT table_name, partition_name,LAST_ANALYZED, num_rows, sample_size
FROM dba_tab_statistics
WHERE table_name = 'ORDERS2'
and owner='OE';  2    3    4

TABLE_NAME                                                   PARTITION_NAME                 LAST_ANALYZED         NUM_ROWS SAMPLE_SIZE
------------------------------------------------------------ ------------------------------ ------------------- ---------- -----------
ORDERS2                                                                                     2024-07-16 21:53:04    3983200       39832
ORDERS2                                                      ORDERS_APR_2007                2024-07-16 21:53:03          0
ORDERS2                                                      ORDERS_APR_2008                2024-07-16 21:53:03          0
ORDERS2                                                      ORDERS_APR_2009                2024-07-16 21:53:03          0
ORDERS2                                                      ORDERS_APR_2010                2024-07-16 21:53:03          0
ORDERS2                                                      ORDERS_APR_2011                2024-07-16 21:53:03          0
ORDERS2                                                      ORDERS_APR_2012                2024-07-16 21:53:03          0
...
ORDERS2                                                      ORDERS_JAN_2008                2024-07-16 21:53:03          0
ORDERS2                                                      ORDERS_JAN_2009                2024-07-16 21:53:03          0
ORDERS2                                                      ORDERS_JAN_2010                2024-07-16 21:53:04    3971000       39710
ORDERS2                                                      ORDERS_JAN_2011                2024-07-16 21:53:04          0
...

SQL>  exec dbms_stats.set_table_prefs('OE','ORDERS2','INCREMENTAL','TRUE');
PL/SQL procedure successfully completed.

SQL> update oe.orders2 partition (ORDERS_JAN_2010) set name='weejar122' WHERE ID=40;
5 rows updated.


SQL> BEGIN
 dbms_stats.gather_table_stats('OE','ORDERS2');
END;
/   

SQL> SELECT table_name, partition_name,LAST_ANALYZED, num_rows, sample_size
FROM dba_tab_statistics
WHERE table_name = 'ORDERS2'
and owner='OE';  2    3    4

TABLE_NAME                                                   PARTITION_NAME                 LAST_ANALYZED         NUM_ROWS SAMPLE_SIZE
------------------------------------------------------------ ------------------------------ ------------------- ---------- -----------
ORDERS2                                                                                     2024-07-16 21:55:47    3975300       39753
ORDERS2                                                      ORDERS_APR_2007                2024-07-16 21:55:46          0
ORDERS2                                                      ORDERS_APR_2008                2024-07-16 21:55:46          0
ORDERS2                                                      ORDERS_APR_2009                2024-07-16 21:55:46          0
ORDERS2                                                      ORDERS_APR_2010                2024-07-16 21:55:46          0
ORDERS2                                                      ORDERS_APR_2011                2024-07-16 21:55:46          0
...
ORDERS2                                                      ORDERS_JAN_2008                2024-07-16 21:55:46          0
ORDERS2                                                      ORDERS_JAN_2009                2024-07-16 21:55:46          0
ORDERS2                                                      ORDERS_JAN_2010                2024-07-16 21:55:46    3964600       39646



SQL>  exec dbms_stats.set_table_prefs('OE','ORDERS2','estimate_percent',DBMS_STATS.AUTO_SAMPLE_SIZE);
PL/SQL procedure successfully completed.

SQL>  update oe.orders2 partition (ORDERS_JAN_2010) set name='weejar122' WHERE ID=40;
5 rows updated.

SQL> BEGIN
 dbms_stats.gather_table_stats('OE','ORDERS2');
END;
/   2    3    4

PL/SQL procedure successfully completed.

SQL> SELECT table_name, partition_name,LAST_ANALYZED, num_rows, sample_size
FROM dba_tab_statistics
WHERE table_name = 'ORDERS2'
and owner='OE';   2    3    4

TABLE_NAME                                                   PARTITION_NAME                 LAST_ANALYZED         NUM_ROWS SAMPLE_SIZE
------------------------------------------------------------ ------------------------------ ------------------- ---------- -----------
ORDERS2                                                                                     2024-07-16 21:57:50    4000160     4000160
ORDERS2                                                      ORDERS_APR_2007                2024-07-16 21:57:50          0
...
ORDERS2                                                      ORDERS_FEB_2009                2024-07-16 21:57:49          0
ORDERS2                                                      ORDERS_FEB_2010                2024-07-16 21:57:49          0
ORDERS2                                                      ORDERS_FEB_2011                2024-07-16 21:57:50          0
ORDERS2                                                      ORDERS_FEB_2012                2024-07-16 21:57:50          0
ORDERS2                                                      ORDERS_JAN_2008                2024-07-16 21:57:50          0
ORDERS2                                                      ORDERS_JAN_2009                2024-07-16 21:57:49          0
ORDERS2                                                      ORDERS_JAN_2010                2024-07-16 21:57:50    4000160     4000160
ORDERS2                                                      ORDERS_JAN_2011                2024-07-16 21:57:50          0
...
SQL>  update oe.orders2 partition (ORDERS_JAN_2010) set name='weejar122' WHERE ID=40;
5 rows updated.

SQL> commit;
Commit complete.

SQL>  BEGIN
 dbms_stats.gather_table_stats('OE','ORDERS2');
END;
/   
PL/SQL procedure successfully completed.

SQL> SELECT table_name, partition_name,LAST_ANALYZED, num_rows, sample_size
FROM dba_tab_statistics
WHERE table_name = 'ORDERS2'
and owner='OE';   2    3    4

TABLE_NAME                                                   PARTITION_NAME                 LAST_ANALYZED         NUM_ROWS SAMPLE_SIZE
------------------------------------------------------------ ------------------------------ ------------------- ---------- -----------
ORDERS2                                                                                     2024-07-16 21:58:39    4000160     4000160
ORDERS2                                                      ORDERS_APR_2007                2024-07-16 21:57:50          0
ORDERS2                                                      ORDERS_APR_2008                2024-07-16 21:57:49          0
ORDERS2                                                      ORDERS_APR_2009                2024-07-16 21:57:49          0
...
ORDERS2                                                      ORDERS_JAN_2008                2024-07-16 21:57:50          0
ORDERS2                                                      ORDERS_JAN_2009                2024-07-16 21:57:49          0
ORDERS2                                                      ORDERS_JAN_2010                2024-07-16 21:58:39    4000160     4000160
ORDERS2                                                      ORDERS_JAN_2011                2024-07-16 21:57:50          0
ORDERS2                                                      ORDERS_JAN_2012                2024-07-16 21:57:50          0


Note:
注意在160行时sample_size 还是100%, 尝试更多的数据后,统计信息使用了全量统计信息但使用了小的采样比例, 再次执行启动表级增量,但依旧是全量加小采样率;
修改采样比例为自动后,第一次是全量统计信息和100%采样第二次开始变成了增量统计信息,但依旧是100%。

当使用INCREMENTAL => TRUE 将忽略 ESTIMATE_PERCENT ,当使用小ESTIMATE_PERCENT时,又忽略INCREMENTAL => TRUE ,也就是增量统计不能与 ESTIMATE_PERCENT 结合使用。当使用INCREMENTAL 统计信息时,建议您使用默认的 ESTIMATE_PERCENT或将其明确设置为 DBMS_STATS.AUTO_SAMPLE_SIZE。

造记录的效率

SQL> insert into oe.ORDERS2 select rownum,'anbob'||rownum,to_date('2010/1/1','yyyy/mm/dd')  from dual connect by rownum<=4000000;
insert into oe.ORDERS2 select rownum,'anbob'||rownum,to_date('2010/1/1','yyyy/mm/dd')  from dual connect by rownum<=4000000
               *
ERROR at line 1:
ORA-30009: Not enough memory for CONNECT BY operation


SQL> insert into oe.ORDERS2  SELECT rownum,'anbob'||rownum,to_date('2010/1/1','yyyy/mm/dd')  from xmltable('1 to 4000000');
4000000 rows created.

Note:
当造几百万记录时,使用xmltype比connect递归节约资源。

总结

MOS在 10.2 和 11 版本中收集大型分区表的增量统计信息”(文档 ID 1319225.1)和 Oracle 11.2 文档的“ Oracle 数据库 PL/SQL 包和类型参考”描述了增量统计信息的以下先决条件:

  • 分区表的 INCREMENTAL 值为真。
  • 分区表的 PUBLISH 值为 true。
  • 用户在收集表上的统计信息时,为 ESTIMATE_PERCENT 指定 AUTO_SAMPLE_SIZE,为 GRANULARITY 指定 AUTO。

换句话说:我们必须决定是使用小样本量(这是许多 DWH 环境中唯一可行的选项)还是使用增量统计。两者同时指定是排斥的。

打赏

,

对不起,这篇文章暂时关闭评论。