A cumulative counter contains earlier activity as well as activity during a slowdown. Capture the same counters at the beginning and end of a relevant window, validate that the captures are comparable, then subtract. This gives evidence about the work accumulated during that interval.
Oracle DBA Lesson 98 Part 2 — Measure waits and service work over an interval
Part 1 identified a session waiting now and its valid blocker. This part measures interval changes and relates service workload to that current-session evidence.
Establish the interval and scope
On an Oracle Database 19c single-instance database, retain the database identity, instance name and number, container context, capture timestamps and startup time. At each capture, record:
SELECT systimestamp AS capture_time,
instance_number, instance_name, startup_time
FROM v$instance;
SELECT sys_context('USERENV','DB_UNIQUE_NAME') AS db_unique_name,
sys_context('USERENV','CON_NAME') AS container_name,
sys_context('USERENV','CON_ID') AS container_id
FROM dual;Keep the same instance and intended container scope for both captures. A changed startup time invalidates simple subtraction across the restart. A known counter reset, service recreation or collection change also calls for a new baseline. A nonnegative difference by itself does not prove continuity; a reset counter might have grown past its old value before the second capture.
Record the start and finish of each collection because separate queries do not form an atomic snapshot. Dynamic performance views change while being queried. Use the actual measured elapsed interval; a nominal one-minute pause does not make two collection timestamps exactly 60 seconds apart.
In a CDB root, V$SYSTEM_EVENT returns instance-wide rows with CON_ID=0. That total includes multiple application services and foreground/background activity. Service rows have their own CON_ID; preserve it rather than treating a root event total as a PDB or service-specific total. RAC requires per-instance identity and suitable GV$/INST_ID handling. These queries are for one instance.
See V$INSTANCE and dynamic performance views.
Capture the relevant event set
SELECT event_id, event, wait_class, con_id,
total_waits, time_waited_micro
FROM v$system_event
WHERE wait_class <> 'Idle'
ORDER BY event_id, con_id;TOTAL_WAITS counts waits. TIME_WAITED_MICRO accumulates wait time in microseconds; TIME_WAITED instead uses hundredths of a second. Keep the exact column and units with each value. Wait counts describe Oracle wait accounting, not a count of user transactions or requests.
Save all relevant non-idle event rows at each point, then join matching event/container keys and rank the differences. A top-ten lifetime list at each endpoint can omit an event whose interval increase matters. Keep missing or discontinuous rows explicitly incomparable rather than silently replacing them with zero. A zero-valued baseline is valid only when its existence and collection continuity are established.
The unqualified total columns include foreground and background waits. If investigating foreground activity specifically, Oracle provides TOTAL_WAITS_FG and TIME_WAITED_MICRO_FG; use the same corresponding pair at both captures and label that scope. Do not combine one foreground counter with an all-process counter.
See V$SYSTEM_EVENT.
Work the subtraction and denominator
For valid, matching captures of the same event and container that are exactly 60 seconds apart:
| Same event and container | Start | End | Difference |
|---|---|---|---|
TIME_WAITED_MICRO | 88,000,000 | 100,000,000 | 12,000,000 microseconds |
TOTAL_WAITS | 40 | 100 | 60 waits |
- Interval accumulated wait time:
12,000,000 / 1,000,000 = 12 seconds. - Average per counted wait, where the count difference is positive:
12 / 60 = 0.2 seconds = 200 milliseconds. - Average accumulated wait time per elapsed second:
12 / 60 = 0.2 wait-seconds per clock-second.
The last two calculations happen to have the same numeric result here, but their denominators and meanings differ. Average wait uses the number of additional waits. A workload rate uses the measured elapsed time. For a zero wait-count difference, leave the average undefined and examine collection/accounting boundaries; do not divide by zero. A mean alone does not describe the slowest wait or a response-time percentile.
Accumulated times can overlap across sessions, so a total wait-time-to-elapsed-time ratio is not automatically a percentage of one request's response time.
See measuring database performance.
Attribute work to the application service
For service APP_SERVICE with CON_ID=3:
SELECT service_name, stat_name, value, con_id
FROM v$service_stats
WHERE service_name = 'APP_SERVICE'
AND con_id = 3
AND stat_name IN ('DB time','DB CPU')
ORDER BY stat_name;Replace the service and container with the actual intended scope. Preserve service/statistic/container keys and instance continuity across captures. STAT_NAME values are case-sensitive data strings; inspect available names when expected rows are absent. The view derives names from session statistics and time-model statistics. Timing values in this view are cumulative microseconds, while non-timing statistics use their own numeric units. Do not transfer the units of the similarly named V$SYSSTAT statistic to this view.
Oracle documents service aggregation as a condition for timing/work statistics for the whole service. Confirm that the required collection is already configured. Absent statistics are not zero.
In the same 60-second interval:
| Service timing statistic, microseconds | Start | End | Increase in seconds |
|---|---|---|---|
DB time | 200,000,000 | 320,000,000 | 120 |
DB CPU | 80,000,000 | 140,000,000 | 60 |
Database time sums foreground database-call time, including processor use and non-idle waits. DB CPU measures foreground processor time and is already contained in DB time. Adding them would double-count processor time. Time-model statistics are not necessarily exclusive; avoid adding parent and child measures together.
Two foreground sessions each active for 60 seconds can contribute 120 seconds of database time in a 60-second clock interval. Under a consistent service scope, 120 / 60 = 2 describes average active database work across that interval. It is not a per-request latency or a CPU-utilization percentage.
User response time measures the elapsed experience of a request and can include application queues, application processing and network delays. Measure it separately and correlate the timestamps. Published time-model values can lag an ongoing operation by up to five seconds, so short-window boundary differences need interpretation.
See V$SERVICE_STATS and V$SESS_TIME_MODEL.
Use the evidence to choose the next investigation
Rank comparable event deltas by accumulated time, consider the number and average of waits, and correlate with current V$SESSION and statement evidence and the affected service. Reuse Part 1's rule: inspect STATE before treating EVENT as current. A high instance event total alone does not attribute that waiting to APP_SERVICE, establish a storage fault, or demonstrate a user's response-time percentile. Use the actual session, statement, object and host evidence needed for the suspected cause.
This method uses live V$INSTANCE, V$SYSTEM_EVENT and V$SERVICE_STATS evidence. ASH, AWR, ADDM and SQL Monitor are separate features with their own licensing boundaries.
See V$SESSION, instance tuning using performance views, and licensing information.
Recap
Capture the same counters at both ends of a measured window, and subtract only after instance, container, startup and collection continuity are established. Keep each counter's column and units. Divide a TIME_WAITED_MICRO difference by one million to get seconds. Average wait time uses a positive difference in wait count; a rate uses measured elapsed time. Rank the full comparable non-idle set, and do not mix a foreground counter with an all-process counter. Service DB time already contains DB CPU. Concurrent foreground sessions can make database time larger than the clock interval. Correlate the interval with current session, statement, object and host evidence before attributing a wait to one service or to a user response time.
No comments:
Post a Comment