oracle 11g 待定釋出統計資訊
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/12020513/viewspace-626584/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 11G新特性，待定的統計資訊
- Oracle 11G 統計資訊TaskOracle
- oracle 11g統計資訊收集Oracle
- 【Oracle】11g Oracle自動收集統計資訊Oracle
- Oracle 11g手工收集表統計資訊Oracle
- Oracle 11g 統計資訊收集指令碼Oracle指令碼
- ORACLE 11g 自動收集統計資訊Oracle
- Oracle 11g釋出Oracle
- Vim 7.4 計劃已公佈，具體釋出時間待定
- [統計資訊系列7] Oracle 11g的自動統計資訊收集Oracle
- 11gR2 新特性--待定的統計資訊（Pending Statistic）
- oracle 11g 統計資訊 相關檢視Oracle
- Oracle 11g 之自動收集統計資訊Oracle
- 【統計資訊】Oracle統計資訊Oracle
- 11g掛起釋出統計資料
- Oracle 11g無法自動收集統計資訊Oracle
- oracle 11g自動收集統計資訊介紹Oracle
- Oracle 10g/11g 統計資訊相關Oracle 10g
- ORACLE統計資訊的匯出、匯入Oracle
- oracle 10g 與11g統計資訊區別Oracle 10g
- oracle 11g 擴充套件統計資訊extended_statsOracle套件
- Oracle 11g新特性之收集多列統計資訊Oracle
- Oracle 11g新特性：多列統計資訊（MultiColumn Statistics）Oracle
- ORACLE表統計資訊與列統計資訊、索引統計資訊Oracle索引
- Oracle 統計資訊Oracle
- Oracle統計資訊Oracle
- ORACLE表統計資訊與列統計資訊Oracle
- Oracle系統統計資訊Oracle
- 【統計資訊】Oracle常用的收集統計資訊方式Oracle
- Oracle收集統計資訊Oracle
- Oracle 統計資訊收集Oracle
- 收集oracle統計資訊Oracle
- ORACLE 收集統計資訊Oracle
- Oracle的統計資訊Oracle
- oracle統計資訊(四)Oracle
- oracle統計資訊(三)Oracle
- oracle統計資訊(二)Oracle
- oracle統計資訊(一)Oracle