首页 » 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 环境中唯一可行的选项）还是使用增量统计。两者同时指定是排斥的。
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。