Top 5 wait events (timed events) : we should investigate the top 5 wait events which are causing the bottleneck.
Load profile: This section gives a glimpse of the database workload activity that occurred within the snapshot interval.
Elapsed Time - Elapsed Time is the wall clock time of duration for which the AWR report has been generated. For example, if we generate an AWR report for 1 hour then Elapsed Time in the AWR report will be 60 mins.
Hit ratios: This section contains ratios or calculations that may provide information regarding different structures and operations in the Oracle instance. Database tuning must never be driven by hit ratios. Hit ratios only provide additional information to help the DBA understand how the instance is operating.
Wait events
Latch waiit
Top SQL
Instance activity
File I/O and segment statistics
Memory allocation
DB Time is the total time spent by all user processes which are actively working or actively waiting in the database calls.
It includes the CPU Time, IO Wait time and non-idle time.
It tells us how much activity performed by the database for the duration.
DB CPU is the CPU consumption by all Oracle server processes/foreground processes during snapshot interval time. The name DB CPU has been taken from statistic name which is found in the view v$sys_time_model.
SQL> select stat_name, value from v$sys_time_model where stat_name='DB CPU'
2 /
STAT_NAME VALUE
------------------------------ ----------
DB CPU 974752790
If we look at any AWR report, we will find DB CPU statistic value in "Time Model Statistics" Section. The value reported in the AWR report represents total CPU consumption by all Oracle server processes during the snapshot interval time. This statistic value is converted into seconds in AWR report.
Yes. The database can have higher DB Time than Elapsed Time as a database can have multiple CPUs and multiple database calls.
Example: Suppose there are 5 active sessions present on the database which are executing some long-running queries. Now consider the duration of 5 Minutes i.e. Elapsed Time = 5 Min. In this case what will be the DB Time? As 5 active Sessions are executing queries for the entire duration of 5 Minutes, my DB Time will be 25 Min.
DB Time increases when the system load increases. If database call increase then DB time increases.
DB Time increases when system performance degrades. When IO wait time increases then Oracle will spend more time for IO which results in higher DB time.
If the database host is CPU bound then sessions will wait on the active run queue resulting in an increase in DB time.
When any Oracle process is not consuming CPU, it will be paused. This time, when a process is not consuming CPU is termed as wait time. The wait time can also be categorised in idle wait time and non-idle wait time. If a process is waiting for something to complete, then it is considered as a non-idle wait time. There are lots of idle and non-idle wait events in the Oracle database.
So, as a DBA, to improve the database performance, we need to focus on non-idle wait events.
Global Messaging Statistics: It provides the large number of statistics involving message traffic between the instances.
Global CR Served Stats: This shows the global cache requests this instance must serve to other instances. A non-zero value can be indicative of issues with the Cluster Interconnect.
Global CURRENT Served Stats: It shows a histogram of the Current blocks served by the instance. Ideally, the majority of the statistics should be no more than ten milliseconds in duration. If these statistics spend time in the longer portions of the histogram, it can be indicative of a slow or congested Cluster Interconnect.
Global Cache Transfer Stats
Interconnect Stats
SQL ordered by Cluster Wait Time: It shows the SQL statements experiencing the most waits in the Cluster class i.e. the “cause” of those wait events.
