SQL Tuning - Expert Oracle RAC Performance Diagnostics and Tuning - page 202

delta_concurrency_time dconwt,
delta_cluster_wait_time dclwt,
delta_user_io_wait_time duiwt
FROM gv$sqlstats
WHERE delta_user_io_wait_time > 0
ORDER BY delta_user_io_wait_time;

Note that the SQL parsing and execution happens locally on each instance in a clustered environment. If the SQL
or any dependencies change in one instance, it will invalidate in all the instances, and they have to be reparsed again
locally on the respective instance.
After the initial header and body creation, subsequent bodies are created for the same header. Here are two
reasons on why this could happen:

• The thresholds of the bind variables change because the string length of the contents of the

bind variable has changed.

For example, if the BOOK table is initially checked with “Oracle,” and the next query checked
the books table with “Testing and Tuning Oracle Real Application Clusters,” Oracle notices
that the length of the bind value has changed and cannot reuse the existing cursor body and
therefore creates another one. The initial space allocation for a bind value is 50 bytes; anything
over that requires a new body.

• SQL statements use literals instead of bind variables.

Hard parses are very costly for the Oracle's optimizer. The amount of validation that has to be performed during
a parse consumes a significant amount of resources. When a user/session executes a query, the query is parsed and
loaded in the library cache after Oracle has generated a hash value for the query. Subsequently, when another session
or user executes the same query depending on the extent of similarity of the query that is already present in the library
cache, it is reused and there is no parse operation involved. However, if it is a new query, it has to go through the
Oracle parsing algorithm; this is considered a hard parse and is very costly. The total amount of hard parses can be
determined using the following query:

SELECT pa.inst_id,
pa.sid,
pa.VALUE "Hard Parses",
ex.VALUE "Execute Count"
FROM gv$sesstat pa,
gv$sesstat ex
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;

■ the preceding query collects hard parses on the active sessions; to get a complete count on the hard parses
since the instance started, please use GV$SYSSTAT instead of GV$SESSTAT .

Note