Deferred Statistics Publishing

来源：互联网发布：淘宝备注在哪编辑：程序博客网时间：2024/06/05 20:08

11g的一个新特性，表示收集完统计信息不是立即发布（默认立即发布），让所有用户可用，而是可以先保存成待定统计信息，供测试用，当觉得测试结果满意，可靠后可以发布
，这个特性给了我们一个测试的机会，还是很不错的.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SQL> conn xh/a831115
Connected.
SQL> show user
USER is "XH"
SQL> create table t1 (a int,b int);

Table created.

SQL> declare
2 begin
3 for i in 1..10000 loop
4 insert into t1 values(i,i+1);
5 end loop;
6 commit;
7 end;
8 /

PL/SQL procedure successfully completed.

SQL> select index_name from user_indexes where table_name='T1';

no rows selected

SQL> select distinct sid from v$mystat;

SID
----------
170

SQL> create index t1_ind on t1(a);

SQL> execute dbms_stats.gather_table_stats('XH','T1');（user sys）

PL/SQL procedure successfully completed.

SQL> select num_rows,null from user_tables where table_name='T1';(user sys)

NUM_ROWS N
---------- -
10000

select * from t1 where a>1000;(sid 170)

SQL> select b.sql_id ,b.sql_text,b.hash_value,b.child_number,PLAN_HASH_VALUE from v$session a ,v$sql b where a.sid=170 and b.hash_value in (a.sql_hash_value,a.prev_hash_value);

SQL_ID        SQL_TEXT                                 HASH_VALUE CHILD_NUMBER
------------- ---------------------------------------- ---------- ------------
PLAN_HASH_VALUE
---------------
28205c4c1vm22 select * from t1 where a>1000            404606018            0
     3617692013

SQL> select * from table(dbms_xplan.display_cursor('28205c4c1vm22',0));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 28205c4c1vm22, child number 0
-------------------------------------
select * from t1 where a>1000

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |       |     7 (100)|          |
|* 1 | TABLE ACCESS FULL| T1   | 9001 | 63007 |     7   (0)| 00:00:01 |

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

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

1 - filter("A">1000)

18 rows selected.

当有统计信息时候oracle会使用统计信息，而不是动态采样

SQL> execut dbms_stats.set_table_prefs(ownname=>'XH',tabname=>'T1',pname=>'PUBLISH',pvalue=>'FALSE');(user sys)

PL/SQL procedure successfully completed.

将表收集统计信息发布设置成false，表示先不发布，这也就成为了待定统计信息

SQL> show user
USER is "SYS"
SQL> delete xh.t1 where a>=2000;

8001 rows deleted.

SQL> commit;

Commit complete.

SQL> execute dbms_stats.gather_table_stats('XH','T1');(user sys)

PL/SQL procedure successfully completed.

SQL> select num_rows,null from dba_tables where table_name='T1' and wner='XH';(user sys)

NUM_ROWS N
---------- -
10000

可以看到刚才查收集的统计信息并没有发布，没有写到数据字典

select * from t1 where a>1000;(sid 170)

999 rows selected.

SQL> select b.sql_id ,b.sql_text,b.hash_value,b.child_number,PLAN_HASH_VALUE from v$session a ,v$sql b where a.sid=170 and b.hash_value in (a.sql_hash_value,a.prev_hash_value);

SQL> select * from table(dbms_xplan.display_cursor('28205c4c1vm22',0));

Plan hash value: 3617692013

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

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

1 - filter("A">1000)

18 rows selected.

看到执行计划没变

SQL> alter session set optimizer_use_pending_statistics =TRUE;(SID 170)，对当前session发布,让其可用待定统计信息进行测试

Session altered.

select * from t1 where a>1000;(sid 170)

999 rows selected.

SQL> select b.sql_id ,b.sql_text,b.hash_value,b.child_number,PLAN_HASH_VALUE from v$session a ,v$sql b where a.sid=170 and b.hash_value in (a.sql_hash_value,a.prev_hash_value);

28205c4c1vm22 select * from t1 where a>1000 404606018 1~~~~~产生了新的子游标
2059591622

SQL> select * from table(dbms_xplan.display_cursor('28205c4c1vm22',1));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 28205c4c1vm22, child number 1
-------------------------------------
select * from t1 where a>1000

Plan hash value: 2059591622

--------------------------------------------------------------------------------
------

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

--------------------------------------------------------------------------------
------

| 0 | SELECT STATEMENT | | | | 6 (100)|
|

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1000 | 7000 | 6 (0)| 00:0
0:01 |

|* 2 | INDEX RANGE SCAN | T1_IND | 1000 | | 4 (0)| 00:0

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
0:01 |

--------------------------------------------------------------------------------
------

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

2 - access("A">1000)

19 rows selected.

执行计划发生了变化（从PLAN_HASH_VALUE也可以判断出来）

SQL> conn xh/a831115
Connected.
SQL> show user
USER is "XH"
SQL> select distinct sid from v$mystat;

SID
----------
130

select * from t1 where a>1000;(sid 130)
999 rows selected.

SQL> select b.sql_id ,b.sql_text,b.hash_value,b.child_number,PLAN_HASH_VALUE from v$session a ,v$sql b where a.sid=130 and b.hash_value in (a.sql_hash_value,a.prev_hash_value);

SQL_ID        SQL_TEXT                                 HASH_VALUE CHILD_NUMBER
------------- ---------------------------------------- ---------- ------------
PLAN_HASH_VALUE
---------------
1ktt7jgtavcmk select * from t1 where a>1000            4071469682            0
     3617692013

SQL> select * from table(dbms_xplan.display_cursor('1ktt7jgtavcmk',0));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 1ktt7jgtavcmk, child number 0
-------------------------------------
select * from t1 where a>1000

Plan hash value: 3617692013

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

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

1 - filter("A">1000)

18 rows selected.

可以看到执行计划没变，刚才那个暂时发布只对当前session，新连接上的来不起作用

SQL> execute dbms_stats.publish_pending_stats(ownname=>'XH',tabname=>'T1');(user sys) 发布统计信息

PL/SQL procedure successfully completed.

SQL> select num_rows,null from dba_tables where table_name='T1' and wner='XH';(user sys)

NUM_ROWS N
---------- -
1999
可以看到新的统计信息记录到数据字典了

select * from t1 where a>1000;(sid 130)
999 rows selected.

SQL> select b.sql_id ,b.sql_text,b.hash_value,b.child_number,PLAN_HASH_VALUE from v$session a ,v$sql b where a.sid=130 and b.hash_value in (a.sql_hash_value,a.prev_hash_value);

SQL> select * from table(dbms_xplan.display_cursor('1ktt7jgtavcmk',0));

Plan hash value: 3617692013

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

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

1 - filter("A">1000)

18 rows selected. 可以看到sid 130执行计划还没变，这是由于no_invalidate参数(默认auto_invalidate 表示游标在一段时间后失效，避免大规模游标失效重新解析 )

SQL> conn xh/a831115
Connected.
SQL> select distinct sid from v$mystat;

SID
----------
128
select * from t1 where a>1000;

999 rows selected.

SQL> select b.sql_id ,b.sql_text,b.hash_value,b.child_number,PLAN_HASH_VALUE from v$session a ,v$sql b where a.sid=128 and b.hash_value in (a.sql_hash_value,a.prev_hash_value);

SQL> select * from table(dbms_xplan.display_cursor('1ktt7jgtavcmk',0));

Plan hash value: 3617692013

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

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

1 - filter("A">1000)

18 rows selected.
可以看到新连接上来的session 还是共享了走fts的执行计划，看来这个游标还没失效（从执行计划rows中可以看出这个统计信息完全不正确）

SQL> alter system flush shared_pool;（强制刷新shared pool，使游标失效）

System altered.

select * from t1 where a>1000;（sid 128）
999 rows selected.

SQL> select b.sql_id ,b.sql_text,b.hash_value,b.child_number,PLAN_HASH_VALUE from v$session a ,v$sql b where a.sid=128 and b.hash_value in (a.sql_hash_value,a.prev_hash_value);

SQL> select * from table(dbms_xplan.display_cursor('1ktt7jgtavcmk',1));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 1ktt7jgtavcmk, child number 1
-------------------------------------
select * from t1 where a>1000

Plan hash value: 2059591622

--------------------------------------------------------------------------------
------

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

--------------------------------------------------------------------------------
------

| 0 | SELECT STATEMENT | | | | 6 (100)|
|

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1000 | 7000 | 6 (0)| 00:0
0:01 |

|* 2 | INDEX RANGE SCAN | T1_IND | 1000 | | 4 (0)| 00:0

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
0:01 |

--------------------------------------------------------------------------------
------

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

2 - access("A">1000)

19 rows selected.

使用新的统计信息

若测试后觉得统计信息不好，可以dbms_stats.delete_pending_stats('XH','T1')删除

相关view，可以从这里审查,待定统计信息的一些统计信息

SQL> desc user_tab_pending_stats;(ind,col,tab_histgrm)
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
TABLE_NAME                                         VARCHAR2(30)
PARTITION_NAME                                     VARCHAR2(30)
SUBPARTITION_NAME                                  VARCHAR2(30)
NUM_ROWS                                           NUMBER
BLOCKS                                             NUMBER
AVG_ROW_LEN                                        NUMBER
SAMPLE_SIZE                                        NUMBER
LAST_ANALYZED                                      DATE

0 0