Database Reference
In-Depth Information
reading data files (
db file scattered read
and
db file sequential read
). In summary, most of the time is spent
on CPU with the rest on disk I/O operations. Notice that the unaccounted-for time is explicitly given:
Resource Usage Profile
**********************
Total Number of Duration per
Component Duration [s] % Events Events [s]
--------------------------- ------------ ------- --------- ------------
CPU 6.969
56.171
n/a n/a
db file scattered read 2.792
22.502
530 0.005
direct path read temp 2.417
19.479
24,015 0.000
direct path write temp 0.513
4.136
11,002 0.000
db file sequential read 0.041
0.326
72 0.001
SQL*Net message from client 0.001 0.008 2 0.001
SQL*Net message to client 0.000 0.000 2 0.000
unaccounted-for -0.325 -2.623 n/a n/a
--------------------------- ------------ -------
Total
12.407
100.000
■
tVd$Xtat always sorts lists according to response time. no option is available to change this behavior because
this is the only order that makes sense to investigate performance problems.
Note
Knowing how the database engine spent time gives a general overview only. To continue the analysis, it's
essential to find out which SQL statements are responsible for that processing time. For this purpose, a list containing
all nonrecursive SQL statements is provided after the overall resource usage profile. In this case, you can see that
a single SQL statement (actually, a PL/SQL block) is responsible for the whole processing time. Note that in the
following list, the total isn't 100 percent because the unaccounted-for time is omitted:
The input file contains 18 distinct statements, 15 of which are recursive.
In the following table, only non-recursive statements are reported.
Total Number of Duration per
Statement ID Type Duration [s] % Executions Execution [s]
------------ ------ ------------ ------- ---------- -------------
#1 PL/SQL 12.724 102.561 1 12.724
#5 PL/SQL 0.006 0.045 1 0.006
#9 PL/SQL 0.002 0.016 1 0.002
------------ ------ ------------ -------
Total 12.732 102.623
Naturally, the next step is to get more information about the SQL statement that's responsible for most of
the processing time. To reference SQL statements easily, TVD$XTAT generates an identifier (the
Statement ID
column in the previous excerpt) for each SQL statement. In the HTML version of the output file, you can simply
click that identifier to locate the SQL statement details. In the text version, however, you have to search for the string
“STATEMENT #1”.
The following information is then given for every SQL statement: general information about the execution
environment, the SQL statement, the execution statistics, the execution plan, the bind variables used for the
