一. 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

-------------------------------------------------------------------------------------------------------

Blog：http://blog.csdn.net/tianlesoftware

Email: dvd.dba@gmail.com

DBA1 群：62697716(滿); DBA2 群：62697977(滿) DBA3 群：62697850(滿)

DBA 超級群：63306533(滿); DBA4 群： 83829929 DBA5群： 142216823

DBA6 群：158654907 聊天 群：40132017 聊天2群：69087192

--加群需要在備注說明Oracle表空間和數據文件的關系，否則拒絕申請

Oracle 游標（cursor） 說明