ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 2012-02-22 Deferred Statistics Publishing

2012-02-22 Deferred Statistics Publishing

原创 Linux操作系统 作者:demonat 时间:2012-02-22 12:23:26 0 删除 编辑
http://space.itpub.net/12020513/viewspace-626584

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_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.

看到执行计划没变


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);

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

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

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

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time
     |

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

--------------------------------------------------------------------------
| 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.

可以看到执行计划没变,刚才那个暂时发布 只对当前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_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

--------------------------------------------------------------------------
| 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. 可以看到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_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

--------------------------------------------------------------------------
| 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.
可以看到新连接上来的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_ID        SQL_TEXT                                 HASH_VALUE CHILD_NUMBER
------------- ---------------------------------------- ---------- ------------
PLAN_HASH_VALUE
---------------
1ktt7jgtavcmk select * from t1 where a>1000            4071469682            1
     2059591622

 

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

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

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time
     |

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24383181/viewspace-716928/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 2012-03-05 ArraySize
请登录后发表评论 登录
全部评论

注册时间:2011-04-15

  • 博文量
    46
  • 访问量
    91204