Performance diagnosis and evidence | Oracle administration: recovery, performance, and production

Concept and mechanism

Diagnosis starts with the operation that regressed, the affected window, and a comparable baseline. DB time aggregates CPU and non-idle waits of foreground sessions and can exceed wall-clock duration under concurrency. It is not the complete browser-to-user request duration. Compare volume, throughput, times, and wait classes before attributing cause. AWR, ASH, and SQL Monitoring can provide detailed evidence, but use depends on the offering and applicable packs. A visible button or enabled parameter does not grant entitlement. Teams should know which mechanisms are authorized for their environment, including direct SQL access. Business latency and database workload should be reported as related but distinct measurements.

Guided application

In a fictional report, a query estimates few rows but processes millions. Examine predicates, statistics, and data distribution using runtime observations when available. An unchanged plan hash does not guarantee unchanged duration: binds, volume, resources, and concurrency can change. Value binds support cursor reuse but do not replace plan evaluation for different distributions. When work spills to TEMP, assess work memory and total consumption before increasing limits. For long queries, RETENTION GUARANTEE can preserve unexpired undo at the cost of DML failures when space runs out. Each mitigation should record expected benefit, transferred risk, and the measurement that will establish improvement rather than relying on a single green indicator.

IN PRACTICE

Ten wall-clock minutes and thirty DB-time minutes represent three average active sessions over that window.

Common pitfalls

Average CPU as complete diagnosis; cost as time; same plan as same performance; memory without aggregate limits.

Related topics: Containers, services, and resources · PDB lifecycle and isolation · Backup and recovery evidence

Take this idea with you

Compare equivalent work and interpret metrics within their scope.

Create account

Reference: Time model and wait events · 1Z0-183 public objectives inspected 2026-09-30; revision date not published