Oracle Cursor

遊標是一個基本物件，它是SQL語句或PL/SQL程式設計式構造的一種完整可執行表示，可以被任何授權會話使用和重用。遊標必須被建立，定位(透過搜尋來查詢)，消毀(回收)，失效與過載。如果遊標的任何部分不在共享池中，並且出於任何原因需要，則必須重新載入該遊標，這會降低效能。

開發人員通常對遊標有很好的理解因為他們需要專門建立，開啟，執行，獲取與關閉遊標。DBA通常將遊標作為與SQL相關的簡單記憶體塊來看待。然而，這種過於簡單的關點限制了我們為與遊標相關的效能問題建立解決方案的能力。因此，如果花時間更好地理解遊標，將會注意到效能解決方案選項將顯著增加。

父遊標與子游標
遊標這個術語本身是一個抽象概念，用來引用共享的資訊(位於共享SQL區)，私有資訊(位於會話的PGA)與用來定位各種遊標元件的library cache chain節點(當引用library cache時就叫作handle)。不幸地是這種多用途的定義也增加了混淆。當一個遊標被關閉時，Oracle不會簡單的回收這三個遊標元件。而是Oracle可能會按需來回收遊標元件。

一個遊標第一次執行時，會存在一個父遊標與子游標。後續的會話，即使相同的會話執行相同的SQL語句(雜湊值相同)，可能會使用不同的子游標。雖然SQL語句在文字上完全相同，但是建立子游標是為了捕獲特定的特徵，比如最佳化模式的差異(例如first_rows)，這會導致不同的執行計劃或不同的會話級引數(cursor_sharing=similar)。下面的例子簡單的顯示了相同會話執行相同SQL語句兩次，只是在兩次執行之間執行了alter session命令，這足以強制建立一個額外的子游標。trace命令用來證明建立了兩個子游標。

SQL> oradebug setmypid
Statement processed.
SQL> alter session set optimizer_mode = all_rows;
Session altered.
SQL> select * from dual;
D
-
X
SQL> alter session set optimizer_mode = first_rows;
Session altered.
SQL> select * from dual;
D
-
X
SQL> alter session set events 'immediate trace name library_cache level 10';
Session altered.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/jy/jy1/trace/jy1_ora_6675.trc

下面的內容是上面的trace命令所建立的跟蹤檔案中的一部分內容。我們透過搜尋select * from dual來定位我們關心的內容並檢查SQL語句。此時，我們感興趣的是，這條SQL語句僅由一個會話執行，但它建立了兩個子游標。

Bucket: #=108289 Mutex=0xc5eeae00(3298534883328, 1118, 0, 6)
  LibraryHandle:  Address=0xcf2e9a48 Hash=382da701 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
    ObjectName:  Name=select * from dual
      FullHashValue=0d54fc02b2ad4044a2cb0974382da701 Namespace=SQL AREA(00) Type=CURSOR(00) ContainerId=1 ContainerUid=1 Identifier=942515969 OwnerIdn=0
    Statistics:  InvalidationCount=0 ExecutionCount=2 LoadCount=3 ActiveLocks=0 TotalLockCount=2 TotalPinCount=1
    Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=2 Version=0 BucketInUse=1 HandleInUse=1 HandleReferenceCount=0
    Concurrency:  DependencyMutex=0xcf2e9af8(0, 2, 0, 0) Mutex=0xcf2e9b98(768, 37, 0, 6)
    Flags=RON/PIN/TIM/PN0/DBN/[10012841] Flags2=[0000]
    WaitersLists:
      Lock=0xcf2e9ad8[0xcf2e9ad8,0xcf2e9ad8]
      Pin=0xcf2e9ab8[0xcf2e9ab8,0xcf2e9ab8]
      LoadLock=0xcf2e9b30[0xcf2e9b30,0xcf2e9b30]
    Timestamp:  Current=04-17-2019 09:33:16
    HandleReference:  Address=0xcf2e9c20 Handle=(nil) Flags=[00]
    ReferenceList:
      Reference:  Address=0x84497a08 Handle=0x818e2850 Flags=ROD[21]
      Reference:  Address=0x84c9e3d0 Handle=0xb28b76a0 Flags=ROD[21]
    LibraryObject:  Address=0xbd5972a8 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
      DataBlocks:
        Block:  #='0' name=KGLH0^382da701 pins=0 Change=NONE
          Heap=0x83043cc0 Pointer=0xbd597378 Extent=0xbd597200 Flags=I/-/P/A/-/-/-
          FreedLocation=0 Alloc=3.390625 Size=3.976562 LoadTime=4111958371
      ChildTable:  size='16'
        Child:  id='0' Table=0xbd598128 Reference=0xbd597bf8 Handle=0xb38e2928
        Child:  id='1' Table=0xbd598128 Reference=0xbd597f48 Handle=0xbdfc20a8
    NamespaceDump:
      Parent Cursor:  sql_id=a5ks9fhw2v9s1 parent=0xbd597378 maxchild=2 plk=n ppn=n prsfcnt=0 obscnt=0
        CursorDiagnosticsNodes:
          ChildNode:  ChildNumber=0 ID=3 reason=Optimizer mismatch(10) size=3x4 optimizer_mode_hinted_cursor=0 optimizer_mode_cursor=1 optimizer_mode_current=2

庫快取物件之間的關係不僅為執行目的而必須維護，而且當其中一個元件發生更改時也必須維護。假設一個表被2000個SQL語句，100個函式與20個包所引用。現在假設表的一列被重新命名。Oracle將會使所有相關的SQL語句與程式結構失效。這可能導致在請求latching與locking時出現級聯效應。多個相關會話、失效、重新編譯和計時的組合導致整個Oracle例項被鎖定。很明顯Oracle已經知道了這種問題的嚴重性並且積極的減小出現這種情況的可能性。但每個DBA要了解library cache之間的關係是非常複雜的並且有時可能導致出現問題。

Cursor Building
當在library cache中搜尋並沒有找到遊標時就會建立遊標。這就是硬解析。很明顯這是一個相對昂貴的操作它需要請求記憶體管理(分配與可能回收)，使用latching來確保序列化，使用locking來阻止不合適的更改，執行核心程式碼需要消耗CPU資源，和可能需要IO操作來將資料字典資訊插入row cache中。

遊標是使用共享池中的資料來建立的，如果資料當前不在共享池中，Oracle將建立它自己的SQL語句來從資料字典表中檢索資料。Oracle動態建立的SQL會命名為遞迴SQL並執行它。為了建立一個遊標Oracle需要的資料是最佳化器統計資訊，會話資訊，安全資訊，物件資訊與物件關聯資訊。

遊標是由稱為堆的共享池記憶體塊建立的。傳統上，不同的SQL語句需要不同大小的記憶體塊。常見的SQL語句通常請求4KB大小的記憶體塊。與free exten管理一樣，請求不一致大小的記憶體塊會導致分配，效能與效率問題。從Oracle 10gr2開始，Oracle將所有的記憶體塊定義為4KB。當合適的記憶體塊不能快速地找到時，Oracle最終可能會放棄並posts一個4031錯誤“out of shared poll memory”並停止SQL語句的處理。

Cursor Searching Introduction
與buffer cache中的每個buffer一樣，每個父遊標與子游標必須被定位並且搜尋必須要快速。這將請求記憶體，一個搜尋結構，序列化，核心程式碼與大量CPU資源。

因為遊標與程式結構存放在library cache中，有一個結構來定位物件。Oracle選擇使用雜湊演算法與相關雜湊類似結構。解析操作的一部分是判斷一個遊標當前是否存放在library cache中了。如果確實在library cache中找到了這個遊標，進行了一些解析操作，因此它確實是一個軟解析。然而如果在library cache中沒有找到這個遊標，整個遊標需要被建立，因此它就是硬解析。遊標建立與硬解析是相當昂貴的操作。

Cursor Pinning and Locking
固定遊標類似於固定buffer。它被用來確保當遊標被引用時不會被回收(有時也叫破壞)。遊標顯然不是關係結構，但是SQL與關係結構(例如employee表)相關，關係結構用於構建遊標(例如sys.col$)，因此使用了鎖——也就是說，使用了佇列。遊標佇列也叫作CU佇列並且就像其它佇列一樣透過Oracle的等待介面可以檢測。

當建立與執行遊標時就要固定遊標。這是很容易理解的，當你建立一個遊標時，它是一種記憶體結構，你不想其它的程式回收相關的記憶體。正常情況下，遊標在建立與執行完成後不會出現固定的情況。這意味著在你執行一個遊標後且等待2分鐘後你想再次執行相同的遊標，這時遊標可能已經被回收了。如果出現這種情況，在library cache中找不到需要的遊標，將會執行硬解析，它將完全重新建立遊標。

在建立與執行遊標時也可能會出現鎖定的情況。但它不同於固定遊禁。固定的關注點在於記憶體回收。而鎖是確保與遊標相關的表在建立與執行遊標時不被修改。顯然，這可能會造成一些相當奇怪的情況，而Oracle不會允許這種情況發生。