• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Database » What is DB time ,DB cpu Average Active sessions, Active session in oracle

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

December 30, 2019 by techgoeasy Leave a Comment


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

Table of Contents

  • DB Time
  • Active Session
  • Elapsed time
  • Average Active Sessions
  • DB CPU

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

db time  in oracle, Active Session

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


Filed Under: Oracle, Oracle Database Tagged With: active sessions in oracle, What is DB time and Average Active sessions

Reader Interactions

Leave a Reply Cancel reply

Primary Sidebar

Top Posts & Pages

  • How to check Tablespace in Oracle -Size ,Free space,datafiles ,growth
  • Query to check table size in Oracle database
  • how to check all constraints on a table in oracle
  • Oracle Indexes and types of indexes in oracle with example
  • ORA-01017 Oracle Error Resolution Tips



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • How to run Autoconfig on Patch system in R12.2
  • Admin scripts in R12.2 Ebuisness Suite
  • Timezone settings in Oracle database
  • how to enable trace in oracle apps r12
  • Transparent Data Encryption Queries

Copyright © 2021 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us