Oracle 遊标（cursor） 說明
一. 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.
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.
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, explainedinOracleDatabase 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
- 上一篇Oracle Library cache 内部機制 說明
- 下一篇一次library cache pin故障的解決過程
檢視評論
- “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中取就可以了，進而減少解析次數。” 對這段有個疑問，session cursor cache 不是在pga中麼？