Oracle performance work is clearer when it begins with DB time: the time spent by foreground sessions in database calls, including CPU and non-idle waits. Host CPU is still important, but a database can be slow with moderate CPU because sessions are waiting for I/O, locks, commits or concurrency resources. DB time gives a better starting point for where users experienced delay.
Compare DB time with elapsed time and CPU
A rise in DB time shows more database work or waiting per interval. Compare it with database CPU, operating-system CPU, active sessions, logical reads, physical reads and transaction rate. If DB time grows much faster than CPU, waits may dominate. If CPU grows with DB time and throughput, identify the SQL responsible before assuming the server needs more cores.
Rank the workload, then validate the plan
Rank SQL by elapsed time, CPU time, executions and reads. A high-total statement may be called frequently; a high-per-execution statement may be a single report. Inspect plan shape, cardinality assumptions, bind behaviour and changed object statistics. Tuning without a representative plan risks replacing one expensive path with another.
- Use workload changes to identify the first interval that diverged.
- Separate foreground response work from background maintenance.
- Check whether CPU is database demand, host contention or inefficient SQL.
- Measure a change against DB time and user response, not CPU alone.
More CPU may be appropriate after the SQL and concurrency evidence supports it. The performance model keeps the decision connected to user-facing time rather than a single utilisation number.
Calculate a meaningful workload interval
Capture DB time and DB CPU from V$SYS_TIME_MODEL at two points, noting the instance and startup boundary. The values are cumulative microseconds. Divide the change in DB time by elapsed wall-clock time in the same units to estimate average active sessions over the interval. This is workload time, not a host CPU percentage, and concurrent sessions can accumulate more database time than wall-clock time.
SELECT stat_name, value
FROM v$sys_time_model
WHERE stat_name IN ('DB time', 'DB CPU');
For example, 240 seconds of DB time in a 60-second interval corresponds to four average active sessions. That alone does not tell you how many CPUs are busy. Compare the CPU component with non-idle waits and host demand before calling the instance CPU-bound. Keep foreground database activity separate from background and other host processes.
Example: a popular query becomes more expensive
A customer lookup doubles its buffer gets per execution after a release while its call rate stays stable. Total CPU rises and application latency follows. That makes the lookup a stronger candidate than an unrelated report with a large lifetime elapsed-time total. Compare interval executions, CPU, buffer gets and plan hash values for the relevant SQL and child cursors.
V$SQL statistics belong to cursors and can disappear when aged out. Different child cursors can have different plans. An instance restart or cursor replacement makes a naive before-and-after subtraction invalid. Preserve the observation times, SQL identifier, child identity and plan with the incident record.
Verify both efficiency and concurrency
Test representative bind values, including skewed customers and date ranges. A faster small lookup can coexist with a much slower large-account path. Record CPU or logical work per completed call together with the number of calls per business operation. A reduction in aggregate CPU caused by a drop in successful transactions is not proof of tuning success.
Use the diagnostics covered by the environment's licensing and configuration. AWR, ASH and additional tuning facilities have entitlement requirements; access to a view or a generated report does not establish permission to use a feature. The initial query and interval approach should be documented separately from optional licensed historical analysis.
Keep the evidence for the next incident
Mini DBA Oracle monitoring combines query and metric history with sessions, waits and resource diagnostics for incident investigation. Available evidence depends on database permissions and collection configuration; operators must confirm diagnostic entitlements for their environment.
References and further reading