Database Reference
In-Depth Information
Parsing
Parsing involves syntactic and semantic analysis of SQL statements as well as determining a
well-suited execution plan. Since this may be a costly operation, the ORACLE DBMS has the
capacity to cache the results of parse calls in the so-called library cache within the System
Global Area (SGA) for reuse by other statements that use the same SQL statement text.
The use of bind variables in SQL statements is crucial for the reuse of cached statements.
Failure to use bind variables causes increased parse CPU consumption, contention for the
library cache, excessive communication round-trips between client and server due to repeated
parse calls of non-reusable statements with literals, and difficulties in diagnosing performance
problems due to the inability of the TKPROF utility to aggregate statements, which are identical
apart from literals. I recommend reading the section “Top Ten Mistakes Found in Oracle Systems”
on page 3-4 of
Oracle Database Performance Tuning Guide 10g Release 2
before beginning
design and coding of an application. While bind variables are mandatory to achieve scalability
in high volume transaction processing (OLTP), literals are usually preferred in data warehousing
applications to provide the CBO with as much information as possible and to avoid the unpre-
dictability inherent in bind variable peeking. The CBO looks at bind variable values when it first
encounters a statement, but not on subsequent executions of the same statement, such that
the plan chosen may be optimal for the initial execution but inappropriate for subsequent
executions. This functionality is called bind variable peeking. It is enabled by default with the
hidden parameter setting
_OPTIM_PEEK_USER_BINDS=TRUE.
Parsing is usually represented by two adjacent entries in the trace file. The first is
PARSING
IN
CURSOR
, and the second is
PARSE
. The minimum SQL trace level for enabling parse related
entries is 1. Here's an example of a
PARSING
IN
CURSOR
followed by a
PARSE
from an Oracle10
g
trace file:
PARSING IN CURSOR #3 len=92 dep=0 uid=30 oct=2 lid=30 tim=81592095533 hv=1369934057
ad='66efcb10'
INSERT INTO poem (author, text) VALUES(:author, empty_clob())
RETURNING text INTO :lob_loc
END OF STMT
PARSE #3:c=0,e=412,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=81592095522
Parameters associated with the
PARSING
IN
CURSOR
entry are explained in Table 24-2.
Table 24-2.
PARSING IN CURSOR Parameters
Parameter
Meaning
len
Length of the SQL statement text in bytes
dep
Recursive call depth
uid
Parsing user identity; corresponds to
ALL_USERS.USER_ID
and
V$SQL.PARSING_USER_ID
oct
ORACLE command type; corresponds to
V$SQL.COMMAND_TYPE
and
V$SESSION.COMMAND
lid
Parsing schema identity; corresponds to
ALL_USERS.USER_ID
and
V$SQL.PARSING_
SCHEMA_ID
; may differ from
uid
(see Chapter 14 on
ALTER
SESSION
SET
CURRENT_SCHEMA
)
tim
Timestamp in microseconds; often slightly earlier than the value of
tim
in the associated
PARSE
entry
