Wait Interface, Time Model Statistics, DB Time, DB CPU, and Bottleneck Classification

Learning outcomes

During one five-minute outage, the largest cumulative wait in the instance is db file sequential read. The team buys faster storage, but the next outage is unchanged because the current problem was actually row-lock contention. Oracle's wait interface and time model turn “slow” into measured interval accounting. The important unit is the same incident window and the same workload volume.

1. Wait events describe where a foreground session cannot continue

A wait event names a reason a session yields/waits, such as a single-block read, log-file sync, row-lock enqueue or client message. A wait class groups events into categories such as User I/O, Commit, Concurrency, Application and Network. Idle waits must be separated from workload delays.

2. DB time is foreground database-call time

DB time is the sum of time foreground sessions spend executing database calls. It roughly consists of DB CPU plus non-idle foreground wait time. Because many sessions run concurrently, 10 sessions each spending one second inside the database can produce roughly 10 DB seconds in one wall-clock second. DB time is therefore a workload-consumption measure, not elapsed clock time.

SELECT stat_name,valueFROM v$sys_time_modelWHERE stat_name IN (  'DB time',  'DB CPU',  'sql execute elapsed time',  'parse time elapsed',  'PL/SQL execution elapsed time')ORDER BY stat_name;

VALUE is in microseconds. Time-model statistics overlap/nest—sql execute elapsed time is part of DB time—so do not sum every row into a total.

3. Build a pack-independent snapshot table

BEGIN  EXECUTE IMMEDIATE 'DROP TABLE sh22_metric_snap PURGE';EXCEPTION WHEN OTHERS THEN  IF SQLCODE != -942 THEN RAISE; END IF;END;/CREATE TABLE sh22_metric_snap (  snap_label   VARCHAR2(8) NOT NULL,  captured_at  TIMESTAMP WITH TIME ZONE NOT NULL,  metric_type  VARCHAR2(12) NOT NULL,  metric_name  VARCHAR2(128) NOT NULL,  metric_value NUMBER NOT NULL);

INSERT INTO sh22_metric_snapSELECT 'T1',SYSTIMESTAMP,'TIME',stat_name,valueFROM v$sys_time_modelWHERE stat_name IN ('DB time','DB CPU');INSERT INTO sh22_metric_snapSELECT 'T1',SYSTIMESTAMP,'WAIT',event,time_waited_microFROM v$system_eventWHERE wait_class <> 'Idle';INSERT INTO sh22_metric_snapSELECT 'T1',SYSTIMESTAMP,'STAT',name,valueFROM v$sysstatWHERE name IN (  'user commits',  'execute count',  'session logical reads',  'physical reads');COMMIT;

4. Generate a small repeatable ServiceHub workload

BEGIN  EXECUTE IMMEDIATE 'DROP TABLE sh22_time_work PURGE';EXCEPTION WHEN OTHERS THEN  IF SQLCODE != -942 THEN RAISE; END IF;END;/CREATE TABLE sh22_time_work ASSELECT  LEVEL AS id,  MOD(LEVEL,1000) AS technician_id,  MOD(LEVEL*17,10000)/10 AS amount,  RPAD('servicehub',80,'x') AS payloadFROM dualCONNECT BY LEVEL <= 100000;CREATE INDEX sh22_time_tech_ixON sh22_time_work(technician_id);BEGIN  DBMS_STATS.GATHER_TABLE_STATS(USER,'SH22_TIME_WORK',cascade=>TRUE);END;/SELECT /*+ gather_plan_statistics */       SUM(amount)FROM sh22_time_workWHERE technician_id BETWEEN 100 AND 700;SELECT *FROM TABLE(  DBMS_XPLAN.DISPLAY_CURSOR(    NULL,NULL,'ALLSTATS LAST +PREDICATE'  ));

The point is to create observable database work, not a performance benchmark. Run the query several times if the interval is otherwise too quiet.

5. Capture T2 and calculate deltas

INSERT INTO sh22_metric_snapSELECT 'T2',SYSTIMESTAMP,'TIME',stat_name,valueFROM v$sys_time_modelWHERE stat_name IN ('DB time','DB CPU');INSERT INTO sh22_metric_snapSELECT 'T2',SYSTIMESTAMP,'WAIT',event,time_waited_microFROM v$system_eventWHERE wait_class <> 'Idle';INSERT INTO sh22_metric_snapSELECT 'T2',SYSTIMESTAMP,'STAT',name,valueFROM v$sysstatWHERE name IN (  'user commits',  'execute count',  'session logical reads',  'physical reads');COMMIT;

SELECT *FROM (  SELECT    b.metric_type,    b.metric_name,    b.metric_value-a.metric_value AS delta_value  FROM sh22_metric_snap a  JOIN sh22_metric_snap b    ON b.metric_type=a.metric_type   AND b.metric_name=a.metric_name  WHERE a.snap_label='T1'    AND b.snap_label='T2'    AND b.metric_value>=a.metric_value  ORDER BY    CASE b.metric_type WHEN 'WAIT' THEN 1 WHEN 'TIME' THEN 2 ELSE 3 END,    delta_value DESC)FETCH FIRST 25 ROWS ONLY;

For TIME/WAIT, divide the microsecond delta by 1,000,000 for seconds. For throughput counters, keep the native count and divide by elapsed seconds only if the two snapshot timestamps define a clean interval.

6. Add a controlled concurrency wait

Reuse Lesson 1's two-session row-lock test for 10–20 seconds, then take another T2. The interval should now include enq: TX - row lock contention. That correlation—known injected blocker plus session evidence plus wait delta—is far stronger than a lifetime wait ranking.

SELECT  wait_class,  event,  total_waits,  time_waited_microFROM v$system_eventWHERE wait_class <> 'Idle'ORDER BY time_waited_micro DESCFETCH FIRST 20 ROWS ONLY;

7. Bottleneck classification is a hypothesis table

A wait class names where time accumulated; it is not automatically the root cause. For example, User I/O can be caused by a bad plan reading too many blocks rather than slow storage.

8. Deliberately wrong: “DB CPU = 70 seconds in a 60-second interval means the clock is wrong”

DB CPU/DB time sum across concurrently active foreground sessions. On a multi-CPU/concurrent workload they can exceed wall elapsed time. The correct comparison is capacity/concurrency-aware: DB CPU seconds per wall second, OS CPU utilization/run queue, and throughput during the same interval.

9. Cleanup

DROP TABLE sh22_metric_snap PURGE;DROP TABLE sh22_time_work PURGE;

10. Production judgment

Always timestamp the interval and capture throughput with waits/time. Compare deltas to a known-good window at similar business load before changing storage, CPU, parameters or plans. Use wait names as navigational evidence, not causal proof. Preserve application/SLO/OS context alongside database counters.

The entire lesson is pack-independent and Free-compatible. No restart or COMPATIBLE change is required. Lesson 3 automates this interval-capture idea through AWR—but only after an explicit Diagnostics Pack entitlement check.

Authoritative references

• Database Time Model — DB time/DB CPU interpretation
• V$SYS_TIME_MODEL — time-model units/statistics
• V$SYSTEM_EVENT — cumulative wait-event statistics
• Classes of Wait Events — wait classes
• Performance Tuning Guide — evidence-driven tuning workflow