Database Reference
In-Depth Information
Statement Total Number of Duration per
ID Type Duration % Executions Execution
--------- ------ -------- ------- ---------- ------------
#1
SELECT
2.791
99.167
1 2.791
#9 PL/SQL 0.005 0.166 1 0.005
#12 PL/SQL 0.002 0.071 1 0.002
--------- ------ -------- -------
Total 2.797 99.404
According to the nonrecursive execution statistics of the SQL statement causing the problem, a single parse
operation was responsible for about 100% (2.654/2.661) of the processing time. This clearly shows that the database
engine did nothing else besides parsing.
Call Count Misses CPU Elapsed PIO LIO Consistent Current Rows
------- ----- ------ ----- ------- --- --- ---------- ------- ----
Parse
1
1 2.653
2.654
0 0 0 0 0
Execute 1 0 0.002 0.002 0 0 0 0 0
Fetch 2 0 0.004 0.006 10 10 10 0 1
------- ----- ------ ----- ------- --- --- ---------- ------- ----
Total 4 1 2.659
2.661
10 10 10 0 1
Summarizing the Problem
The analysis shows that a single SQL statement is responsible for almost the whole response time. In addition, the
whole response time is due to parsing for this particular SQL statement. Eliminating it would probably greatly reduce
the response time.
Solving Parsing Problems
The obvious way to solve parsing problems is to avoid the parse phase. Unfortunately, this isn't always that easy. In
fact, depending on whether the parsing problem is related to quick parses or long parses, you have to implement
different techniques to solve the problem. I discuss these separately in the following sections. In both cases, the
examples described in the previous “Identifying Parsing Problems” section are used as the basis for explaining
possible solutions.
■
the following sections describe the impact of parsing by showing the results of different performance tests.
the performance figures are intended only to help compare different kinds of processing and to give you a feel for their
impact. remember, every system and every application has its own characteristics. therefore, the relevance of using
each technique might be very different depending on where it's applied.
Note
Quick Parses
This section describes how to take advantage of prepared statements to avoid unnecessary parse operations. Since
implementation details depend on the development environment, they aren't covered here. Later in this chapter,
specifically in the “Using Application Programming Interfaces” section, I provide details for PL/SQL, OCI, JDBC,
