Database Reference
In-Depth Information
In active session history, the
in_parse
and
in_hard_parse
flags inform you whether, at the time a sample was
taken, the session was parsing a SQL statement. Based on these flags, you can write a query like the following that
estimates, for a specific session, not only the DB time, but also the time spent parsing SQL statements (note that both
figures are in seconds):
SQL> SELECT count(*) AS db_time,
2 count(nullif(in_parse, 'N')) AS parse_time,
3 count(nullif(in_hard_parse, 'N')) AS hard_parse_time
4 FROM v$active_session_history
5 WHERE session_id = 68
6 AND session_serial# = 23;
DB_TIME PARSE_TIME HARD_PARSE_TIME
------- ---------- ---------------
5 4 4
If you recognize that parsing is a problem (for example, according to the previous output, 80% of the time is spent
for parsing), you need to know not only which SQL statements were parsed, but also how much time was spent for
each of them. Unfortunately, one of the limitations of active session history is that the text of a SQL statement itself
isn't directly available. To get the statement text, you have to perform a lookup based on the SQL ID in another view.
For example, you might join
v$active_session_history
to
v$sqlarea
.
Unfortunately, especially on a busy database instance that's suffering because of quick parses, it's quite common
that cursors stay in the library cache for a short period of time. As a result, you might not manage to get as much
information as required. To slightly increase the likelihood of getting more information, you might use
v$sqlstats
instead of
v$sqlarea
. In fact, the former has a greater retention. For example, the following query is able to retrieve
the SQL statement of only two of the four samples related to parsing (remember, test case 1 executes 10,000 SQL
statements):
SQL> SELECT a.sql_id, s.sql_text, count(*) AS parse_time
2 FROM v$active_session_history a, v$sqlstats s
3 WHERE a.sql_id = s.sql_id(+)
4 AND a.session_id = 68
5 AND a.session_serial# = 23
6 AND a.in_parse = 'Y'
7 GROUP BY a.sql_id, s.sql_text
8 ORDER BY count(*) DESC;
SQL_ID SQL_TEXT PARSE_TIME
------------- ---------------------------------- ----------
a6z6qamdcwqdv 1
2hcrrthw3w4y8 1
aydf9rbd6mz1m SELECT pad FROM t WHERE val = 9580 1
50m9q01tmghmw SELECT pad FROM t WHERE val = 7574 1
Summarizing the Problem
The analysis performed via active session history isn't very useful in this case. This is expected because sampling a
single session over a dozen seconds can result in only a few samples. However, the analysis performed by TKPROF
and TVD$XTAT clearly shows that the processing performed by the database engine is solely due to parsing. However,
on the database side, parsing is responsible for only about 39% (5.705/14.494) of the overall response time.
