Question: What is
the DB Time metric in an AWR report and how do I generate
the DB Time directly from the AWR tables? I need to
see the AWR DB Time and understand this metric.
On a four CPU system ( 4 CPU CORE ) , for one hour elapsed time, what is
the maximum amount of DB Time that can be accumulated in an
AWR report for the hour?
Answer: First, read these
important notes on the
DB Tme
metric.
The DB Time is a time model statistic that is the sum of
all Oracle process' CPU consumption plus the sum of non-idle
wait time. When optimizing Oracle databases, we focus on
reducing the processing "time", usually by tuning SQL
statements.
WORKLOAD REPOSITORY
report for
DB Name
DB Id Instance
Inst Num Startup Time Release
RAC
------------ ----------- ------------ --------
--------------- ----------- ---
FINONE
1252548811 mydb
1 13-Dec-12 22:10 11.2.0.3.0 NO
Host Name
Platform
CPUs Cores Sockets Memory(GB)
----------------
-------------------------------- ---- ----- -------
----------
MYPROD
AIX-Based Systems (64-bit)
24 12
80.00
Snap Id Snap Time
Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin
Snap: 25130 15-Dec-12 03:00:39
425 12.2
End Snap:
25131 15-Dec-12 04:00:45
430 14.7
Elapsed: 60.09
(mins)
DB Time:
298.22 (mins)
The formula for DB Time can be
expressed as CPU time plus non-idle wait time:
DB Time
= DB CPU + non_idle_wait_time
and it follows that
non_idle_wait_time = DB Time - DB CPU
This "time" in AWR is the same as DB Time and it is the
same as the DB Time statistic name in v$sess_time_model and
v$sys_time_model. The maximum DB Time metric in AWR is
influenced by these metric values:
- The number of connected sessions (the max is the
"sessions parameter in the init.ora (spfile)
- The number of CPU's (cpu_count parameter) on the
server
- The number of parallel processes running
As we see, the DB Time will vary wildly depending on the
amount of activity on the database.
For a SQL statement, the db time does not really
apply because
db time is a system-level metric:
Statistic Name
Time (s) Percent of Total DB Time
---------------------------------- --------
------------------------
DB time
169
sql execute elapsed time 156
93
DB CPU
153
90
PL/SQL execution elapsed time
77
46
background cpu time
53
31
parse time elapsed
6
4
hard parse elapsed time
4
3
connection management call elapsed time 0
0
This script will display the DB Time for a specific AWR
snapshot:
SELECT *
FROM
(
SELECT
A.INSTANCE_NUMBER,
LAG(A.SNAP_ID) OVER (ORDER BY
A.SNAP_ID) BEGIN_SNAP_ID,
A.SNAP_ID END_SNAP_ID,
TO_CHAR(B.BEGIN_INTERVAL_TIME,'DD-MON-YY HH24:MI')
SNAP_BEGIN_TIME,
TO_CHAR(B.END_INTERVAL_TIME
,'DD-MON-YY HH24:MI') SNAP_END_TIME,
ROUND((A.VALUE-LAG(A.VALUE) OVER
(ORDER BY A.SNAP_ID ))/1000000/60,2) DB_TIME_MIN
FROM
DBA_HIST_SYS_TIME_MODEL A,
DBA_HIST_SNAPSHOT B
WHERE
A.SNAP_ID = B.SNAP_ID AND
A.INSTANCE_NUMBER =
B.INSTANCE_NUMBER AND
A.STAT_NAME = 'DB time'
)
WHERE
DB_TIME_MIN IS NOT NULL AND DB_TIME_MIN > 0
ORDER BY 2 DESC;
|
|
|
|
|
Oracle Training from Don Burleson
The best on site
"Oracle
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
|
|
|
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
|
