[20130502]dbms_stats缺省参数.txt
今天抽空看了dbms_stats缺省参数，我的测试环境是11G。
column cascade format a30
column degree format a10
column estimate_percent format a30
column method_opt format a30
column no_invalidate format a30
column granularity format a10
column publish format a10
column INCREMENTAL format a10
column STALE_PERCENT format a10
column AUTOSTATS_TARGET format a10
SELECT DBMS_STATS.get_param ('CASCADE') CASCADE, DBMS_STATS.get_param ('DEGREE') DEGREE,
DBMS_STATS.get_param ('ESTIMATE_PERCENT') estimate_percent, DBMS_STATS.get_param ('METHOD_OPT') method_opt,
DBMS_STATS.get_param ('NO_INVALIDATE') no_invalidate, DBMS_STATS.get_param ('GRANULARITY') granularity,
DBMS_STATS.get_param ('PUBLISH') publish, DBMS_STATS.get_param ('INCREMENTAL') incremental,
DBMS_STATS.get_param ('STALE_PERCENT') stale_percent, DBMS_STATS.get_param ('AUTOSTATS_TARGET') autostats_target
FROM DUAL;
CASCADE DEGREE ESTIMATE_PERCENT METHOD_OPT NO_INVALIDATE GRANULARIT PUBLISH INCREMENTA STALE_PERC AUTOSTATS_
------------------------------ ---------- ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ----------
DBMS_STATS.AUTO_CASCADE NULL DBMS_STATS.AUTO_SAMPLE_SIZE FOR ALL COLUMNS SIZE AUTO DBMS_STATS.AUTO_INVALIDATE AUTO TRUE FALSE 10 AUTO
11G建立使用dbms_stats.GET_PREFS()代替。
SELECT DBMS_STATS.get_prefs ('CASCADE') CASCADE, DBMS_STATS.get_prefs ('DEGREE') DEGREE,
DBMS_STATS.get_prefs ('ESTIMATE_PERCENT') estimate_percent, DBMS_STATS.get_prefs ('METHOD_OPT') method_opt,
DBMS_STATS.get_prefs ('NO_INVALIDATE') no_invalidate, DBMS_STATS.get_prefs ('GRANULARITY') granularity,
DBMS_STATS.get_prefs ('PUBLISH') publish, DBMS_STATS.get_prefs ('INCREMENTAL') incremental,
DBMS_STATS.get_prefs ('STALE_PERCENT') stale_percent, DBMS_STATS.get_prefs ('AUTOSTATS_TARGET') autostats_target
FROM DUAL;
CASCADE DEGREE ESTIMATE_PERCENT METHOD_OPT NO_INVALIDATE GRANULARIT PUBLISH INCREMENTA STALE_PERC AUTOSTATS_
------------------------------ ---------- ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ----------
DBMS_STATS.AUTO_CASCADE NULL DBMS_STATS.AUTO_SAMPLE_SIZE FOR ALL COLUMNS SIZE AUTO DBMS_STATS.AUTO_INVALIDATE AUTO TRUE FALSE 10 AUTO
1.修改某个缺省参数：
SQL> exec DBMS_STATS.set_param('METHOD_OPT','FOR ALL COLUMNS SIZE REPEAT');
PL/SQL procedure successfully completed.
--注意：11G建议使用 dbms_stats.SET_GLOBAL_PREFS() 代替。
SELECT DBMS_STATS.get_prefs ('CASCADE') CASCADE, DBMS_STATS.get_prefs ('DEGREE') DEGREE,
DBMS_STATS.get_prefs ('ESTIMATE_PERCENT') estimate_percent, DBMS_STATS.get_prefs ('METHOD_OPT') method_opt,
DBMS_STATS.get_prefs ('NO_INVALIDATE') no_invalidate, DBMS_STATS.get_prefs ('GRANULARITY') granularity,
DBMS_STATS.get_prefs ('PUBLISH') publish, DBMS_STATS.get_prefs ('INCREMENTAL') incremental,
DBMS_STATS.get_prefs ('STALE_PERCENT') stale_percent, DBMS_STATS.get_prefs ('AUTOSTATS_TARGET') autostats_target
FROM DUAL;
CASCADE DEGREE ESTIMATE_PERCENT METHOD_OPT NO_INVALIDATE GRANULARIT PUBLISH INCREMENTA STALE_PERC AUTOSTATS_
------------------------------ ---------- ------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ----------
DBMS_STATS.AUTO_CASCADE NULL DBMS_STATS.AUTO_SAMPLE_SIZE FOR ALL COLUMNS SIZE REPEAT DBMS_STATS.AUTO_INVALIDATE AUTO TRUE FALSE 10 AUTO
--如果跟踪就可以发现，这些信息保存在SYS.OPTSTAT_HIST_CONTROL$表中。
SQL> column spare2 noprint
SQL> column spare3 noprint
SQL> column spare5 noprint
SQL> column spare6 noprint
SQL> column spare1 format 9999
SQL> column spare4 format a30
SQL> column SNAME format a20
SQL> column SVAL2 format a30
SQL> select * FROM SYS.OPTSTAT_HIST_CONTROL$;
SNAME SVAL1 SVAL2 SPARE1 SPARE4
-------------------- ---------- ------------------------------ ------ ------------------------------
SKIP_TIME 2013-04-01 09:26:33.144318
STATS_RETENTION 31 2013-03-21 10:25:37.390083 1
TRACE 2013-03-21 10:25:37.390083 1 0
DEBUG 2013-03-21 10:25:37.390083 1 0
SYS_FLAGS 2011-11-22 22:00:02.623095 1
APPROXIMATE_NDV 2013-03-21 10:25:37.390083 1 TRUE
CASCADE 2013-03-21 10:25:37.390083 1 DBMS_STATS.AUTO_CASCADE
ESTIMATE_PERCENT 2013-03-21 10:25:37.390083 1 DBMS_STATS.AUTO_SAMPLE_SIZE
DEGREE 2013-03-21 10:25:37.390083 1 NULL
METHOD_OPT 2013-05-02 16:48:08.653128 FOR ALL COLUMNS SIZE REPEAT
NO_INVALIDATE 2013-03-21 10:25:37.390083 1 DBMS_STATS.AUTO_INVALIDATE
GRANULARITY 2013-03-21 10:25:37.390083 1 AUTO
PUBLISH 2013-03-21 10:25:37.390083 1 TRUE
STALE_PERCENT 2013-03-21 10:25:37.390083 1 10
INCREMENTAL 2013-03-21 10:25:37.390083 1 FALSE
INCREMENTAL_INTERNAL 2013-03-21 10:25:37.390083 1 TRUE
_CONTROL
AUTOSTATS_TARGET 2013-03-21 10:25:37.390083 1 AUTO
CONCURRENT 2013-03-21 10:25:37.390083 1 FALSE
18 rows selected.
--注意SNAME=METHOD_OPT这行，spare1不等于1，估计表示不是缺省参数。修改回来：
SQL> exec DBMS_STATS.set_param('METHOD_OPT','FOR ALL COLUMNS SIZE AUTO');
PL/SQL procedure successfully completed.
SQL> select * FROM SYS.OPTSTAT_HIST_CONTROL$;
SNAME SVAL1 SVAL2 SPARE1 SPARE4
-------------------- ---------- ------------------------------ ------ ------------------------------
SKIP_TIME 2013-04-01 09:26:33.144318
STATS_RETENTION 31 2013-03-21 10:25:37.390083 1
TRACE 2013-03-21 10:25:37.390083 1 0
DEBUG 2013-03-21 10:25:37.390083 1 0
SYS_FLAGS 2011-11-22 22:00:02.623095 1
APPROXIMATE_NDV 2013-03-21 10:25:37.390083 1 TRUE
CASCADE 2013-03-21 10:25:37.390083 1 DBMS_STATS.AUTO_CASCADE
ESTIMATE_PERCENT 2013-03-21 10:25:37.390083 1 DBMS_STATS.AUTO_SAMPLE_SIZE
DEGREE 2013-03-21 10:25:37.390083 1 NULL
METHOD_OPT 2013-05-02 17:00:14.266331 FOR ALL COLUMNS SIZE AUTO
NO_INVALIDATE 2013-03-21 10:25:37.390083 1 DBMS_STATS.AUTO_INVALIDATE
GRANULARITY 2013-03-21 10:25:37.390083 1 AUTO
PUBLISH 2013-03-21 10:25:37.390083 1 TRUE
STALE_PERCENT 2013-03-21 10:25:37.390083 1 10
INCREMENTAL 2013-03-21 10:25:37.390083 1 FALSE
INCREMENTAL_INTERNAL 2013-03-21 10:25:37.390083 1 TRUE
_CONTROL
AUTOSTATS_TARGET 2013-03-21 10:25:37.390083 1 AUTO
CONCURRENT 2013-03-21 10:25:37.390083 1 FALSE
18 rows selected.
--注意SNAME=METHOD_OPT这行，spare1不等于1，why？表示改动过吗？设置缺省参数呢？
exec DBMS_STATS.reset_param_defaults();
SQL> select * FROM SYS.OPTSTAT_HIST_CONTROL$;
SNAME SVAL1 SVAL2 SPARE1 SPARE4
-------------------- ---------- ------------------------------ ------ ------------------------------
SKIP_TIME 2013-04-01 09:26:33.144318
STATS_RETENTION 31 2013-03-21 10:25:37.390083 1
TRACE 2013-03-21 10:25:37.390083 1 0
DEBUG 2013-03-21 10:25:37.390083 1 0
SYS_FLAGS 2011-11-22 22:00:02.623095 1
APPROXIMATE_NDV 2013-05-02 17:04:44.913628 1 TRUE
CASCADE 2013-05-02 17:04:44.913628 1 DBMS_STATS.AUTO_CASCADE
ESTIMATE_PERCENT 2013-05-02 17:04:44.913628 1 DBMS_STATS.AUTO_SAMPLE_SIZE
DEGREE 2013-05-02 17:04:44.913628 1 NULL
METHOD_OPT 2013-05-02 17:04:44.913628 1 FOR ALL COLUMNS SIZE AUTO
NO_INVALIDATE 2013-05-02 17:04:44.913628 1 DBMS_STATS.AUTO_INVALIDATE
GRANULARITY 2013-05-02 17:04:44.913628 1 AUTO
PUBLISH 2013-05-02 17:04:44.913628 1 TRUE
STALE_PERCENT 2013-05-02 17:04:44.913628 1 10
INCREMENTAL 2013-05-02 17:04:44.913628 1 FALSE
INCREMENTAL_INTERNAL 2013-05-02 17:04:44.913628 1 TRUE
_CONTROL
AUTOSTATS_TARGET 2013-05-02 17:04:44.913628 1 AUTO
CONCURRENT 2013-05-02 17:04:44.913628 1 FALSE
18 rows selected.
--注意SNAME=METHOD_OPT这行，spare1等于1。
2.修改某个表的缺省参数。11G最大的改进在于它可以改变表各种参数，比如在表的那些字段建立直方图，设置STALE_PERCENT的百分比等等。
也可以改变一个schema的统计参数。dbms_stats.set_schema_prefs(),dbms_stats.set_table_prefs().
例子如下：
exec dbms_stats.set_table_prefs(user,'emp','STALE_PERCENT',5);
exec dbms_stats.set_table_prefs(user,'emp','METHOD_OPT','FOR ALL COLUMNS size 1 FOR columns job size 254 for columns deptno size 50');
exec dbms_stats.gather_table_stats(user, 'emp');
SQL> select column_name,num_buckets,histogram from dba_tab_cols where wner=user and table_name='EMP';
COLUMN_NAME NUM_BUCKETS HISTOGRAM
-------------------- ----------- ---------------
EMPNO 1 NONE
ENAME 1 NONE
JOB 5 FREQUENCY
MGR 1 NONE
HIREDATE 1 NONE
SAL 1 NONE
COMM 1 NONE
DEPTNO 4 FREQUENCY
8 rows selected.
--可以发现仅仅在job，deptno上建立直方图。
SQL> select dbms_stats.get_prefs('STALE_PERCENT',user,'EMP') from dual;
DBMS_STATS.GET_PREFS('STALE_PERCENT',USER,'EMP')
-----------------------------------------------------------------------
5
3.再来看看这些信息保存哪里？
alter system set events '10046 trace name context forever,level 12';
exec dbms_stats.set_table_prefs(user,'EMP','STALE_PERCENT',5);
select dbms_stats.get_prefs('STALE_PERCENT',user,'EMP') from dual;
alter system set events '10046 trace name context off';
--跟踪可以发现这些sql语句:
SELECT SPARE4 FROM SYS.OPTSTAT_HIST_CONTROL$ WHERE SNAME = :B1
SELECT P.VALCHAR FROM SYS.OPTSTAT_USER_PREFS$ P, OBJ$ O, USER$ U WHERE P.OBJ#=O.OBJ# AND U.USER#=O.OWNER# AND U.NAME=:B3 AND O.NAME=:B2 AND P.PNAME=:B1;
SELECT * FROM SYS.OPTSTAT_HIST_CONTROL$;
SELECT * from SYS.OPTSTAT_USER_PREFS$
column VALCHAR format a80
column CHGTIME format a30
SELECT * from SYS.OPTSTAT_USER_PREFS$ where chgtime>=trunc(systimestamp);
OBJ# PNAME VALNUM VALCHAR CHGTIME SPARE1
---------- ---------------- ---------- -------------------------------------------------------------------------------- ------------------------------ ------
73201 METHOD_OPT FOR ALL COLUMNS SIZE 1 FOR COLUMNS JOB SIZE 254 FOR COLUMNS DEPTNO SIZE 50 2013-05-02 17:22:53.516773
73201 STALE_PERCENT 5 2013-05-02 17:22:25.408668
SQL> select * from dba_objects where wner=user and object_name='EMP';
OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME
------ -------------------- ---------- ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------------------------
SCOTT EMP 73201 73201 TABLE 2009-08-15 00:50:14 2013-04-03 16:52:05 2012-06-14:16:04:42 VALID N N N 1
--可以发现一致。要删除exec dbms_stats.delete_table_prefs(user,'EMP','STALE_PERCENT');
4.总结：
可以发现11G在统计收集上更加灵活。
时间： 2024-08-24 04:24:18