How to Diagnose and Resolve Oracle DBtime Fluctuations: A Step‑by‑Step Case Study

Background and Monitoring Setup

The environment hosts multiple database platforms, not only Oracle. System‑level monitoring is handled by Zabbix, while Oracle is monitored through the Orabbix extension. Because Oracle does not expose DBtime directly, the author extracts it from AWR reports.

Understanding DBtime

DBtime is a classic DBA metric used to gauge the amount of time the database spends processing work. Oracle does not provide a convenient real‑time view, so the author manually extracts DBtime snapshots from AWR.

First Customisation – Snapshot DBtime

By capturing DBtime at snapshot boundaries (e.g., snapshot 36343‑36344 covering 60 minutes), the author obtains a list of DBtime values that already hint at potential issues.

Second Customisation – v$sys_time_model Correlation

The author correlates v$sys_time_model values with snapshot DBtime to compute a more precise per‑interval DBtime. For example, a snapshot DBtime of 100 seconds at 13:00 and a v$sys_time_model value of 160 seconds at 13:05 yields a load of (160‑100)/(5*60)=20 %.

Observing DBtime Jitter

Two‑day DBtime graphs show clear jitter, with short‑term spikes that can be pinpointed to exact timestamps.

During the spike, parallel processes exceed 100 and active sessions surpass 150, confirmed by SMS alerts.

Four‑Step Diagnosis Process

Check database alert logs – no obvious errors.

Capture an ASH report via a TNS session; top wait events are identified, but the report lacks relevant SQL statements.

Inspect scheduler jobs – several jobs ran during the problematic window.

Analyze the materialized view refresh that was active at the time.

Materialized View Investigation

The offending scheduler refreshed a materialized view whose source table resides in another database via a DB link and contains over 200 million rows. A full‑refresh was suspected, but attempts to add a materialized view log failed with ORA‑12000 (log already exists). Fast‑refresh and default refresh options both performed quickly, suggesting the view itself was not the root cause.

Index Health Check

Further analysis revealed that several partitioned indexes were in an UNUSABLE state, causing the optimizer to perform full table scans despite existing indexes. Rebuilding the affected index partitions restored index usage.

Execution plans before and after the rebuild show a switch from full scans to index scans.

Parallelism Misconfiguration

The table in question had its parallel degree set to DEFAULT via ALTER TABLE TEST_BILLDETAIL PARALLEL. In Oracle, DEFAULT means the degree is derived from available CPU resources, which in this case resulted in degrees of 48 and 96, causing a burst of parallel processes during the spike.

Reducing the parallel degree or setting it explicitly to 1 eliminated the excessive parallelism.

Custom Monitoring Script

Because built‑in AWR/ASH did not capture the necessary details, the author wrote a Bash/Python script that queries v$session and v$process every five seconds for ten hours, storing active session counts, parallel process numbers, and the associated sql_id. The script ran in the background and produced a detailed report the next morning.

Final Outcome

After rebuilding the unusable indexes and correcting the parallel degree, DBtime jitter flattened, and parallel process counts remained within a reasonable range, as shown by the final monitoring graphs.

Lessons Learned

This case demonstrates that fine‑grained, custom metrics can reveal performance issues that standard AWR/ASH miss. Systematic step‑by‑step analysis—checking logs, ASH, scheduler jobs, index health, and parallel settings—combined with targeted scripting, enables DBAs to quickly locate and resolve the root causes of DBtime fluctuations.