ITPub博客

首页 > 数据库 > Oracle > Deferred statistics publish-延迟统计信息发布功能介绍

Deferred statistics publish-延迟统计信息发布功能介绍

原创 Oracle 作者:oliseh 时间:2014-12-22 22:15:19 0 删除 编辑
Deferred statistics publish,延迟统计信息发布,将新生成的统计信息存放到一块临时的区域,供充分测试以验证统计信息对执行计划确有促进作用的情况下,再发布到数据字典供全局session使用,规避了因收集方法不当等原因引起统计信息不准确从而导致optimizer选择次优plan的问题


---创建测试表
drop table dsptest1;
create table dsptest1 tablespace ts_acct_dat_01 as select * from dba_objects where object_type in ('TABLE','PROGRAM');


select object_type,count(1) from dsptest1 group by object_type;
OBJECT_TYPE           COUNT(1)
------------------- ----------
PROGRAM                     19
TABLE                    63650


create index ind_objtype on dsptest1(object_type) tablespace ts_acct_ind_01;


---在没有统计信息的情况下、以禁用dynamic sampling的方式运行查询,在数据分布具有明显倾斜度的情况下,迫使optimizer走索引,如果不禁用dynamic sampling optimizer会选择FTS
alter session set optimizer_dynamic_sampling=0;
set linesize 170
explain plan for select * from dsptest1 where object_type='TABLE';
select * from table(dbms_xplan.display());


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2822084974


-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |   758 |   153K|    79   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DSPTEST1    |   758 |   153K|    79   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_OBJTYPE |   303 |       |    75   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------


   2 - access("OBJECT_TYPE"='TABLE')


14 rows selected.


---查看dsptest1表的统计信息偏好值
select dbms_stats.get_prefs('publish','ad','dsptest1') from dual;
DBMS_STATS.GET_PREFS('PUBLISH','AD','DSPTEST1')
--------------------------------------------------------------------------------
TRUE


---设置publish偏好值为FALSE
exec dbms_stats.set_table_prefs('ad','dsptest1','publish','false');


select dbms_stats.get_prefs('publish','ad','dsptest1') from dual;
DBMS_STATS.GET_PREFS('PUBLISH','AD','DSPTEST1')
--------------------------------------------------------------------------------
FALSE


---执行统计(带histogram信息)
exec dbms_stats.gather_Table_stats('ad','dsptest1',cascade=>TRUE,method_opt=>'for all columns size skewonly');


---查看数据字典里无相关统计信息
set linesize 150
select owner,table_name,num_rows,last_analyzed from dba_tab_statistics where table_name='DSPTEST1';
OWNER                          TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -----------------
AD                             DSPTEST1


select owner,index_name,blevel,clustering_factor from dba_ind_statistics where index_name='IND_OBJID';


no rows selected


---查看pending区域里的统计信息
select owner,table_name,num_rows,last_analyzed from dba_tab_pending_stats where table_name='DSPTEST1';


OWNER                          TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -----------------
AD                             DSPTEST1                            63669 20150104 07:43:42


select owner,index_name,blevel,clustering_factor from dba_ind_pending_stats where index_name='IND_OBJTYPE';


OWNER                          INDEX_NAME                         BLEVEL CLUSTERING_FACTOR
------------------------------ ------------------------------ ---------- -----------------
AD                             IND_OBJTYPE                                             913


---OPTIMIZER_USE_PENDING_STATISTICS=FALSE时重新执行查询,用的还是range scan
SQL> show parameter optimizer_use_pending_statistics


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_pending_statistics     boolean     FALSE


alter session set optimizer_dynamic_sampling=0;


set linesize 170
explain plan for select * from dsptest1 where object_type='TABLE';
select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2822084974


-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |   758 |   153K|    79   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DSPTEST1    |   758 |   153K|    79   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_OBJTYPE |   303 |       |    75   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------


   2 - access("OBJECT_TYPE"='TABLE')


---OPTIMIZER_USE_PENDING_STATISTICS=TRUE时重新执行查询,用上了FTS,说明optimizer使用了pending statistics
alter Session set OPTIMIZER_USE_PENDING_STATISTICS=TRUE;


SQL> show parameter optimizer_use_pending_statistics


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_pending_statistics     boolean     TRUE


alter session set optimizer_dynamic_sampling=0;


set linesize 170
explain plan for select * from dsptest1 where object_type='TABLE';
select * from table(dbms_xplan.display());


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2565776708


------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 63652 |  5967K|   164   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| DSPTEST1 | 63652 |  5967K|   164   (2)| 00:00:02 |
------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------


   1 - filter("OBJECT_TYPE"='TABLE')


13 rows selected.


---publish pending stats
exec dbms_stats.publish_pending_stats(ownname=>'ad',tabname=>'dsptest1');


---查看数据字典里的统计信息
set linesize 150
select owner,table_name,num_rows,last_analyzed from dba_tab_statistics where table_name='DSPTEST1';
OWNER                          TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -----------------
AD                             DSPTEST1                            63669 20150104 07:43:42


select owner,index_name,blevel,clustering_factor,last_analyzed from dba_ind_statistics where index_name='IND_OBJTYPE';


OWNER                          INDEX_NAME                         BLEVEL CLUSTERING_FACTOR LAST_ANALYZED
------------------------------ ------------------------------ ---------- ----------------- -----------------
AD                             IND_OBJTYPE                             1               913 20150104 07:43:45


---OPTIMIZER_USE_PENDING_STATISTICS=FALSE再次执行查询,optimizer准确的选择了FTS,这次是从data dictionary里获取的statistics
alter Session set OPTIMIZER_USE_PENDING_STATISTICS=FALSE;


set linesize 170
explain plan for select * from dsptest1 where object_type='TABLE';
select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2565776708


------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 63652 |  5967K|   164   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| DSPTEST1 | 63652 |  5967K|   164   (2)| 00:00:02 |
------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------


   1 - filter("OBJECT_TYPE"='TABLE')


13 rows selected.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/53956/viewspace-1376621/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
不仅仅专注Oracle database技术, member of SHOUG

注册时间:2014-04-06

  • 博文量
    128
  • 访问量
    1616855