Pending statistics
11gr2开始，可以使用下面类型的操作来收集优化器统计信息：
1. 自动发布收集的统计信息在收集操作结束以后（默认选项publish）
2. 保存新的统计信息，并且待定（暂不发布pending）
这个特性可以将新收集的统计信息置为待定状态，所以可以先验证新统计信息的有效性然后再发布。
可以使用下面的命令来查看是否默认发布新的统计信息。
sys@DAVID> SELECTDBMS_STATS.GET_PREFS('PUBLISH') PUBLISH FROM DUAL;
PUBLISH
----------------------------------------------------------------------------------------------------
TRUE
返回为true或者false。True表示新的统计信息收集后即发布，也就是说优化器会使用新的统计信息来生查询计划，False表示收集的统计信息会被放入USER_TAB_PENDING_STATS和 USER_IND_PENDING_STATS，并且不会立刻被优化器使用，为待定状态。
可以使用下面的包来改变各个级别(global,schema,table)的默认publish选项。
Global
exec Dbms_stats.set_global_prefs(pname =>'PUBLISH' ,pvalue=> 'FALSE') ;
Schema
exec dbms_stats.set_schema_prefs(ownname => 'DEXTER',pname=>'PUBLISH' ,pvalue => 'TRUE') ;
table
Exec dbms_stats.set_table_prefs('DEXTER', 'PUBLISH_TEST','PUBLISH', 'false');
假设你执行了上面的关于table的操作，那么关于schema dexter 上publish_test表的统计信息收集以后就不会立刻应用于优化器上面，而是先置于USER_TAB_PENDING_STATS表里面为待定状态。
设置好默认的publish选项之后，就可以开始验证新统计信息了。
默认的优化器会使用已经发布的存放在数据字典里面的统计信息，可以通过更改初始化参数OPTIMIZER_USE_PENDING_STATISTICS来设定优化器使用哪一种类型的统计信息（published or pending），比如使用下面的操作来更改session级别的优化器统计信息来源（不要写成alter system了）。
alter session set optimizer_use_pending_statistics = TRUE;
这样在session级别内就可以使用待定的统计信息来编译sql语句并且生成查询计划，如果新的统计信息已经被验证，那么可以使用下面的语句发布统计信息。
Execdbms_stats.publish_pending_stats('DEXTER','PUBLISH_TEST');
如果不想使用新的统计信息，那么可以使用下面的语句去删除。
Execdbms_stats.delete_pending_stats('DEXTER','PUBLISH_TEST');
也可以使用dbms_stats.export_pending_stats将待定的统计信息导出，并且导入到测试系统上面运行一个全面的负载测试，以确定问题的根源。
下面是一个完整的示例：
创建测试表
_dexter@DAVID> createtable publish_test (id number , name varchar2(20) ) ;
Table created.
插入数据
_dexter@DAVID> insertinto publish_test select level , 'name' || level from dual connect by level<= 10000 ;
10000 rows created.
_dexter@DAVID> commit ;
Commit complete.
创建索引
_dexter@DAVID> createindex idx_publish_test_id on publish_test(id) ;
Index created.
收集统计信息
_dexter@DAVID> execdbms_stats.gather_table_stats('DEXTER','PUBLISH_TEST') ;
PL/SQL procedure successfully completed.
查看一下历史统计信息（这个表中只显示已经发布过的统计信息）
_dexter@DAVID> selecth.table_name, to_char(h.STATS_UPDATE_TIME, 'yyyymmddhh24miss')
2 from user_TAB_STATS_HISTORY h
3 where h.table_name = 'PUBLISH_TEST';
TABLE_NAME TO_CHAR(H.STAT
------------------------------ --------------
PUBLISH_TEST 20121120161308
进行一个简单查询，可以看到，走索引的效率还是比较高的
_dexter@DAVID> set autotrace on
_dexter@DAVID> select p.id,p.name from publish_test p whereid=1 ;
ID NAME
---------- --------------------
1 name1
Execution Plan
----------------------------------------------------------
Plan hash value: 1085097009
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECTSTATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| PUBLISH_TEST | 1 | 13 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_PUBLISH_TEST_ID | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -access("ID"=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
596 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
设定一下表的publish选项
_dexter@DAVID> Exec dbms_stats.set_table_prefs('DEXTER','PUBLISH_TEST', 'PUBLISH', 'false');
PL/SQL procedure successfully completed.
_dexter@DAVID> selectdbms_stats.get_prefs('PUBLISH','DEXTER','PUBLISH_TEST') FROM DUAL ;
DBMS_STATS.GET_PREFS('PUBLISH','DEXTER','PUBLISH_TEST')
----------------------------------------------------------------------------------------------------
FALSE
再次向表中插入数据
_dexter@DAVID> insertinto publish_test(id,name) select 1, 'name' || level from dual connect by level<= 10000 ;
10000 rows created.
_dexter@DAVID> commit ;
Commit complete.
在没有再次收集统计信息之前查看一下执行计划，可以看到，依旧使用旧的统计信息
_dexter@DAVID> select p.id,p.name from publish_test p whereid=1 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1085097009
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECTSTATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID|PUBLISH_TEST | 1 | 13 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_PUBLISH_TEST_ID | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -access("ID"=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1424 consistent gets
0 physical reads
2644 redo size
293416 bytes sent via SQL*Net to client
7850 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10001 rows processed
再次收集一下统计信息，这个时候收集的统计信息不会立刻被优化器使用
_dexter@DAVID> execdbms_stats.gather_table_stats('DEXTER','PUBLISH_TEST') ;
PL/SQL procedure successfully completed.
如所料，这里还是使用旧的统计信息，依旧使用index rangescan 代价比较高
_dexter@DAVID> select p.id,p.name from publish_test p whereid=1 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1085097009
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECTSTATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID|PUBLISH_TEST | 1 | 13 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_PUBLISH_TEST_ID | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -access("ID"=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1391 consistent gets
0 physical reads
0 redo size
293416 bytes sent via SQL*Net to client
7850 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10001 rows processed
看一下统计信息的情况，已经发布的统计信息还是比较老的，而如下所示pending表里面的统计信息表示新收集的待定的统计信息
_dexter@DAVID> select 'publish' as stat,t.NUM_ROWS,t.BLOCKS,to_char(t.LAST_ANALYZED,'yyyymmddhh24miss') from USER_TAB_STATISTICS t where table_name='PUBLISH_TEST'
2 union
3 select 'pending' as stat,s.num_rows,s.blocks,to_char(s.LAST_ANALYZED,'yyyymmddhh24miss') fromUSER_TAB_PENDING_STATS s where table_name='PUBLISH_TEST'
4 ;
STAT NUM_ROWS BLOCKS TO_CHAR(T.LAST
------- ---------- ---------- --------------
pending 20000 50 20121120162534
publish 10000 28 20121120161308
下面我们来验证一下新的统计信息是否有助于改善sql语句的执行
_dexter@DAVID> alter session setoptimizer_use_pending_statistics = TRUE;
Session altered.
可以看到，使用优化器使用待定的统计信息生成的查询计划使用的是全表扫描，更加有效率
_dexter@DAVID> select p.id,p.name from publish_test p whereid=1 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 3346034967
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECTSTATEMENT | | 9921 | 116K| 15 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| PUBLISH_TEST | 9921 | 116K| 15 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 -filter("ID"=1)
Statistics
----------------------------------------------------------
148 recursive calls
0 db block gets
750 consistent gets
0 physical reads
0 redo size
261413 bytes sent via SQL*Net to client
7850 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
10001 rows processed
验证结束，无误，可以发布新的统计信息了
_dexter@DAVID> Execdbms_stats.publish_pending_stats('DEXTER','PUBLISH_TEST');
PL/SQL procedure successfully completed.
_dexter@DAVID> altersession set optimizer_use_pending_statistics = false;
Session altered.
可以看到pending的统计信息已经发布并且从user_tab_pending_stats表中删除，user_tab_statistics表中的last_analyzed时间显示的是统计信息收集的时间
_dexter@DAVID> select 'publish' as stat ,t.NUM_ROWS,t.BLOCKS,to_char(t.LAST_ANALYZED,'yyyymmddhh24miss') from USER_TAB_STATISTICS t where table_name='PUBLISH_TEST'
2 union
3 select 'pending' as stat,s.num_rows,s.blocks,to_char(s.LAST_ANALYZED,'yyyymmddhh24miss') fromUSER_TAB_PENDING_STATS s where table_name='PUBLISH_TEST'
4 ;
STAT NUM_ROWS BLOCKS TO_CHAR(T.LAST
------- ---------- ---------- --------------
publish 20000 50 20121120162534
可以看到user_tab_stats_history表中的stats_update_time收集的是统计信息发布的时间
_dexter@DAVID> select h.table_name,to_char(h.STATS_UPDATE_TIME, 'yyyymmddhh24miss')
2 from user_TAB_STATS_HISTORYh
3 where h.table_name = 'PUBLISH_TEST';
TABLE_NAME TO_CHAR(H.STAT
------------------------------ --------------
PUBLISH_TEST 20121120161308
PUBLISH_TEST 20121120163017
好验证结束
如果已经发布了统计信息，想要恢复从前的统计信息，可以根据user_TAB_STATS_HISTORY中的stats_update_time，来确定timestamp，执行下面的操作，最后一个参数as_of_timestamp指的是恢复在这个时间点生效的统计信息，所以不能写20121120161308因为在这个时间点内它的统计信息是空的
PL/SQL procedure successfully completed
_dexter@DAVID> select 'publish' as stat,t.NUM_ROWS,t.BLOCKS,to_char(t.LAST_ANALYZED,'yyyymmddhh24miss') from USER_TAB_STATISTICS t where table_name='PUBLISH_TEST'
2 union
3 select 'pending' as stat,s.num_rows,s.blocks,to_char(s.LAST_ANALYZED,'yyyymmddhh24miss') fromUSER_TAB_PENDING_STATS s where table_name='PUBLISH_TEST' ;
STAT NUM_ROWS BLOCKS TO_CHAR(T.LAST
------- ---------- ---------- --------------
publish 10000 28 20121120161308
_dexter@DAVID> select h.table_name,to_char(h.STATS_UPDATE_TIME, 'yyyymmddhh24miss')
2 from user_TAB_STATS_HISTORY h
3 where h.table_name = 'PUBLISH_TEST';
TABLE_NAME TO_CHAR(H.STAT
------------------------------ --------------
PUBLISH_TEST 20121120161308
PUBLISH_TEST 20121120163017
PUBLISH_TEST 20121120165341
附录
dbms_stats.restore_table_stats参数说明
--
-- This procedure enables the user to restore statisticsof a table as of
-- a specified timestamp (as_of_timestamp). The procedurewill restore
-- statistics of associated indexes and columns as well.If the table
-- statistics were locked at the specified timestamp theprocedure will
-- lock the statistics.
-- Note:
-- The proceduremay not restore statistics correctly if analyze interface
-- is used forcomputing/deleting statistics.
-- Old statisticsversions are not saved when SYSAUX tablespace is
-- offline, thisaffects restore functionality.
-- The proceduremay not restore statistics if the table defn is
-- changed (eg:column added/deleted, partition exchanged etc).
-- Also it willnot restore stats if the object is created after
-- the specifiedtimestamp.
-- The procedurewill not restore user defined statistics.
-- Input arguments:
-- ownname - schema of table for which statistics to berestored
-- tabname - table name
-- as_of_timestamp- statistics as of this timestamp will be restored.
-- restore_cluster_index - If the table is part of a cluster,
-- restorestatistics of the cluster index if set to TRUE.
-- force -restore statistics even if the table statistics are locked.
-- if thetable statistics were not locked at the specified
-- timestamp, it will unlock the statistics
-- no_invalidate- Do not invalide the dependent cursors if set to TRUE.
-- Theprocedure invalidates the dependent cursors immediately
-- if set toFALSE.
-- Theprocedure invalidates the dependent cursors immediately
-- if set toFALSE.
-- UseDBMS_STATS.AUTO_INVALIDATE to have oracle decide when to
-- invalidatedependend cursors. This is the default. The default
-- can bechanged using set_param procedure.
--
-- Exceptions:
-- ORA-20000:Object does not exist or insufficient privileges
-- ORA-20001:Invalid or inconsistent values
-- ORA-20006: Unable to restorestatistics , statistics history not available
5341
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
