在oracle 10g当中，数据库收集完统计信息之后会立马发布。而11g有新的选项。PUBLISH选项。它的主要作用是决定是否把新收集的统计信息发布到数据字典中。或者是将他们先存放在待处理区域中。我们看到我们的U1.TEST1表，是立马发布的。那么为什么要设置这么一个选项呢？
1、DBA和开发人员在统计信息发布之前无法验证统计信息。也就是说收集完是什么样的信息，并不确定。
2、统计信息可能不一致。这主要会发生在分区表上，分区表首先是发布表，然后是分区，然后是索引统计信息。这会导致一个问题，在你收集统计信息还没完成，你的主表已经更新了，但是你的分区很多。还在继续收集。这就会导致一些执行计划不准确。当你全部收集完成之后在发布就不会存在这个问题。
3、统计信息收集可能会在部分发布信息的步骤上失败，例如执行一个对schema的收集，在中间出现了问题失败就退出了，结果一半表统计信息更新了，一半没有更新。
所以针对上述这些问题，Oracle在11g版本推出了这个功能。我们把publish设置成flase，我收集了统计信息，但是在dba_tables中并没有更新。此时我可以在DBA_TAB_PENDING_STATS中查看未发布的表的统计信息，而DBA_COL_PENDING_STATS视图可以查看
未发布的列的统计信息。DBA_IND_PENDING_STATS可以查看未发布的索引的统计信息，DBA_TAB_HISTGRM_PENDING_STATS可以查看未发布的直方图的统计信息。
create table test1
(
id1 number,
id2 varchar2(10),
id3 varchar2(10)
)
partition by range(id1)
(
partition p1 values less than(100),
partition p2 values less than(200),
partition p3 values less than(300),
partition p4 values less than(400)
);
begin
for i in 0..399
loop
insert into test1 values(i, 'a'||i, 'b'||i);
end loop;
commit;
end;
/
select count(*) from test1;
SQL> select DBMS_STATS.GET_PREFS('PUBLISH','U1','TEST1') from dual;
DBMS_STATS.GET_PREFS('PUBLISH','U1','TEST1')
--------------------------------------------------------------------------------
TRUE
SQL> exec dbms_stats.set_table_prefs('U1','TEST1', 'PUBLISH', 'false');
PL/SQL procedure successfully completed.
SQL> select * from dba_tab_stat_prefs where table_name ='TEST1' and owner='U1';
OWNER TABLE_NAME PREFERENCE_NAME PREFERENCE_VALUE
--------------- --------------- ------------------------------ ------------------------------
U1 TEST1 PUBLISH FALSE
SQL> execute dbms_stats.gather_table_stats(ownname => 'U1',tabname => 'TEST1' ,estimate_percent => 100 ,method_opt =>'for all columns size 254',cascade => true);
PL/SQL procedure successfully completed.
SQL> select table_name,num_rows,last_analyzed from dba_tables where table_name='TEST1' and OWNER='U1';
TABLE_NAME NUM_ROWS LAST_ANAL
-------------------------------------------------- ---------- ---------
TEST1
SQL> select OWNER,TABLE_NAME,NUM_ROWS,LAST_ANALYZED from DBA_TAB_PENDING_STATS where table_name='TEST1' and OWNER='U1';
OWNER TABLE_NAME NUM_ROWS LAST_ANAL
------------------------------ ---------------------------------------- ---------- ---------
U1 TEST1 2130 04-JUN-18
SQL> select OWNER,TABLE_NAME,COLUMN_NAME,NUM_DISTINCT,LAST_ANALYZED from DBA_COL_PENDING_STATS;
OWNER TABLE_NAME COLUMN_NAME NUM_DISTINCT LAST_ANAL
------------------------------ ---------------------------------------- ------------------------------ ------------ ---------
U1 TEST1 TABLE_NAME 2124 04-JUN-18
U1 TEST1 TABLESPACE_NAME 5 04-JUN-18
U1 TEST1 CLUSTER_NAME 10 04-JUN-18
U1 TEST1 IOT_NAME 22 04-JUN-18
U1 TEST1 STATUS 1 04-JUN-18
U1 TEST1 PCT_FREE 4 04-JUN-18
U1 TEST1 PCT_USED 3 04-JUN-18
U1 TEST1 INI_TRANS 5 04-JUN-18
U1 TEST1 MAX_TRANS 2 04-JUN-18
U1 TEST1 INITIAL_EXTENT 11 04-JUN-18
U1 TEST1 NEXT_EXTENT 6 04-JUN-18
U1 TEST1 MIN_EXTENTS 1 04-JUN-18
U1 TEST1 MAX_EXTENTS 1 04-JUN-18
U1 TEST1 PCT_INCREASE 0 04-JUN-18
U1 TEST1 FREELISTS 1 04-JUN-18
SQL> select OWNER,TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE,ENDPOINT_ACTUAL_VALUE,ENDPOINT_ACTUAL_VALUE_RAW from DBA_TAB_HISTGRM_PENDING_STATS where rownum<=10;
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE ENDPOINT_ACTUAL_VALUE_RAW
------ ----------- --------------------------------- -------------- ----------------------- ------------------------------
U1 TEST1 LAST_ANALYZED 1833 2457954.95 19-JUL-17 7875071317353A
U1 TEST1 LAST_ANALYZED 1835 2457954.95 19-JUL-17 7875071317353B
U1 TEST1 LAST_ANALYZED 1837 2457954.95 19-JUL-17 7875071317353C
U1 TEST1 LAST_ANALYZED 1846 2457955.25 20-JUL-17 7875071407010F
U1 TEST1 LAST_ANALYZED 1848 2457955.25 20-JUL-17 78750714070110
U1 TEST1 LAST_ANALYZED 1849 2457955.25 20-JUL-17 78750714070113
U1 TEST1 LAST_ANALYZED 1850 2457955.25 20-JUL-17 78750714070115
U1 TEST1 LAST_ANALYZED 1853 2457955.25 20-JUL-17 78750714070117
U1 TEST1 LAST_ANALYZED 1855 2457955.25 20-JUL-17 78750714070118
U1 TEST1 LAST_ANALYZED 1856 2457955.25 20-JUL-17 78750714070119
那么这些统计信息收集之后，尚未发布，我们怎么使用它做测试呢？或者收集了统计信息，会不会影响SQL语句，如何验证呢？
那么一个办法就是把这些未发布的导出来（DBMS_STATS.EXPORT_PENDING_STATS），然后导入（DBMS_STATS.IMPORT_TABLE_STATS）到测试系统，在测试系统进行测试。另外一个办法就是使用参数OPTIMIZER_USE_PENDING_STATISTICS，将其设置成true，让优化器能使用挂起的统计信息。默认情况下，该参数为flase。
SQL> explain plan for select * from u1.test1;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4122059633
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2232 | 3199K| 29 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST1 | 2232 | 3199K| 29 (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
12 rows selected.
我们设置参数OPTIMIZER_USE_PENDING_STATISTICS为true，可以看到执行计划没有再次显示“dynamic statistics used”。而Rows返回的2130正好是统计信息中num_rows的值，证明该执行计划使用了未发布的统计信息。
alter session set OPTIMIZER_USE_PENDING_STATISTICS=true;
SQL> explain plan for select * from u1.test1;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4122059633
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2130 | 594K| 29 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST1 | 2130 | 594K| 29 (0)| 00:00:01 |
---------------------------------------------------------------------------
8 rows selected.
觉得统计信息没有问题了，我们可以实施发布。
SQL> begin
2 dbms_stats.publish_pending_stats('U1','TEST1');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select table_name,num_rows,last_analyzed from dba_tables where table_name='TEST1' and OWNER='U1';
TABLE_NAME NUM_ROWS LAST_ANAL
--------------- ---------- ---------
TEST1 2130 04-JUN-18
如果你想要删除未发布的统计信息，直接运行dbms_stats.delete_pending_stats，参数需要带入用户名和表名，然后运行就能删除掉。
Post a Comment