Database Reference
In-Depth Information
Time model statistics are externalized at the system level and for all connected sessions through the
v$sys_time_model
and
v$sess_time_model
views, respectively. In addition, in a 12.1 multitenant environment, the
v$con_sys_time_model
view shows statistics at the container level. In these dynamic performance views, there are
two key columns:
•
stat_name
identifies the statistic, and
•
value
provides the amount of time (in microseconds) cumulated since the component they
belong to (database instance, session or container) was initiated.
It goes without saying that for the session-level statistics (
v$sess_time_model
), there's also a column (
sid
) that
identifies the session they belong to. And in a 12.1 multitenant environment, there's also a column (
con_id
) that
identifies the container.
The following query, based on the
v$sess_time_model
view, shows how a specific session spent its processing
time since it was started (97.3 percent of the time was spent executing SQL statements):
SQL> WITH
2 db_time AS (SELECT sid, value
3 FROM v$sess_time_model
4 WHERE sid = 42
5 AND stat_name = 'DB time')
6 SELECT ses.stat_name AS statistic,
7 round(ses.value / 1E6, 3) AS seconds,
8 round(ses.value / nullif(tot.value, 0) * 1E2, 1) AS "%"
9 FROM v$sess_time_model ses, db_time tot
10 WHERE ses.sid = tot.sid
11 AND ses.stat_name <> 'DB time'
12 AND ses.value > 0
13 ORDER BY ses.value DESC;
STATISTIC SECONDS %
--------------------------------------- ------- -----
sql execute elapsed time 99.437 97.3
DB CPU 4.46 4.4
parse time elapsed 0.308 0.3
connection management call elapsed time 0.004 0.0
PL/SQL execution elapsed time 0.000 0.0
repeated bind elapsed time 0.000 0.0
Notice that in this example, the percentages are computed based on the value
DB time
, which is the overall
elapsed time spent by the database engine processing user calls. Because
DB time
accounts only for the database
processing time, the time spent by the database engine waiting on user calls isn't included. As a result, with only the
information provided by the time model statistics, you can't know whether the problem is located inside or outside
the database. In addition, any difference between elapsed time and CPU time can't be explained based on time model
statistics (for example, only 4.4 percent of the time is spent on CPU in the previous example). To know exactly what's
going on, information about wait classes and wait events (covered in the next section) is necessary.
