What is db time in awr report?

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 that how much activity performed by the database for the duration.

Can database have higher DB Time than Elapsed Time?

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. Now again read the DB Time definition stated above.

When DB time gets increase?

Where to find DB time in AWR report?

There are two places where you can find the DB Time in AWR report.

For More Details, you can reach us using the below link.

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.

Introduced in Oracle 10g and having evolved from the Statspack functionality of Oracle 8i/9i, the Automatic Workload Repository report or AWR report (as it is more commonly known as) is a vital tool to collect historical performance statistics of an Oracle Database by DBAs and System Performance engineers. An AWR report is primarily useful in gathering data on the Wait Events, Active Session History (ASH) Statistics, Object Usage Statistics and Resource hungry SQL Queries, which together help in identifying, analyzing and resolving performance bottlenecks of the database. Apart from this, ADDM (Automatic Database Diagnostic Monitor), the self-diagnosis utility of Oracle DB relies on the stats contained in AWR reports for accurately identifying root causes of performance issues. The AWR report in itself runs into hundreds of metrics and can be quite overwhelming to analyze at once, for someone not making a living out of doing so! This article is intended to call out the most important sections from the report as listed below, for drilling down the performance bottlenecks and fixing them fast. (For an advanced user, this can be clubbed with the recommendations from the advisory sections towards the end of an AWR report and ADDM report recommendations for getting an in-depth information of DB health, both of which are beyond the scope of this article).

2. Load Profile: The first row in this table i.e. DB Time per second is the ratio of Elapsed DB Time to elapsed Clock time as listed in the Snapshot details section, while DB CPU per second indicates average number of CPU cores in use per second. Ideally this value should be less than the total number of CPU cores available for most of the time in order for the DB to perform well. Next metric to look for in this section is the ratio of Hard Parses and Parses, which should not be much higher when compared to the baseline report, as it indicates potential issues with cursor management and bind variables (More on Parsing). High hard parse rates cause serious performance issues. A high hard parse rate is usually accompanied by latch contention on the shared pool and library cache latches. Frequent hard parses are caused by inadequately sized shared pool and ineffective utilization of bind variables.

3. Instance Level Statistics: All the metrics in this section like Buffer Hit % (% of times a particular block was found in buffer cache instead of performing a physical I/O (reading from disk), buffer Nowait% and Latch Hit % are targeted at 100% for best performance. % Non-Parse CPU indicates percentage of CPU time spent in non-parsing activities which is preferred to be on the higher side for better DB performance, since parsing is CPU intensive. Also, the execute to parse ratio should be very high in an ideal situation.

4. Top 5 Timed Events: This is the most important table to consider when identifying potential bottlenecks. The last column Wait Class if having the value "Concurrency" indicates serious issues. Also, the Average wait time in fourth column should be noticed for alarmingly high values when compared to the other events listed. The fifth column %DB Time or %Total Call Time denotes the percentage of total DB time spent on the corresponding event and ideally DB CPU should be taking the major chunk of it. If not, then the corresponding event (say X) needs to be investigated further. For this, one can click on SQL Statistics link in the Main Report Section and click on the 'SQL Ordered by' link closest to the wait event (X) identified earlier. The obtained table can be used to drill down the SQL Statements using the maximum of the identified resource (X) for further optimization.

5. Shared Pool Statistics: Indicates percentage usage of memory allocated to the shared pool and the percent of SQLs with execution count > 1. Extremely high percent usage of Shared pool indicates a need for increasing the shared pool size while a very low utilization indicates a larger than required pool size for the given workload. Also, the percent of SQL with executions > 1, should be near to 100 for effective utilization of shared SQL statements. If not so, this indicates ineffective utilization of bind variables.

6. Time Model Statistics: This section provides a summary of components where the database is spending it's time. The table contains system resource consumption metrics, which can be ordered by Time (s) and % of DB Time. One has to look for the statistic taking maximum % of DB time and compare it with baseline for any abnormal behavior. If SQL time>>DB CPU time then the system needs to be diagnosed for I/O issues. Also, as stated earlier, hard parse time is an important metric to watch for and compare with baseline.

7. OS Details: Lists the CPU Utilization breakup across system, user, busy, iowait and idle along with load statistics.

8. SQL Statistics: This section gives the option of sorting all the SQL Queries executed in desired fashion like 'SQL ordered by elapsed Time', 'SQL ordered by CPU Time' etc. Any query figuring in top five of two or more sorted lists is a definite candidate for tuning. This section when used in combination with 'Top 5 Timed events', is the most vital and fastest way to identify the bottleneck from an AWR Report. More specifically, a query with less number of executions and high value of 'Elapsed Time per Exec (s) ' should be a prime suspect. Similarly, in case of a high CPU Utilization observed on the DB, one should check for queries with highest 'CPU per exec(s)' and low execution count and target them for optimization.

Appendix:

References:

1. http://pafumi.net/AWR%20Reports.html

2. http://www.orafaq.com/wiki/Parsing

3. https://oracle-base.com/articles/10g/automatic-workload-repository-10g

4. http://docs.oracle.com/cd/B19306_01/server.102/b14211/autostat.htm#i29253

5. http://docs.oracle.com/cd/E11882_01/server.112/e41573/diag.htm#PFGRF02602