[20150705]11G表统计信息与PUBLISH.txt
--11G表统计信息可以先不发布(在PUBLISH参数的控制下),等检测合适再发布.
--确实参数optimizer_use_pending_statistics为false,可以在session级别打开为true,检测统计是否有用.
SYS@test> @hide optimizer_use_pending_statistics
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
--------------------------------- ---------------------------------------------------- -------------- -------------- -------------
optimizer_use_pending_statistics Control whether to use optimizer pending statistics TRUE FALSE FALSE
--我一直对这个功能不感兴趣,主要是自己不用.但是这并不能阻止别人不用,自己也做一些简单探究.
1.测试环境是12c,应该并不影响结果:
SCOTT@test01p> @ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
SCOTT@test01p> Select dbms_stats.GET_PREFS('PUBLISH') from dual;
DBMS_STATS.GET_PREFS('PUBLISH')
---------------------------------
TRUE
--缺省为真,也就是统计立即发布,另外注意如何cursor是否失效,还受另外参数no_invalidate影响,参考:
--http://blog.itpub.net/267265/viewspace-742147/
SCOTT@test01p> create table t1 as select rownum id , cast('test' as varchar2(20)) name from xmltable('1 to 20');
Table created.
SCOTT@test01p> select dbms_stats.get_prefs('PUBLISH', user, 'T1') c10 from dual;
C10
----------
TRUE
--缺省也为真.也就是对表T1分析后立即发布.
SCOTT@test01p> select OWNER,TABLE_NAME,LAST_ANALYZED from dba_tables where owner=user and table_name='T1';
OWNER TABLE_NAME LAST_ANALYZED
------ ---------- -------------------
SCOTT T1 2015-07-03 20:54:45
--建表时已经自动分析了.
2.首先在表级别修改publish属性:
SCOTT@test01p> EXEC DBMS_STATS.set_table_prefs(user, 'T1', 'PUBLISH', 'false');
PL/SQL procedure successfully completed.
SCOTT@test01p> select dbms_stats.get_prefs('PUBLISH', user, 'T1') c10 from dual;
C10
----------
FALSE
SCOTT@test01p> exec sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't1',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false)
PL/SQL procedure successfully completed.
SCOTT@test01p> select OWNER,TABLE_NAME,LAST_ANALYZED from dba_tables where owner=user and table_name='T1';
OWNER TABLE_NAME LAST_ANALYZED
------ ---------- -------------------
SCOTT T1 2015-07-03 20:54:45
--可以发现LAST_ANALYZED没有变化,对比上面.
SCOTT@test01p> select text_vc c100 from dba_views where view_name='DBA_TAB_PENDING_STATS';
C100
--------------------------------------------------------------------------------------------------
select u.name, o.name, null, null, h.rowcnt, h.blkcnt, h.avgrln,
h.samplesize, h.analyzetime
from sys.user$ u, sys.obj$ o, sys.wri$_optstat_tab_history h
where h.obj# = o.obj# and o.type# = 2 and o.owner# = u.user#
and h.savtime > systimestamp
union all
-- partitions
select u.name, o.name, o.subname, null, h.rowcnt, h.blkcnt,
h.avgrln, h.samplesize, h.analyzetime
from sys.user$ u, sys.obj$ o, sys.wri$_optstat_tab_history h
where h.obj# = o.obj# and o.type# = 19 and o.owner# = u.user#
and h.savtime > systimestamp
union all
-- sub partitions
select u.name, osp.name, ocp.subname, osp.subname, h.rowcnt,
h.blkcnt, h.avgrln, h.samplesize, h.analyzetime
from sys.user$ u, sys.obj$ osp, obj$ ocp, sys.tabsubpart$ tsp,
sys.wri$_optstat_tab_history h
where h.obj# = osp.obj# and osp.type# = 34 and osp.obj# = tsp.obj# and
tsp.pobj# = ocp.obj# and osp.owner# = u.user#
and h.savtime > systimestamp
--如果查看视图DBA_TAB_PENDING_STATS定义,可以发现实际上sys.wri$_optstat_tab_history的savtime取一个很大的时间.
--就不会发布.
SCOTT@test01p> select obj#,SAVTIME,ANALYZETIME from sys.WRI$_OPTSTAT_TAB_HISTORY where obj# in (select object_id from dba_objects where owner=user and object_name='T1');
OBJ# SAVTIME ANALYZETIME
---------- ----------------------------- -------------------
98024 3000-12-01 01:00:00.000000 2015-07-03 21:20:01
--而真正的分析时间保存在字段ANALYZETIME.
--如何知道这些参数保存在那里,实际上以前也写过一些,系统参数在SYS.OPTSTAT_HIST_CONTROL$,而用户参数保存在SYS.OPTSTAT_USER_PREFS$.
--知道这些容易定位那些在分析时取不同的分析参数.
SCOTT@test01p> SELECT * from SYS.OPTSTAT_USER_PREFS$ where obj# in (select object_id from dba_objects where owner=user and object_name='T1');
OBJ# PNAME VALNUM VALCHAR CHGTIME SPARE1
---------- --------- ------ -------------------- --------------------------- -------
98024 PUBLISH FALSE 2015-07-03 20:58:43.312000
3.需要发布很简单:
SCOTT@test01p> EXEC DBMS_STATS.publish_pending_stats(user,'T1');
PL/SQL procedure successfully completed.
SCOTT@test01p> select obj#,SAVTIME,ANALYZETIME from sys.WRI$_OPTSTAT_TAB_HISTORY where obj# in (select object_id from dba_objects where owner=user and object_name='T1');
OBJ# SAVTIME ANALYZETIME
---------- ---------------------------- -------------------
98024 2015-07-03 21:39:49.658000 2015-07-03 20:54:45
SCOTT@test01p> SELECT * from SYS.OPTSTAT_USER_PREFS$ where obj# in (select object_id from dba_objects where owner=user and object_name='T1');
OBJ# PNAME VALNUM VALCHAR CHGTIME SPARE1
---------- -------------------- ---------- -------------------- --------------------------- ------
98024 PUBLISH FALSE 2015-07-03 20:58:43.312000
SCOTT@test01p> select OWNER,TABLE_NAME,LAST_ANALYZED from dba_tables where owner=user and table_name='T1';
OWNER TABLE_NAME LAST_ANALYZED
---------- ---------- -------------------
SCOTT T1 2015-07-03 21:20:01
4.使用analyze不受影响:
SCOTT@test01p> analyze table T1 compute statistics;
Table analyzed.
SCOTT@test01p> select OWNER,TABLE_NAME,LAST_ANALYZED from dba_tables where owner=user and table_name='T1';
OWNER TABLE_NAME LAST_ANALYZED
---------- ---------- -------------------
SCOTT T1 2015-07-03 21:48:21
