翻譯metalink上關於high version count的文章

like052629發表於2015-07-08
什麼是“high version count”？
對於一個cursor，沒有一個明確的定義到底多高的verstion算高，不同的系統可以容忍不同的version count。
AWR報告列出了versions排前20的cursor，使你很容易發現潛在的問題。

當你發現了成百上千個version的cursor，就需要考慮version為何如此之高，以及產生高version的原因，並通過讓SQL共享來減少version count。

什麼是SQL共享？
首先，所有SQL都是有可能被共享的，輸入SQL時，資料庫計算SQL的hash值，通過hash值來方便地查詢它是否已經存在於共享池了。這方面的細節不是這片文章的討論範疇，你只要知道SQL被輸入後，會被計算成hash值就行了。

例如：select count(*) from emp 的hash值是 4085390015

當這條SQL第一次被解析時，不管這條SQL將來會不會被共享，我們都會為這條它建立一個父遊標（parent cursor）和一個子遊標。簡而言之，父遊標描述了一個SQL的hash值，而子游標描述了SQL的後設資料（metadata）。

什麼是SQL Metadata？
metadata是讓一個SQL能夠執行所需要的全部資訊。例如，在這個例子裡，我給出了SCOTT使用者的EMP表，那麼，就有一個object_id指向這個使用者下的EMP。當SCOTT登陸，在session的範圍裡，這條SQL鎖需要的優化器引數就初始化了。這些都屬於metadata資訊，它們會被優化器使用。下面還會提到另一個metadata的例子。

再說說這個session登出後又重新登陸的情況。他有執行了一次和剛才相同的SQL。這是，共享池裡已經有一個相同的SQL了（但是session不知道）。我們要做的就是計算這條SQL的hash值，然後通過它在共享池中搜尋（譯者：搜尋父遊標），如果找到了，就進一步檢視它有沒有可以被我們使用的子游標（需要這個子游標有與我們這條SQL相同的metadata）這樣一來，我們在共享池中，這個cursor還是隻有一個version，因為我們能共享使用已存在的cursor。
也就是說，所謂遊標共享，共享的不是父遊標，而是子游標。

現在，一個叫TEST的使用者，也有一張叫EMP的表，他也執行了和上面相同的SQL。
此時會發生下面的事情：
1 SQL語句被計算出hash值，同樣是4085390015
2 在共享池中會找到相同的SQL
3 子游標被找到
4 因為TEST.EMP的object_id不等於SCOTT.EMP的object_id（譯者：sql metadata不相同），因此發生一次‘mismatch’
從內部來講，剛才的過程是，父遊標下的子游標被串成了一個連結串列，掃描子游標的時候，將這條SQL的metadata和已有子游標的metadata進行比對，如果有100個子遊標，就要掃描100個子遊標（遇到一個mismatch就繼續檢視下一個），知道找到了可以共享的子游標。如果最終沒有找到可以共享的子游標，就需要建立一個新的子游標（譯者：連結串列被加長，version count加1，version就是子游標的個數）。
5 由於找不到可共享的子游標，因此需要建立新的子游標。現在這個父遊標包含了兩個子游標。

我為什麼要關心‘high versions’？
無謂的non-sharing SQL，和它所造成的versions，是library cache掙用的主要原因。掙用會降低資料庫的效能，在極端情況下，還會把資料庫HANG住。SQL每次執行時，解析器都必須在子游標的連結串列上搜尋，看看有沒有遊標可以共享，這些被浪費的CPU週期完全可以去做其它事情。

我怎麼檢視遊標的version？怎麼知道他們為什麼沒有共享？
有個簡單的辦法，可以把version的資訊格式化成可讀性強的格式，就是使用下面的指令碼：
Document 438755.1 High SQL Version Counts - Script to determine reason(s)
要檢視遊標mismatch的原因，可以檢視v$sql_shared_cursor。

如果你得不到上面的指令碼，可以通過下面的方法查詢到相同的資訊。

我們還用上面的SQL，看看共享池中有沒有可以共享的遊標。

SCOTT使用者執行select count(*) from emp
執行下面查詢，檢視父遊標和它的hash值
select sql_text, hash_value,address from v$sqlarea where sql_text like 'select count(*) from emp%';

SQL_TEXT                 HASH_VALUE    ADDRESS
------------------------ ------------ ----------------
select count(*) from emp 4085390015   0000000386BC2E58

檢視子游標：
oracle 9.2.x.x之前： 
select * from v$sql_shared_cursor where kglhdpar = '0000000386BC2E58'
oracle 10.0.X.X以後：
select * from v$sql_shared_cursor where address = '0000000386BC2E58'

輸出如下：
ADDRESS          KGLHDPAR         U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F
---------------- ---------------- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
0000000386BC2D08 0000000386BC2E58 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 

可以看到，有一個子遊標（地址0000000386BC2D08 ）
mismatch資訊（U S O O S L）都是N，因為這是第一個子游標。現在，如果我登陸另一個使用者，執行相同的SQL
再查詢，輸出如下
ADDRESS          KGLHDPAR         U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F
---------------- ---------------- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
0000000386BC2D08 0000000386BC2E58 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
0000000386A91AA0 0000000386BC2E58 N N N N N N N N N N N N N Y N N N Y N N N N N N N N N N N N N

我們發現第二個子游標（0000000386A91AA0 ） ，以及它不能共享第一個遊標的原因（‘Y’表示mismatch）
原因是：
1 AUTH_CHECK_MISMATCH 
2 TRANSLATION_MISMATCH 

因為當前使用者的物件不能對映到SCOTT的物件，它們的object_id不一樣，我們不能訪問SCOTT的EMP，因此發生了mismatch。

v$sql_shared_cursor裡給出的原因都是什麼意思？
下面列出了不能共享的原因（帶**的說明是經常發生的情況）
UNBOUND_CURSOR - 已存在的子游標並不完整 （或者說，它沒有被優化）
SQL_TYPE_MISMATCH - SQL語句型別和已存在的子游標不一致
**OPTIMIZER_MISMATCH - 優化器環境引數和已存在的子游標不一致
例如：
select count(*) from emp; ->> 一個父遊標、一個子遊標
alter session set optimizer_mode=ALL_ROWS
select count(*) from emp; ->> 一個父遊標，兩個子游標
     (設定event也會產生同樣的現象 - 如果開啟了10046，同樣會出現一次OPTIMIZER_MISMATCH，並且產生第三個子游標)


從cursor trace能看到更多細節

Optimizer mismatch(12)

        上面的數字給出了具體的原因
        1 = 使用了非預設的並行度。
        2 = 在RAC環境中，例項編號，session使用的CPU個數，或thread發生變化。
        3 = _parallel_syspls_obey_force 被設為 FALSE。
        4 = PQ mode發生變化。
        5 = 並行度發生變化。
        6 = 並行度策略發生變化。
        7 = The session limit is not the same as the cursor limit but the cursor limit is the same as the degree used.
        8 = The cursor limit is greater than the degree used and the session limit is less than the cursor limit
        9 = The cursor limit is less than the degree used and the session limit is not the same as the cursor limit
       10 = 優化器模式發生變化。
       11 = 物化檢視不匹配
       12 = 優化器引數不匹配
       13 = 使用了基數反饋

OUTLINE_MISMATCH - outlines和已有cursor不一致。 

    例如
alter session set use_stored_outlines = OUTLINES1;
select count(*) from emp;
alter session set use_stored_oulines= OUTLINES2;
select count(*) from emp;
    將會建立第二個子游標，和第一個的outline不一樣。

STATS_ROW_MISMATCH - 統計資訊發生變化。 檢視10046/sql_trace是否被設定到所有session上了，這樣也會造成這種現象。
LITERAL_MISMATCH - 非資料的字面值與現有遊標不匹配。
SEC_DEPTH_MISMATCH - 安全級別發生變化。
EXPLAIN_PLAN_CURSOR - 這個子游標是由explain plan for強制建立的。
BUFFERED_DML_MISMATCH - Buffered DML does not match the existing child cursor
PDML_ENV_MISMATCH - PDML environment does not match the existing child cursor
INST_DRTLD_MISMATCH - Insert direct load does not match the existing child cursor
SLAVE_QC_MISMATCH - 新遊標和老遊標，其中一個是coordinator發起的，另一個是slave cursor。
TYPECHECK_MISMATCH - 已有遊標還沒有完全優化完成。
AUTH_CHECK_MISMATCH - 許可權認證不匹配。使用者無權訪問已有遊標中的同名物件。
**BIND_MISMATCH - 繫結變數定義不匹配。
     例如
variable a varchar2(100);
select count(*) from emp where ename = :a ->> 一個父遊標，一個子遊標
variable a varchar2(400);
select count(*) from emp where ename = :a ->> 一個父遊標，兩個子游標

DESCRIBE_MISMATCH - The typecheck heap is not present during the describe for the child cursor
LANGUAGE_MISMATCH - The language handle does not match the existing child cursor
TRANSLATION_MISMATCH - 和已有遊標的同名物件不是一個物件。通常和AUTH_CHECK_MISMATCH是一個意思，只不過不是許可權問題，只是物件同名。
ROW_LEVEL_SEC_MISMATCH - 行級安全設定不匹配。
INSUFF_PRIVS - 對已有SQL操作的物件沒有許可權。
INSUFF_PRIVS_REM - 對已有SQL操作的遠端物件沒有許可權。
REMOTE_TRANS_MISMATCH - 遠端物件和已有SQL同名，但是不匹配。
     USER1: select count(*) from table@remote_db
     USER2: select count(*) from table@remote_db
雖然SQL一樣，但是他們呼叫的物件可能不一樣。
LOGMINER_SESSION_MISMATCH
INCOMP_LTRL_MISMATCH
OVERLAP_TIME_MISMATCH - error_on_overlap_time mismatch
SQL_REDIRECT_MISMATCH - sql redirection mismatch
MV_QUERY_GEN_MISMATCH - SQL是由物化檢視產生的。
USER_BIND_PEEK_MISMATCH - 繫結變數窺探導致執行計劃變化。
TYPCHK_DEP_MISMATCH - cursor has typecheck dependencies
NO_TRIGGER_MISMATCH - no trigger mismatch
FLASHBACK_CURSOR - 閃回查詢。
ANYDATA_TRANSFORMATION - anydata transformation change
INCOMPLETE_CURSOR - 當繫結變數長度增加，已有遊標的繫結變數不夠長了，就會產生新遊標，同時將老遊標標記為不可用。
TOP_LEVEL_RPI_CURSOR - top level/rpi cursor，在並行查詢中，這是正常的行為。
DIFFERENT_LONG_LENGTH - long資料長度發生變化。
LOGICAL_STANDBY_APPLY - logical standby apply mismatch
DIFF_CALL_DURN - different call duration
BIND_UACS_DIFF - bind uacs mismatch
PLSQL_CMP_SWITCHS_DIFF - plsql compiler switches mismatch
CURSOR_PARTS_MISMATCH - cursor-parts executed mismatch
STB_OBJECT_MISMATCH - 檢視https://blogs.oracle.com/optimizer/entry/my_cursor_wasn_t_shared
ROW_SHIP_MISMATCH - row shipping capability mismatch
PQ_SLAVE_MISMATCH - PQ slave mismatch
Check you want to be using PX with this reason code, as the problem could be caused by running lots of small SQL statements which do not really need PX. If you are on < 11i you may be hitting Bug:4367986
TOP_LEVEL_DDL_MISMATCH - top-level DDL cursor
MULTI_PX_MISMATCH - multi-px and slave-compiled cursor
BIND_PEEKED_PQ_MISMATCH - bind-peeked PQ cursor
MV_REWRITE_MISMATCH - MV rewrite cursor
ROLL_INVALID_MISMATCH - rolling invalidation window exceeded
This is caused by the rolling invalidation capability in DBMS_STATS. The child cannot be shared as it's invalidation window is exceeded. See:
Document 557661.1  Rolling Cursor Invalidations with DBMS_STATS in Oracle10g
OPTIMIZER_MODE_MISMATCH - 優化器模式發生變化
PX_MISMATCH - parallel query mismatch
If running 11.1.0.6 and RAC see Bug:7352775. Check that if (on each instance) parallel_instance_groups is set then instance_groups is set to the same.
MV_STALEOBJ_MISMATCH - mv stale object mismatch
FLASHBACK_TABLE_MISMATCH - flashback table mismatch
LITREP_COMP_MISMATCH - literal replacement compilation mismatch 

11g新增 :
  * PLSQL_DEBUG - 當前session的plsql_debug為true。
  * LOAD_OPTIMIZER_STATS  - Load optimizer stats for cursor sharing
  * ACL_MISMATCH   -  Check ACL mismatch
  * FLASHBACK_ARCHIVE_MISMATCH  - Flashback archive mismatch
  * LOCK_USER_SCHEMA_FAILED  - Failed to lock user and schema
  * REMOTE_MAPPING_MISMATCH  - Remote mapping mismatch
  * LOAD_RUNTIME_HEAP_FAILED  - Runtime heap mismatch
  * HASH_MATCH_FAILED  - Hash mismatch
Set to "Y" if sharing fails due to a hash mismatch, such as the case with mismatched histogram data or a range predicate marked as unsafe by literal replacement (See Bug 3461251)

11.2新增  :
  * PURGED_CURSOR - cursor marked for purging
         The cursor has been marked for purging with dbms_shared_pool.purge
  * BIND_LENGTH_UPGRADEABLE - bind length upgradeable
          Could not be shared because a bind variable size was smaller than the new value beiing inserted    (marked as BIND_MISMATCH in earlier versions).
  * USE_FEEDBACK_STATS - cardinality feedback
         Cardinality feedback is being used and therefore a new plan could be formed for the current execution.
  * BIND_EQUIV_FAILURE - The bind value's selectivity does not match that used to optimize the existing child cursor

11.2中不再有ROW_LEVEL_SEC_MISMATCH


Version_rpt指令碼:
這個指令碼可以用來生成基於v$sql_shared_cursor的分析報告。可以從下面文件找到該指令碼

Document 438755.1 High SQL Version Counts - Script to determine reason(s)

執行指令碼:
給所有子游標數超過100的SQL_ID生成報告 (10g以上):
select b.* from v$sqlarea a ,table(version_rpt(a.sql_id)) b where loaded_versions >=100;

-- 給所有子游標數超過100的HASH_VALUE生成報告:

select b.* from v$sqlarea a ,table(version_rpt(null,a.hash_value)) b where loaded_versions>=100;

-- 為特定SQL生成報告

select * from table(version_rpt('cyzznbykb509s'));


接下來還能進行哪些排查

10g中可以通過corsor trace來幫助排查遊標共享失敗的原因。這個事件只能在售後指導下使用，並且生成的trace檔案可讀性較差。要得到這個trace，需要先從v$sqlarea中取得sql的hash_value。

alter system set events 'immediate trace name cursortrace level 577, address hash_value';

(level 578/580 用來進行高階 tracing (577=level 1, 578=level 2, 580=level 3)

這個事件會在每次執行這個SQL時，在user_dump_dest生成trace檔案。

關掉event：
alter system set events 'immediate trace name cursortrace level 2147483648, address 1';
在10.2中有一個Bug 5555371 (在10.2.0.4中被修正了) 就是cursor trace沒法徹底關掉，其結果是在trace檔案中仍然會產生條目。只能重啟例項解決。這個BUG的危害取決於執行的cursor(以及它生成的trace檔案大小)

在11.2中還有一個cursordump事件:
alter system set events 'imemediat trace name cursordump level 16'
(please ensure system , not session, is used as the level meaning changes)
這個dump包含了一些額外的資訊，例如擴充套件了'optimizer_mismatch'.

有沒有即使使用了繫結變數，但還是希望有多個version count的時候？
當設定了cursor_sharing=SIMILAR
select /* TEST */ * from emp where sal > 100;
select /* TEST */ * from emp where sal > 101;
select /* TEST */ * from emp where sal > 102;
select /* TEST */ * from emp where sal > 103;
select /* TEST */ * from emp where sal > 104;

SELECT sql_text,version_count,address
FROM V$SQLAREA
WHERE sql_text like 'select /* TEST */%';

SELECT * FROM V$SQL_SHARED_CURSOR WHERE kglhdpar = '&my_addr';
你會看到一些子游標，這些子游標都沒有明顯的不能共享的原因。

原因是
當設定了cursor_sharing=similar，SQL中的字面值就會被替換成繫結變數，此時要共享遊標的一個條件就是繫結的變數需要和字面值需要匹配。執行計劃是否會發生變化取決於SQL使用的字面值，當我執行相同的cursor時，有可能需要針對具體的字面值，進一步優化出更好的執行計劃。
具體到這個例子，我們使用了大於號條件 > , 如果採用的是等值條件，遊標將會一直被共享。 如果開發人員執意要拒絕優化器根據具體的字面值進一步優化執行計劃，可以將cursor_sharing設為force。

"SIMILAR和FORCE的區別是，SIMILAR強迫相似的SQL共享SQL area，同時又避免很差的執行計劃被執行。如果將CURSOR_SHARING設定為FORCE同樣強迫相似的SQL共享SQL area，但是有可能會出現對於某個字面值來說很差的執行計劃"

從4和12級的10046 trace可以看到字面值被替換成繫結變數後，是否是unsafe的
9i的oacfl2標誌和10g的fl2標誌顯示是否是unsafe的

9i：
BINDS #2:
bind 0: dty=2 mxl=22(04) mal=00 scl=00 pre=00 oacflg=10 oacfl2=500 size=24
offset=0
bfp=1036d6408 bln=22 avl=04 flg=09
value=16064
bind 1: dty=2 mxl=22(04) mal=00 scl=00 pre=00 oacflg=10 oacfl2=500 size=24
offset=0
bfp=1036d4340 bln=22 avl=04 flg=09

10g (10.2.0.5)和11g
alter session set cursor_sharing=force;
alter session set events '10046 trace name context forever,level 12';


select /* TEST */ * from emp where sal > :"SYS_B_0"
END OF STMT
..
BINDS #3071441600:
Bind#0
oacdty=02 mxl=22(03) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0300 frm=00 csi=00 siz=24 off=0
kxsbbbfp=295c96f0 bln=22 avl=03 flg=09
value=103

"fl2=0300"表示當字面值被替換成繫結變數後，這個變數值是Unsafe的:
#define UACFBLTR 0x00000100 /* Bind was generated by LiTeRal replacement */
#define UACFUNSL 0x00000200 /* UNSafe Literal */

0x200是判斷字面值是否'safety'的重要標誌。
更多細節需要檢視：
Document 377847.1 Unsafe Literals or Peeked Bind Variables
Document 261020.1 High Version Count with CURSOR_SHARING = SIMILAR or FORCE

cursor版本過高，超過了一個閾值，就會將父遊標老化

11.2，子游標增長過多的問題更加惡化了，具體參考Bug 10187168。
所以當子游標數量增長到20到100需要自動將父遊標老化掉。
要啟用這一特性，需要檢視:
1. 11.2.0.3以上使用這個引數:
"_cursor_obsolete_threshold"設定為100 (當子游標數量超過它時，父遊標就被老化)
2. 11.2.0.2.2，設定:
"_cursor_features_enabled"=1026
event 106001 with value 100 (as the parameter _cursor_obsolete_threshold is not present)
更多資訊請檢視下面文件:
Document 10187168.8 Enhancement to obsolete parent cursors if Version Count exceeds a threshold

自適應遊標共享
當子游標慢慢增加，version count也隨之子鞥家，自適應遊標共享的意思是基於變數的選擇性自動適配執行計劃。更多資訊請參考下面文件:
Document 740052.1 Adaptive Cursor Sharing Overview

下面是自適應遊標共享帶來的已知問題:
Document 7213010.8 Bug 7213010 - Adaptive cursor sharing generates lots of child cursors
Document 8491399.8 Bug 8491399 - Adaptive Cursor Sharing does not match the correct cursor version for queries using CHAR datatype

解決其他問題
處理效能問題請參考下面文件：
Document 1377446.1 Troubleshooting Performance Issues





























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

相關文章