在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