设置统计信息参数介绍
在Oracle 11g或更高版本中，修改用于收集统计信息的参数的默认值主要有4个存储过程:
在DBMS_STATS包下的如下4个Procedure：
11g官方文档：DBMS_STATS (oracle.com)
-
SET_GLOBAL_PREFS
此存储过程用于设置全局统计信息参数首选项。 -
SET_DATABASE_PREFS
此存储过程用于设置所有现存表(不包括Oracle内置用户表)的统计信息收集参数首选项。通过为add_sys参数传递TRUE可以包含这些内置用户表。
删除对应 DELETE_DATABASE_PREFS -
SET_SCHEMA_PREFS
此存储过程用于设置指定schema拥有的所有表的统计信息收集参数首选项。
删除对应 DELETE_SCHEMA_PREFS -
SET_TABLE_PREFS
此存储过程用于设置指定table的统计信息收集参数首选项。
删除对应 DELETE_TABLE_PREFS
主要包含如下参数：
AUTOSTATS_TARGET
CASCADE
DEGREE
ESTIMATE_PERCENT
METHOD_OPT
NO_INVALIDATE
GRANULARITY
PUBLISH
INCREMENTAL
STALE_PERCENT
实际测试（11.2.0.4）：
修改全局统计信息参数
结论：设置全局统计信息参数，所有表都会生效，即使是新建的表。
SQL> exec DBMS_STATS.SET_GLOBAL_PREFS('ESTIMATE_PERCENT','5');
PL/SQL procedure successfully completed.
SQL> SELECT DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT') FROM DUAL;
DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT')
--------------------------------------------------
5
SQL> SELECT DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT'，'SYS') FROM DUAL;
DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT'，'SYS')
--------------------------------------------------
5
SQL> SELECT DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT'，'SYS','T1') FROM DUAL;
DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT'，'SYS','T1
--------------------------------------------------
5
SQL> SELECT DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT'，'DHW','T1') FROM DUAL;
DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT'，'DHW','T1
--------------------------------------------------
5
SQL> create table t3 as select * from dba_tables ;
Table created.
SQL> SELECT DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT'，'SYS','T3') FROM DUAL;
DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT'，'SYS','T3
--------------------------------------------------
5
修改数据库统计信息参数
结论：修改数据库统计信息参数，默认Oracle内置用户表不修改（因为默认没有指定add_sys=>TRUE参数），当前业务表都会随之修改。新对象将对所有参数使用GLOBAL_PREF值。
SQL> exec DBMS_STATS.SET_DATABASE_PREFS('ESTIMATE_PERCENT','10');
PL/SQL procedure successfully completed.
SQL> SELECT DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT') FROM DUAL;
DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT')
--------------------------------------------------
5
SQL> SELECT DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT'，'SYS') FROM DUAL;
DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT'，'SYS')
--------------------------------------------------
5
SQL> SELECT DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT'，'SYS','T1') FROM DUAL;
DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT'，'SYS','T1
--------------------------------------------------
5
SQL> SELECT DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT'，'DHW','T1') FROM DUAL;
DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT'，'DHW','T1
--------------------------------------------------
10.000000
SQL> SELECT DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT'，'DHW') FROM DUAL;
DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT'，'DHW')
--------------------------------------------------
5
SQL> SELECT DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT'，'DHW','T2' ) FROM DUAL;
DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT'，'DHW','T2
--------------------------------------------------
10.000000
SQL> create table dhw.t4 as select * from dba_objects;
Table created.
SQL> SELECT DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT'，'DHW','T4' ) FROM DUAL;
DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT'，'DHW','T4
--------------------------------------------------
5
SQL> select * from dba_tab_stat_prefs;
OWNER TABLE_NAME PREFERENCE_NAME PREFERENCE_VALUE
---------- -------------------- ------------------------------ ------------------------------
SCOTT USLOG$_MV_TESTD ESTIMATE_PERCENT 10.000000
SCOTT MV_TESTD ESTIMATE_PERCENT 10.000000
SCOTT RUPD$_TESTD ESTIMATE_PERCENT 10.000000
SCOTT MLOG$_TESTD ESTIMATE_PERCENT 10.000000
SCOTT TESTD ESTIMATE_PERCENT 10.000000
SCOTT BONUS ESTIMATE_PERCENT 10.000000
SCOTT EMP ESTIMATE_PERCENT 10.000000
SCOTT DEPT ESTIMATE_PERCENT 10.000000
SCOTT SALGRADE ESTIMATE_PERCENT 10.000000
DHW T3 ESTIMATE_PERCENT 10.000000
DHW T2 ESTIMATE_PERCENT 10.000000
DHW T1 ESTIMATE_PERCENT 10.000000
12 rows selected.
修改schema统计信息参数
结论：修改schema统计信息参数，只对业务schema生效，内置sys用户测试修改不生效。并且修改后需要指定表名查询进行验证，或者dba_tab_stat_prefs表验证。
这样做的原因是由于DBMS_STATS.SET_SCHEMA_PREFS将更改用于收集指定schema中所有现有对象的统计信息的参数的默认值。这个过程实际上为指定schema中的每个表调用SET_TABLE_PREFS。调用SET_SCHEMA_PREFS不会影响它运行后创建的任何新对象，新对象将对所有参数使用GLOBAL_PREF值。
因此，在不包含表名的特定模式下查询GET_PREFS将返回默认值。
SQL> exec DBMS_STATS.SET_SCHEMA_PREFS('SYS','ESTIMATE_PERCENT','15');
PL/SQL procedure successfully completed.
SQL> exec DBMS_STATS.SET_SCHEMA_PREFS('DHW','ESTIMATE_PERCENT','15');
PL/SQL procedure successfully completed.
SQL> SELECT DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT'，'SYS') FROM DUAL;
DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT'，'SYS')
------------------------------------------------------------
5
SQL> SELECT DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT'，'DHW') FROM DUAL;
DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT'，'DHW')
------------------------------------------------------------
5
SQL> SELECT DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT'，'SYS','T1') FROM DUAL;
DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT'，'SYS','T1')
------------------------------------------------------------
5
SQL> SELECT DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT'，'DHW','T1') FROM DUAL;
DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT'，'DHW','T1')
------------------------------------------------------------
15.000000
SQL> select * from dba_tab_stat_prefs;
OWNER TABLE_NAME PREFERENCE_NAME PREFERENCE_VALUE
---------- -------------------- ------------------------------ ------------------------------
SCOTT USLOG$_MV_TESTD ESTIMATE_PERCENT 10.000000
SCOTT MV_TESTD ESTIMATE_PERCENT 10.000000
SCOTT RUPD$_TESTD ESTIMATE_PERCENT 10.000000
SCOTT MLOG$_TESTD ESTIMATE_PERCENT 10.000000
SCOTT TESTD ESTIMATE_PERCENT 10.000000
SCOTT BONUS ESTIMATE_PERCENT 10.000000
SCOTT EMP ESTIMATE_PERCENT 10.000000
SCOTT DEPT ESTIMATE_PERCENT 10.000000
SCOTT SALGRADE ESTIMATE_PERCENT 10.000000
DHW T4 ESTIMATE_PERCENT 15.000000
DHW T3 ESTIMATE_PERCENT 15.000000
DHW T2 ESTIMATE_PERCENT 15.000000
DHW T1 ESTIMATE_PERCENT 15.000000
13 rows selected.
修改table统计信息参数
结论：修改table统计信息参数针对指定的表生效。
SQL> exec DBMS_STATS.SET_TABLE_PREFS('SYS','T1','ESTIMATE_PERCENT','20');
PL/SQL procedure successfully completed.
SQL> exec DBMS_STATS.SET_TABLE_PREFS('DHW','T1','ESTIMATE_PERCENT','20');
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT'，'SYS','T1') FROM DUAL;
DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT'，'SYS','T1
--------------------------------------------------
20.000000
SQL> SELECT DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT'，'DHW','T1') FROM DUAL;
DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT'，'DHW','T1
--------------------------------------------------
20.000000
SQL> select * from dba_tab_stat_prefs;
OWNER TABLE_NAME PREFERENCE_NAME PREFERENCE_VALUE
---------- -------------------- ------------------------------ ------------------------------
SYS T1 ESTIMATE_PERCENT 20.000000
SCOTT USLOG$_MV_TESTD ESTIMATE_PERCENT 10.000000
SCOTT MV_TESTD ESTIMATE_PERCENT 10.000000
SCOTT RUPD$_TESTD ESTIMATE_PERCENT 10.000000
SCOTT MLOG$_TESTD ESTIMATE_PERCENT 10.000000
SCOTT TESTD ESTIMATE_PERCENT 10.000000
SCOTT BONUS ESTIMATE_PERCENT 10.000000
SCOTT EMP ESTIMATE_PERCENT 10.000000
SCOTT DEPT ESTIMATE_PERCENT 10.000000
SCOTT SALGRADE ESTIMATE_PERCENT 10.000000
DHW T4 ESTIMATE_PERCENT 15.000000
DHW T3 ESTIMATE_PERCENT 15.000000
DHW T2 ESTIMATE_PERCENT 15.000000
DHW T1 ESTIMATE_PERCENT 20.000000
14 rows selected.
回退统计信息配置参数
结论：全局统计信息参数指定参数为NULL回退；数据库，schema，table的参数需要通过对应delete_*_prefs存储过程进行删除。DELETE_DATABASE_PREFS会删除所有自建用户对象的统计信息收集参数。
SQL> exec DBMS_STATS.SET_GLOBAL_PREFS('ESTIMATE_PERCENT',NULL);
PL/SQL procedure successfully completed.
SQL> exec DBMS_STATS.DELETE_DATABASE_PREFS('ESTIMATE_PERCENT');
PL/SQL procedure successfully completed.
SQL> select * from dba_tab_stat_prefs;
OWNER TABLE_NAME PREFERENCE_NAME PREFERENCE_VALUE
---------- -------------------- ------------------------------ ------------------------------
SYS T1 ESTIMATE_PERCENT 20.000000
SQL> exec DBMS_STATS.DELETE_TABLE_PREFS('SYS','T1','ESTIMATE_PERCENT');
PL/SQL procedure successfully completed.
SQL> select * from dba_tab_stat_prefs;
no rows selected
最后修改时间：2022-11-04 15:11:19
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
