Parsing - Troubleshooting Oracle Performance - page 412

11 AND ses.stat_name <> 'DB time'
12 AND ses.value > 0
13 ORDER BY ses.value DESC;

STATISTIC SECONDS %
--------------------------------------- ---------- ----------
DB CPU 18.204 99.3
parse time elapsed 10.749 58.6
hard parse elapsed time 8.048 43.9
sql execute elapsed time 1.968 10.7
connection management call elapsed time .021 .1
PL/SQL execution elapsed time .009 .1
repeated bind elapsed time 0 0

Information like that provided by this query is useful in determining whether there is a problem with parsing.
Unfortunately, the time model statistics provided by the dynamic performance views just mentioned aren't helpful in
finding out just which SQL statements are causing the problem!
If you are looking for hard facts and not just clues, there are only two sources of information you can use:
the output generated by SQL trace, and the active session history from either v$active_session_history or
dba_hist_active_sess_history . In fact, these are the only sources that can provide timing information about parsing
at the SQL statement level. That's why in this chapter I discuss the identification of parsing problems based only on
SQL trace and active session history.

■ if you want to use active session history to analyze a parsing problem, you have to be aware of four limitations.
First, using active session history requires not only enterprise edition, but also the Diagnostics pack option. second, active
session history provides the necessary information to analyze a parsing problem (the flags in_parse and in_hard_parse )
only from version 11.1 onward. third, you cannot use enterprise Manager for the analysis. Fourth, since the text of the
sQL statement isn't directly available through active session history (you get only the sQL iD), the information provided
isn't always sufficient to identify the sQL statements that are causing a parsing problem.

Note

There are two main kinds of parsing problems. The first is associated with parses lasting a very short time. Let's
call them quick parses . Of course, to be noticeable, a lot of them have to be executed. The second kind of parsing
problem is associated with parses lasting a long time. Let's call this type long parses . This usually happens when the
SQL statement is fairly complex and the query optimizer needs a long time to generate an efficient execution plan. In
this case, the number of executions isn't relevant.
I describe the method used to identify the two kinds of parsing problems in the next two sections. Since there is
no real difference in their identification, I describe only the first one in full detail.

Quick Parses
The following sections describe how to identify performance problems caused by quick parses. The load used as an
example is generated by executing the Java class stored in the ParsingTest1.java file against a version 11.2.0.3 database.
Implementations of the same processing in PL/SQL, C (OCI), C# (ODP.NET), and PHP (PECL OCI8 extension) are
available as well. Since Chapter 3 describes two profilers, TKPROF and TVD$XTAT, I discuss the same example for the
output file of both profilers. The trace file and both output files are available in the ParsingTest1.zip file.