Key Concepts - Troubleshooting Oracle Performance - page 19

What Is a Cursor?
A cursor is a handle (that is, a memory structure that enables a program to access a resource) that references a private
SQL area with an associated shared SQL area. As shown in Figure 2-1 , although the handle is a client-side memory
structure, it references a memory structure allocated by a server process that, in turn, references a memory structure
stored in the SGA, and more precisely in the library cache.

Figure 2-1. A cursor is a handle to a private SQL area with an associated shared SQL area

A private SQL area stores data such as bind variable values and query execution state information. As its name
suggests, a private SQL area belongs to a specific session. The session memory used to store private SQL areas is
called user global area (UGA).
A shared SQL area consists of two separate structures: the so-called parent cursor and child cursor . The key
information stored in a parent cursor is the text of the SQL statement associated with the cursor. Simply put, the
SQL statement specifies the processing to be performed. The key elements stored in a child cursor are the execution
environment and the execution plan. These elements specify how the processing is carried out. A shared SQL area can
be used by several sessions, and therefore it's stored in the library cache.

■ Note In practice, the terms cursor and private/shared SQL area are used interchangeably.

Life Cycle of a Cursor
Having a good understanding of the life cycle of cursors is required knowledge for optimizing applications that
execute SQL statements. The following are the steps carried out during the processing of a cursor:

1. Open cursor: A private SQL area is allocated in the UGA of the session used to open the

cursor. A client-side handle referencing the private SQL area is also allocated. Note that no
SQL statement is associated with the cursor yet.

2. Parse cursor: A shared SQL area containing the parsed representation of the SQL statement

associated to it and its execution plan (which describes how the SQL engine will execute
the SQL statement) is generated and loaded in the SGA, specifically into the library cache.
The private SQL area is updated to store a reference to the shared SQL area. (The next
section describes parsing in more detail.)

3. Define output variables: If the SQL statement returns data, the variables receiving it must

be defined. This is necessary not only for queries but also for DELETE , INSERT , and UPDATE
statements that use the RETURNING clause.

4. Bind input variables: If the SQL statement uses bind variables, their values must be