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 setmypidStatement 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不会允许这种情况发生。