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

Even after all of these steps have been completed and the statement has been loaded into the library
cache/shareable area, it actually may not be in a shareable state. This is because Oracle has to place a lock on the
cursor/statement header (cursors have two parts: the cursor header and the cursor body). The lock is placed on the header
when the cursor is initially opened and released when the cursor is closed. Once the cursor is closed, the cursor
obtains the shareable state (i.e., other sessions could see this statement in the library cache).
Before storing the statement in the library cache, Oracle computes a hash value and stores it on the hash table.
Hash values are computed for every statement executed. These hash values are then compared against those in
memory to find any matching values. If a matching value is found in memory, Oracle will use the execution plan
already generated. If no matching values are found, it will start hard parsing the query.
Normally there is only one copy of the header that can be found in the library cache. However, there could be
many copies of the cursor body. The reason why there could be many cursor bodies for the same cursor header is
due to variations on the SQL statements. Variations in spacing, formatting, non-consistent usage of case, and so forth
could cause variations because from Oracle's perspective, when it performs a comparison, it is going to compare
character for character.
Versions of cursor body can be found by querying the V$SQLAREA view:
SELECT sql_id,
version_count,
loaded_versions,
executions,
hash_value
FROM v$sqlarea
WHERE version_count > 100;
SQL_ID VERSION_COUNT LOADED_VERSIONS EXECUTIONS HASH_VALUE
------------- ------------- --------------- ---------- ----------
arx2rgss6k33m 104 104 42536 8.12E+08
0v6s91manuhz8 238 238 3272 3.58E+09
2nk2p4h18rbwf 110 110 2185 42708878
dbp7qgbgvmqgz 104 104 42536 3.75E+09
In an RAC environment, multiple versions of these SQL bodies could exist on every instance in the cluster where
the queries are executed. In the illustration following, notice the SQL_ID across several instances:
SELECT inst_id,
sql_id,
version_count vcnt,
loaded_versions lver,
executions excs,
hash_value hval
FROM gv$sqlarea
WHERE version_count > 100
AND executions > 1000
ORDER BY sql_id;
INST_ID SQL_ID VCNT LVER EXCS HVAL
---------- ------------- ---------- ---------- ---------- ----------
4 4shbc31fv2jk7 141 61 4371 1571898951
5 4shbc31fv2jk7 123 61 4967 1571898951
5 a8q5m69h6npk6 125 52 4603 1617581638
4 a8q5m69h6npk6 152 57 5015 1617581638