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