(转)Oracle 游标（cursor）说明

原博客地址：http://blog.csdn.net/tianlesoftware/article/details/6638062

 

一.  Cursor说明

       Oracle里的cursor分为两种：一种是shared cursor，一种是session cursor。

 

1.1 Shared cursor 说明

       sharedcursor就是指缓存在librarycache（SGA下的Shared Pool）里的一种library cache object，说白了就是指缓存在library cache里的sql和匿名pl/sql。 它们是Oracle缓存在librarycache中的几十种librarycache object之一，它所属于的namespace是CRSR（也就是cursor的缩写）。

      

       Oracle Shared pool 详解

       http://blog.csdn.net/tianlesoftware/article/details/6560956

 

       Oracle Namespace 说明

       http://blog.csdn.net/tianlesoftware/article/details/6624122

 

 

Sharedpool 主要组成由Library cache 和 Data Dictionary cache：

       （1）Library Cache主要用于存储SQL语句、SQL语句相关的解析树、执行计划、PL/SQL程序块（包括匿名程序块、存储过程、包、函数等）以及它们转换后能够被Oracle执行的代码等，这部分信息可以通过v$librarycache视图查询；

       （2）Data Dictionary Cache主要用于存放数据字典信息，包括表、视图等对象的结构信息，用户以及对象权限信息，这部分信息相对稳定，在Shared Pool中通过字典缓存单独存放，字典缓存的内容是按行（Row）存储的（其他数据通常按Buffer存储），所以又被称为Row Cache，其信息可以通过v$rowcache查询。

 

关于LibraryCache 的管理机制，参考blog：

       Oracle Library cache 内部机制 说明

       http://blog.csdn.net/tianlesoftware/article/details/6629869

 

 

shared SQL, parent cursor和child cursor 概念

所有SQL都是Oracle暗式共享的(implicitlysharable)。当用户A发出一条SQL后，Oracle会根据SQL文本内容生成hash value(10g还有唯一的SQL_ID)，以便能够快速找到 Shared pool已经存在的相同SQL。如果找不到，则Oracle会为这个SQL创建一个parent cursor和一个child cursor，这与SQL是否共享是没有关系的。

       parentcursor包含了SQL TEXT和相关的hash value，v$sqlarea中的每一行代表了一个parent cursor，根据address表示了其内存地址。

       childcursor包含了SQL的metadata，即使得这个SQL可以执行的所有相关信息，如OBJECT和权限，优化器设置，执行计划等。v$sql中中 的每一行表示了一个child cursor，根据hash value和address与parent cursor 关联。child cursor有自己的address，即V$SQL.CHILD_ADDRESS。

       第一个child cursor总是使用0来表示其创建顺序，V$SQL.CHILD_NUMBER = 0。因此，当从V$SQL_PLAN中查找某个SQL的执行计划时，要注意你写对了CHILD_NUMBER。

       如果有多个child cursor，则表示parent cursor有多个版本，v$sqlarea中的version_count字段就会纪录下来。

 

Oracle 高 Version counts 问题说明

http://blog.csdn.net/tianlesoftware/article/details/6628232

 

 

       当SQL 语句第一次执行时，会进行硬解析。此时会生成一个parent cursor 和一个child cursor。 他们都属于Shared cursor。 一个SQL 语句至少有一个parent cursor 和一个child cursor。 parent cursor 里保存的是hash value 和所有child cursor 的一个地址。 child cursor 的Heap 6（SQL context）里保存了SQL 的执行计划。

       sharedcursor 和child cursor 存放在library cache，而library cache 是用hash table 来存放的，hash table 又是由一系列buckets 组成。 huckets 指向library chache object handle，handle 指向一个library cache object，即heap 0. 这个就是parent。 heap 0里保存了hash 值和所有child cursor 的地址。 parent cursor 和child cursor 结构是完全一样的。

 

 

       当第二次执行相同的SQL 时， 因为第一次硬解析时已经生成了parent cursor和child cursor。 所以在SQL 语句进行hash 运算后，在该hash value在hash bucket里进行查找，如果找到了相同的parent cursor，那么就使用该parent cursor和child cursor。 这个就是软解析。

       也可能因为一些其他的原因，导致child cursor 不能重用。 这时，虽然parent cursor 相同，但是Oracle 还是需要重新生成一个child cursor。 这个就是version count。

       如果连parent cursor 都没有找到，这时候就需要进行硬解析。

 

 

1.2 Session cursor

       sessioncursor其实就是指的跟这个session相对应的server process的PGA里（准确的说是UGA）的一块内存区域（或者说内存结构），它的目的是为了处理且一次只处理一条sql语句。

       一个session cursor只能对应一个shared cursor，而一个shared cursor却可能同时对应多个session cursor。

       当某个session cursor和其对应的shared cursor建立关联后，如果把cursor_space_for_time调成true，当一个session cursor处理完一条sql后，它就不会被destroy，Oracle会把其cache起来（我们称之为soft closed session cursor），这么做的目的是很明显的，因为这个soft closed掉的sessioncursor已经和包含其执行计划和parse tree的sharedcursor建立了联系，那么当在这个session中再次执行同样的sql的时候，Oracle就不再需要去扫描library cache了，直接把刚才已经soft closed掉的session cursor拿过来用就好了，这就是所谓的软软解析。

 

 

二. Session Cursor 说明

2.1官网对cursor的定义   

   Ahandle or name for a private SQL area in the PGA. Because cursors are closely associated with private SQL areas, theterms are sometimes used interchangeably.

       This isthe object that lives in a session’s memory , that dies, therefore, with the session,and whose metadata is exposed in the v$open_cursor view; it supports anindividual session’s SQL processing

 

•Stack Space是用来存储用户会话变量和数组的存储区域；

•User Session Data是为用户会话使用的附加存储区。

|--Session Information

|--Sort Area

|--Cursor Information

       注意Session information（用户会话信息）在独占服务器中与在共享服务器中所处的内存区域是不同的。

 

 

2.2 PGA的可变区主要组成

       1）私有SQL区

       2）游标和SQL区

       3）会话内存

 

2.2.1私有SQL区（PrivateSQL Area）

       私有SQL区包含了绑定变量值和运行时期内存结构信息等数据。每一个运行SQL语句的会话都有一个块私有SQL区。所有提交了相同SQL语句的用户都有各自的私有SQL区，并且他们共享一个共享SQL区。因此，一个共享SQL区可能和多个私有共享区相关联。

一个游标的私有SQL区又分为两个生命周期不同的区：

       永久区：包含绑定变量信息。当游标关闭时被释放。

       运行区：当执行结束时释放。

       创建运行区是一次执行请求的第一步。对于INSERT、UPDATE和DELETE语句，Oracle在语句运行结束时释放运行区。对于查询操作，Oracle只有在所有记录被fetch到或者查询被取消时释放运行区。

 

2.2.2 游标和SQL区（Cursorsand SQL Areas）

       一个Oracle预编译程序或OCI程序的应用开发人员能够很明确的打开一个游标，或者控制一块特定的私有SQL区，将他们作为程序运行的命名资源。另外，oracle隐含的为一些SQL语句产生的递归调用（前面有介绍，读取数据字典信息）也使用共享SQL区。

       私有SQL区是由用户进程管理的。如何分配和释放私有SQL区极大的依赖与你所使用的应用工具。而用户进程可以分配的私有SQL区的数量是由参数OPEN_CURSORS控制的，它的默认值是50。

       在游标关闭前或者语句句柄被释放前，私有SQL区将一直存在（但其中的运行区是在语句执行结束时被释放，只有永久区一直存在）下去。应用开发人员可以通过将所有打开的不再使用的游标都关闭来释放永久区，以减少用户程序所占用的内存。

 

2.2.3 会话内存（SessionMemory）

       会话内存是一段用于保存会话变量（如登录信息）和其他预会话相关信息的内存。对于共享服务器模式下，会话内存是共享的，而不是私有的。

       对于复杂的查询（如决策支持系统中的查询），运行区的很大一部分被那些内存需求很大的操作分配给SQL工作区（SQL WorkArea）。这些操作包括：

基于排序的操作（ORDERBY、GROUP BY、ROLLUP、窗口函数）；

       HashJoin

       Bitmapmerge

       Bitmapcreate

       例如，一个排序操作使用工作区（这时也可叫排序区Sort Area）来将一部分数据行在内存排序；而一个Hash Join操作则使用工作区（这时也可以叫做Hash区 Hash Area）来建立Hash表。如果这两种操作所处理的数据量比工作区大，那就会将输入的数据分成一些更小的数据片，使一些数据片能够在内存中处理，而其他的就在临时表空间的磁盘上稍后处理。尽管工作区太小时，Bitmap操作不会将数据放到磁盘上处理，但是他们的复杂性是和工作区大小成反比的。因此，总的来说，工作区越大，这些操作就运行越快。

       工作区的大小是可以调整的。一般来说，大的工作区能让一些特定的操作性能更佳，但也会消耗更多的内存。工作区的大小足够适应输入的数据和相关的SQL操作所需的辅助的内存就是最优的。如果不满足，因为需要将一部分数据放到临时表空间磁盘上处理，操作的响应时间会增长。

 

2.3 open_cursors 参数说明

       OPEN_CURSORS specifies the maximum number of open cursors(handles to private SQL areas) a session can have at once. You can usethis parameter to prevent a session from opening an excessive number ofcursors.

       Itis important to set the value of OPEN_CURSORS high enough to prevent yourapplication from running out of open cursors. The number will vary from oneapplication to another. Assuming that a session does not open the number ofcursors specified by OPEN_CURSORS, there is no added overhead to setting thisvalue higher than actually needed.

 

2.4  CURSOR_SPACE_FOR_TIME

       Ifyou have no library cache misses, then you might be able to accelerateexecution calls by setting the value of the initialization parameter CURSOR_SPACE_FOR_TIME to true. This parameter specifieswhether a cursor can be deallocated from the library cache to make room for anew SQL statement. CURSOR_SPACE_FOR_TIME has the following valuesmeanings:

       （1）If CURSOR_SPACE_FOR_TIMEis set to false (the default), then a cursor can be deallocated from the library cache regardless ofwhether application cursors associated with its SQL statement are open.In this case, Oracle Database must verify that the cursor containing the SQLstatement is in the library cache.

       （2）If CURSOR_SPACE_FOR_TIMEis set to true, then a cursorcan be deallocated only when all application cursors associated with itsstatement are closed. In this case, Oracle Database need not verify thata cursor is in the cache because it cannot be deallocated while an applicationcursor associated with it is open.

 

 

       Settingthe value of the parameter to true saves Oracle Database a small amount of timeand can slightly improve the performance of execution calls. This value alsoprevents the deallocation of cursors until associated application cursors areclosed.

 

 

       Do not set the value of CURSOR_SPACE_FOR_TIMEto true if you have found library cache misses on execution calls. Suchlibrary cache misses indicate that the shared pool is not large enough to holdthe shared SQL areas of all concurrently open cursors.

       If the value is true, and if the shared pool has no space fora new SQL statement, then the statement cannot be parsed, and Oracle Databasereturns an error saying that there is no more shared memory.

       Ifthe value is false, and if there is no space for a new statement, then OracleDatabase deallocates an existing cursor. Although deallocating a cursor couldresult in a library cache miss later (only if the cursor is reexecuted), it ispreferable to an error halting your application because a SQL statement cannotbe parsed.

 

 

       Do not set the value of CURSOR_SPACE_FOR_TIME to true if theamount of memory available to each user for private SQL areas is scarce.This value also prevents the deallocation of private SQL areas associated withopen cursors. If the private SQL areas for all concurrently open cursors fillsyour available memory so that there is no space for a new SQL statement, thenthe statement cannot be parsed. Oracle Database returns an error indicatingthat there is not enough memory.

 

 

关于cursor_space_for_time有三点需要注意：

       (1)10.2.0.5和11.1.0.7里它已经作废了；

       (2)把它的值调成true后如果还同时用到了绑定变量，则由于Bug 6696453的关系,可能会导致logical data corruption；

       (3)把它的值调成true后，所有的child cursor在执行完后依然会持有library cache pin，直到其父cursor关闭

 

 

2.5  Caching Session Cursors

       Thesession cursor cache contains closed session cursorsfor SQL and PL/SQL, including recursive SQL.

       This cache can be useful for applications that useOracle Forms because switching from one form to another closes all sessioncursors associated with the first form. If an application repeatedly issuesparse calls on the same set of SQL statements, then reopening session cursorscan degrade performance. By reusing cursors, thedatabase can reduce parse times, leading to faster overall execution times.

 

 

2.5.1 How the Session Cursor Cache Works

       Asession cursor represents an instantiation of a sharedchild cursor, which is stored in the sharedpool, for a specific session. Each session cursor stores a reference toa child cursor that it has instantiated.

 

 

       OracleDatabase checks the library cache to determine whethermore than three parse requests have been issued on a given statement. Ifa cursor has been closed three times, then Oracle Database assumes that thesession cursor associated with the statement should be cached and moves thecursor into the session cursor cache.

 

 

       SharedPool 的本质是共享，Oracle 会检查library cache 中parse request的次数是否超过3次。 如果一个cursor 被关闭3次，Oracle 就认为这个session cursor 需要移动到session cursor cache。 而这个session cursor cache 是保存在SharedPool中的。 即对应的cursor 从PGA 移动到了SGA。 那么放到Session cursor cache 之后，对于同样的查询直接从cursor cache中取就可以了，从而减少解析次数。

 

 

       Subsequentrequests to parse a SQL statement by the same session search an array forpointers to the shared cursor. If the pointer is found, then the databasedereferences the pointer to determine whether the shared cursor exists. To reusea cursor from the cache, the cache manager checks whether the cached states ofthe cursor match the current session and system environment.

 

 

Note:

       Reuse of a cached cursor still registers as a parse, eventhough it is not a hard parse.

       AnLRU algorithm removes entries in the session cursor cache to make room for newentries when needed. The cache also uses an internal time-based algorithm toevict cursors that have been idle for an certain amount of time.

       Oracle 使用LRU 算法来管理session cursor cache。如果有新的游标需要缓存，而当前游标缓存已经满，最少使用的游标将会被清除出去。

 

 

2.5.2 Enabling the Session Cursor Cache

The following initialization parameters arerelevant to the cursor cache:

（1）SESSION_CACHED_CURSORS

       This parameter sets the maximum number of cached closedcursors for each session. The default setting is 50. You can use thisparameter to prevent a session from opening an excessive number of cursors,thereby filling the library cache or forcing excessive hard parses.

       --当前Session已经关闭并被缓存的游标的最大数量,即单个session中同时能cache住的softclosed session cursor的最大数量。

 

 

（2）OPEN_CURSORS

       Thisparameter specifies the maximum number of cursors a session can have opensimultaneously. For example, if OPEN_CURSORS is set to1000, then each session can have up to 1000 cursors open at one time.

       --open_cursors指的是在单个session中同时能以open状态存在的sessioncursor的最大数量

 

 

       SESSION_CACHED_CURSORSand OPEN_CURSORS parameters are independent. For example, you can setSESSION_CACHED_CURSORS higher than OPEN_CURSORS because session cursors are notcached in an open state.

 

 

To enable caching of session cursors:

（1）Determine the maximum number ofsession cursors to keep in the cache.

（2）Do one of the following:

       A)To enable caching statically, set the initialization parameterSESSION_CACHED_CURSORS to the number determined in the previous step.

       B) To enable caching dynamically, execute the following statement:

              ALTERSESSION SET SESSION_CACHED_CURSORS = value;

       V$OPEN_CURSOR中显示的当前Session游标缓存中游标，V$SESSION_CACHED_CURSOR，当前Session已经关闭并被缓存的游标。

 

 

2.5.3Tuning the Session Cursor Cache

       Youcan query V$SYSSTAT to determine whether the session cursor cache issufficiently large for the database instance.

To tune the session cursor cache:

（1）Determine how many cursors are currently cached ina particular session.

/* Formatted on 2011/7/20 19:52:51(QP5 v5.163.1008.3004) */

SELECT a.VALUEcurr_cached,

       p.VALUEmax_cached,

       s.username,

       s.sid,

       s.serial#

  FROMv$sesstat a,

       v$statname b,

       v$session s,

       v$parameter2p

WHERE     a.statistic# = b.statistic#

       AND s.sid = a.sid

       AND a.sid = &sid

       AND p.name = 'session_cached_cursors'

       AND b.name = 'sessioncursor cache count';

 

 

（2）Find the percentage of parse callsthat found a cursor in the session cursor cache.

/* Formatted on 2011/7/20 19:55:42(QP5 v5.163.1008.3004) */

SELECT cach.VALUEcache_hits,

       prs.VALUEall_parses,

       ROUND ( (cach.VALUE / prs.VALUE) * 100, 2) AS "%found in cache"

  FROMv$sesstat cach,

       v$sesstatprs,

       v$statnamenm1,

       v$statnamenm2

WHERE     cach.statistic#= nm1.statistic#

       AND nm1.name = 'sessioncursor cache hits'

       AND prs.statistic#= nm2.statistic#

       AND nm2.name = 'parsecount (total)'

       AND cach.sid = &sid

       AND prs.sid = cach.sid;

 

 

（3）Consider increasingSESSION_CURSOR_CACHE when the following statements are true:

       1）The session cursor cache count isclose to the maximum.

       2）The percentage of session cursorcache hits is low relative to the total parses.

       3）The application repeatedly makesparse calls for the same queries.

 

http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/memory.htm#PFGRF94335

 

 

三.  Cursor 的生命周期

3.1 生命周期

（1）打开游标（dbms_sql.open_cursor)

       Open cursor: A memory structure for the cursor isallocated in the server-side private memory of the server process associatedwith the session, the user global area (UGA). Note that no SQL statement isassociated with the cursor yet.

       系统会在UGA中分配相关的内存结构,就是获得游标句柄的过程，这时的游标还未和sql语句有关联；

（2）解析游标(dbms_sql.parse)

       Parse cursor: A SQL statement is associated with thecursor. Its parsed representation that includes the execution plan (whichdescribes how the SQL engine will execute the SQL statement) is loaded in theshared pool, specifically, in the library cache. The structure in the UGA isupdated to store a pointer to the location of the shareable cursor in thelibrary cache. The next section will describe parsing in more detail.

       有一条sql与游标相关联，且单个游标句柄可能用于许多不同的被分析的语句，但一次只有一个语句有效，并将执行解析过后的执行计划放在library cache（SGA的shared pool下）中，UGA中生成指向这个共享游标的指针；即session cursor 指向shared cursor。 一个session cursor 只能指向一个shared cursor，而一个shared cursor 可以指向多个session cursor。
（3）定义输出变量（dbms_sql.define_column)

       Define output variables: If the SQL statement returnsdata, the variables receiving it must be defined. This is necessary not onlyfor queries but also for DELETE, INSERT, and UPDATE statements that use theRETURNING clause.

       如果sql语句返回数据，必须定义接收数据的变量，对delete，update，insert来说是returning；

（4）绑定输入变量（dbms_sql.bind_variable/bind_array)

       Bind input variables: If the SQL statement uses bindvariables, their values must be provided. No check is performed during thebinding. If invalid data is passed, a runtime error will be raised during theexecution.

       绑定过程是不做检查的；
（5）执行游标(dbms_sql.execute)

       Execute cursor: The SQL statement is executed. But becareful, because the database engine doesn’t always do anything significantduring this phase. In fact, for many types of queries, the real processing isusually delayed to the fetch phase.

       这步数据库引擎其实不做什么重要事情，而对大多数sql语句来说，真正处理过程是到fetch获取数据阶段；
（6）获取游标(dbms_sql.fetch_rows)

       Fetch cursor: If the SQL statement returns data, thisstep retrieves it. Especially for queries, this step is where most of theprocessing is performed. In the case of queries, rows might be partiallyfetched. In other words, the cursor might be closed before fetching all therows.

       真正的处理过程，有返回数据的话，必须提供输出变量(dbms_sql.column_value)；
（7）关闭游标(dbms_sql.close_cursor)

       Close cursor: The resources associated with the cursorin the UGA are freed and consequently made available for other cursors. Theshareable cursor in the library cache is not removed. It remains there in thehope of being reused in the future.

       释放UGA中相关资源，库缓存中共享游标不会被清除。

 

 

3.2 查看cursor 流程图

通过查询dba_source视图，可以看到dbms_sql包的图解：

SQL>select text fromdba_source where name='DBMS_SQL';

 

The flow of procedurecalls will typically look like this:

  
         

  

   

 

也就是说共享游标，就是在库缓存中的，将被尽可能的保持长久；而UGA中的相关游标指针和私有数据，将在游标close时被释放；

 

 

3.3 cursor （SQL）解析过程

       （1）IncludeVPD predicates: If Virtual Private Database (VPD,formerly known as row-level security) is in use and active for one of thetables referenced in the parsed SQL statement, the predicates generated by thesecurity policies are included in its WHERE clause.

       （2）Checksyntax, semantics, and access rights: This step makessure not only that the SQL statement is correctly written but also that allobjects referenced by the SQL statement exist and the current user parsing ithas the necessary privileges to access them.

       --语法语义以及访问权限的检查，也就是检查sql的语句是否正确，访问对象是否存在，是否有访问权限等；

       （3）Storeparent cursor in library cache: Whenever a shareableparent cursor is not yet available, some memory is allocated from the librarycache, and a new parent cursor is stored inside it. The key informationassociated with the parent cursor is the text of the SQL statement.

       --将父游标load到库cache中；

       （4）Logical optimization: Duringthis phase, new and semantically equivalent SQL statements are produced byapplying different transformation techniques. In doing so, the amount of executionplans considered, the search space, is increased. The purpose is to exploreexecution plans that would not be considered without such transformations.

       --逻辑优化：通过不同的转换技巧，生成相同语义的sql语句；

       （5）Physicaloptimization: During this phase, several operations areperformed. At first, the execution plans related to each SQL statementresulting from the logical optimization are generated. Then, based onstatistics found in the data dictionary or gathered through dynamic sampling, acost is associated with each execution plan. Lastly, the execution plan withthe lowest cost is selected. Simply put, the query optimizer explores thesearch space to find the most efficient execution plan.

       --物理优化：生成每个逻辑优化产生的sql语句的执行计划，然后根据数据字典找到统计信息，计算出逻辑优化所产生的语义相同的sql最佳执行计划；

       （6）Storechild cursor in library cache: Some memory isallocated, and the shareable child cursor is stored inside it and associatedwith its parent cursor. The key elements associated with the child cursor arethe execution plan and the execution environment.

       --将子游标load到库缓存中，与子游标最关键内容就是执行计划和执行环境；

 

 

      Once stored in the library cache, parent and child cursorsare externalized through the views v$sqlarea and v$sql, respectively.The cursors are identified in three columns: address, hash_value, andchild_number. With address and hash_value, the parent cursors are identified; withall three values, the child cursors are identified. In addition, as of OracleDatabase 10g, it is also possible, and it is more common as well, to use sql_idinstead of the pair address and hash_value for the same purpose.

       Whenshareable parent and child cursors are available and, consequently, only thefirst two operations are carried out, the parse is called a soft parse. Whenall operations are carried out, it is called a hard parse.

 

 

       总之解析过程就是将父游标和子游标缓存到库缓存中。其中v$sqlarea是父游标相关信息视图，v$sql是子游标的。

       v$sql中通过child_number,hash_value,address来确定一个子游标，而v$sqlarea通过address和hash_value就可以确定一个父游标；而从10g过后，通过sql_id就能确定一个游标。

 

       在SQL 优化中，应该尽量使用绑定变量，这样就能避免硬解析，减少context switch次数。 硬解析会造成父游标的不能共享，对于父游标相同的情况下，子游标的不能共享可以通过v$sql_shared_cursor 视图查看。

 

 

在如下blog 里有一个示例：

       由 bind_mismatch 引起的 大量version_count 问题

       http://blog.csdn.net/tianlesoftware/article/details/6566658

       Oracle SQL的硬解析和软解析
       http://blog.csdn.net/tianlesoftware/article/details/5458896

 

 

3.4  Session Cursor 的分类

session cursor又分为三种：分别是implicit cursor，explicit cursor和ref cursor。

 

 

关于Cursor 的分类，官网有详细的说明和示例：

http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/static.htm#LNPLS99957

 

       A cursor is a pointer to a private SQL area（In PGA） that stores information aboutprocessing a specific SELECT or DML statement.

       Thecursors that this chapter explains are session cursors. A session cursor lives in session memory until thesession ends, when it ceases to exist. Session cursors are different from thecursors in the private SQL area of the program global area (PGA), which areexplained in OracleDatabase Concepts.

       Asession cursor that is constructed and managed by PL/SQL is an implicitcursor. A session cursor that you construct and manage is an explicitcursor.

       Youcan get information about any session cursor from its attributes(which you can reference in procedural statements, but not in SQL statements).

       Tolist the session cursors that each user session currently has opened andparsed, query the dynamic performance view V$OPEN_CURSOR, explainedin OracleDatabase Reference.

 

 

3.4.1 implicit cursor

       Implicitcursors are managed automatically by PL/SQL so you are not required to writeany code to handle these cursors. However, you can track information about theexecution of an implicit cursor through its cursor attributes. It is the PL/SQLrun-time system that manages the session cursor without the help of explicitlanguage constructs that specify operations like open,parse, bind, execute, fetch, and close.

 

 

       Implicitcursor attributes return information about the execution of DML statements,such as insert, update, delete and select into statements. The values of thecursor attributes always refer to the most recently executed SQL statement.Before Oracle opens the implicit cursor, the implicit cursor attributes yieldNULL.

       SQL%FOUND

       SQL%NOTFOUND

       SQL%ISOPEN

       SQL%ROWCOUNT

       SQL%BULK_ROWCOUNT

 

 

3.4.2  explicit cursor

       Anexplicit cursor cannot be defined using dynamic SQL; embedded SQL is the onlypossibility.

       Critically,though the programmer invents the name of an explicit cursor, this is not avariable: it cannot be used as an actual argument in a subprogram invocation;nor can it be returned by a function. In this way, it is very much like aprocedure; it can be forward declared and the declaration and the definitioncan be split between a package and its body; and it can have formal parameters.

 

 

       youuse three commands to control a explicit cursor: OPEN, FETCH, and CLOSE.

       donot use ‘for update’or ‘for update nowait’when you open explicit cursor.

‘where current of cursorname’equal torowid! Every explicit cursor and cursor variable has four attributes:

       CURSORNAME%FOUND

       CURSORNAME%NOTFOUND

       CURSORNAME%ISOPEN

       CURSORNAME%ROWCOUNT

 

 

3.4.3 ref cursor

       likea cursor, a ref cursor points to the current row in the result set of amulti-row query. A ref cursor is more flexible because it is not tied to aspecific query. You can open a ref cursor for any query that returns the rightset of columns.

       thisis a PL/SQL-only data type declared. A ref cursor may be used to declare avariable, a formal parameter for a subprogram, or a function’s return value.

 

 

type typ_cur_dep is ref cursor returndep%rowtype;

cur_dep typ_cur_dep;

type typ_result is record(pkt.pk%type, v1t.v1%type);

type typ_cur_strong is ref cursor returntyp_result;

cur_strong typ_cur_stong;

type typ_cur_weak is ref cursor;

cur_weak typ_cur_weak;

cur_weak_sys SYS_REFCURSOR;

 

 

open-for fetch close

CURSORNAME%FOUND

CURSORNAME%NOTFOUND

CURSORNAME%ISOPEN

CURSORNAME%ROWCOUNT

 

 

四. cursor 相关的等待事件

官网说明：

http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/waitevents003.htm#sthref3883

 

 

4.1 cursor: mutex X

       Thesession requests the mutex for a cursor object in exclusive mode, and it mustwait because the resource is busy. The mutex is busy because either the mutexis being held in exclusive mode by another session or the mutex is being heldshared by one or more sessions. The existing mutex holder(s) must release themutex before the mutex can be granted exclusively.

 

 

4.2 cursor: pin S

       Asession waits on this event when it wants to update a shared mutex pin andanother session is currently in the process of updating a shared mutex pin forthe same cursor object. This wait event should rarely be seen because a sharedmutex pin update is very fast.

       WaitTime: Microseconds

 

 

4.3 cursor: pin S wait on X

       Asession waits for this event when it is requesting a shared mutex pin and anothersession is holding an exclusive mutex pin on the same cursor object.

       WaitTime: Microseconds

 

 

4.4 cursor: pin X

       Asession waits on this event when it is requesting an exclusive mutex pin for acursor object and it must wait because the resource is busy. The mutex pin fora cursor object can be busy either because a session is already holding itexclusive, or there are one or more sessions which are holding shared mutexpin(s). The exclusive waiter must wait until all holders of the pin for thatcursor object have released it, before it can be granted.

       WaitTime: Microseconds

 

 

五. Cursor 与 绑定变量

 

Oracle 绑定变量 详解

http://blog.csdn.net/tianlesoftware/article/details/5856430

 

Oracle 绑定变量 示例

http://blog.csdn.net/tianlesoftware/article/details/6324243

 

 

       PL/SQL中的动态SQL就是指要执行的SQL直到真正执行的时候PL/SQL引擎才知道你要执行的SQL是什么！PL/SQL中的动态SQL通常与绑定变量有关，使用绑定变量的SQL，根据绑定方式的不同又可分为普通绑定与批量绑定。

 

 

•为什么要使用绑定变量（普通绑定和批量绑定）？

       减少硬解析和PL/SQL引擎和SQL引擎上下文切换的次数。

 

 

•使用绑定变量的基本注意事项：

       1、你不能用占位符代替表或者视图的名称，一般来说，占位符通常用来代替where字句中的条件

       2、绑定变量通常只适用于数值型或者字符型变量，BOOLEAN不能用于绑定变量

       3、要注意动态SQL语句中什么时候能有分号，什么时候又不能有分号

       4、对于不带分号的动态SQL，占位符的命名是无所谓的，这种情况using时传入的绑定变量的值取决于占位符的位置，跟占位符的命名无关。但对于带分号的动态SQL，占位符的命名就有所谓了。

       5、对于普通绑定有效的attribute：

              SQL%FOUND、SQL%NOTFOUND、

              SQL%ISOPEN、SQL%ROWCOUNT

       6、对于批量绑定有效的attribute：

              SQL%FOUND、SQL%NOTFOUND、

              SQL%ISOPEN、SQL%BULK_ROWCOUNT

       7、如果绑定变量的值要传入一个null，怎样处理？

              c_nullchar(1);

              executeimmediate ‘updateemployees set commission_pct= :x' using c_null;

 

 

有关cursor 的相关示例，参考dbsnake 在北京搞的一个培训PPT。 下载地址：

       http://download.csdn.net/source/3473148

 

 

常用的PL/SQL开发原则 by dbsanke

http://blog.csdn.net/tianlesoftware/article/details/6618115

 

 

 

参考资料：

http://www.laoxiong.net/shared-pool-latch-and-library-cache-latch.html

http://dbsnake.com/2011/07/deep-into-cursor.html