Oracle共享游標有哪些

本篇內容介紹了“Oracle共享游標有哪些”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

首先，明確一個概念，這里討論的游標主要是共享游標（shared cursor），跟SQL語句中定義的游標（session cursor）不是一個概念。

共享游標是用戶提交SQL或PL/SQL程序塊到Oracle的share pool之后，在library cache中生成的一個可執行對象，這個對象我們稱之為游標（cursor）。而SQL定義游標則是SELECT語句產生的多行結果集，需要聲明、打開、提取、關閉。
游標定義與分類

游標包括shared cursor和session cursor：
shared cursor即是共享游標，是SQL語句在游標解析階段生成獲得的，是位于library cache中的sql或匿名的pl/sql等。其元數據被在視圖V$sqlarea與v$sql中具體化。如果library cache中的父游標與子游標能夠被共享，此時則為共享游標。父游標能夠共享即為共享的父游標，子游標能夠共享即為共享的子游標。

session cursor即系統為用戶分配緩存區，用于存放SQL語句的執行結果。用戶可以通過這個中間緩沖區逐條取出游標中的記錄并對其處理，直到所有的游標記錄被逐一處理完畢。session cursor指的跟這個session相對應的server process的PGA里(準確的說是UGA)的一塊內存區域(或者說內存結構)即其主要特性表現在記錄的逐條定位，逐條處理。session cursor的元數據通過v$open_cursor視圖來具體化，每一個打開或解析的SQL都將位于該視圖。

游標的生命周期

shared cursor生命周期

1）包含vpd的約束條件：SQL語句如果使用的表使用了行級安全控制，安全策略生成的約束條件添加到where子句中。
2）語法、語義、訪問權限檢查：檢查SQL語句書寫的正確性，對象存在性，用戶的訪問權限。
3）父游標緩存：將該游標（SQL語句）的文本進行哈希得到哈希值并在library cache尋找相同的哈希值，如不存在則生存父游標且保存在library cache中，按順序完成后續步驟。如果此時存在父游標，則進一步判斷是否存在子游標。若存在相同的子游標，則直接調用其子游標的執行計劃執行該SQL語句，否則轉到下一步進行邏輯優化。
4）邏輯優化：使用不同的轉換技巧，生成語義上等同的新的SQL語句（SQL語句的改寫），一旦該操作完成，則執行計劃數量、搜索空間將會相應增長。其主要目的未進行轉換的情況下是尋找無法被考慮到的執行計劃。
5）物理優化：為邏輯優化階段的SQL語句產生執行計劃，讀取數據字典中的統計信息以及動態采樣的統計信息，計算開銷，開銷最低的執行計劃將被選中。
6）子游標緩存：分配內存，生成子游標（即最佳執行計劃），與父游標關聯。可以在v$sqlarea, v$sql得到具體游標信息，父子游標通過sql_id關聯。

對于僅僅完成步驟1與2的SQL語句即為軟解析，否則即為硬解析。SQL語句在Oracle中的執行機理大概也類似這個，具體可見“Oracle SQL語句執行流程與順序原理解析”。

共享游標包括父游標和子游標。

父游標是在進行硬解析時產生的，父游標里主要包含兩種信息：SQL文本以及優化目標(optimizer goal)，首次打開父游標被鎖定，直到其他所有的session都關閉該游標后才被解鎖。當父游標被鎖定的時候是不能被LRU算法置換出library cache，只有在解鎖以后才能置換出library cache，此時該父游標對應的所有子游標也同樣被置換出library cache。v$sqlarea中的每一行代表了一個parent cursor，address表示其內存地址。

子游標在發生硬解析時，在產生父游標的同時，則跟隨父游標會產生相應的子游標，此時V$SQL.CHILD_NUMBER的值為0。如果存在父游標，由于不同的運行環境，此時同樣會產生新的子游標，新子游標的CHILD_NUMBER在已有子游標基礎上以1為單位累計。子游標包括游標所有相關信息，如具體的執行計劃、綁定變、OBJECT、權限、優化器設置等。子游標隨時可以被LRU算法置換出library cache，當子游標被置換出library cache時，oracle可以利用父游標的信息重新構建出一個子游標來，這個過程叫reload。v$sql中的每一行表示了一個child cursor，根據hash value和address與parent cursor關聯。child cursor有自己的address，即v$sql.child_address。
確定一個游標的三個主要字段：address、hash_value和child_number。sql_id可以唯一確定一個父游標，sql_id、child_number唯一確定一個子游標。

session cursor生命周期：
session cursor需要從UGA中分配內存，因此有其生命周期。其生命周期主要包括：
    打開游標(根據游標聲明的名稱在UGA中分配內存區域)；
    解析游標(將SQL語句與游標關聯，并將其執行計劃加載到Library Cache)；
    定義輸出變量(僅當游標返回數據時)；
    綁定輸入變量(如果與游標關聯的SQL語句使用了綁定變量)；
    執行游標(即執行SQL語句)；
    獲取游標(即獲取SQL語句記錄結果,根據需要對記錄作相應操作。游標將逐條取出查詢的記錄，直到取完所有記錄)；
    關閉游標(釋放UGA中該游標占有的相關資源，但Library Cache中的游標的執行計劃按LRU原則清除，為其游標共享提供可能性)；

對于session cursor而言，可以將游標理解為任意的DML，DQL語句（個人理解，有待核實）。即一條SQL語句實際上就是一個游標，只不過session cursor分為顯示游標和隱式游標，以及游標指針。由上面游標的生命周期可知，任何的游標(SQL語句)都必須經歷內存分配，解析，執行與關閉的過程。故對隱式游標而言，生命周期的所有過程由系統來自動完成。對所有的DML和單行查詢(select ... into ...)而言，系統自動使用隱式游標。多行結果集的DQL則通常使用顯示游標。

一個session cursor只能對應一個shared cursor，而一個shared cursor卻可能同時對應多個session cursor。

共享游標舉例
假設有用戶SCOTT和KING，兩者均有表EMP。先以SCOTT為例，執行如下語句：
select * from emp where empno = 7788;
SELECT * from emp where empno = 7788;
SELECT * FROM emp WHERE empno = 7788;
select * from emp where empno = 7788;

以上4條語句，第1條和第4條完全相同，第1條、第2條、第3條在大小寫上有不同，查詢v$sqlarea：
select sql_id, sql_text, executions
  from v$sqlarea
 where sql_text like '%empno = 7788%'
   and sql_text not like '%from v$sqlarea%';
   
執行結果見下圖，有3條記錄，說明產生了3個父游標，其中一個父游標執行了2次。這說明，SQL語句必須完全一致（大小寫、空格回車等）才能共享，進而避免硬解析。



這3個父游標對應的子游標可以在v$sql中獲得：

select sql_id,
       hash_value,
       child_number,
       plan_hash_value,
       sql_text,
       executions
  from v$sql
 where sql_text like '%empno = 7788%'
   and sql_text not like '%from v$sql%';

執行結果見下圖，可見生成父游標時同時也生成一個以0為child_number的子游標，其sql_id和hash_value都和父游標相同。



然后使用KING用戶，執行相同的語句。然后查詢v$sqlarea父游標，結果仍然是3條記錄，不過執行次數發生了變化：



再查詢子游標，結果是6條記錄，說明由于語句執行的環境不同而造成生成不同的子游標：


產生子游標的原因很多，比如上邊的用戶（SCHEMA）改變的例子，當然還有很多其他原因也可以導致子游標的產生，比如優化器模式的改變，或者綁定變量的窺視等，如果你想確定是由那種原因造成的，需要查看v$sql_shared_cursor。

共享游標其他知識點

    查看語句共享可以借助兩個數據字典：V$SQLAREA和V$SQL。V$SQLAREA保留SQL語句的父游標信息，可以通過SQL_ID標識，其中的VERSION_COUNT列表示子游標的數量。V$SQL保留SQL語句的子游標信息，可以通過SQL_ID和CHILD_NUMBER標識。V$SQL_SHARED_CURSOR可以查看語句產生子游標的原因。
關于v$sql和v$sqlarea視圖字段及其詳解見“Oracle高資源消耗SQL語句定位”。
    父游標的關鍵信息是sql文本，子游標的關鍵信息是執行計劃和執行環境。
    硬解析通常是由于不可共享的父游標造成的，如經常變動的SQL語句，或動態SQL或未使用綁定變量等。
    解決硬解析的辦法則通常是使用綁定變量來解決。
    與父游標SQL文本完全一致的情形下，多個相同的SQL語句可以共享一個父游標。
    SQL文本、執行環境完全一致的情形下，子游標能夠被共享，否則如果執行環境不一致則生成新的子游標。
    游標是可以被所有進程共享的，也就是說如果100個進程都執行相同的SQL語句，那么這100個進程都可以同時使用該SQL語句所產生的游標，從而節省了內存。每個游標都是由library cache中的兩個或多個對象所體現的，至少兩個對象：一個對象叫做父游標（parent cursor），包含游標的名稱以及其他獨立于提交用戶的信息，從v$sqlarea視圖里看到的都是有關父游標的信息；另外一個或多個對象叫做子游標（child cursors），如果SQL文本相同，但是可能提交SQL語句的用戶不同，或者用戶提交的SQL語句所涉及到的對象為同名詞等，都有可能生成不同的子游標。因為這些SQL語句的文本雖然完全一樣，但是上下文環境卻不一樣，因此這樣的SQL語句不是一個可執行的對象，必須細化為多個子游標后才能夠執行。子游標含有執行計劃或者PL/SQL對象的程序代碼塊等。

“Oracle共享游標有哪些”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！