Coding TricksOracle Cursor description
Cursor description
There are two types of cursor in Oracle: one is shared cursor and the other is session cursor.
1.1 Shared cursor description
Sharedcursor refers to a kind of libry cache object cache and anonymous pl/sql. They are one of dozens of librarycache objectages cached in The Oracle cache, which belongs to the namespace that is CRSR (also known as cursor abbreviation).
http://www.cndba.cn/Dave/article/1540
http://blog.csdn.net/tianlesoftware/article/details/6624122
Sharedpool consists mainly of Library cache and Data Dictionary cache:
(1) Library Cache is mainly used to store SQL statements, SQL statement-related parsing trees, execution plans, PL/SQL blocks (including anonymous blocks, stored procedures, packages, functions, etc.) and code that can be executed by Oracle after conversion, this part of the information can be queried through the v$librarycache view;
(2) Data Dictionary Cache is mainly used to store data dictionary information, including table, view and other object structure information, user and object permission information, this part of the information is relatively stable, in Shared Pool through the dictionary cache alone, the contents of the dictionary cache is stored by row (other data is usually stored by Buffer), so also known as Row Cache, its information can be queried through v$rowcache.
For the management mechanism of LibraryCache, refer to blog:
Oracle Library cache internal mechanism description
http://www.cndba.cn/Dave/article/1381
Shared SQL, parent cursor and cursor concepts
All SQL is implicitly shared by Oracle. When User A issues an SQL, Oracle generates hash value (10g and a unique SQL_ID) based on the SQL text content so that the same SQL that already exists in Shared Pool can be quickly found. If not, Oracle creates a park cursor and a curchildsor for this SQL, which has nothing to do with whether sql is shared or not.
Parentcursor contains SQL TEXT and related hash value, and each line in v$sqlarea represents a parent cursor, which represents its memory address according to addres.
Childcursor contains SQL's metadata, even if this SQL can execute all relevant information such as OBJECT and permissions, optimizer settings, execution plan, etc. Each line in v$sql represents a child cursor, which is associated with parent cursor based on hash value and addres. child cursor has its own add-onres, i.e. V$SQL. CHILD_ADDRESS。
The first curchild always uses 0 to indicate the order in which it was created, V$SQL. CHILD_NUMBER = 0。 Therefore, when looking for an SQL execution plan from a V$SQL_PLAN, be aware that you've written the right CHILD_NUMBER.
if there are multiple child cursor, there are multiple versions of the parent cursor, and the version_count fields in v$sqlarea are recorded.
Oracle High Version counts problem description
http://www.cndba.cn/Dave/article/1382
When the SQL statement is first executed, hard resolution occurs. A parent cursor and a child cursor are generated. They all belong to Shared cursor. An SQL statement has at least one parent cursor and one child cursor. The parent cursor holds an address for hash value and all child cursor. SQL's execution plan is saved in child cursor's Heap 6 (SQL context).
sharedcursor and child cursor are stored in library cache, which is stored with hash table, which in turn consists of a series of bucketts. huckets point to the libry chache object handle, which points to a libry cache object, i.e. heap 0. this is the parent. the hash value and all child cursor addresses are saved in heap 0. the parent cursor and child cursor structures are exactly the same.
When the same SQL is executed the second time, parent cursor and child cursor have been generated by the first hard resolution. So after the SQL statement has been hashed, look in the hash bucket, and if the same parent cursor is found, use the parent cursor and cursor. This is soft parsing.
It may also be due to a number of other reasons that child cursor cannot be reused. At this point, Oracle needs to regenerate a child cursor, even though the parent cursor is the same. This is the version count.
if you don't even find the parent cursor, you'll need to hard parse it.
1.2 Session cursor
Sessioncursor is actually a memory area (or memory structure) in the PGA of server process (or UGA, to be precise) that corresponds to this session, and is intended to process and only process one sql statement at a time.
a session cursor can correspond to only one shared cursor, while a shared cursor may correspond to multiple session cursor at the same time.
When a session cursor is associated with its counterpart, if the cursor_space_for_time is turned into true, when a session cursor is processed after a sql, it will not be destroy, Oracle will be its cache (we call it soft closed session cursor), The purpose of this is obvious, because this soft closed-out session has been linked to the sessioncursor that contains its execution plan and the pose treesharedcursor, so oracle no longer needs to scan the libry cache and simply take the session that has just been closed Cursor just take it and use it, it's called soft resolution.
Session Cursor instructions
2.1 the definition of cursor on the official website
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 is a storage area used to store user session variables and arrays;
User Session Data is an additional store that is used for user sessions.
|--Session Information
|--Sort Area
|--Cursor Information
Note that Session information (user session information) is different from the memory area in the shared server in the exclusive server.
2.2 THE VARIABLE ZONE OF THE PGA IS MAINLY COMPOSED
1) PRIVATE SQL ZONE
2) CURSOR AND SQL ZONE
3) session memory
2.2.1 Private SQL Area
THE PRIVATE SQL ZONE CONTAINS DATA SUCH AS BINDING VARIABLE VALUES AND RUN-TIME MEMORY STRUCTURE INFORMATION. EACH SESSION RUNNING SQL STATEMENTS HAS A BLOCK OF PRIVATE SQL ZONES. ALL USERS WHO SUBMIT THE SAME SQL STATEMENT HAVE THEIR OWN PRIVATE SQL ZONE, AND THEY SHARE A SHARED SQL ZONE. THEREFORE, A SHARED SQL ZONE MAY BE ASSOCIATED WITH MULTIPLE PRIVATE SHARED ZONES.
THE PRIVATE SQL AREA OF A CURSOR IS DIVIDED INTO TWO DIFFERENT LIFECYCLE ZONES:
permanent zone: contains binding variable information. released when the cursor is closed.
run zone: released when execution ends.
Creating a run zone is the first step in executing a request at a time. For INSERT, UPDATE, and DELETE statements, Oracle releases the run zone at the end of the statement run. For query operations, Oracle releases the run zone only if all records are fetched or the query is canceled.
2.2.2 Cursor and SQL Area
An Oracle precompiler or OCI program's application developer can clearly open a cursor, or control a specific private SQL area and use them as named resources for the program to run. In addition, oracle implicitly uses shared SQL zones for recursive calls (as described earlier, reading data dictionary information).
THE PRIVATE SQL ZONE IS MANAGED BY THE USER PROCESS. HOW TO ALLOCATE AND RELEASE PRIVATE SQL ZONES IS HEAVILY DEPENDENT ON THE APPLICATION TOOLS YOU USE. THE NUMBER OF PRIVATE SQL ZONES THAT A USER PROCESS CAN ALLOCATE IS CONTROLLED BY PARAMETER OPEN_CURSORS, AND ITS DEFAULT VALUE IS 50.
THE PRIVATE SQL ZONE WILL REMAIN IN PLACE UNTIL THE CURSOR IS CLOSED OR THE STATEMENT HANDLE IS RELEASED (BUT THE RUN ZONE IN IT WILL BE RELEASED AT THE END OF THE STATEMENT EXECUTION, AND ONLY THE PERMANENT ZONE WILL REMAIN). APP DEVELOPERS CAN FREE UP PERPETUAL ZONES BY CLOSING ALL OPEN CURSORS THAT ARE NO LONGER IN USE, REDUCING THE MEMORY CONSUMED BY USER PROGRAMS.
2.2.3 Session Memory
session memory is a piece of memory that holds information about session variables, such as login information, and other pre-sessions. for shared server mode, session memory is shared, not private.
For complex queries, such as those in decision support systems, a large portion of the run zone is allocated to SQL WorkArea by operations that require a lot of memory. These actions include:
SORT-BASED OPERATIONS (ORDERBY, GROUP BY, ROLLUP, WINDOW FUNCTIONS);
HashJoin
Bitmapmerge
Bitmapcreate
For example, a sort operation uses a workspace (also known as sort area Sort Area) to sort a portion of the data rows in memory, while a Hash Join operation uses a workspace (also known as Hash Area in the Hash Area) to establish a Hash table. If both operations process a larger amount of data than the workspace, the input data is divided into smaller slices, allowing some to be processed in memory, while others are processed later on disks in temporary table space. Although the workspace is too small, bitmap operations do not put data on disk for processing, but their complexity is inversely proportional to the size of the workspace. Therefore, in general, the larger the workspace, the faster these operations will run.
THE SIZE OF THE WORKSPACE CAN BE ADJUSTED. IN GENERAL, LARGE WORKSPACES CAN MAKE CERTAIN OPERATIONS PERFORM BETTER, BUT THEY ALSO CONSUME MORE MEMORY. THE SIZE OF THE WORKSPACE IS SUFFICIENT TO ACCOMMODATE THE INPUT DATA AND THE SECONDARY MEMORY REQUIRED FOR RELATED SQL OPERATIONS. IF NOT, THE RESPONSE TIME FOR THE OPERATION INCREASES BECAUSE SOME OF THE DATA NEEDS TO BE PROCESSED ON A TEMPORARY TABLESPACE DISK.
2.3 open_cursors parameter description
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.
there are three things to note about cursor_space_for_time:
(1) 10.2.0.5 and 11.1.0.7 it has been invalidated;
(2) If the binding variable is also used after turning its value into true, it may cause datalogical corruption due to the relationship between bug 6696453;
(3) after turning its value into true, all child cursor will still hold the libry cache pin after execution until its parent cursor closes
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.
The essence of SharedPool is sharing, and Oracle checks if the number of times the request for parse in the libry cache is more than three times. If a cursor is shut down three times, Oracle thinks the session cursor needs to be moved to the session cursor cache. And this session cursor cache is saved in SharedPool. That is, the corresponding cursor moves from the PGA to the SGA. Then put it after the Session cursor cache, and for the same query, take it directly from the cursor cache, reducing the number of resolutions.
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 uses the LRU algorithm to manage the session cursor cache. If a new cursor needs to be cached and the current cursor cache is full, the least used cursor will be cleared.
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.
--The maximum number of cursors that are currently closed and cached, that is, the maximum number of softclosed session cursor in a single session that can simultaneously cache.
（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 refers to the maximum number of sessioncursor that can exist in the open state at the same time in a single session
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;
The current Sessions cursor cache displayed in the V$OPEN_CURSOR is a cursor, V$SESSION_CACHED_CURSOR, and the current Sessions is closed and cached.
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's life cycle
3.1 life cycle
(1) open cursor (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.
The system will allocate the relevant memory structure in the UGA, which is the process of obtaining the cursor handle, at which time the cursor has not been associated with the sql statement;
(2) resolve cursors (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.
There is a sql associated with a cursor, and a single cursor handle may be used for many different analyzed statements, but only one statement at a time is valid, and the executed plan after the execution of the parsing is placed in the libry cache (under SGA's shared pool), where a pointer to the shared cursor is generated; A session cursor can only point to one shared cursor, while a shared cursor can point to multiple session cursor.
(3) Define the output variable (dbms_sql.definition-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.
if the sql statement returns data, the variables that receive the data must be defined, which is a returning for the delete, update, insert;
(4) binding input variables (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.
the binding process is
not checked;
(5) perform a cursor (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.
this database engine doesn't really do anything important, and for most sql statements, the real process is to get the data from
fetch; (6) get the cursor (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.
the real process, if there is data returned, must provide
an output variable (dbms_sql.column-value
dbms_sql);
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.
WHEN THE RELEVANT RESOURCES IN THE UGA ARE RELEASED, SHARED CURSORS IN THE LIBRARY CACHE ARE NOT CLEARED.
3.2 view the cursor flowchart
by querying the dba_source view, you can see the illustration of the dbms_sql package:
SQL>select text fromdba_source where name='DBMS_SQL';
The flow of procedurecalls will typically look like this:
That is to say, shared cursors, which are in the library cache, will be kept as long as possible, while the relevant cursor pointers and private data in the UGA will be released when the cursor is over;
3.3 Cursor (SQL) resolution process
（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.
--semantic french and access check, that is, check whether the statement of sql is correct, whether the access object exists, whether there is access, etc.;
（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 the parent cursor into the library 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.
--logic optimization: generating sql statements of the same semantics through different conversion techniques;
（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.
--physical optimization: generates the execution plan of sql statements produced by each logical optimization, and then finds the statistics according to the data dictionary, calculates the best execution plan of sql with the same semantics produced by logical optimization;
（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.
--the most critical content of loading subcursors into the library cache is the execution plan and execution environment;
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.
in short, the parsing process is to cache the parent and child cursors into the library cache. where v$sqlarea is the parent cursor-related view, v$sql is the child cursor.
a child cursor is determined by child_number, hash_value, address, and v$sqlarea can identify a parent cursor by addres and hash_value, and a cursor can be determined by sql_id after 10g.
In SQL optimization, binding variables should be used as much as possible to avoid hard resolution and reduce the number of context switch times. Hard resolution causes the parent cursor to not be shared, and for the same parent cursor, the child cursor's unshareable can be viewed through the v$sql_shared_cursor view.
there is an example in the following blog:
a large number of version_count problems caused by bind_mismatch
http://www.cndba.cn/Dave/article/1537
Hard and soft
resolution
http://www.cndba.cn/Dave/article/1185
of Oracle SQL
3.4 Classification of Session Cursor
session cursor is divided into three different types: implicit cursor, explicit cursor and ref cursor.
There are detailed instructions and examples of Cursor's classification on the official website:
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
four. cursor-related waiting events
the official website states:
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 and binding variables
Oracle binding variables are explained in detail
http://www.cndba.cn/Dave/article/1572
Oracle binding variable example
http://www.cndba.cn/Dave/article/1560
DYNAMIC SQL IN PL/SQL MEANS THAT THE SQL YOU WANT TO EXECUTE IS NOT KNOWN TO THE PL/SQL ENGINE UNTIL IT IS ACTUALLY EXECUTED! DYNAMIC SQL IN PL/SQL IS USUALLY RELATED TO BINDING VARIABLES, USING THE SQL OF BINDING VARIABLES, DEPENDING ON THE BINDING METHOD CAN BE DIVIDED INTO ORDINARY BINDING AND BULK BINDING.
why use binding variables (normal and bulk bindings)?
REDUCE THE NUMBER OF HARD RESOLUTION AND PL/SQL ENGINE AND SQL ENGINE CONTEXT SWITCHES.
basic considerations for using binding variables:
1. you can't replace the name of a table or view with a placeholder, which is generally used to replace the conditions in the where clause
2. BINDING VARIABLES USUALLY ONLY APPLY TO NUMERICAL OR CHARACTER VARIABLES, BOOLEAN CAN NOT BE USED TO BIND VARIABLES
3, PAY ATTENTION TO THE DYNAMIC SQL STATEMENT WHEN THERE CAN BE SEMICOLONS, WHEN CAN NOT HAVE SEMICOLONS
4, for dynamic SQL without semicolons, the naming of placeholders is irrelevant, in this case the value of the binding variable passed in depends on the location of placeholders, regardless of the name of placeholders. But for dynamic SQL with semicolons, the naming of placeholders is so-called.
5, for ordinary binding effective attribute:
SQL%FOUND、SQL%NOTFOUND、
SQL%ISOPEN、SQL%ROWCOUNT
6, for bulk binding valid attribute:
SQL%FOUND、SQL%NOTFOUND、
SQL%ISOPEN、SQL%BULK_ROWCOUNT
7, if the value of the binding variable to pass in a null, how to deal with it?
c_nullchar(1);
executeimmediate ‘updateemployees set commission_pct= :x' using c_null;
For examples of cursor, refer to a training PPT conducted by dbsnake in Beijing. Download address:
http://download.csdn.net/source/3473148
Common PL/SQL development principles by dbsanke
http://www.cndba.cn/Dave/article/14995
resources:
http://www.laoxiong.net/shared-pool-latch-and-library-cache-latch.html
http://dbsnake.com/2011/07/deep-into-cursor.html
-------------------------------------------------------------------------------------------------------
QQ:492913789
Email:[email protected]
Blog: http://www.cndba.cn/dave
Weibo: http://weibo.com/tianlesoftware
Twitter: http://twitter.com/tianlesoftware
Facebook: http://www.facebook.com/tianlesoftware
Linkedin: http://cn.linkedin.com/in/tianlesoftware
DBA1 GROUP: 62697716 (FULL); DBA2 GROUP: 62697977 (FULL) DBA3 GROUP: 62697850 (FULL)
DBA SUPERGROUP: 63306533 (FULL); DBA4 GROUP: 83829929 DBA5 GROUP: 142216823
DBA6 GROUP: 158654907 CHAT GROUP: 40132017 CHAT GROUP 2: 69087192
--Adding groups is required to explain the relationship between Oracle table space and data files in the notes, otherwise the request will be rejected