【DB筆試面試578】在Oracle中，什麼是會話游標？會話游標和共享游標的區別有哪些？會話游標分為哪幾類？
- 2019 年 10 月 10 日
- 筆記
♣
題目部分
在Oracle中，什麼是會話游標？會話游標和共享游標的區別有哪些？會話游標分為哪幾類？
♣
答案部分
（一）會話游標的含義
會話游標（Session Cursor）是當前會話（Session）解析和執行SQL的載體，即會話游標用於在當前會話中解析和執行SQL，會話游標是以哈希表的方式快取在PGA中（共享游標是快取在SGA的庫快取里）。在目標SQL的執行過程中，會話游標起承上啟下的作用。因為Oracle依靠會話游標來將目標SQL所涉及的數據從Buffer Cache的對應數據塊讀到PGA里，然後在PGA里做後續的排序、表連接等處理，最後將最終的處理結果返回給用戶，所以，會話游標是當前會話解析和執行SQL的載體。
共享游標和會話游標的對比如下表所示：
|
|
共享游標（Shared Cursor） |
會話游標（Session Cursor） |
|---|---|---|
|
快取位置 |
快取在SGA中的共享池裡的庫快取（Library Cache）中。 |
快取在每個會話的PGA中。 |
|
共享 |
共享游標在所有會話之間共享。 |
會話游標（Session Cursor）與會話（Session）是一一對應的，不同會話的會話游標之間不能共享，這是與共享游標（Shared Cursor）的本質區別。 |
|
生命周期 |
共享游標無生命周期，會進行快取。 |
會話游標是有生命周期的，每個會話游標在使用的過程中都至少會經歷一次Open、Parse、Bind、Execute、Fetch和Close中的一個或多個階段。Oracle會根據參數SESSION_CACHED_CURSORS的值來決定是否將已經用過的會話游標快取在對應會話的PGA中。 |
|
聯繫 |
1.會話游標是以哈希表的方式快取在PGA中，意味著Oracle會通過相關的哈希運算來存儲和訪問在當前會話的PGA中的對應會話游標。這種訪問機制和共享游標是一樣的，可以簡單地認為Oracle是根據目標SQL的SQL文本的哈希值去PGA中的相應Hash Bucket中找匹配的會話游標。由於在快取會話游標的哈希表的對應Hash Bucket中，Oracle會存儲目標SQL對應的父游標的庫快取對象句柄地址，所以，Oracle可以通過會話游標找到對應的父游標，進而就可以找到對應子游標中目標SQL的解析樹和執行計劃，然後Oracle就可以重用目標SQL的解析樹和執行計劃來執行SQL語句了。2.一個會話游標只能對應一個共享游標，而一個共享游標卻可以同時對應多個會話游標。 |
|
（二）會話游標的分類
會話游標的詳細分類參考下表：
表 3-20 Oracle中會話游標的分類
在上表中需要注意的是，動態游標是Oracle資料庫中最靈活的一種會話游標，它的靈活性表現在：①動態游標的定義方式非常靈活，它可以有多種定義方式。②動態游標可以作為存儲過程的輸入參數和函數的輸出參數。上表中的各種游標希望讀者可以通過做大量的練習題來掌握，畢竟游標是存儲過程開發過程中必不可少的內容。
（三）會話游標的屬性
會話游標有4個屬性，見下表：
表 3-21 游標的屬性
|
屬性 |
類型 |
簡介 |
適用對象 |
適用SQL |
|---|---|---|---|---|
|
SQL%FOUND |
布爾型 |
最近的FETCH是否提取到數據，表示一條SQL語句被執行成功後受其影響而改變的記錄數是否大於或等於1，若是則賦值為TRUE，否則為FALSE。在一條DML語句被執行前，SQL%FOUND的值是NULL。 |
隱式游標、顯式游標 |
INSERT、DELETE、UPDATE、SELECT … INTO … |
|
SQL%NOTFOUND |
布爾型 |
最近的FETCH是否沒有提取到數據，表示一條SQL語句被執行成功後受其影響而改變的記錄數是否為0，若是則賦值為TRUE，否則為FALSE。在一條DML語句被執行前，SQL%NOTFOUND的值是NULL。 |
隱式游標、顯式游標 |
|
|
SQL%ROWCOUNT |
數值型 |
表示最近的一條SQL語句成功執行後受其影響而改變的記錄的數量，後續執行的SQL會覆蓋SQL%ROWCOUNT的值。 |
隱式游標、顯式游標 |
|
|
SQL%ISOPEN |
布爾型 |
游標是否打開，當游標打開時返回TRUE。對於隱式游標而言，SQL%ISOPEN的值永遠是FALSE。 |
顯式游標 |
當執行一條DML語句後，DML語句的結果保存在這四個游標屬性中，這些屬性用於控制程式流程或者了解程式的狀態。當運行DML語句時，PL/SQL打開一個內建游標並處理結果。在這些屬性中，SQL%FOUND和SQL%NOTFOUND是布爾值，SQL%ROWCOUNT是整數值。需要注意的是，若游標屬於隱式游標，則在PL/SQL中可以直接使用上表中的屬性，若游標屬於顯式游標，則上表中的屬性里「SQL%」需要替換為自定義顯式游標的名稱。上表中的這4個屬性對於動態游標依然適用。
（四）會話游標的相關參數
和會話游標相關的有兩個重要參數，分別為OPEN_CURSORS和SESSION_CACHED_CURSORS，下面詳細介紹這兩個參數。
（1）參數OPEN_CURSORS用於設定單個會話中同時能夠以OPEN狀態並存的會話游標的總數，默認值為50。若該值為300，則表示單個會話中同時能夠以OPEN狀態並存的會話游標的總數不能超過300，否則Oracle會報錯「ORA-1000：maximum open cursors exceeded」。視圖V$OPEN_CURSOR可以用來查詢資料庫中狀態為OPEN或者己經被快取在PGA中的會話游標的數量和具體資訊（例如，SQL_ID和SQL文本等）。當然，也可以從視圖V$SYSSTAT中查到當前所有以OPEN狀態存在的會話游標的總數。
LHR@orclasm > show parameter open_cursors NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ open_cursors integer 65535 SELECT USERENV('SID') FROM DUAL; SELECT * FROM V$OPEN_CURSOR WHERE SID=16; SELECT * FROM V$SYSSTAT D WHERE D.NAME ='opened cursors current';
（2）參數SESSION_CACHED_CURSORS用於設定單個會話中能夠以Soft Closed狀態快取在PGA中的會話游標的總數。在Oracle 10g中默認為20（注意：在官方文檔中記錄的該值默認為0是有誤的），11g中默認為50。
LHR@orclasm > show parameter session_cached_cursors NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ session_cached_cursors integer 50
從上述顯示結果可以看出，SESSION_CACHED_CURSORS的值為50，意味著在這個庫里，單個會話中同時能夠以Soft Closed狀態快取在PGA中的會話游標的總數不能超過50。
關於參數SESSION_CACHED_CURSORS需要注意以下幾點：
① Oracle會用LRU演算法來管理這些已快取的會話游標（從會話游標的dump文件中可以證實這一點），所以即便某個Session以Soft Closed狀態快取在PGA中的會話游標的總數己經達到了SESSION_CACHED_CURSORS所設置的上限也沒有關係，LRU演算法依然能夠保證那些頻繁反覆執行的SQL所對應的會話游標的快取命中率要高於那些不頻繁反覆執行的SQL。
① 在Oracle 11gR2中，一個會話游標能夠被快取在PGA中的必要條件是該會話游標所對應的SQL解析和執行的次數要超過3次。Oracle這麼做的目的是為了避免那些執行次數很少的SQL所對應的會話游標也被快取在PGA里，這些SQL很可能只執行一次而且不會重複執行，所以把這些執行次數很少的SQL所對應的會話游標快取在PGA中是沒有太大意義的。可以使用如下的SQL語句查詢快取在當前系統中的所有會話游標：
SELECT D.INST_ID, D.SQL_ID,D.SQL_TEXT,D.SID,D.USER_NAME,D.HASH_VALUE FROM GV$OPEN_CURSOR D WHERE D.CURSOR_TYPE='SESSION CURSOR CACHED';
下面給出一個會話游標快取的示例：
LHR@orclasm > alter system flush shared_pool;--生產庫慎用 System altered. --開始第1次執行 LHR@orclasm > SELECT D.SQL_ID,D.CURSOR_TYPE FROM V$OPEN_CURSOR D WHERE D.SID=USERENV('SID') AND D.SQL_TEXT LIKE 'SELECT /*test scc*/ COUNT(*)%' ; no rows selected LHR@orclasm > SELECT /*test scc*/ COUNT(*) FROM SCOTT.EMP; COUNT(*) ---------- 14 LHR@orclasm > SELECT D.SQL_ID,D.CURSOR_TYPE FROM V$OPEN_CURSOR D WHERE D.SID=USERENV('SID') AND D.SQL_TEXT LIKE 'SELECT /*test scc*/ COUNT(*)%' ; no rows selected --開始第2次執行： LHR@orclasm > SELECT /*test scc*/ COUNT(*) FROM SCOTT.EMP; COUNT(*) ---------- 14 LHR@orclasm > SELECT D.SQL_ID,D.CURSOR_TYPE FROM V$OPEN_CURSOR D WHERE D.SID=USERENV('SID') AND D.SQL_TEXT LIKE 'SELECT /*test scc*/ COUNT(*)%' ; no rows selected --開始第3次執行： LHR@orclasm > SELECT /*test scc*/ COUNT(*) FROM SCOTT.EMP; COUNT(*) ---------- 14 LHR@orclasm > SELECT D.SQL_ID,D.CURSOR_TYPE FROM V$OPEN_CURSOR D WHERE D.SID=USERENV('SID') AND D.SQL_TEXT LIKE 'SELECT /*test scc*/ COUNT(*)%' ; SQL_ID CURSOR_TYPE ------------- ---------------------------------------------------------------- 9r01dt51f46tf DICTIONARY LOOKUP CURSOR CACHED 從結果可以看到，雖然已經快取到PGA中了，但是類型為「DICTIONARY LOOKUP CURSOR CACHED」，並不是「SESSION CURSOR CACHED」，所以下面開始第4次執行： LHR@orclasm > SELECT /*test scc*/ COUNT(*) FROM SCOTT.EMP; COUNT(*) ---------- 14 LHR@orclasm > SELECT D.SQL_ID,D.CURSOR_TYPE FROM V$OPEN_CURSOR D WHERE D.SID=USERENV('SID') AND D.SQL_TEXT LIKE 'SELECT /*test scc*/ COUNT(*)%' ; SQL_ID CURSOR_TYPE ------------- ---------------------------------------------------------------- 9r01dt51f46tf SESSION CURSOR CACHED LHR@orclasm > SELECT a.VERSION_COUNT,a.EXECUTIONS,a.PARSE_CALLS,a.LOADS FROM v$sqlarea a WHERE a.SQL_ID='9r01dt51f46tf'; VERSION_COUNT EXECUTIONS PARSE_CALLS LOADS ------------- ---------- ----------- ---------- 1 4 3 1 --從結果可以看到，在SQL語句「SELECT /*test scc*/ COUNT(*) FROM SCOTT.EMP;」第4次執行完畢後，Oracle已經將其對應的會話游標快取在當前會話的PGA中了，而此時快取的會話游標的類型為「SESSION CURSOR CACHED」。下面開始第5次執行： LHR@orclasm > SELECT /*test scc*/ COUNT(*) FROM SCOTT.EMP; COUNT(*) ---------- 14 LHR@orclasm > SELECT D.SQL_ID,D.CURSOR_TYPE FROM V$OPEN_CURSOR D WHERE D.SID=USERENV('SID') AND D.SQL_TEXT LIKE 'SELECT /*test scc*/ COUNT(*)%' ; SQL_ID CURSOR_TYPE ------------- ---------------------------------------------------------------- 9r01dt51f46tf SESSION CURSOR CACHED LHR@orclasm > SELECT a.VERSION_COUNT,a.EXECUTIONS,a.PARSE_CALLS,a.LOADS FROM v$sqlarea a WHERE a.SQL_ID='9r01dt51f46tf'; VERSION_COUNT EXECUTIONS PARSE_CALLS LOADS ------------- ---------- ----------- ---------- 1 5 3 1
從結果看出，快取的會話游標的類型依然為「SESSION CURSOR CACHED」，不再改變。
（五）會話游標的dump文件
會話游標的dump文件可以通過Level值為3的errorstack得到，獲取過程如下所示：
SELECT COUNT(*) FROM SCOTT.EMP;--執行5次，讓其快取在PGA中 ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME ERRORSTACK LEVEL 3'; SELECT COUNT(*) FROM SCOTT.EMP; ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME ERRORSTACK OFF'; SELECT VALUE FROM V$DIAG_INFO;
本文選自《Oracle程式設計師面試筆試寶典》，作者：李華榮。
