Database Reference
In-Depth Information
information at the file level. For example, the following table shows the statistics related to the
db file scattered
read
wait event of SQL statement 2. In this example, you can see that 530 disk I/O operations were performed on data
file 4 in 2.792 seconds. This means that each disk I/O operation lasted 5.267 milliseconds on average (be careful, the
table displays this in microseconds):
File Total Number of Duration per
Number Duration [s] % Events % Blocks [b] % Event [µs]
------- ------------- ------- ---------- ------- ----------- ------- -------------
4 2.792
100.000
530
100.000 38,462 100.000
5,267
As you might expect, for all SQL statements, the structure is the same. However, one piece of information is
missing in the first two SQL statements. To illustrate, let's have a look at an excerpt of the information provided for a
SQL statement using bind variables. As the output for SQL statement 3 shows, if information about bind variables has
been recorded in the trace file, TVD$XTAT shows their datatype and value. In addition, if several executions have been
performed (in this case, ten), bind variables will be tagged by a number of execution. For example:
Session ID 156.29
Service Name SYS$USERS
Module Name SQL*Plus
Parsing User 34
Recursive Level 1
Parent Statement ID 1
Hash Value 1035370675
SQL ID 7fjjjf0yvd05m
SELECT COUNT(N) FROM T WHERE ID < :B1 *123
Bind Variables
**************
10 bind variable sets were used to execute this statement.
Number of
Execution Bind Datatype Value
---------- ----- --------- ------
1 1 NUMBER "1"
2 1 NUMBER "2"
3 1 NUMBER "3"
4 1 NUMBER "4"
5 1 NUMBER "5"
6 1 NUMBER "6"
7 1 NUMBER "7"
8 1 NUMBER "8"
9 1 NUMBER "9"
10 1 NUMBER "10"
In summary, even if plenty of SQL statements are executed (18 in total), SQL statement 2 is responsible for most
of the response time. Therefore, to improve performance, the execution of that SQL statement should be avoided or
optimized.
