Database Reference
In-Depth Information
Once stored in the library cache, parent and child cursors are externalized through the
v$sqlarea
and
v$sql
views, respectively. Strictly speaking, the identifier of a cursor is its memory address, both for the parent and the
child. But in most situations, cursors are identified with two columns:
sql_id
and
child_number
. The
sql_id
column
identifies parent cursors. Both values together identify child cursors. There are cases, though, where the two values
together aren't sufficient to identify a cursor. In fact, depending on the version
1
, parent cursors with many children are
obsoleted and replaced by new ones. As a result, the
address
column is also required to identify a cursor.
When shareable parent and child cursors are available and, consequently, only the first two operations are
carried out, the parse is called a
soft parse
. When all operations are carried out, it's called a
hard parse
.
From a performance point of view, you should avoid hard parses as much as possible. This is precisely why the
database engine stores shareable cursors in the library cache. In this way, every process belonging to the instance
might be able to reuse them. There are two reasons why hard parses should be avoided. The first is that the generation
of an execution plan is a very CPU-intensive operation. The second is that memory in the shared pool is needed for
storing the parent and child cursors in the library cache. Because the shared pool is shared over all sessions,
memory allocations in the shared pool are serialized. For that purpose, one of the latches protecting the shared
pool (known as shared pool latches) must be obtained to allocate the memory needed for both the parent and child
cursors. Because of this serialization, an application causing a lot of hard parses is likely to experience contention
for shared pool latches. Even if the impact of soft parses is much lower than that of hard parses, avoiding soft parses
is desirable as well because they're also subject to some serialization. In fact, the database engine must guarantee
that the memory structures it accesses aren't modified while it's searching for a shareable cursor. The actual
implementation depends on the version: through version 10.2.0.1 one of the library cache latches must be obtained,
but from version 10.2.0.2 onward Oracle started replacing the library cache latches with mutexes, and as of version
11.1 only mutexes are used for that purpose. In summary, you should avoid soft and hard parses as much as possible
because they inhibit the scalability of applications. (Chapter 12 covers this topic in detail.)
Shareable Cursors
The result of a parse operation is a parent cursor and a child cursor stored in a shared SQL area inside the library
cache. Obviously, the aim of storing them in a shared memory area is to allow their reutilization and thereby avoid
hard parses. Therefore, it's necessary to discuss in what situations it's possible to reuse a parent or child cursor.
To illustrate how sharing parent and child cursors works, this section covers three examples.
The purpose of the first example, based on the
sharable_parent_cursors.sql
script, is to show a case where
the parent cursor can't be shared. The key information related to a parent cursor is the text of a SQL statement.
Therefore, in general, several SQL statements share the same parent cursor if their text is exactly the same. This is
the most essential requirement. There's, however, an exception to this when
cursor sharing
is enabled. In fact, when
cursor sharing is enabled, the database engine can automatically replace the literals used in SQL statements with
bind variables. Hence, the text of the SQL statements received by the database engine is modified before being stored
in parent cursors. (Chapter 12 covers cursor sharing in more detail.) In the first example, four SQL statements are
executed. Two have the same text. Two others differ only because of lowercase and uppercase letters or blanks:
SQL> SELECT * FROM t WHERE n = 1234;
SQL> select * from t where n = 1234;
SQL> SELECT * FROM t WHERE n=1234;
SQL> SELECT * FROM t WHERE n = 1234;
