[20120829]分析表與no_invalidate=AUTO_INVALIDATE.txt
[20120829]分析表與no_invalidate=AUTO_INVALIDATE.txt
以前寫過一篇blog。
在分析表的是否有一個引數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.建立測試環境:
--如果檢視dbms_stats文件,可以發現實際上就是等於NULL。
-- oracle decides when to invalidate dependend cursors
AUTO_INVALIDATE CONSTANT BOOLEAN := null;
--分析表T,並且在表T上建立直方圖。
2.測試:
--測試發現可以選擇索引。
但是如果我改變id的分佈,加入大量id=10001,在分析後是什麼情況呢?
3.分析表後在測試:
--正常按照許多人的理解如果查詢:select * from t where id=10001;應該不會使用索引,實際情況呢?
修改語句,select換成大寫,再測試可以很好的說明問題:
--可以發現執行計劃選擇全表掃描。
4.而隱藏引數_optimizer_invalidation_period預設測試=18000.
以前寫過一篇blog。
在分析表的是否有一個引數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 offcol name for a36col description format a76col default_value format a10col session_value format a20col system_value format a20select 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 cEOF
elsesqlplus -S "/ as sysdba" < set echo off lin 9999 trimsp on feedb off head on pages 0 newp 0 tab offcol name for a36col description format a76col default_value format a10col session_value format a20col system_value format a20select 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 cEOF
fiecho ' '}
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> @dpcPLAN_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 00yzrd8s6vbfcc 1 1 1 1 0
--可以發現執行計劃生成了新的子游標。
總結:
oracle系統預設存在自動分析一般在晚上10點分析,而no_invalidate的預設值正好是DBMS_STATS.AUTO_INVALIDATE.這樣預設分析
DBMS_STATS.AUTO_INVALIDATE,如果處理不好,一些效能問題會延遲出現,在最佳化SQL應該引起注意。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-742147/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- DBMS_STATS分析表與備份分析資訊
- oracle dbms_stats(no_invalidate引數)Oracle
- Oracle收集統計資訊之NO_INVALIDATE引數Oracle
- Oracle 統計量NO_INVALIDATE引數配置(下)Oracle
- Oracle 統計量NO_INVALIDATE引數配置(上)Oracle
- Oracle表與索引的分析及索引重建Oracle索引
- Oracle表與索引的分析及索引重建(轉)Oracle索引
- ORACLE表連線方式的分析與優化Oracle優化
- 表分析
- Oracle 表分析Oracle
- 特殊表分析
- 跳躍表資料結構與演算法分析資料結構演算法
- MySQL優化表和分析表MySql優化
- 【Redis】跳躍表原理分析與基本程式碼實現(java)RedisJava
- 構造 LL(1) 分析表的步驟與例題解析
- 電商系統商品資料表設計分析與總結
- 《編譯原理》LR 分析法與構造 LR(1) 分析表的步驟 - 例題解析編譯原理
- 分析表時遇到BUG
- oracle 表分析和索引Oracle索引
- 財務報表分析
- DBMS_STATS分析表
- 不讓oracle分析表Oracle
- 庫存分析報表
- 表分析初體驗
- 如何分析損益表
- 對大表的分析
- Analyze分析表或者索引索引
- 關於MySql資料庫設計表與查詢耗時分析MySql資料庫
- 財務報表分析是在分析什麼?如何選擇財務報表分析工具
- 符號表與雜湊表符號
- 強!分庫分表與分散式資料庫技術選項分析分散式資料庫
- SAP QM MB56 報表沒有結果之分析與對策
- [20120206]Cursor Invalidation與分析表.txt
- PE節表詳細分析
- openflow流表分析(草稿)
- SAP存貨分析報表
- oracle的表分析語句Oracle
- 如何分析資產負債表