DB TIME and DB CPU in Oracle

 

What is DB TIME?

DB Time in Oracle is an important metric used to measure database performance. 

Here’s an easy-to-understand explanation:

---

Definition of DB Time

DB Time is the total time spent by all user sessions either:

1. Actively performing work in the database (e.g., executing queries, processing transactions).
2. Actively waiting for resources (e.g., waiting for I/O, locks, or other events).

This includes all activity in database calls (SQL execution, parsing, PL/SQL execution, etc.), but it does not include time spent idle, such as when the session is simply waiting for user input.

---

Key Points About DB Time

1. Includes Waits:

   • Unlike just CPU time (time actively using the CPU), DB Time also includes time spent waiting for resources.

2. Cumulative Across All Sessions:

   • DB Time is calculated for all sessions combined. For example:
     • If two sessions are running for 1 second each, DB Time will be 2 seconds, even though only 1 second has passed in real time.

3. Focuses on Active Database Sessions:

   • It doesn't include time where sessions are idle (e.g., connected but not executing any operations).

---

Formula for DB Time

You can think of DB Time as:
DB Time = Work Time + Wait Time

• Work Time: Time actively executing SQL or PL/SQL.
• Wait Time: Time spent waiting for resources like I/O, locks, or other events.

---

How DB Time Helps in Performance Tuning

1. Performance Indicator:

   • High DB Time relative to wall-clock time (elapsed time) means the database is spending too much time working or waiting.

2. Troubleshooting:

   • Analyzing where the DB Time is being spent (e.g., CPU, I/O, or specific wait events) helps pinpoint performance bottlenecks.

3. Comparative Analysis:

   • Comparing DB Time across different workloads or time periods can indicate whether performance is improving or degrading.

---

How to Measure DB Time

Oracle provides several tools to measure and analyze DB Time:

1. Automatic Workload Repository (AWR):
   DB Time is prominently displayed in AWR reports, typically at the top.

2. V$SYSMETRIC:
   Use this view to check DB Time metrics over short intervals:

   
   SELECT METRIC_NAME, VALUE
   FROM V$SYSMETRIC
   WHERE METRIC_NAME = 'Database Time Per Sec';
   

3. Active Session History (ASH):
   ASH captures snapshots of active sessions, contributing to DB Time analysis.

---

Example to Understand DB Time

Scenario:

• 3 user sessions are active in the database:
  • Session 1 executes a query for 2 seconds.
  • Session 2 waits for a resource (e.g., I/O) for 1 second.
  • Session 3 processes data for 3 seconds.

Calculation:

• DB Time = (2 seconds for Session 1) + (1 second for Session 2) + (3 seconds for Session 3)
• Total DB Time = 6 seconds.

Even if this activity occurs in just 3 seconds of wall-clock time, DB Time captures all the time spent by all sessions cumulatively.

---------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------------

What is DB CPU?

DB CPU is the amount of time the database spends using the CPU for processing tasks. 

It represents the active CPU time consumed by database user sessions for executing SQL queries, PL/SQL code, and other database operations. 

It does not include time spent waiting for resources or performing I/O.

---

Key Points About DB CPU

1. Focuses on Active CPU Usage:

   • Unlike DB Time, which includes both work and wait time, DB CPU measures only the time sessions are actively using the CPU.

2. Tracks Work Done by the Database:

   • It reflects how much processing power the database is using to handle queries and operations.

3. Captured in AWR and Statspack:

• DB CPU is a key metric in performance reports like AWR or Statspack, helping to understand where the CPU is being spent.

---

How is DB CPU Measured?

Oracle measures DB CPU in centiseconds (1/100 of a second). For example:

• 1000 centiseconds = 10 seconds of CPU time.

It is a cumulative metric, meaning if multiple sessions are using CPU simultaneously, their CPU time is added together.

Example:

• Session 1 uses the CPU for 5 seconds.
• Session 2 uses the CPU for 3 seconds.
• Total DB CPU = 5 + 3 = 8 seconds.

---

How DB CPU Differs from Other Metrics

How to Analyze DB CPU

DB CPU is usually analyzed alongside DB Time and other metrics to understand database performance:

1. Check DB CPU Usage:

   • If DB CPU is high compared to the total available CPU, it indicates CPU contention or high workload.

2. Compare with DB Time:

   • If DB CPU is low compared to DB Time, most of the time is spent waiting (e.g., for I/O or locks).
   • If DB CPU is high relative to DB Time, it means the workload is CPU-intensive.

3. Use AWR Report:
   DB CPU is shown under Load Profile and Top Timed Events in AWR reports.

---

Query to Check DB CPU

You can query DB CPU usage from V$SYS_TIME_MODEL or V$OSSTAT.

Query from V$SYS_TIME_MODEL:

SELECT STAT_NAME, VALUE / 100 AS DB_CPU_SECONDS
FROM V$SYS_TIME_MODEL
WHERE STAT_NAME = 'DB CPU';

Query from V$OSSTAT:

SELECT METRIC_NAME, VALUE
FROM V$SYSMETRIC
WHERE METRIC_NAME = 'Host CPU Utilization %';

---

Example to Understand DB CPU

Scenario:

• A database server has 8 CPUs.
• Total elapsed time is 60 seconds.
• 4 sessions run simultaneously for 30 seconds, actively using the CPU.

Calculation:

• DB CPU = 4 sessions x 30 seconds = 120 seconds.

Even though the wall-clock time is only 60 seconds, DB CPU captures the cumulative CPU usage across all sessions.

---

Why is DB CPU Important?

1. Identify CPU Bottlenecks:

   • High DB CPU values may indicate queries or operations that need tuning to reduce CPU usage.

2. Capacity Planning:

   • Helps understand how much CPU is required for the workload, aiding in resource allocation.

3. Performance Troubleshooting:

• High DB CPU combined with high elapsed time (DB Time) suggests CPU-intensive queries are a bottleneck.