Database Reference
In-Depth Information
Table 24-2.
PARSING IN CURSOR Parameters
Parameter
Meaning
hv
Hash value; corresponds to
V$SQL.HASH_VALUE
ad
Address; corresponds to
V$SQL.ADDRESS
sqlid
SQL identifier; corresponds to
V$SQL.SQL_ID
(emitted by Oracle11
g
)
PARSING IN CURSOR Entry Format
Caching of SQL statements in the shared pool is based on a hash value that is derived from the
SQL or PL/SQL statement text. Changes in optimizer settings have no effect on the hash value,
whereas slight changes to the statement text such as insertion of a blank or tab character do.
Rarely, two statements with different statements texts may have the same hash value.
The hash value may be retrieved from many V$ views such as
V$SQL
,
V$SQLTEXT
,
V$SQLAREA
,
V$OPEN_CURSOR
, and
V$SESSION
. It remains constant across instance startups, but might change
after an upgrade to a new release. In fact, the algorithm for computing hash values has changed
in Oracle10
g
. The hash value compatible with previous releases is available in the column
OLD_HASH_VALUE
of the views
V$SQL
and
V$SQLAREA
. Merely the hash value is emitted to trace
files. Since Statspack stuck with the “old school” hash value but merely the new hash value is
emitted to trace files, this adds the complexity of translating from the new hash value to the old
hash value when searching a Statspack repository for information pertinent to statements in a
trace file (more on this in Chapter 25).
Oracle10
g
introduced the new column
SQL_ID
to some of the aforementioned V$ views.
The value of this new column is not written to SQL trace files in releases prior to Oracle11
g
, but
is used in Active Workload Repository reports, such that translation from the new hash value
(column
HASH_VALUE
) to the
SQL_ID
may be required when looking up information on a state-
ment in AWR. For cached SQL statements, translation among
SQL_ID
,
HASH_VALUE
, and
OLD_
HASH_VALUE
may be performed using
V$SQL
. For statements that are no longer cached, but were
captured by a Statspack snapshot,
STATS$SQL_SUMMARY
serves as a translation table (the Rosetta
Stone of SQL statement identifiers). AWR has no facility for translating the hash value found in
trace files to the corresponding
SQL_ID
. In releases prior to Oracle11
g
, matching the statement
text between both types of capture is the only time consuming approach for extracting histor-
ical information on a statement, such as past execution time and plan, from AWR (see Chapter 26).
Considering that Statspack requires no extra license and includes session level capture and
reporting (watch out for bug 5145816; see Table 25-4 in Chapter 25), this shortcoming of AWR
might be another reason for favoring Statspack.
Oracle11
g
is the first DBMS release that emits the SQL identifier (
V$SQL.SQL_ID
) in addition
to the hash value to trace files. Hence the statement matching issue between extended SQL
trace and AWR is a thing of the past for users of Oracle11
g
. Following is an example of an
Oracle11
g
PARSING
IN
CURSOR
entry:
PARSING IN CURSOR #3 len=116 dep=0 uid=32 oct=2 lid=32 tim=15545747608 hv=1256130531
ad='6ab5ff8c'
sqlid
='b85s0yd5dy1z3'
INSERT INTO customer(id, name, phone)
VALUES (customer_id_seq.nextval, :name, :phone) RETURNING id INTO :id
END OF STMT
