Oracle 11gR2-Pending and Published Statistics

在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,参数需要带入用户名和表名,然后运行就能删除掉。

关注dbDao.com的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569