一.? Cursor說(shuō)明

?????? Oracle里的cursor分為兩種：一種是shared cursor，一種是session cursor。

?

1.1 Shared cursor 說(shuō)明

?????? sharedcursor就是指緩存在librarycache（SGA下的Shared Pool）里的一種library cache object，說(shuō)白了就是指緩存在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 說(shuō)明

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

?

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

?????? （1）Library Cache主要用于存儲(chǔ)SQL語(yǔ)句、SQL語(yǔ)句相關(guān)的解析樹、執(zhí)行計(jì)劃、PL/SQL程序塊（包括匿名程序塊、存儲(chǔ)過(guò)程、包、函數(shù)等）以及它們轉(zhuǎn)換后能夠被Oracle執(zhí)行的代碼等，這部分信息可以通過(guò)v$librarycache視圖查詢；

?????? （2）Data Dictionary Cache主要用于存放數(shù)據(jù)字典信息，包括表、視圖等對(duì)象的結(jié)構(gòu)信息，用戶以及對(duì)象權(quán)限信息，這部分信息相對(duì)穩(wěn)定，在Shared Pool中通過(guò)字典緩存單獨(dú)存放，字典緩存的內(nèi)容是按行（Row）存儲(chǔ)的（其他數(shù)據(jù)通常按Buffer存儲(chǔ)），所以又被稱為Row Cache，其信息可以通過(guò)v$rowcache查詢。

關(guān)于LibraryCache 的管理機(jī)制，參考blog：

?????? Oracle Library cache 內(nèi)部機(jī)制 說(shuō)明

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

?

shared SQL, parent cursor和child cursor 概念

所有SQL都是Oracle暗式共享的(implicitlysharable)。當(dāng)用戶A發(fā)出一條SQL后，Oracle會(huì)根據(jù)SQL文本內(nèi)容生成hash value(10g還有唯一的SQL_ID)，以便能夠快速找到 Shared pool已經(jīng)存在的相同SQL。如果找不到，則Oracle會(huì)為這個(gè)SQL創(chuàng)建一個(gè)parent cursor和一個(gè)child cursor，這與SQL是否共享是沒有關(guān)系的。

?????? parentcursor包含了SQL TEXT和相關(guān)的hash value，v$sqlarea中的每一行代表了一個(gè)parent cursor，根據(jù)address表示了其內(nèi)存地址。

?????? childcursor包含了SQL的metadata，即使得這個(gè)SQL可以執(zhí)行的所有相關(guān)信息，如OBJECT和權(quán)限，優(yōu)化器設(shè)置，執(zhí)行計(jì)劃等。v$sql中中 的每一行表示了一個(gè)child cursor，根據(jù)hash value和address與parent cursor 關(guān)聯(lián)。child cursor有自己的address，即V$SQL.CHILD_ADDRESS。

?????? 第一個(gè)child cursor總是使用0來(lái)表示其創(chuàng)建順序，V$SQL.CHILD_NUMBER = 0。因此，當(dāng)從V$SQL_PLAN中查找某個(gè)SQL的執(zhí)行計(jì)劃時(shí)，要注意你寫對(duì)了CHILD_NUMBER。

?????? 如果有多個(gè)child cursor，則表示parent cursor有多個(gè)版本，v$sqlarea中的version_count字段就會(huì)紀(jì)錄下來(lái)。

?

Oracle 高 Version counts 問(wèn)題說(shuō)明

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

?

?????? 當(dāng)SQL 語(yǔ)句第一次執(zhí)行時(shí)，會(huì)進(jìn)行硬解析。此時(shí)會(huì)生成一個(gè)parent cursor 和一個(gè)child cursor。 他們都屬于Shared cursor。 一個(gè)SQL 語(yǔ)句至少有一個(gè)parent cursor 和一個(gè)child cursor。 parent cursor 里保存的是hash value 和所有child cursor 的一個(gè)地址。 child cursor 的Heap 6（SQL context）里保存了SQL 的執(zhí)行計(jì)劃。

?????? sharedcursor 和child cursor 存放在library cache，而library cache 是用hash table 來(lái)存放的，hash table 又是由一系列buckets 組成。 huckets 指向library chache object handle，handle 指向一個(gè)library cache object，即heap 0. 這個(gè)就是parent。 heap 0里保存了hash 值和所有child cursor 的地址。 parent cursor 和child cursor 結(jié)構(gòu)是完全一樣的。

?

?????? 當(dāng)?shù)诙螆?zhí)行相同的SQL 時(shí)， 因?yàn)榈谝淮斡步馕鰰r(shí)已經(jīng)生成了parent cursor和child cursor。 所以在SQL 語(yǔ)句進(jìn)行hash 運(yùn)算后，在該hash value在hash bucket里進(jìn)行查找，如果找到了相同的parent cursor，那么就使用該parent cursor和child cursor。 這個(gè)就是軟解析。

?????? 也可能因?yàn)橐恍┢渌脑?#xff0c;導(dǎo)致child cursor 不能重用。 這時(shí)，雖然parent cursor 相同，但是Oracle 還是需要重新生成一個(gè)child cursor。 這個(gè)就是version count。

?????? 如果連parent cursor 都沒有找到，這時(shí)候就需要進(jìn)行硬解析。

?

1.2 Session cursor

?????? sessioncursor其實(shí)就是指的跟這個(gè)session相對(duì)應(yīng)的server process的PGA里（準(zhǔn)確的說(shuō)是UGA）的一塊內(nèi)存區(qū)域（或者說(shuō)內(nèi)存結(jié)構(gòu)），它的目的是為了處理且一次只處理一條sql語(yǔ)句。

?????? 一個(gè)session cursor只能對(duì)應(yīng)一個(gè)shared cursor，而一個(gè)shared cursor卻可能同時(shí)對(duì)應(yīng)多個(gè)session cursor。

?????? 當(dāng)某個(gè)session cursor和其對(duì)應(yīng)的shared cursor建立關(guān)聯(lián)后，如果把cursor_space_for_time調(diào)成true，當(dāng)一個(gè)session cursor處理完一條sql后，它就不會(huì)被destroy，Oracle會(huì)把其cache起來(lái)（我們稱之為soft closed session cursor），這么做的目的是很明顯的，因?yàn)檫@個(gè)soft closed掉的sessioncursor已經(jīng)和包含其執(zhí)行計(jì)劃和parse tree的sharedcursor建立了聯(lián)系，那么當(dāng)在這個(gè)session中再次執(zhí)行同樣的sql的時(shí)候，Oracle就不再需要去掃描library cache了，直接把剛才已經(jīng)soft closed掉的session cursor拿過(guò)來(lái)用就好了，這就是所謂的軟軟解析。

?

二. Session Cursor 說(shuō)明

2.1官網(wǎng)對(duì)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是用來(lái)存儲(chǔ)用戶會(huì)話變量和數(shù)組的存儲(chǔ)區(qū)域；

?User Session Data是為用戶會(huì)話使用的附加存儲(chǔ)區(qū)。

|--Session Information

|--Sort Area

|--Cursor Information

?????? 注意Session information（用戶會(huì)話信息）在獨(dú)占服務(wù)器中與在共享服務(wù)器中所處的內(nèi)存區(qū)域是不同的。

?

2.2 PGA的可變區(qū)主要組成

?????? 1）私有SQL區(qū)

?????? 2）游標(biāo)和SQL區(qū)

?????? 3）會(huì)話內(nèi)存

?

2.2.1私有SQL區(qū)（PrivateSQL Area）

?????? 私有SQL區(qū)包含了綁定變量值和運(yùn)行時(shí)期內(nèi)存結(jié)構(gòu)信息等數(shù)據(jù)。每一個(gè)運(yùn)行SQL語(yǔ)句的會(huì)話都有一個(gè)塊私有SQL區(qū)。所有提交了相同SQL語(yǔ)句的用戶都有各自的私有SQL區(qū)，并且他們共享一個(gè)共享SQL區(qū)。因此，一個(gè)共享SQL區(qū)可能和多個(gè)私有共享區(qū)相關(guān)聯(lián)。

一個(gè)游標(biāo)的私有SQL區(qū)又分為兩個(gè)生命周期不同的區(qū)：

?????? 永久區(qū)：包含綁定變量信息。當(dāng)游標(biāo)關(guān)閉時(shí)被釋放。

?????? 運(yùn)行區(qū)：當(dāng)執(zhí)行結(jié)束時(shí)釋放。

?????? 創(chuàng)建運(yùn)行區(qū)是一次執(zhí)行請(qǐng)求的第一步。對(duì)于INSERT、UPDATE和DELETE語(yǔ)句，Oracle在語(yǔ)句運(yùn)行結(jié)束時(shí)釋放運(yùn)行區(qū)。對(duì)于查詢操作，Oracle只有在所有記錄被fetch到或者查詢被取消時(shí)釋放運(yùn)行區(qū)。

?

2.2.2 游標(biāo)和SQL區(qū)（Cursorsand SQL Areas）

?????? 一個(gè)Oracle預(yù)編譯程序或OCI程序的應(yīng)用開發(fā)人員能夠很明確的打開一個(gè)游標(biāo)，或者控制一塊特定的私有SQL區(qū)，將他們作為程序運(yùn)行的命名資源。另外，oracle隱含的為一些SQL語(yǔ)句產(chǎn)生的遞歸調(diào)用（前面有介紹，讀取數(shù)據(jù)字典信息）也使用共享SQL區(qū)。

?????? 私有SQL區(qū)是由用戶進(jìn)程管理的。如何分配和釋放私有SQL區(qū)極大的依賴與你所使用的應(yīng)用工具。而用戶進(jìn)程可以分配的私有SQL區(qū)的數(shù)量是由參數(shù)OPEN_CURSORS控制的，它的默認(rèn)值是50。

?????? 在游標(biāo)關(guān)閉前或者語(yǔ)句句柄被釋放前，私有SQL區(qū)將一直存在（但其中的運(yùn)行區(qū)是在語(yǔ)句執(zhí)行結(jié)束時(shí)被釋放，只有永久區(qū)一直存在）下去。應(yīng)用開發(fā)人員可以通過(guò)將所有打開的不再使用的游標(biāo)都關(guān)閉來(lái)釋放永久區(qū)，以減少用戶程序所占用的內(nèi)存。

?

2.2.3 會(huì)話內(nèi)存（SessionMemory）

?????? 會(huì)話內(nèi)存是一段用于保存會(huì)話變量（如登錄信息）和其他預(yù)會(huì)話相關(guān)信息的內(nèi)存。對(duì)于共享服務(wù)器模式下，會(huì)話內(nèi)存是共享的，而不是私有的。

?????? 對(duì)于復(fù)雜的查詢（如決策支持系統(tǒng)中的查詢），運(yùn)行區(qū)的很大一部分被那些內(nèi)存需求很大的操作分配給SQL工作區(qū)（SQL WorkArea）。這些操作包括：

基于排序的操作（ORDERBY、GROUP BY、ROLLUP、窗口函數(shù)）；

?????? HashJoin

?????? Bitmapmerge

?????? Bitmapcreate

?????? 例如，一個(gè)排序操作使用工作區(qū)（這時(shí)也可叫排序區(qū)Sort Area）來(lái)將一部分?jǐn)?shù)據(jù)行在內(nèi)存排序；而一個(gè)Hash Join操作則使用工作區(qū)（這時(shí)也可以叫做Hash區(qū) Hash Area）來(lái)建立Hash表。如果這兩種操作所處理的數(shù)據(jù)量比工作區(qū)大，那就會(huì)將輸入的數(shù)據(jù)分成一些更小的數(shù)據(jù)片，使一些數(shù)據(jù)片能夠在內(nèi)存中處理，而其他的就在臨時(shí)表空間的磁盤上稍后處理。盡管工作區(qū)太小時(shí)，Bitmap操作不會(huì)將數(shù)據(jù)放到磁盤上處理，但是他們的復(fù)雜性是和工作區(qū)大小成反比的。因此，總的來(lái)說(shuō)，工作區(qū)越大，這些操作就運(yùn)行越快。

?????? 工作區(qū)的大小是可以調(diào)整的。一般來(lái)說(shuō)，大的工作區(qū)能讓一些特定的操作性能更佳，但也會(huì)消耗更多的內(nèi)存。工作區(qū)的大小足夠適應(yīng)輸入的數(shù)據(jù)和相關(guān)的SQL操作所需的輔助的內(nèi)存就是最優(yōu)的。如果不滿足，因?yàn)樾枰獙⒁徊糠謹(jǐn)?shù)據(jù)放到臨時(shí)表空間磁盤上處理，操作的響應(yīng)時(shí)間會(huì)增長(zhǎng)。

?

2.3 open_cursors 參數(shù)說(shuō)明

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

?

關(guān)于cursor_space_for_time有三點(diǎn)需要注意：

?????? (1)10.2.0.5和11.1.0.7里它已經(jīng)作廢了；

?????? (2)把它的值調(diào)成true后如果還同時(shí)用到了綁定變量，則由于Bug 6696453的關(guān)系,可能會(huì)導(dǎo)致logical data corruption；

?????? (3)把它的值調(diào)成true后，所有的child cursor在執(zhí)行完后依然會(huì)持有l(wèi)ibrary cache pin，直到其父cursor關(guān)閉

?

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 的本質(zhì)是共享，Oracle 會(huì)檢查library cache 中parse request的次數(shù)是否超過(guò)3次。 如果一個(gè)cursor 被關(guān)閉3次，Oracle 就認(rèn)為這個(gè)session cursor 需要移動(dòng)到session cursor cache。 而這個(gè)session cursor cache 是保存在SharedPool中的。 即對(duì)應(yīng)的cursor 從PGA 移動(dòng)到了SGA。 那么放到Session cursor cache 之后，對(duì)于同樣的查詢直接從cursor cache中取就可以了，從而減少解析次數(shù)。

?

?????? 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 算法來(lái)管理session cursor cache。如果有新的游標(biāo)需要緩存，而當(dāng)前游標(biāo)緩存已經(jīng)滿，最少使用的游標(biāo)將會(huì)被清除出去。

?

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.

? ???? --當(dāng)前Session已經(jīng)關(guān)閉并被緩存的游標(biāo)的最大數(shù)量,即單個(gè)session中同時(shí)能cache住的softclosed session cursor的最大數(shù)量。

?

（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指的是在單個(gè)session中同時(shí)能以open狀態(tài)存在的sessioncursor的最大數(shù)量

?

?????? 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中顯示的當(dāng)前Session游標(biāo)緩存中游標(biāo)，V$SESSION_CACHED_CURSOR，當(dāng)前Session已經(jīng)關(guān)閉并被緩存的游標(biāo)。

?

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）打開游標(biāo)（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.

?????? 系統(tǒng)會(huì)在UGA中分配相關(guān)的內(nèi)存結(jié)構(gòu),就是獲得游標(biāo)句柄的過(guò)程，這時(shí)的游標(biāo)還未和sql語(yǔ)句有關(guān)聯(lián)；

（2）解析游標(biāo)(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與游標(biāo)相關(guān)聯(lián)，且單個(gè)游標(biāo)句柄可能用于許多不同的被分析的語(yǔ)句，但一次只有一個(gè)語(yǔ)句有效，并將執(zhí)行解析過(guò)后的執(zhí)行計(jì)劃放在library cache（SGA的shared pool下）中，UGA中生成指向這個(gè)共享游標(biāo)的指針；即session cursor 指向shared cursor。 一個(gè)session cursor 只能指向一個(gè)shared cursor，而一個(gè)shared cursor 可以指向多個(gè)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語(yǔ)句返回?cái)?shù)據(jù)，必須定義接收數(shù)據(jù)的變量，對(duì)delete，update，insert來(lái)說(shuō)是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.

?????? 綁定過(guò)程是不做檢查的；
（5）執(zhí)行游標(biāo)(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.

?????? 這步數(shù)據(jù)庫(kù)引擎其實(shí)不做什么重要事情，而對(duì)大多數(shù)sql語(yǔ)句來(lái)說(shuō)，真正處理過(guò)程是到fetch獲取數(shù)據(jù)階段；
（6）獲取游標(biāo)(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.

?????? 真正的處理過(guò)程，有返回?cái)?shù)據(jù)的話，必須提供輸出變量(dbms_sql.column_value)；
（7）關(guān)閉游標(biāo)(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中相關(guān)資源，庫(kù)緩存中共享游標(biāo)不會(huì)被清除。

?

3.2 查看cursor 流程圖

通過(guò)查詢dba_source視圖，可以看到dbms_sql包的圖解：

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

The flow of procedurecalls will typically look like this:

?
??? ?????

?

??

也就是說(shuō)共享游標(biāo)，就是在庫(kù)緩存中的，將被盡可能的保持長(zhǎng)久；而UGA中的相關(guān)游標(biāo)指針和私有數(shù)據(jù)，將在游標(biāo)close時(shí)被釋放；

?

?

3.3 cursor （SQL）解析過(guò)程

?????? （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.

?????? --語(yǔ)法語(yǔ)義以及訪問(wèn)權(quán)限的檢查，也就是檢查sql的語(yǔ)句是否正確，訪問(wèn)對(duì)象是否存在，是否有訪問(wèn)權(quán)限等；

?????? （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.

?????? --將父游標(biāo)load到庫(kù)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.

?????? --邏輯優(yōu)化：通過(guò)不同的轉(zhuǎn)換技巧，生成相同語(yǔ)義的sql語(yǔ)句；

?????? （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.

?????? --物理優(yōu)化：生成每個(gè)邏輯優(yōu)化產(chǎn)生的sql語(yǔ)句的執(zhí)行計(jì)劃，然后根據(jù)數(shù)據(jù)字典找到統(tǒng)計(jì)信息，計(jì)算出邏輯優(yōu)化所產(chǎn)生的語(yǔ)義相同的sql最佳執(zhí)行計(jì)劃；

?????? （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.

?????? --將子游標(biāo)load到庫(kù)緩存中，與子游標(biāo)最關(guān)鍵內(nèi)容就是執(zhí)行計(jì)劃和執(zhí)行環(huán)境；

?

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

?

?????? 總之解析過(guò)程就是將父游標(biāo)和子游標(biāo)緩存到庫(kù)緩存中。其中v$sqlarea是父游標(biāo)相關(guān)信息視圖，v$sql是子游標(biāo)的。

?????? v$sql中通過(guò)child_number,hash_value,address來(lái)確定一個(gè)子游標(biāo)，而v$sqlarea通過(guò)address和hash_value就可以確定一個(gè)父游標(biāo)；而從10g過(guò)后，通過(guò)sql_id就能確定一個(gè)游標(biāo)。

?????? 在SQL 優(yōu)化中，應(yīng)該盡量使用綁定變量，這樣就能避免硬解析，減少context switch次數(shù)。 硬解析會(huì)造成父游標(biāo)的不能共享，對(duì)于父游標(biāo)相同的情況下，子游標(biāo)的不能共享可以通過(guò)v$sql_shared_cursor 視圖查看。

?

在如下blog 里有一個(gè)示例：

?????? 由 bind_mismatch 引起的 大量version_count 問(wèn)題

?????? 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。

?

關(guān)于Cursor 的分類，官網(wǎng)有詳細(xì)的說(shuō)明和示例：

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 相關(guān)的等待事件

官網(wǎng)說(shuō)明：

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中的動(dòng)態(tài)SQL就是指要執(zhí)行的SQL直到真正執(zhí)行的時(shí)候PL/SQL引擎才知道你要執(zhí)行的SQL是什么！PL/SQL中的動(dòng)態(tài)SQL通常與綁定變量有關(guān)，使用綁定變量的SQL，根據(jù)綁定方式的不同又可分為普通綁定與批量綁定。

?

?為什么要使用綁定變量（普通綁定和批量綁定）？

?????? 減少硬解析和PL/SQL引擎和SQL引擎上下文切換的次數(shù)。

?

?使用綁定變量的基本注意事項(xiàng)：

?????? 1、你不能用占位符代替表或者視圖的名稱，一般來(lái)說(shuō)，占位符通常用來(lái)代替where字句中的條件

?????? 2、綁定變量通常只適用于數(shù)值型或者字符型變量，BOOLEAN不能用于綁定變量

?????? 3、要注意動(dòng)態(tài)SQL語(yǔ)句中什么時(shí)候能有分號(hào)，什么時(shí)候又不能有分號(hào)

?????? 4、對(duì)于不帶分號(hào)的動(dòng)態(tài)SQL，占位符的命名是無(wú)所謂的，這種情況using時(shí)傳入的綁定變量的值取決于占位符的位置，跟占位符的命名無(wú)關(guān)。但對(duì)于帶分號(hào)的動(dòng)態(tài)SQL，占位符的命名就有所謂了。

?????? 5、對(duì)于普通綁定有效的attribute：

????????????? SQL%FOUND、SQL%NOTFOUND、

????????????? SQL%ISOPEN、SQL%ROWCOUNT

?????? 6、對(duì)于批量綁定有效的attribute：

????????????? SQL%FOUND、SQL%NOTFOUND、

????????????? SQL%ISOPEN、SQL%BULK_ROWCOUNT

?????? 7、如果綁定變量的值要傳入一個(gè)null，怎樣處理？

????????????? c_nullchar(1);

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

?

有關(guān)cursor 的相關(guān)示例，參考dbsnake 在北京搞的一個(gè)培訓(xùn)PPT。 下載地址：

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

?

常用的PL/SQL開發(fā)原則 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 超級(jí)群：63306533(滿);? DBA4 群： 83829929? DBA5群： 142216823???

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

--加群需要在備注說(shuō)明Oracle表空間和數(shù)據(jù)文件的關(guān)系，否則拒絕申請(qǐng)

轉(zhuǎn)載于:https://www.cnblogs.com/hibernate315/archive/2011/07/27/2398984.html

《新程序員》：云原生和全面數(shù)字化實(shí)踐50位技術(shù)專家共同創(chuàng)作，文字、視頻、音頻交互閱讀

總結(jié)

以上是生活随笔為你收集整理的Oracle 游标（cursor） 说明的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。