Database Reference
In-Depth Information
By just looking at the summary of nonrecursive SQL statements, you can see that a single SQL statement is
responsible for the whole processing. This is a significant difference between TKPROF and TVD$XTAT. In fact,
TVD$XTAT recognizes similar SQL statements and reports them together.
Statement Total Number of Duration per
ID Type Duration % Executions Execution
--------- ------ -------- ------- ---------- ------------
#1 SELECT 12.130 83.689 10,000 0.001
#2 COMMIT 0.000 0.002 1 0.000
--------- ------ -------- -------
Total 12.130 83.691
According to the execution statistics without recursive statements of the SQL statement number 1, the parse time is
responsible for about 95% (5.705/6.009) of the processing time. This clearly shows that the database engine did nothing
else besides parsing. The slight difference in the execution statistics between the output file of TKPROF and TVD$XTAT
is that TVD$XTAT shows the number of misses (in other words, hard parses) next to the number of parse calls.
Call Count Misses CPU Elapsed PIO LIO Consistent Current Rows
------- ------ ------ ----- ------- --- ------ ---------- ------- -----
Parse
10,000 10,000
5.548
5.705
0 0 0 0 0
Execute 10,000 0 0.176 0.156 0 0 0 0 0
Fetch 10,000 0 0.138 0.148 0 23,051 23,051 0 3,048
------- ------ ------ ----- ------- --- ------ ---------- ------- -----
Total 30,000 10,000 5.862
6.009
0 23,051 23,051 0 3,048
The problem with these execution statistics is that about 51% (1 - 6.009/12.130) of the response time is missing
from them. At any rate, you can see part of the missing time by looking at the resource usage profile at the SQL
statement level shown here; specifically, 6.243 seconds were spent waiting for the client.
Total Number of Duration per
Component Duration % Events Event
--------------------------- -------- ------- --------- ------------
SQL*Net message from client
6.243
51.470 10,000 0.001
CPU 5.862 48.327 n/a n/a
SQL*Net message to client 0.024 0.201 10,000 0.000
latch: shared pool 0.000 0.003 5 0.000
--------------------------- -------- -------
Total
12.130
100.000
Using Active Session History
Active session history is based on sampling. You thus require plenty of samples for performing a sensible analysis.
Given that test case 1 runs only for a dozen seconds, using active session history for the analysis isn't likely to result in
accurate information. The aim of this section is to show you the kind of queries you might want to use for identifying
which SQL statements were parsed and how much time was spent.
