What is DB time ,DB cpu Average Active sessions, Active session in oracle

In this section we will be defining the terms like Database time(DB time in oracle ), Active session ,elapsed time and Average active session

DB Time

Database Time, or DB Time, is defined by Oracle as the total time by foreground sessions executing database calls. This includes CPU time, IO time, and non-idle wait time.  In other words, it’s the total time spent either actively working or actively waiting in a database call

select stat_name, value from v$sys_time_model where stat_name='DB time';

DB time in the AWR report is generated using the below query

SELECT Round(NVL((e.value - s.value),-1)/60/1000000,2)||' minutes' "DB Time"
FROM DBA_HIST_SYS_TIME_MODEL s,
DBA_HIST_SYS_TIME_MODEL e
WHERE s.snap_id = &AWRStartSnapID AND
e.snap_id = &AWREndSnapID anD
e.dbid = s.dbid AND
e.instance_number = s.instance_number AND
s.stat_name = 'DB time' AND
e.stat_id = s.stat_id;

You can query the DB time for a session using the below query

select
sum(value) "DB time"
from
v$sess_time_model
where
stat_name='DB time';

Active Session

An Active Session in oracle is defined as an Oracle session currently spending time in a database call, and the average activity of a session is the ratio of active to total wall clock time

Elapsed time

It is the time which user experiences when running a query. When looking into AWR report, the Elapsed Time is the wall clock time of duration for which AWR report has been generated. For example, if we generate AWR report for 1 hour then Elapsed Time in AWR report will be 60 mins.

So,The “Elapsed” Time is the AWR report time is the “End Snap” time minus the “Begin Snap” time

Average Active Sessions

At a macroscopic level, DB Time is the sum of DB time over all sessions.  Average Active Sessions, or AAS, represents the sum of active sessions over all sessions, at any given point in time

AAS = (DB Time / Elapsed Time)

The Average Active Sessions metric is important because it’s best representation of your database system load.

DB CPU

It is the CPU consumption by all Oracle server processes/foreground processes during snapshot interval time

select stat_name, value from v$sys_time_model where stat_name='DB CPU'

DB CPU in the AWR report is generated using the below query

SELECT Round(NVL((e.value - s.value),-1)/60/1000000,2)||' minutes' "DB Time"
FROM DBA_HIST_SYS_TIME_MODEL s,
DBA_HIST_SYS_TIME_MODEL e
WHERE s.snap_id = &AWRStartSnapID AND
e.snap_id = &AWREndSnapID anD
e.dbid = s.dbid AND
e.instance_number = s.instance_number AND
s.stat_name = 'DB CPU' AND
e.stat_id = s.stat_id;

Now

DB Time = DB CPU + Non Idle Time

Hope you like this content on db time in oracle, Active Session ,Average Active Sessions, DB CPU. Please do let me know the feedback

Also Reads
Automatic Workload Repository
ADDM
Sql Tuning Advisor
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:229943800346471782