Thursday, 26 May 2011

ORACLE CURSORS BASICS


An SQL cursor is nothing but an area in user session memory which contains data about a specific statement. An SQL cursor actually has 2 parts.. The first heap which contains library cache metadata and second heap which is a larger heap contains executable representation of the cursor (usually called SQLAREA). Each heap is comprised of one or more chunks of memory.

SQL AREA can be thought of as a handle which points to a private sql area in UGA and the further pointing to the shared SQL area. All SQL cursors are allocated in 4K chunks. The bigger the cursor the more 4K chunks are allocated. An open cursor has already been parsed and the cursor handle is in library cache.

If the cursor is not in shared pool obviosuly it has to be reconstructed (hard parse) which requires a shared pool and library cache latch. A soft parse is preferable to a hard parse because the database skips the optimization and row source generation steps, proceeding straight to execution.

To reduce the mount of latching in your database DBA's configure a session cursor cache which contains instantiation of shared child cursors (closed session cursors) so that repeatable statements can reuse these cursors. You can store the reusable cursors in this session cursor cache. And the parameter session_cached_Cursors is independent of open_cursors. That means you can keep it higher than open_cursors because session cursors are not cached in open state. In 11G R2 default value of open_cursor is 300 and for session_Cached_cursors is 50

When the statement is in library cache, the associated cursor for the statement is placed on a hash chain. All these cursor handles are in shared pool inside hash chains. The parsing procedure actually has the following workings :=

1) find and execute an open cursor
2) Find a closed cursor in session cursor cache
3) search the hash chains for a soft parse
4) Construct the cursor (hard parse)

A Cursor is a handle to a specific PRIVATE SQL AREA. PRIVATE SQL AREA which is in the UGA keeps information about a parsed sql statement and session specific information for processing. When a statement is executed the process uses private sql area to store bind variable values, query execution work area and so on...
A Shared SQL area is different from private sql area. The shared sql area is in library cache containing execution plans in the SGA.

In the context of PL/SQL, the following is a notion of cursors

STATIC CURSORS

Static cursors are normal cursors like (explicit and implicit). For explicit cursors...A cursor is a mechanism by which you can assign a name to a "select statement" and manipulate the information within that SQL statement. A programmer can open, fetch data, close and check attributes of this kind of cursor.

Implicit cursors are more fast and they have less coding effort. And this one will never raise INVALID_CURSOR error and it cannot be opened outside the statement.

DYNAMIC CURSORS

Ref CURSORS created only when it is opened. A REF CURSOR is basically a data type.  A variable created based on such a data type is generally called a cursor variable.  A cursor variable can be associated with different queries at run-time.  The primary advantage of using cursor variables is their capability to pass result sets between sub programs (like stored procedures, functions, packages etc....)


2 comments:

  1. Usually I never comment on blogs but your article is so convincing that I never stop myself to say something about it. You’re doing a great job Man learn Oracle SOA Online Training

    ReplyDelete
  2. Enjoyed reading the article above, really explains everything in detail, the article is very interesting and effective. Thank you and good luck for the upcoming articles. OSB Online Training

    ReplyDelete