Save big on AI tools today!

DB Time and DB CPU using real-world database issues

Low Database Performance

Angudi Tech

6/13/20262 min read

Scenario 1: The CPU-Bound Database (Inefficient Code)

The application feels sluggish, and the server's fans are spinning at maximum speed.

  • Elapsed Time: 600 seconds

  • DB Time: 2,200 seconds

  • DB CPU: 2,000 seconds

  • Wait Time: 200 seconds (DB Time minus DB CPU)

Analysis

  • Ratio:

    20002200

    ×100

    =𝟗𝟏%

    of DB Time is spent on CPU.

  • Average Active Sessions (AAS):

    2200600

    =𝟑.𝟔𝟕

    .

  • Diagnosis: The database is highly efficient but heavily loaded. It spends almost all its time processing data rather than waiting for disks or locks. Since the AAS (3.67) is very close to the total CPU capacity (4 cores), the server is running out of processing headroom.

  • Typical Cause: A developer deployed a query that performs a full table scan on a million-row table without using an index, forcing the CPU to read millions of data blocks in memory over and over.

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

Scenario 2: The Resource-Bound Database (Disk Bottleneck)

Users report that saving or loading data takes forever, but server monitoring tools show almost 0% CPU utilization.

  • Elapsed Time: 600 seconds

  • DB Time: 4,500 seconds

  • DB CPU: 300 seconds

  • Wait Time: 4,200 seconds

Analysis

  • Ratio:

    3004500

    ×100

    =𝟔.𝟔%

    of DB Time is spent on CPU.

    93.4%93.4 %

    𝟗𝟑.𝟒%

    is spent waiting.

  • Average Active Sessions (AAS):

    4500600

    =𝟕.𝟓

    .

  • Diagnosis: The system is severely choked. The AAS (7.5) is much higher than the physical CPU cores (4). However, the CPUs are sitting idle because user sessions are stuck waiting for something else.

  • Typical Cause: The storage area network (SAN) is failing or overloaded. In the AWR report, you would see massive wait times for the db file sequential read or db file scattered read wait events, meaning sessions are blocked waiting for physical hard drives to return data.

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

Scenario 3: The Application Locking Bottleneck (Concurrency Issue)

A specific business process (like an end-of-day checkout) completely freezes, while the rest of the application runs perfectly.

  • Elapsed Time: 600 seconds

  • DB Time: 12,000 seconds

  • DB CPU: 100 seconds

  • Wait Time: 11,900 seconds

Analysis

  • Ratio: Less than 1% of DB Time is active CPU calculation.

  • Average Active Sessions (AAS):

    12000600

    =𝟐𝟎

    .

  • Diagnosis: Extreme serialization. 20 concurrent user sessions are completely frozen. They are not using the CPU, and they are not waiting on hardware disks.

  • Typical Cause: Row locking. One user session opened an uncommitted transaction on a row (e.g., updating an inventory balance table) and went to lunch. 19 other sessions tried to update that exact same row and are now stacked up waiting. In your AWR report, you would see the top wait event listed as enq: TX - row lock contention.

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

Scenario 4: The Healthy, Idle Database

The system is performing well, and users face no delays.

  • Elapsed Time: 600 seconds

  • DB Time: 45 seconds

  • DB CPU: 40 seconds

  • Wait Time: 5 seconds

Analysis

  • Average Active Sessions (AAS):

    45600

    =𝟎.𝟎𝟕𝟓

    .

  • Diagnosis: The database is mostly idle. Over the 10-minute window, the total workload from all users combined only required 45 seconds of total database attention. The system has massive capacity to handle sudden spikes in traffic.