dbms_stats.set_table_prefs之publish

2024-01-29 17:01:16

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):

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

Predicate Information (identified by operation id):

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());

------------------------------------------------------------------------------
| 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;

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

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

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

13 rows selected.

码农公寓

相关文章