建站服務(wù)器
Library cache是Shared pool的一部分，它幾乎是Oracle內(nèi)存結(jié)構(gòu)中最復(fù)雜的一部分.
一 ， Library cache存放什么(存放的信息單元都叫做對象) ?
Library存放的信息單元都叫做對象，這些對象可以分為兩類：
1. 存儲對象
2. 過渡對象(游標(biāo)Cursor,這里的游標(biāo)指生成的可執(zhí)行的對象， 運(yùn)行相同SQL的多個進(jìn)程可以共享該SQL產(chǎn)生的游標(biāo)，節(jié)省內(nèi)存。)
A. 用戶提交的SQL
B. SQL語句相關(guān)的解析樹
C. 執(zhí)行計劃
D. 用戶提交的PL/SQL程序塊(包括匿名程序塊，procedure，packages，function等)
E. PL/SQL對象依賴的table,index,view等對象
F. 控制結(jié)構(gòu)：lock,pin,dependency table 等
備注： LIBRARY CACHE的對象可以在V$DB_OBJECT_CACHE中找到，這個視圖基于X$KGLOB。
二， SQL的解析及游標(biāo)
SQL在解析階段主要完成以下步驟 ：
1. 將父游標(biāo)保存到Library Cache中 (父游標(biāo)的概念參考后面的說明，這一步其實(shí)不包含
在解析過程中)
先將SQL轉(zhuǎn)化為ASCII數(shù)值，然后對這些ASCII數(shù)值進(jìn)行hash函數(shù)的運(yùn)算生成hash value (10g還有唯一的SQL_ID)，運(yùn)算后匹配library cache里的hash bucket (hash bucket簡單來 講是使用hash算法將進(jìn)入library cache中的SQL 通過一個類似二維數(shù)組來表示，比如t[3][6], 每次查找時通過hash算法算出符合的bucket號，找到對應(yīng)bucket，比如前面t[3][6]中的3號， 每個bucket后面會掛載所有滿足hash算法的object handle, object handle會存儲SQL名稱 [對于SQL而言就是SQL文本]， namespace等) ，再匹配hash bucket上面的handle，也就是句柄， 如果匹配成功，那么去找子游標(biāo) (子游標(biāo)的概念參考后面的說明，找到子游標(biāo)那么直接執(zhí)行， 如果子游標(biāo)被交換出庫緩存, 那么通過父游標(biāo)信息重新構(gòu)造reload一個子游標(biāo)) , 如果不成功, 即不存在共享的父游標(biāo)，就會在庫緩存中分配一些內(nèi)存(Chunk)，并將新產(chǎn)生的父游標(biāo)保存進(jìn) 庫緩存，生成一個handle(對象句柄)，掛載hash bucket上。接下來進(jìn)行硬解析。
2 . 包含VPD(虛擬專用數(shù)據(jù)庫)的約束條件
虛擬專用數(shù)據(jù)庫VPD詳細(xì)信息見后備注。比如對于HR工資的查詢，select salary from emp ; 如果設(shè)置VPD, 會隱含加入每個用戶各自的賬號，只能查看自己的，句子會變成類似： select salary from emp where name=\'susan\' ;
3. 對SQL語句進(jìn)行文法檢查，如果存在文法錯誤，則退出解析過程
確認(rèn)sql語句是否正確書寫(比如沒有寫from,select拼寫錯誤等)，
4. 到數(shù)據(jù)字典校驗SQL涉及的對象和列是否存在，不存在就退出解析過程，這個過程會加載 Dictionary Cache .
5. 將對象進(jìn)行名稱轉(zhuǎn)換，比如將synonym 轉(zhuǎn)換為實(shí)際的對象等。若轉(zhuǎn)換失敗則退出解析。
6. 檢查發(fā)出SQL語句的用戶(一般指連接用戶)是否有訪問SQL中引用的對象的權(quán)限，若沒有則 退出解析。
7. 邏輯優(yōu)化 -- 用一定的轉(zhuǎn)換技巧(Transforming Queries,查詢轉(zhuǎn)換器)，生成語法語義上等同 的新的SQL語句。查詢語句的形式會影響所產(chǎn)生的執(zhí)行計劃，查詢轉(zhuǎn)換器的作用就是改變查詢語 句的形式以產(chǎn)生較好的執(zhí)行計劃。四種常見轉(zhuǎn)換技術(shù)：視圖合并（View Merging）、謂詞推進(jìn) （Predicate Pushing）、非嵌套子查詢（Subquery Unnesting）和物化視圖的查詢重寫（Query
Rewrite with Materialized Views）。
詳細(xì)可以參考以下文檔及后面?zhèn)渥?：
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#i37745
8. 物理優(yōu)化 -- 首先，生成與每個邏輯優(yōu)化產(chǎn)生的sql語句有關(guān)的執(zhí)行計劃, 接著, 根據(jù) 數(shù)據(jù)字典找到相關(guān)的統(tǒng)計信息或者動態(tài)收集的統(tǒng)計信息，計算出與執(zhí)行計劃相關(guān)的開銷。最后， 選中低開銷的執(zhí)行計劃。涉及大量數(shù)學(xué)運(yùn)算，所以這一步最消耗CPU資源。 子游標(biāo)會在這一步 生成 ，執(zhí)行計劃,綁定變量及執(zhí)行環(huán)境是子游標(biāo)中的主要內(nèi)容?！?/P>
9. 將子游標(biāo)load到庫緩存 -- 首先分配內(nèi)存(Chunk)，然后將共享子游標(biāo)存儲進(jìn)去，最后將它與父游標(biāo) 關(guān)聯(lián)，與子游標(biāo)有關(guān)的關(guān)鍵內(nèi)容是執(zhí)行計劃和執(zhí)行環(huán)境，一旦保存到庫緩存，父游標(biāo)與子游標(biāo)就可以 分別通過視圖v$sqlarea和v$sql被具體化。
v$sql中通過child_number,hash_value,address來確定一個子游標(biāo)，而v$sqlarea通過address和hash_value可以確定一個父游標(biāo)； 而從10g過后，通過sql_id就能確定一個游標(biāo)； 查找是否有共享的父游標(biāo)
和硬解析是兩個不同的過程，父游標(biāo)共享與否和硬解析沒有直接關(guān)系，子游標(biāo)的共享狀態(tài)決定軟硬解析 。
備注：
----------------------------------------------------------------------------------
Namespace:
使用hash算法對SQL語句對應(yīng)的ASCII進(jìn)行運(yùn)算時，傳入函數(shù)的參數(shù)有SQL語句名稱及namespace(可通過v$librarycache查詢到各種不同的namespace，對于SQL而言值為SQL AREA) .
VPD虛擬專用數(shù)據(jù)庫的詳細(xì)信息：
http://www.oracle.com/technology/global/cn/pub/articles/10gdba/week14_10gdba.html
SQL Parsing Flow Diagram [ID 32895.1]
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=32895.1
解析過程中的邏輯優(yōu)化部分的查詢轉(zhuǎn)換器 ---
從Oracle 8i開始就有四種轉(zhuǎn)換技術(shù)：視圖合并（View Merging）、謂詞推進(jìn)（Predicate Pushing）、非嵌套子查詢（Subquery Unnesting）和物化視圖的查詢重寫（Query Rewrite with Materialized Views）。
視圖合并：如果SQL語句中含有視圖，經(jīng)分析后會把視圖放在獨(dú)立的“視圖查詢塊”中，每個視圖會產(chǎn)生一個視圖子計劃，當(dāng)為整個語句產(chǎn)生執(zhí)行計劃時，視圖子計劃會被直接拿來使用而不會照顧到語句的整體性，這樣就很容易導(dǎo)致不良執(zhí)行計劃的生成。視圖合并就是為了去掉“視圖查詢塊”，將視圖合并到一個整體的查詢塊中，這樣就不會有視圖子計劃產(chǎn)生，執(zhí)行計劃的優(yōu)良性得到提升。
謂詞推進(jìn)：不是所有的視圖都能夠被合并，對于那些不能被合并的視圖Oracle會將相應(yīng)的謂詞推進(jìn)到視圖查詢塊中，這些謂詞通常是可索引的或者是過濾性較強(qiáng)的。
非嵌套子查詢：子查詢和視圖一樣也是被放于獨(dú)立查詢塊中的，查詢轉(zhuǎn)換器會將絕大多數(shù)子查詢轉(zhuǎn)換為連接從而合并為同一查詢塊，少量不能被轉(zhuǎn)換為連接的子查詢，會將它們的子計劃安照一個高效的方式排列。
物化視圖的查詢重寫：當(dāng)query_rewrite_enabled=true時，查詢轉(zhuǎn)換器尋找與該查詢語句相關(guān)聯(lián)的物化視圖，并用物化視圖改寫該查詢語句。
----------------------------------------------------------------------------------
三， 父游標(biāo)與子游標(biāo)
部分內(nèi)容參考：
http://www.oraclefans.cn/forum/showblog.jsp?rootid=5553
http://www.itpub.net/thread-1362874-1-1.html (問題)
在硬解析的過程中，進(jìn)程會一直持有l(wèi)ibrary cache latch，直到硬解析結(jié)束。硬解析過程會為該SQL產(chǎn)生兩個游標(biāo)，一個是父游標(biāo)，另一個是子游標(biāo)。
父游標(biāo)和子游標(biāo)相關(guān)問題的討論：
http://www.itpub.net/thread-1362874-1-1.html
父游標(biāo)(parent cursor) ---
當(dāng)用戶A發(fā)出一條SQL后，Oracle會根據(jù)SQL文本內(nèi)容生成hash value(10g還有唯一的SQL_ID)，對比庫緩存中的hash value, 以便能夠快速找到Shared pool中已經(jīng)存在的相同SQL。如果找不到，則Oracle會為這個SQL創(chuàng)建一個parent cursor和一個child cursor。
父游標(biāo)里主要包含兩種信息：SQL文本以及優(yōu)化目標(biāo)（optimizer goal）。從v$sqlarea視圖中看到的都是有關(guān)父游標(biāo)的信息，v$sqlarea中的每一行代表了一個parent cursor, 比如SQL文本對應(yīng)字段SQL_TEXT, 優(yōu)化目標(biāo)（optimizer goal）對應(yīng)后面的RUNTIME_MEM，EXECUTIONS，CPU_TIME, DISK_READS, BUFFER_GETS 等等 。
父游標(biāo)在第一次打開時被鎖定，直到其他所有的session都關(guān)閉游標(biāo)后才被解鎖。當(dāng)父游標(biāo)被鎖定的時候它是不能被交換出library cache的，只有在解鎖以后才能被交換出library cache，這時該父游標(biāo)對應(yīng)的所有子游標(biāo)也被交換出library cache。
一個CURSOR的結(jié)構(gòu)包括PARENT CURSOR和CHILD CURSOR，每個CURSOR至少包含一個CHILD CURSOR。這個CURSOR通過HASHVALUE來區(qū)別，每個PARENT CURSOR至少包含一個HEAP0，里面存放環(huán)境、狀態(tài)和綁定變量的信息。每個PARENT CURSOR至少有一個CHILD CURSOR 。handle其實(shí)就是存放的父游標(biāo)，真正的執(zhí)行計劃是存放在子游標(biāo)上的，也就是heap6上。
PARENT CURSOR是由一個handle和一個object組成，可以通過在庫緩存hash table中的hash value查找到handle, 而object 包含了指向它的每個 child cursor的指針 。
V$SQLAREA中version_count看到一個父游標(biāo)對應(yīng)多少個子游標(biāo)，對應(yīng)關(guān)系是靠hash_value及adress(SQL文本的地址)聯(lián)系的，V$SQL中相同SQL文本的不同子游標(biāo)，hash_value及adress是相同的，但是子地址child_address卻不一樣，這里的子地址實(shí)際就是子游標(biāo)所對應(yīng)的Heap0的句柄(handel)。 V$SQL中的hild_number編號從0開始，同樣SQL文本(父游標(biāo)共享)不同的child_number對應(yīng)不同的child_address 。Oracle10g版本下V$SQL中有有3個字段bind_data,
optimizer_env , optimizer_env_hash_value 應(yīng)該是用于決定取哪個子游標(biāo)的字段。不過9i 中v$sql中沒有這些字段，具體如何查找到子游標(biāo)的參考討論 ：
http://www.itpub.net/thread-1362874-1-1.html
子游標(biāo) (Child Cursors) ---
子游標(biāo)包括游標(biāo)所有的信息，比如具體的執(zhí)行計劃、綁定變量等。子游標(biāo)隨時可以被交換出library cache，當(dāng)子游標(biāo)被交換出library cache時，oracle可以利用父游標(biāo)的信息重新構(gòu)建出一個子游標(biāo)來，這個過程叫reload。 子游標(biāo)具體的個數(shù)可以從v$sqlarea的version_count字段體現(xiàn)出來。而每個具體的子游標(biāo)則全都在v$sql里體現(xiàn)?？梢允褂孟旅娴姆绞絹泶_定reload的比率：
SELECT 100*sum(reloads)/sum(pins) Reload_Ratio FROM v$librarycache;
一個父游標(biāo)可以對應(yīng)多個子游標(biāo)。當(dāng)具體的綁定變量的值與上次的綁定變量的值有較大差異（比如上次執(zhí)行的綁定變量的值的長度是6位，而這次執(zhí)行的綁定變量的值的長度是200 位）時或者當(dāng)SQL語句完全相同，但是所引用的對象屬于不同的schema時，或執(zhí)行SQL的環(huán)境不同(優(yōu)化器模式不一樣)， 都會創(chuàng)建一個新的子游標(biāo)。
關(guān)于子游標(biāo)新建和reload 的區(qū)別，如果所有版本的子游標(biāo)都不能被共享，那么會創(chuàng)建一個新的子游標(biāo) (new create) ，這種情況指的就是 environment 或bind var 長度不一樣等 導(dǎo)致的情況 ；而reload 指的是父游標(biāo)可以共享， 同樣的子游標(biāo) (environment 或bind var 等都一樣)原來已經(jīng)存在于library cache, 因為某種原因被aged out出去，而現(xiàn)在需要它了，要重新裝載回來。
每個child cursor也是由一個handle和一個object構(gòu)成. child object 又是由兩個heap即heap0及heap6 組成，其中Heap0包含所有的針對SQL語句每個版本的標(biāo)示信息(比如Environment, Statistics, Bind Variables等，比如綁定變量值不同的長度可能導(dǎo)致sql解析版本的不同; Child cursors are also called versions. )，Heap6包含執(zhí)行計劃 。
Child cursor包含了SQL的metadata，也就是使這個SQL可以執(zhí)行的所有相關(guān)信息，如OBJECT和權(quán)限，優(yōu)化器設(shè)置，執(zhí)行計劃等。v$sql中的每一行表示了一個child cursor，根據(jù)hash value和address與parent cursor 關(guān)聯(lián)。child cursor有自己的address，即V$SQL.CHILD_ADDRESS。如果有多個child cursor，則表示parent cursor有多個版本，v$sqlarea中的version_count字段就會紀(jì)錄下來。
每種類型的dml語句都需要如下階段：
Create a Cursor 創(chuàng)建游標(biāo)
Parse the Statement 解析語句
Bind Any Variables 綁定變量
Run the Statement 運(yùn)行語句
Close the Cursor 關(guān)閉游標(biāo)
四， 硬解析與軟解析，軟軟解析，RELOAD
硬解析 ---
首先了解父游標(biāo)共享的條件 ：
1. 字符級的比較, 要求文本完全一致
SQL語句必須完全相同，select * from emp; 和select * from emp; 是不一樣的。不能共享。
2. 必須使用相同名稱的綁定變量(其實(shí)就是文本字符不一致)，比如
select age from pepoo where name=:var_p
select age from pepoo where name=:var_f
(即使在運(yùn)行的時候賦予這兩個不同名稱的綁定變量一樣的值，也不能通向父游標(biāo))
從SQL解析過程可以看出，父游標(biāo)是否共享是發(fā)生在硬解析之前，所以父游標(biāo)是否能共享和硬解析沒有關(guān)系，不過父游標(biāo)不能共享則一定是硬解析，硬解析的整個過程見上面的第二節(jié) 。但是父游標(biāo)共享了不一定就是軟解析。能否避免硬解析，還要看子游標(biāo) 。
---------------------------------------------------------
父游標(biāo)共享已經(jīng)討論過，這里討論子游標(biāo)共享的幾種情況 (假設(shè)CURSOR_SHARING=EXACT )：
第一種是A發(fā)出的原始SQL語句和其他用戶B之前發(fā)出的SQL文本一模一樣，父親游標(biāo)可以共享，但是因為優(yōu)化器環(huán)境設(shè)置不同( OPTIMIZER_MISMATCH)， 綁定變量的值的長度在第二次執(zhí)行的時候發(fā)生顯著的變化(BIND_MISMATCH) , 授權(quán)關(guān)系不匹配(AUTH_CHECK_MISMATCH ) 或者 基礎(chǔ)物件轉(zhuǎn)換不匹配(TRANSLATION_MISMATCH) 等導(dǎo)致子游標(biāo)不能共享，需要新生成一個子游標(biāo) 。 這與SQL共享(即游標(biāo)共享)是有關(guān)系的 。 這種情況下的執(zhí)行計劃可能不同，也可能相同(我們可以通過plan_hash_value看出);
這里因為除SQL TEXT之外的其他條件不符合，所以reload 也不會發(fā)生 。子游標(biāo)就是new create and load，應(yīng)該是硬解析 。具體的mismatch可以查詢 V$SQL_SHARED_CURSOR . ；
例如：
--窗口1執(zhí)行
sys/SYS>alter session set optimizer_mode=all_rows;
Session altered.
sys/SYS>select * from tt;
no rows selected
sys/SYS>alter session set optimizer_mode=first_rows_10;
Session altered.
sys/SYS>select * from tt;
no rows selected
--窗口2執(zhí)行
sys/SYS>select hash_value,sql_text,executions,VERSION_COUNT from
v$sqlarea where sql_text like \'%from tt\';
HASH_VALUE SQL_TEXT EXECUTIONS VERSION_COUNT
---------- ---------------------------------------- ---------- -------------
3762890390 select * from tt 2 2
sys/SYS>select HASH_VALUE,child_number,sql_text from v$sql where sql_text like \'%from tt\';
HASH_VALUE CHILD_NUMBER SQL_TEXT
---------- ------------ ----------------------------------------
3762890390 0 select * from tt
3762890390 1 select * from tt
可以看到，SQL文本是完全相同的，所以兩個子游標(biāo)共享了一個父游標(biāo)。但是由于optimizer_mode的不同，所以生成了2個子游標(biāo)。如果產(chǎn)生了子游標(biāo)，那么說明肯定產(chǎn)生了某種mismatch，如何來查看是何種原因產(chǎn)生了mismatch，要通過v$sql_shared_cursor。
sys/SYS>select kglhdpar, address,auth_check_mismatch, translation_mismatch,OPTIMIZER_MISMATCH
2 from v$sql_shared_cursor
3 where kglhdpar in
4 ( select address
5 from v$sql
6 where sql_text like \'%from tt\');
KGLHDPAR ADDRESS A T O
-------- -------- - - -
89BB8948 89BB83CC N N N
89BB8948 89BB5E78 N N Y
可以看到OPTIMIZER_MISMATCH列第二行的值為Y，這說明了正是由于optimizer_mode的不同而產(chǎn)生了子游標(biāo)。最后，父游標(biāo)和子游標(biāo)的意義何在？其實(shí)一切都是為了共享。以減少再次解析的資源浪費(fèi)。
第二種是A發(fā)出的原始SQL語句和與在shared pool 中的SQL文本一模一樣，父游標(biāo)可以共享，子游標(biāo)不存在所謂的mismatch , 目前也存在于庫緩存中，可以共享子游標(biāo)，那么應(yīng)該是軟解析 。
第三種，父游標(biāo)可以共享， 不同的是，子游標(biāo)本來是可以共享的，但是目前被交換出(aged out)庫緩存，這時會reload 子游標(biāo)，也就是利用父游標(biāo)的信息重新構(gòu)造出一個子游標(biāo) ，Oracle已經(jīng)知道應(yīng)該共享哪個子游標(biāo)，只是它暫時被交換出庫緩存， reload應(yīng)該不屬于硬解析，是否屬于軟解析呢 ？雖然被aged out 出庫緩存，但是可能某個地方會記錄這個子游標(biāo)的一些信息，而不需要重新生成子游標(biāo)的相關(guān)信息(比如執(zhí)行計劃等), 而只需要reload (reload的具體過程是什么還需要研究) 。
查找是否有共享的父游標(biāo)和硬解析是兩個不同的過程，父游標(biāo)共享與否和硬解析沒有直接關(guān)系， 子游標(biāo)的共享狀態(tài)決定軟硬解析 。
---------------------------------------------------------
從性能的角度來看，盡量避免硬解析，為什么？
第一： 因為邏輯優(yōu)化(Transforming Queries)和物理優(yōu)化(選擇最優(yōu)執(zhí)行計劃)都非常依賴CPU的操作。
第二： 需要分配內(nèi)存來將父游標(biāo)與子游標(biāo)保存到庫緩存中。由于庫緩存是在所有的會話之間共享，
庫緩存中的內(nèi)存分配必須是串行執(zhí)行。
軟解析，軟軟解析 ---
軟解析是相對于硬解析而言的，其實(shí)只要在hash bucket里可以匹配對應(yīng)的SQL文本(算一次get)，那么就是軟解析，說明之前運(yùn)行過該sql，其實(shí)sql執(zhí)行期間只要一個或多個步驟可以跳過，那么我們就可以定位為軟解析。如果這個SQ語句沒有被找到，就進(jìn)行硬解析。軟解析有三種類型：
A. 第一種是某session發(fā)出的SQL語句與在library cache里其他session發(fā)出的SQL一致，父游標(biāo)和子游標(biāo)都可以共享，邏輯優(yōu)化(Transforming Queries)，和物理優(yōu)化(選擇最優(yōu)執(zhí)行計劃)及將這些信息裝載到庫緩存的heap中 這幾個步驟可以省略，表名，列名，名稱轉(zhuǎn)化及權(quán)限檢查還是需要的。
B. 第二種是某session發(fā)出的SQL是該session之前發(fā)出的曾經(jīng)執(zhí)行過的SQL。這時，解析過程只需要進(jìn)行文法檢查及權(quán)限檢查。
C. 第三種是當(dāng)設(shè)置了session_cached_cursors時，當(dāng)某個session第三次執(zhí)行相同的SQL時，則會把該SQL的游標(biāo)信息轉(zhuǎn)移到該session的PGA中。這樣，該session以后再執(zhí)行相同的SQL語句時，會直接從PGA里取出執(zhí)行計劃，跳過硬解析的所有步驟，這是高效的解析方式，但是會消耗很大的內(nèi)存。俗稱為軟軟解析 。
Reload ---
關(guān)于子游標(biāo)新建和reload 的區(qū)別，如果各版本的子游標(biāo)都不能被共享，那么會創(chuàng)建一個新的子游標(biāo) (new create) ，這種情況指的就是 environment 或綁定變量長度不一樣等 導(dǎo)致的情況。 而reload 指的是父游標(biāo)可以共享，同樣的子游標(biāo) (執(zhí)行計劃，environment 或bind var 等都一樣)原來已經(jīng)存在于library cache, 因為某種原因被aged out出去，而現(xiàn)在需要它了，要重新裝載回來 (Oracle數(shù)據(jù)庫可能在某個地方保存了原來相同的子游標(biāo)信息)。
在Hash bucket中查找SQL，如果有的話就算作是一次get，并查找這個SQL語句的執(zhí)行計劃，如果執(zhí)行計劃已經(jīng)不存在了(age out)或者是存在但不可用(Invalidation)，那么就必須對這條sql語句重新裝載，這就叫reload，如果執(zhí)行計劃存在并且可用的話，oracle就執(zhí)行這句話，這就叫做execution
五， 綁定變量(Bind Variables)
優(yōu)點(diǎn)： 共享游標(biāo)，減少硬解析
綁定變量分級 --
前面說到執(zhí)行環(huán)境的變化比如綁定變量定義的類型大小不同會導(dǎo)致生成不同的游標(biāo)，為了使游標(biāo)的數(shù)量不至于太多，產(chǎn)生了這個功能。此功能將變量的長度分為4個級別，0-32字節(jié)，33-128字節(jié)，129-2000字節(jié)，>2000字節(jié) 這四個等級。不用說，同一個綁定變量(長度)的變化，最多能生成4個游標(biāo)。
缺點(diǎn)： 綁定變量也有缺點(diǎn)。缺點(diǎn)就是，相對于字面量而言，會減弱查詢優(yōu)化器的功能。
比如：
select count(*) from t where id > 10;
select count(*) from t where id > 99999;
根據(jù)id值10,99999和表的統(tǒng)計信息，查詢優(yōu)化器可能會選擇全表掃描或者索引掃描，是合理的。
使用了綁定變量，優(yōu)化器會忽略他們的具體值，從而生成相同的執(zhí)行計劃。為了解決這個問題，
oracle9i引入了綁定變量窺測(bind variable peeking)的功能。
綁定變量窺測的優(yōu)點(diǎn)，就是窺測綁定變量的值，把它們當(dāng)做字面量來使用。這樣的好處，就是能獲得最優(yōu)查詢路徑，比如是選擇全表掃描還是索引掃描。
綁定變量窺測也有缺點(diǎn)，即生成的執(zhí)行計劃依賴第一次生成執(zhí)行計劃時所提供的值。舉例來說，就是如果第一次是全掃描，以后永遠(yuǎn)都是全表掃描了。這個方法對于非OLTP系統(tǒng)的缺點(diǎn)非常明顯，因為一個綁定變量集可能返回的結(jié)果集只包含幾百行的數(shù)據(jù)，而另一套綁定變量可能返回幾百萬行數(shù)據(jù)，因此，Oracle建議保留CURSOR_SHARING作為該初始化參數(shù)的默認(rèn)值，以強(qiáng)制產(chǎn)生一個新的更有效的執(zhí)行計劃 (cursor_sharing的詳細(xì)解釋見后面)。
那么如何避免這個缺點(diǎn)呢？只有升級到oracle11g了。
oracle11g引用一個新功能，自適應(yīng)游標(biāo)共享(ACS)。這個功能就是根據(jù)綁定變量的值，可以為相同的sql語句，生成不同子游標(biāo)，及不同的執(zhí)行計劃。ACS使用了兩個新的度量機(jī)制：綁定敏感度和綁定感知。具體可以參考Oracle11g文檔。
什么時候不使用綁定變量？
批量任務(wù)處理，報表生成，運(yùn)用OLAP的數(shù)據(jù)倉庫，因為這種大型的查詢時間較長，一次新的硬解析相對于這個查詢時間不算什么, 所以不用綁定變量沒有什么影響 。如果使用綁定變量，10g或以前的版本，一旦第一次執(zhí)行時綁定變量第一次提供的值如果是小范圍的，那么可能是索引掃描，但是第二次可能是數(shù)據(jù)倉庫典型的大時間范圍的查詢，需要全表掃描，但是還是沿用了前面的索引掃描，這樣導(dǎo)致性能下降。OLTP類型大多數(shù)是小量密集的操作，所以使用綁定變量時相對最優(yōu)的執(zhí)行計劃比較穩(wěn)定 。
在我們不使用where等條件判斷時我們就要盡量使用綁定變量（比如普通insert操作），沒理由不使用綁定變量； 而涉及到基數(shù)選擇性判斷時我們應(yīng)該盡量避免使用綁定變量，因為在物理優(yōu)化階段的綁定變量窺測遇到較大負(fù)面風(fēng)險。
也可以參考下面的兩種建議：
如果sql處理的數(shù)據(jù)較少, 解析時間顯然比執(zhí)行時間多很多了，那么我們應(yīng)該盡量使用綁定變量，這種適用于 OLTP（聯(lián)機(jī)事務(wù)處理系統(tǒng)）；
而如果是數(shù)據(jù)倉庫類型的數(shù)據(jù)庫，我們對綁定變量的使用就應(yīng)該慎重了，因為這時的執(zhí)行時間有可能遠(yuǎn)遠(yuǎn)大于解析時間，解析時間相對于執(zhí)行時間近乎可以忽略，所以這時應(yīng)該盡量不使用綁定變量。
參數(shù)CURSOR_SHARING ---
oracle是為了滿足一些以前開發(fā)的程序，里面有大量的相似的statement，沒有很好的使用綁定變量，但是重寫有不現(xiàn)實(shí)的情況下使用的一個參數(shù)。并且oracle也不建議修改這個參數(shù)。保持默認(rèn)即可。
語法 CURSOR_SHARING = { SIMILAR | EXACT | FORCE } ，默認(rèn)值為 EXACT
EXACT --
僅僅允許絕對一樣的SQL語句共享同樣的游標(biāo)。當(dāng)一個SQL語句解析的時候，首先到shared pool區(qū)查看是否有完全一樣的語句存在，如果不存在(其實(shí)此時是找不到共享的父游標(biāo))，就執(zhí)行hard parse .
SIMILAR --
如果在shared pool中無法找到完全一樣的語句的時候，就會在shared pool進(jìn)行一次新的查找，就是查找和當(dāng)前要解析的語句相似的SQL語句。 similar語句就是除了value of some literal不同，別的地方都相同的語句。比如下面：
select * from a where age=2;
select * from a where age=5;
如果在shared pool中查找到這樣的語句，就會做下一步的檢查，看shared pool中緩存的這個語句的execution plan是否適合當(dāng)前解析的語句，如果適合，就會使用shared pool的語句，而不去做hard parse。
FORCE --
強(qiáng)制將字面值不一樣的但是其他方面是一樣的SQL語句共享游標(biāo)。如果cursor_sharing設(shè)置為force的時候，當(dāng)在shared pool中發(fā)現(xiàn)了similar statement之后，就不會再去檢查執(zhí)行計劃了，而直接使用在shared pool中的這個語句了。
將cursor_sharing設(shè)置為force實(shí)際上是危險的。這會可能形成suboptimal的執(zhí)行計劃。比如對于一個范圍查找的語句，比如select * from a where a>10 and a<20這樣類型的語句，緩存中的語句的執(zhí)行計劃可能對于正在解析的語句就是不適合的，不是最優(yōu)的執(zhí)行計劃。這樣看起來是減少了解析的時間，但是大大增大了execution的時間。
什么時候需要修改這個參數(shù)呢？需要滿足以下的條件：
一個是由于大量的shared pool hitmis影響了用戶的響應(yīng)時間（就是當(dāng)前的shared pool無法滿足共享sql語句存儲的需要），如果沒有這個問題，那么設(shè)置這個參數(shù)，可能會造成更糟糕的性能。這個參數(shù)僅僅只是減少parse的時間。另外一個就是在現(xiàn)有程序中有大量的similar statement，可通過設(shè)置這個參數(shù)來獲得相對比較好的性能。
---------------------------------------------------------------
關(guān)于cursor_sharing = similar的測試 ：
http://www.wangchao.net.cn/bbsdetail_60551.html
若存在object_id的 histograms ,則每次是不同的 值 的時候都產(chǎn)生硬解析 ,若不存在 histograms ，則不產(chǎn)生硬解析 。換句話說，當(dāng)表的字段被分析過存在histograms的時候，similar 的表現(xiàn)和exact一樣，當(dāng)表的字段沒被分析不存在histograms的時候,similar的表現(xiàn)和force一樣。這樣避免了一味地如force一樣轉(zhuǎn)換成變量形式，因為有hostograms的情況下轉(zhuǎn)換成變量之后就容易產(chǎn)生錯誤的執(zhí)行計劃，沒有利用上統(tǒng)計信息。而exact呢，在沒有histograms的情況下也要分別產(chǎn)生硬解析，這樣的話，由于執(zhí)行計劃不會受到數(shù)據(jù)分布的影響（因為沒有統(tǒng)計信息）重新解析是沒有實(shí)質(zhì)意義的。而similar則綜合了兩者的優(yōu)點(diǎn)。
備注： cursor_sharing=force or similar時,在9205以下的版本BUG不少 。
---------------------------------------------------------------
Library cache內(nèi)部機(jī)制詳解 參考：
http://www.hellodba.net/2010/07/oracle-library-cache.html
重慶分公司
重慶分公司
