Database Reference
In-Depth Information
SQL> SELECT stat_name, value/1000000 time_secs FROM v$sess_time_model
WHERE (stat_name IN ('sql execute elapsed time','PL/SQL execution elapsed time')
OR stat_name like 'DB%')
AND sid=userenv('sid');
STAT_NAME TIME_SECS
---------------------------------------------------------------- ----------
DB time .018276
DB CPU .038276
sql execute elapsed time .030184
PL/SQL execution elapsed time .007097
Next, we generate some wait time, by artificially waiting one second for the event
db file
scattered read
, which otherwise occurs when a
SELECT
statement causes a full table scan. Although
in reality the wait happens in a PL/SQL procedure, it is accounted for as if a full table scan due
to a SQL statement had occurred.
SQL> EXECUTE dbms_system.wait_for_event('db file scattered read', 1, 1);
PL/SQL procedure successfully completed.
Note how the metrics “sql execute elapsed time” and “PL/SQL execution elapsed time”
both have increased by almost one second. Obviously, due to the artificial nature of this test,
the elapsed time is accounted for twice. The metric “DB CPU” has risen only slightly and “DB
time” has also increased by one second, since it aggregates SQL and PL/SQL elapsed time.
11
SQL> SELECT stat_name, value/1000000 time_secs FROM v$sess_time_model
WHERE (stat_name IN ('sql execute elapsed time','PL/SQL execution elapsed time')
OR stat_name like 'DB%')
AND sid=userenv('sid');
STAT_NAME TIME_SECS
---------------------------------------------------------------- ----------
DB time 1.030818
DB CPU .045174
sql execute elapsed time 1.017276
PL/SQL execution elapsed time .987208
The test shows that waiting on events, which occur as part of SQL execution, is rolled up
into the metric “sql execute elapsed time”. Wait time, except for wait events in wait class Idle,
12
is also rolled up into “DB time”.
Just like with CPU usage, we need to somehow normalize “DB time”. Computer systems
have a more or less unlimited capacity for wait time. The more processes run on a system and
compete for the same resources, the more wait time is accumulated at a system level. When ten
processes each wait one second for the same TX enqueue, ten times the total wait time of a
single process results. The metric “DB time” may be normalized by the snapshot interval. I
shall call this metric
relative DB time
. We will again start with a manual calculation. The relevant
11. Interestingly, the one second waited is not accounted for twice in the metric “DB time”.
12. To retrieve all events in wait class Idle, run the following query on an Oracle10
g
instance:
SELECT name FROM v$event_name WHERE wait_class='Idle'
.
