Tuning the Database - Expert Oracle RAC Performance Diagnostics and Tuning - page 289

WHERE pa.sid = ex.sid
AND pa.inst_id = ex.inst_id
AND pa.statistic# = (SELECT statistic#
FROM v$statname
WHERE NAME = 'parse count (hard)')
AND ex.statistic# = (SELECT statistic#
FROM v$statname
WHERE NAME = 'execute count')
AND pa.VALUE > 0;

When the query is executed for the very first time, it is parsed since the library cache needs to be “loaded,”
memory allocated, etc. It is considered normal behavior in Oracle's architecture to hardparse the statement. It is
when repeated hard parses are made that it becomes a concern. Some of the reasons for hard parse operations are
the following:

• Queries that use literals in the

WHERE clause, making every query executed unique to the
Oracle's optimizer because it will perform hard parse operations. The solution to these issues
is to use bind variables instead of hard coded values in the queries.

• Bad or inefficient SQL is the cause for hard parses. In this inefficiency are included queries that

do not use bind variables. Not using bind variables causes SQL queries to be parsed every single
time because literals in queries make them different from a query that was executed previously.

Inefficient queries and using hard coded values/literals are within the control of the developer and can be
rewritten so as to use bind variables. In the case of Java applications, the method is to use prepared statements.
In both these situations using bind variables and prepared statements, the query is by itself the same and will be
reused every single time that a user executes this query and its present in the shared pool.
While using bind variables or prepared statements is an efficient practice, Oracle provides parameters that help
improve efficiency of SQL queries.
To overcome the difficulties faced by most applications that use literals, Oracle has introduced a parameter called
CURSOR_SHARING = FORCE . This parameter creates bind variables for all literals; thus, forcing cursor sharing.
•

CURSOR_SHARING : To help with the bad SQL and to improve Oracle execution and reusability of
the queries that use literals, Oracle introduced a parameter in Oracle database version 8 called
CURSOR_SHARING . By enabling this parameter, Oracle will generate bind variables for all literals
that it encounters, which means queries become reusable.

•

SESSION_CACHED_CURSORS : This parameter specifies the number of session cursors to cache.
When the cursors are cached, subsequent calls of the same SQL statement will move the
cursor to the session cursor cache. Subsequently, when the same SQL statement is executed,
the parse calls will find the cursor in the cache and use the already open cursor.

Another reason is the insufficient allocation of the SGA. When a large number of queries are executed, the
queries have to be flushed out to give space for new ones. This repeated loading and unloading the SQL statements
into the instance buffers can create high hard parse operations. The number reloads can be determined using the
following query:

SELECT inst_id,
sql_text,
loads
FROM gv$sqlstats
WHERE loads > 100;