[20151202]表统计信息stale百分比.txt
--昨天被别人问及一个问题缺省如果某个表修改信息超过10%，oracle即认为这个表需要重新统计分析。
--这个百分比如何计算的，实际上只要自己仔细观察就可以确定oracle如何算的。
1.环境：
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> column SPARE4 format a30
SCOTT@book> select sname, sval1,spare1, spare4 from sys.OPTSTAT_HIST_CONTROL$ order by sname;
SNAME SVAL1 SPARE1 SPARE4
------------------------------ ---------- ---------- ------------------------------
APPROXIMATE_NDV 1 TRUE
AUTOSTATS_TARGET 1 AUTO
CASCADE 1 DBMS_STATS.AUTO_CASCADE
CONCURRENT 1 FALSE
DEBUG 1 0
DEGREE 1 NULL
ESTIMATE_PERCENT 1 DBMS_STATS.AUTO_SAMPLE_SIZE
GRANULARITY 1 AUTO
INCREMENTAL 1 FALSE
INCREMENTAL_INTERNAL_CONTROL 1 TRUE
METHOD_OPT 1 FOR ALL COLUMNS SIZE AUTO
NO_INVALIDATE 1 DBMS_STATS.AUTO_INVALIDATE
PUBLISH 1 TRUE
SKIP_TIME
STALE_PERCENT 1 10
STATS_RETENTION 31 1
SYS_FLAGS 1
TABLE_CACHED_BLOCKS 1 1
TRACE 1 0
19 rows selected.
--可以发现缺省STALE_PERCENT=10.
SCOTT@book> create table empx as select * from emp ;
Table created.
SCOTT@book> exec dbms_stats.set_table_prefs('scott','EMPX','STALE_PERCENT',5);
PL/SQL procedure successfully completed.
--这样变化5%，就认为陈旧。
2.如何计算呢？
--实际上看看DBA_TAB_STATISTICS定义就很容易明白：
CREATE OR REPLACE FORCE VIEW SYS.DBA_TAB_STATISTICS
(
OWNER
,TABLE_NAME
,PARTITION_NAME
,PARTITION_POSITION
,SUBPARTITION_NAME
,SUBPARTITION_POSITION
,OBJECT_TYPE
,NUM_ROWS
,BLOCKS
,EMPTY_BLOCKS
,AVG_SPACE
,CHAIN_CNT
,AVG_ROW_LEN
,AVG_SPACE_FREELIST_BLOCKS
,NUM_FREELIST_BLOCKS
,AVG_CACHED_BLOCKS
,AVG_CACHE_HIT_RATIO
,SAMPLE_SIZE
,LAST_ANALYZED
,GLOBAL_STATS
,USER_STATS
,STATTYPE_LOCKED
,STALE_STATS
)
AS
SELECT /* TABLES */
u.name
,o.name
,NULL
,NULL
,NULL
,NULL
,'TABLE'
,t.rowcnt
,DECODE (BITAND (t.property, 64), 0, t.blkcnt, TO_NUMBER (NULL))
,DECODE (BITAND (t.property, 64), 0, t.empcnt, TO_NUMBER (NULL))
,DECODE (BITAND (t.property, 64), 0, t.avgspc, TO_NUMBER (NULL))
,t.chncnt
,t.avgrln
,t.avgspc_flb
,DECODE (BITAND (t.property, 64), 0, t.flbcnt, TO_NUMBER (NULL))
,ts.cachedblk
,ts.cachehit
,t.samplesize
,t.analyzetime
,DECODE (BITAND (t.flags, 512), 0, 'NO', 'YES')
,DECODE (BITAND (t.flags, 256), 0, 'NO', 'YES')
,DECODE
(
BITAND (t.trigflag, 67108864) + BITAND (t.trigflag, 134217728)
,0, NULL
,67108864, 'DATA'
,134217728, 'CACHE'
,'ALL'
)
,CASE
WHEN t.analyzetime IS NULL
THEN
NULL
WHEN ( (m.inserts + m.deletes + m.updates) >
t.rowcnt
* TO_NUMBER
(
DBMS_STATS.GET_PREFS
(
'STALE_PERCENT'
,DBMS_STATS_INTERNAL.DQ (u.name)
,DBMS_STATS_INTERNAL.DQ (o.name)
)
)
/ 100
OR BITAND (m.flags, 1) = 1)
THEN
'YES'
ELSE
'NO'
END
FROM sys.user$ u
,sys.obj$ o
,sys.tab$ t
,sys.tab_stats$ ts
,sys.mon_mods_all$ m
WHERE o.owner# = u.user#
AND o.obj# = t.obj#
AND BITAND (t.property, 1) = 0 /* not a typed table */
AND o.obj# = ts.obj#(+)
AND t.obj# = m.obj#(+)
AND o.subname IS NULL
AND o.namespace = 1
AND o.remoteowner IS NULL
AND o.linkname IS NULL
AND BITAND (o.flags, 128) = 0 -- not in recycle bin
UNION ALL
SELECT /* PARTITIONS, NOT IOT */
u.name
,o.name
,o.subname
,tp.part#
,NULL
,NULL
,'PARTITION'
,tp.rowcnt
,tp.blkcnt
,tp.empcnt
,tp.avgspc
,tp.chncnt
,tp.avgrln
,TO_NUMBER (NULL)
,TO_NUMBER (NULL)
,ts.cachedblk
,ts.cachehit
,tp.samplesize
,tp.analyzetime
,DECODE (BITAND (tp.flags, 16), 0, 'NO', 'YES')
,DECODE (BITAND (tp.flags, 8), 0, 'NO', 'YES')
,DECODE
(
/*
* Following decode returns 1 if DATA stats locked for partition
* or at table level
*/
DECODE
(
BITAND (tab.trigflag, 67108864) + BITAND (tp.flags, 32)
,0, 0
,1
)
+ /*
* Following decode returns 2 if CACHE stats locked for partition
* or at table level
*/
DECODE
(
BITAND (tab.trigflag, 134217728) + BITAND (tp.flags, 64)
,0, 0
,2
)
, /* if 0 => not locked, 3 => data and cache stats locked */
0, NULL
,1, 'DATA'
,2, 'CACHE'
,'ALL'
)
,CASE
WHEN tp.analyzetime IS NULL
THEN
NULL
WHEN ( (m.inserts + m.deletes + m.updates) >
tp.rowcnt
* TO_NUMBER
(
DBMS_STATS.GET_PREFS
(
'STALE_PERCENT'
,DBMS_STATS_INTERNAL.DQ (u.name)
,DBMS_STATS_INTERNAL.DQ (o.name)
)
)
/ 100
OR BITAND (m.flags, 1) = 1)
THEN
'YES'
ELSE
'NO'
END
FROM sys.user$ u
,sys.obj$ o
,sys.tabpartv$ tp
,sys.tab_stats$ ts
,sys.tab$ tab
,sys.mon_mods_all$ m
WHERE o.owner# = u.user#
AND o.obj# = tp.obj#
AND tp.bo# = tab.obj#
AND BITAND (tab.property, 64) = 0
AND o.obj# = ts.obj#(+)
AND tp.obj# = m.obj#(+)
AND o.namespace = 1
AND o.remoteowner IS NULL
AND o.linkname IS NULL
AND BITAND (o.flags, 128) = 0 -- not in recycle bin
UNION ALL
SELECT /* IOT Partitions */
u.name
,o.name
,o.subname
,tp.part#
,NULL
,NULL
,'PARTITION'
,tp.rowcnt
,TO_NUMBER (NULL)
,TO_NUMBER (NULL)
,TO_NUMBER (NULL)
,tp.chncnt
,tp.avgrln
,TO_NUMBER (NULL)
,TO_NUMBER (NULL)
,TO_NUMBER (NULL)
,TO_NUMBER (NULL)
,tp.samplesize
,tp.analyzetime
,DECODE (BITAND (tp.flags, 16), 0, 'NO', 'YES')
,DECODE (BITAND (tp.flags, 8), 0, 'NO', 'YES')
,DECODE
(
/*
* Following decode returns 1 if DATA stats locked for partition
* or at table level
*/
DECODE
(
BITAND (tab.trigflag, 67108864) + BITAND (tp.flags, 32)
,0, 0
,1
)
+ /*
* Following decode returns 2 if CACHE stats locked for partition
* or at table level
*/
DECODE
(
BITAND (tab.trigflag, 134217728) + BITAND (tp.flags, 64)
,0, 0
,2
)
, /* if 0 => not locked, 3 => data and cache stats locked */
0, NULL
,1, 'DATA'
,2, 'CACHE'
,'ALL'
)
,CASE
WHEN tp.analyzetime IS NULL
THEN
NULL
WHEN ( (m.inserts + m.deletes + m.updates) >
tp.rowcnt
* TO_NUMBER
(
DBMS_STATS.GET_PREFS
(
'STALE_PERCENT'
,DBMS_STATS_INTERNAL.DQ (u.name)
,DBMS_STATS_INTERNAL.DQ (o.name)
)
)
/ 100
OR BITAND (m.flags, 1) = 1)
THEN
'YES'
ELSE
'NO'
END
FROM sys.user$ u
,sys.obj$ o
,sys.tabpartv$ tp
,sys.tab$ tab
,sys.mon_mods_all$ m
WHERE o.owner# = u.user#
AND o.obj# = tp.obj#
AND tp.bo# = tab.obj#
AND tp.obj# = m.obj#(+)
AND BITAND (tab.property, 64) = 64
AND o.namespace = 1
AND o.remoteowner IS NULL
AND o.linkname IS NULL
AND BITAND (o.flags, 128) = 0 -- not in recycle bin
UNION ALL
SELECT /* COMPOSITE PARTITIONS */
u.name
,o.name
,o.subname
,tcp.part#
,NULL
,NULL
,'PARTITION'
,tcp.rowcnt
,tcp.blkcnt
,tcp.empcnt
,tcp.avgspc
,tcp.chncnt
,tcp.avgrln
,NULL
,NULL
,ts.cachedblk
,ts.cachehit
,tcp.samplesize
,tcp.analyzetime
,DECODE (BITAND (tcp.flags, 16), 0, 'NO', 'YES')
,DECODE (BITAND (tcp.flags, 8), 0, 'NO', 'YES')
,DECODE
(
/*
* Following decode returns 1 if DATA stats locked for partition
* or at table level
*/
DECODE
(
BITAND (tab.trigflag, 67108864) + BITAND (tcp.flags, 32)
,0, 0
,1
)
+ /*
* Following decode returns 2 if CACHE stats locked for partition
* or at table level
*/
DECODE
(
BITAND (tab.trigflag, 134217728) + BITAND (tcp.flags, 64)
,0, 0
,2
)
, /* if 0 => not locked, 3 => data and cache stats locked */
0, NULL
,1, 'DATA'
,2, 'CACHE'
,'ALL'
)
,CASE
WHEN tcp.analyzetime IS NULL
THEN
NULL
WHEN ( (m.inserts + m.deletes + m.updates) >
tcp.rowcnt
* TO_NUMBER
(
DBMS_STATS.GET_PREFS
(
'STALE_PERCENT'
,DBMS_STATS_INTERNAL.DQ (u.name)
,DBMS_STATS_INTERNAL.DQ (o.name)
)
)
/ 100
OR BITAND (m.flags, 1) = 1)
THEN
'YES'
ELSE
'NO'
END
FROM sys.user$ u
,sys.obj$ o
,sys.tabcompartv$ tcp
,sys.tab_stats$ ts
,sys.tab$ tab
,sys.mon_mods_all$ m
WHERE o.owner# = u.user#
AND o.obj# = tcp.obj#
AND tcp.bo# = tab.obj#
AND o.obj# = ts.obj#(+)
AND tcp.obj# = m.obj#(+)
AND o.namespace = 1
AND o.remoteowner IS NULL
AND o.linkname IS NULL
AND BITAND (o.flags, 128) = 0 -- not in recycle bin
UNION ALL
SELECT /* SUBPARTITIONS */
u.name
,po.name
,po.subname
,tcp.part#
,so.subname
,tsp.subpart#
,'SUBPARTITION'
,tsp.rowcnt
,tsp.blkcnt
,tsp.empcnt
,tsp.avgspc
,tsp.chncnt
,tsp.avgrln
,NULL
,NULL
,ts.cachedblk
,ts.cachehit
,tsp.samplesize
,tsp.analyzetime
,DECODE (BITAND (tsp.flags, 16), 0, 'NO', 'YES')
,DECODE (BITAND (tsp.flags, 8), 0, 'NO', 'YES')
,DECODE
(
/*
* Following decode returns 1 if DATA stats locked for partition
* or at table level.
* Note that dbms_stats does n't allow locking subpartition stats.
* If the composite partition is locked, all subpartitions are
* considered locked. Hence decode checks for tcp entry.
*/
DECODE
(
BITAND (tab.trigflag, 67108864) + BITAND (tcp.flags, 32)
,0, 0
,1
)
+ /*
* Following decode returns 2 if CACHE stats locked for partition
* or at table level
*/
DECODE
(
BITAND (tab.trigflag, 134217728) + BITAND (tcp.flags, 64)
,0, 0
,2
)
, /* if 0 => not locked, 3 => data and cache stats locked */
0, NULL
,1, 'DATA'
,2, 'CACHE'
,'ALL'
)
,CASE
WHEN tsp.analyzetime IS NULL
THEN
NULL
WHEN ( (m.inserts + m.deletes + m.updates) >
tsp.rowcnt
* TO_NUMBER
(
DBMS_STATS.GET_PREFS
(
'STALE_PERCENT'
,DBMS_STATS_INTERNAL.DQ (u.name)
,DBMS_STATS_INTERNAL.DQ (po.name)
)
)
/ 100
OR BITAND (m.flags, 1) = 1)
THEN
'YES'
ELSE
'NO'
END
FROM sys.user$ u
,sys.obj$ po
,sys.obj$ so
,sys.tabcompartv$ tcp
,sys.tabsubpartv$ tsp
,sys.tab_stats$ ts
,sys.tab$ tab
,sys.mon_mods_all$ m
WHERE so.obj# = tsp.obj#
AND po.obj# = tcp.obj#
AND tcp.obj# = tsp.pobj#
AND tcp.bo# = tab.obj#
AND u.user# = po.owner#
AND BITAND (tab.property, 64) = 0
AND so.obj# = ts.obj#(+)
AND tsp.obj# = m.obj#(+)
AND po.namespace = 1
AND po.remoteowner IS NULL
AND po.linkname IS NULL
AND BITAND (po.flags, 128) = 0 -- not in recycle bin
UNION ALL
SELECT /* FIXED TABLES */
'SYS'
,t.kqftanam
,NULL
,NULL
,NULL
,NULL
,'FIXED TABLE'
,DECODE (NVL (fobj.obj#, 0), 0, TO_NUMBER (NULL), st.rowcnt)
,TO_NUMBER (NULL)
,TO_NUMBER (NULL)
,TO_NUMBER (NULL)
,TO_NUMBER (NULL)
,DECODE (NVL (fobj.obj#, 0), 0, TO_NUMBER (NULL), st.avgrln)
,TO_NUMBER (NULL)
,TO_NUMBER (NULL)
,TO_NUMBER (NULL)
,TO_NUMBER (NULL)
,DECODE (NVL (fobj.obj#, 0), 0, TO_NUMBER (NULL), st.samplesize)
,DECODE (NVL (fobj.obj#, 0), 0, TO_DATE (NULL), st.analyzetime)
,DECODE
(
NVL (fobj.obj#, 0)
,0, NULL
,DECODE (NVL (st.obj#, 0), 0, NULL, 'YES')
)
,DECODE
(
NVL (fobj.obj#, 0)
,0, NULL
,DECODE
(
NVL (st.obj#, 0)
,0, NULL
,DECODE (BITAND (st.flags, 1), 0, 'NO', 'YES')
)
)
,DECODE
(
NVL (fobj.obj#, 0)
,0, NULL
,DECODE
(
BITAND (fobj.flags, 67108864)
+ BITAND (fobj.flags, 134217728)
,0, NULL
,67108864, 'DATA'
,134217728, 'CACHE'
,'ALL'
)
)
,NULL
FROM sys.x$kqfta t, sys.fixed_obj$ fobj, sys.tab_stats$ st
WHERE t.kqftaobj = fobj.obj#(+)
/*
* if fobj and st are not in sync (happens when db open read only
* after upgrade), do not display stats.
*/
AND t.kqftaver =
fobj.timestamp(+) - TO_DATE ('01-01-1991', 'DD-MM-YYYY')
AND t.kqftaobj = st.obj#(+);
--仔细看STALE_STATS的计算，仅仅看第一个关于表的
CASE
WHEN t.analyzetime IS NULL
THEN
NULL
WHEN ( (m.inserts + m.deletes + m.updates) >
t.rowcnt
* TO_NUMBER
(
DBMS_STATS.GET_PREFS
(
'STALE_PERCENT'
,DBMS_STATS_INTERNAL.DQ (u.name)
,DBMS_STATS_INTERNAL.DQ (o.name)
)
)
/ 100
OR BITAND (m.flags, 1) = 1)
THEN
'YES'
ELSE
'NO'
END
FROM sys.user$ u
,sys.obj$ o
,sys.tab$ t
,sys.tab_stats$ ts
,sys.mon_mods_all$ m
WHERE o.owner# = u.user#
AND o.obj# = t.obj#
AND BITAND (t.property, 1) = 0 /* not a typed table */
AND o.obj# = ts.obj#(+)
AND t.obj# = m.obj#(+)
AND o.subname IS NULL
AND o.namespace = 1
AND o.remoteowner IS NULL
AND o.linkname IS NULL
AND BITAND (o.flags, 128) = 0 -- not in recycle bin
--它是把(inserts+deletes+updates)相加 > 总记录数*stale_percent ,就认为陈旧。
--还有一种情况BITAND (m.flags, 1) = 1，这个表示什么什么呢？通过例子说明：
exec sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 'empx',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false)
PL/SQL procedure successfully completed.
SCOTT@book> insert into empx select * from emp ;
14 rows created.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO();
PL/SQL procedure successfully completed.
SCOTT@book> select * from sys.mon_mods_all$ where obj# in (select data_object_id from dba_objects where owner='SCOTT' and object_name='EMPX');
OBJ# INSERTS UPDATES DELETES TIMESTAMP FLAGS DROP_SEGMENTS
---------- ---------- ---------- ---------- ------------------- ---------- -------------
90149 14 0 0 2015-12-02 09:50:20 0 0
SCOTT@book> truncate table empx;
Table truncated.
SCOTT@book> exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO();
PL/SQL procedure successfully completed.
SCOTT@book> select * from sys.mon_mods_all$ where obj# in (select data_object_id from dba_objects where owner='SCOTT' and object_name='EMPX');
no rows selected
SCOTT@book> select * from sys.mon_mods_all$ where obj# in (select object_id from dba_objects where owner='SCOTT' and object_name='EMPX');
OBJ# INSERTS UPDATES DELETES TIMESTAMP FLAGS DROP_SEGMENTS
---------- ---------- ---------- ---------- ------------------- ---------- -------------
90149 14 0 42 2015-12-02 09:51:16 1 0
--奇怪查询的是object_id,很明显就是truncate后也认为是陈旧的。
3.再来看看定义5%保存在那里？
SCOTT@book> column PNAME format a15;
SCOTT@book> select * from SYS.OPTSTAT_USER_PREFS$;
OBJ# PNAME VALNUM VALCHAR CHGTIME SPARE1
---------- --------------- ---------- ------------------------------ ---------------------------------- ------
89069 METHOD_OPT FOR ALL COLUMNS SIZE REPEAT 2012-10-16 15:06:21.000000 +00:00
89068 METHOD_OPT FOR ALL COLUMNS SIZE REPEAT 2012-10-16 15:06:21.000000 +00:00
89067 METHOD_OPT FOR ALL COLUMNS SIZE REPEAT 2012-10-16 15:06:21.000000 +00:00
89066 METHOD_OPT FOR ALL COLUMNS SIZE REPEAT 2012-10-16 15:06:21.000000 +00:00
89065 METHOD_OPT FOR ALL COLUMNS SIZE REPEAT 2012-10-16 15:06:21.000000 +00:00
89064 METHOD_OPT FOR ALL COLUMNS SIZE REPEAT 2012-10-16 15:06:21.000000 +00:00
89063 METHOD_OPT FOR ALL COLUMNS SIZE REPEAT 2012-10-16 15:06:21.000000 +00:00
89062 METHOD_OPT FOR ALL COLUMNS SIZE REPEAT 2012-10-16 15:06:21.000000 +00:00
90149 STALE_PERCENT 5 2015-12-02 09:34:26.096450 +08:00
9 rows selected.
4.取消设置：
exec dbms_stats.set_table_prefs('scott','EMPX','STALE_PERCENT',10);
数据还是保存在SYS.OPTSTAT_USER_PREFS$中。
要使用
exec dbms_stats.delete_table_prefs('scott','EMPX','STALE_PERCENT');
SCOTT@book> select * from SYS.OPTSTAT_USER_PREFS$;
OBJ# PNAME VALNUM VALCHAR CHGTIME SPARE1
---------- --------------- ---------- ------------------------------ --------------------------------- ------
89069 METHOD_OPT FOR ALL COLUMNS SIZE REPEAT 2012-10-16 15:06:21.000000 +00:00
89068 METHOD_OPT FOR ALL COLUMNS SIZE REPEAT 2012-10-16 15:06:21.000000 +00:00
89067 METHOD_OPT FOR ALL COLUMNS SIZE REPEAT 2012-10-16 15:06:21.000000 +00:00
89066 METHOD_OPT FOR ALL COLUMNS SIZE REPEAT 2012-10-16 15:06:21.000000 +00:00
89065 METHOD_OPT FOR ALL COLUMNS SIZE REPEAT 2012-10-16 15:06:21.000000 +00:00
89064 METHOD_OPT FOR ALL COLUMNS SIZE REPEAT 2012-10-16 15:06:21.000000 +00:00
89063 METHOD_OPT FOR ALL COLUMNS SIZE REPEAT 2012-10-16 15:06:21.000000 +00:00
89062 METHOD_OPT FOR ALL COLUMNS SIZE REPEAT 2012-10-16 15:06:21.000000 +00:00
8 rows selected.
