收集統計資訊中的no_invalidate選項對執行計劃的影響

bfc99發表於2014-07-11
以下轉自：http://blog.itpub.net/267265/viewspace-742147/ 作者：lfree
在分析表的是否有一個引數no_invalidate：預設值是DBMS_STATS.AUTO_INVALIDATE.

        10g中預設是AUTO_INVALIDATE，就是說分析表後，遊標不會馬上invalidate，已經存在的SQL的執行計劃不會受新的統計資訊影響。可以手工DDL 
invalidate遊標。又或者等待隱藏引數_optimizer_invalidation_period(time window for invalidation of cursors of analyzed objects)秒後,
Oracle自動invalidate遊標並使SQL能夠讀取新的統計資訊產生新的執行計劃。

        如果想要dbms_stats分析立馬見效,需要使用no_invalidate=false option或者DBA自己手工invalidate遊標。

--說明一下，我個人感覺這個引數理解起來很煩，validate表示有效，no_invalidate反了2次,也是表示有效的意思。

dbms_stats收集統計資訊時候no_invalidate引數
用於是否與收集相關object的cursor失效，defalut(9i false, 10g dbms_stats.auto_invalidate(既null))
true：當收集完統計資訊後，收集物件的cursor不會失效（不會產生新的執行計劃，子游標）
false:當收集完統計資訊後，收集物件的cursor會立即失效（新的執行計劃，新的子游標）
no_invalidate=>DBMS_STATS.AUTO_INVALIDATE，分析表後，遊標不會馬上invalidate，已經存在的SQL的執行計劃不會受新的統計資訊影響。可以手工
DDL invalidate遊標。又或者等待隱藏引數_optimizer_invalidation_period(time window for invalidation of cursors of analyzed objects)秒後,
Oracle自動invalidate遊標並使SQL能夠讀取新的統計資訊產生新的執行計劃。

預設隱藏引數_optimizer_invalidation_period設定的時間太長=18000。

再建立一個例子來說明問題：
1.建立測試環境:
SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
create table t as select rownum id ,'test' name from dual connect by level <=10000;
create index i_t_id on t(id);
SQL> select dbms_stats.get_param('no_invalidate') from dual;
DBMS_STATS.GET_PARAM('NO_INVALIDATE')
-------------------------------------------------------------
DBMS_STATS.AUTO_INVALIDATE
--可以發現預設no_invalidate=DBMS_STATS.AUTO_INVALIDATE.
--如果檢視dbms_stats文件，可以發現實際上就是等於NULL。
-- oracle decides when to invalidate dependend cursors
AUTO_INVALIDATE CONSTANT BOOLEAN := null;

SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true,method_opt=>'for all columns size 254',no_invalidate=>DBMS_STATS.AUTO_INVALIDATE);
PL/SQL procedure successfully completed.

--分析表T，並且在表T上建立直方圖。

SQL> select column_name,data_type,histogram from dba_tab_cols where wner=user and table_name='T';
COLUMN_NAME  DATA_TYPE  HISTOGRAM
------------ ---------- ---------------
ID           NUMBER     HEIGHT BALANCED
NAME         CHAR       FREQUENCY

2.測試：
SQL> select * from t where id=10001;
no rows selected
SQL> @dpc
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID  0yzrd8s6vbfcc, child number 0
-------------------------------------
select * from t where id=10001
Plan hash value: 4153437776
--------------------------------------------------------------------
| Id  | Operation                   | Name   | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |        |     2 (100)|
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |      1 |     2   (0)|
|*  2 |   INDEX RANGE SCAN          | I_T_ID |      1 |     1   (0)|
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=10001)


--測試發現可以選擇索引。

但是如果我改變id的分佈，加入大量id=10001,在分析後是什麼情況呢?

SQL> insert into t  select 10001 id ,'book' name from dual connect by level<=10000;
10000 rows created.
SQL> commit ;
Commit complete.

3.分析表後在測試：
SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true,method_opt=>'for all columns size 254',no_invalidate=>DBMS_STATS.AUTO_INVALIDATE);
PL/SQL procedure successfully completed.

--正常按照許多人的理解如果查詢：select * from t where id=10001;應該不會使用索引，實際情況呢？

select * from t where id=10001;
SQL> @dpc
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID  0yzrd8s6vbfcc, child number 0
-------------------------------------
select * from t where id=10001
Plan hash value: 4153437776
--------------------------------------------------------------------
| Id  | Operation                   | Name   | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |        |     2 (100)|
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |      1 |     2   (0)|
|*  2 |   INDEX RANGE SCAN          | I_T_ID |      1 |     1   (0)|
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=10001)
--可以發現執行計劃並沒有因為我們分析表而改變執行計劃。

修改語句，select換成大寫，再測試可以很好的說明問題：
SQL> @dpc
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID  0sf1updb7x3xf, child number 0
-------------------------------------
SELECT * from t where id=10001
Plan hash value: 1601196873
--------------------------------------------------------
| Id  | Operation         | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |    14 (100)|
|*  1 |  TABLE ACCESS FULL| T    |  10039 |    14   (0)|
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID"=10001)


--可以發現執行計劃選擇全表掃描。

4.而隱藏引數_optimizer_invalidation_period預設測試＝18000.
$ P _optimizer_invalidation_period
NAME                                 DESCRIPTION                                                                  DEFAULT_VA SESSION_VALUE        SYSTEM_VALUE
------------------------------------ ---------------------------------------------------------------------------- ---------- -------------------- --------------------
_optimizer_invalidation_period       time window for invalidation of cursors of analyzed objects                  TRUE       18000                18000
我在.bashrc中定義了一個shell函式P。
P() {
echo '  '
if [ -z "$1" ]; then
sqlplus -S "/ as sysdba" <

set echo off lin 9999 trimsp on feedb off head on pages 0 newp 0 tab off


col name for a36


col description format a76


col default_value format a10


col session_value format a20


col system_value format a20


select a.ksppinm name, a.ksppdesc DESCRIPTION, b.ksppstdf DEFAULT_VALUE, b.ksppstvl SESSION_VALUE, c.ksppstvl SYSTEM_VALUE from sys.x\$ksppi a, sys.x\$ksppcv b, sys.x\$ksppsv c


EOF


else


sqlplus -S "/ as sysdba" <

set echo off lin 9999 trimsp on feedb off head on pages 0 newp 0 tab off


col name for a36


col description format a76


col default_value format a10


col session_value format a20


col system_value format a20


select a.ksppinm name, a.ksppdesc DESCRIPTION, b.ksppstdf DEFAULT_VALUE, b.ksppstvl SESSION_VALUE, c.ksppstvl SYSTEM_VALUE from sys.x\$ksppi a, sys.x\$ksppcv b, sys.x\$ksppsv c


EOF


fi


echo '  '


}





18000/3600=5 ,要5個小時後，select * from t where id=10001;執行計劃才會變成full 掃描，顯然測試不能等這麼長時間。




SQL> select sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_id ='0yzrd8s6vbfcc';


SQL_ID        CHILD_NUMBER EXECUTIONS PARSE_CALLS      LOADS INVALIDATIONS


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


0yzrd8s6vbfcc            0          2           2          1             0


SQL> alter system set "_optimizer_invalidation_period" = 10 scope=memory;


System altered.


--等待10秒............


SQL> @dpc


PLAN_TABLE_OUTPUT


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


SQL_ID  0yzrd8s6vbfcc, child number 1


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


select * from t where id=10001


Plan hash value: 1601196873


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


| Id  | Operation         | Name | E-Rows | Cost (%CPU)|


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


|   0 | SELECT STATEMENT  |      |        |    14 (100)|


|*  1 |  TABLE ACCESS FULL| T    |  10039 |    14   (0)|


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


Predicate Information (identified by operation id):


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


   1 - filter("ID"=10001)





--可以發現執行計劃變成了全表掃描。




SQL> select sql_id,child_number,executions,parse_calls,loads,invalidations from v$sql where sql_id ='0yzrd8s6vbfcc';


SQL_ID        CHILD_NUMBER EXECUTIONS PARSE_CALLS      LOADS INVALIDATIONS


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


0yzrd8s6vbfcc            0          2           2          1             0


0yzrd8s6vbfcc            1          1           1          1             0



--可以發現執行計劃生成了新的子游標。





總結：

    oracle系統預設存在自動分析一般在晚上10點分析，而no_invalidate的預設值正好是DBMS_STATS.AUTO_INVALIDATE.這樣預設分析

DBMS_STATS.AUTO_INVALIDATE，如果處理不好，一些效能問題會延遲出現，在優化SQL應該引起注意。



                                                            

                    

                                                    來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22207394/viewspace-1216476/，如需轉載，請註明出處，否則將追究法律責任。

相關文章