Benchmarking, Baselines, Capacity Planning, Load Profiles, and Performance Regression Testing

Learning outcomes

A schema change “improves” one developer's query from 800 ms to 500 ms, so it is promoted. Under production concurrency the release is slower because CPU saturates earlier, hard parses rise and tail latency doubles. A performance claim needs a reproducible benchmark: recorded software/data/hardware limits, workload mix/concurrency/think time, cache/warmup state, statistics, throughput, latency distribution and saturation evidence.

1. A benchmark is a controlled experiment

At minimum, record data volume/distribution, concurrency, workload mix, think time, cache/warmup state, bind values, optimizer statistics timestamp, Oracle version/RU, COMPATIBLE, memory/CPU limits, storage, PDB/service, client/driver version and application build. Without those, “before” and “after” may be different experiments.

2. Environment fingerprint

SELECT banner_fullFROM v$versionWHERE banner_full LIKE 'Oracle%';SELECT name,open_mode,database_role,platform_nameFROM v$database;SELECT instance_name,host_name,startup_time,statusFROM v$instance;SELECT name,valueFROM v$parameterWHERE name IN (  'compatible',  'statistics_level',  'optimizer_features_enable',  'memory_target',  'sga_target',  'pga_aggregate_target',  'cpu_count',  'parallel_degree_policy')ORDER BY name;

SELECT  table_name,  num_rows,  blocks,  last_analyzedFROM user_tablesWHERE table_name IN (  'WORK_ORDERS',  'TECHNICIANS',  'SERVICE_REGIONS')ORDER BY table_name;

Do not change OPTIMIZER_FEATURES_ENABLE merely to make a benchmark stable. Record it; use supported plan-management mechanisms if release stability is needed.

3. Create a disposable benchmark table and run log

BEGIN  EXECUTE IMMEDIATE 'DROP TABLE sh23_bench_work PURGE';EXCEPTION WHEN OTHERS THEN  IF SQLCODE != -942 THEN RAISE; END IF;END;/BEGIN  EXECUTE IMMEDIATE 'DROP TABLE sh23_bench_runs PURGE';EXCEPTION WHEN OTHERS THEN  IF SQLCODE != -942 THEN RAISE; END IF;END;/CREATE TABLE sh23_bench_work ASSELECT  LEVEL AS event_id,  MOD(LEVEL,10000) AS technician_id,  MOD(LEVEL,6) AS status_bucket,  MOD(LEVEL*37,100000)/10 AS amount,  RPAD('benchmark',100,'x') AS payloadFROM dualCONNECT BY LEVEL <= 150000;CREATE TABLE sh23_bench_runs (  run_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  variant_name VARCHAR2(30) NOT NULL,  started_at TIMESTAMP WITH TIME ZONE NOT NULL,  ended_at TIMESTAMP WITH TIME ZONE NOT NULL,  iterations NUMBER NOT NULL,  elapsed_seconds NUMBER NOT NULL,  logical_reads_delta NUMBER,  physical_reads_delta NUMBER,  cpu_centisecs_delta NUMBER,  result_checksum NUMBER,  notes VARCHAR2(500));BEGIN  DBMS_STATS.GATHER_TABLE_STATS(USER,'SH23_BENCH_WORK');END;/

4. Benchmark harness records actual measurements

DECLARE  l_start_cs       NUMBER;  l_end_cs         NUMBER;  l_start_logical  NUMBER;  l_end_logical    NUMBER;  l_start_physical NUMBER;  l_end_physical   NUMBER;  l_start_cpu      NUMBER;  l_end_cpu        NUMBER;  l_sum            NUMBER := 0;  l_v              NUMBER;  l_iterations     CONSTANT PLS_INTEGER := 300;BEGIN  DBMS_APPLICATION_INFO.SET_MODULE('SH23_BENCH','BASELINE');  SELECT MAX(CASE WHEN n.name='session logical reads' THEN s.value END),         MAX(CASE WHEN n.name='physical reads' THEN s.value END),         MAX(CASE WHEN n.name='CPU used by this session' THEN s.value END)  INTO l_start_logical,l_start_physical,l_start_cpu  FROM v$mystat s JOIN v$statname n    ON n.statistic#=s.statistic#  WHERE n.name IN (    'session logical reads','physical reads','CPU used by this session'  );  l_start_cs := DBMS_UTILITY.GET_TIME;  FOR i IN 1..l_iterations LOOP    SELECT SUM(amount)    INTO l_v    FROM sh23_bench_work    WHERE technician_id=MOD(i*37,10000);    l_sum := l_sum + NVL(l_v,0);  END LOOP;  l_end_cs := DBMS_UTILITY.GET_TIME;  SELECT MAX(CASE WHEN n.name='session logical reads' THEN s.value END),         MAX(CASE WHEN n.name='physical reads' THEN s.value END),         MAX(CASE WHEN n.name='CPU used by this session' THEN s.value END)  INTO l_end_logical,l_end_physical,l_end_cpu  FROM v$mystat s JOIN v$statname n    ON n.statistic#=s.statistic#  WHERE n.name IN (    'session logical reads','physical reads','CPU used by this session'  );  INSERT INTO sh23_bench_runs(    variant_name,started_at,ended_at,iterations,elapsed_seconds,    logical_reads_delta,physical_reads_delta,cpu_centisecs_delta,    result_checksum,notes  ) VALUES(    'BASELINE',    SYSTIMESTAMP-NUMTODSINTERVAL((l_end_cs-l_start_cs)/100,'SECOND'),    SYSTIMESTAMP,    l_iterations,    (l_end_cs-l_start_cs)/100,    l_end_logical-l_start_logical,    l_end_physical-l_start_physical,    l_end_cpu-l_start_cpu,    l_sum,    'Serial single-session microbenchmark; record host/cache state separately'  );  COMMIT;END;/

The harness records the values Oracle actually produced. It deliberately does not print a made-up expected latency. DBMS_UTILITY.GET_TIME is suitable for coarse elapsed comparison in this lab; production load tests should also capture per-request latency distribution from the application/load generator.

5. Create one controlled change

CREATE INDEX sh23_bench_tech_ixON sh23_bench_work(technician_id);BEGIN  DBMS_STATS.GATHER_TABLE_STATS(    USER,'SH23_BENCH_WORK',cascade=>TRUE  );END;/

Rerun the same PL/SQL harness with variant_name='INDEXED' and module action INDEXED. Keep iterations/data/binds/session mode fixed. If the checksum differs, the experiment is invalid regardless of speed.

6. Compare measured outcomes

SELECT  run_id,  variant_name,  iterations,  elapsed_seconds,  ROUND(iterations/NULLIF(elapsed_seconds,0),2) AS operations_per_second,  logical_reads_delta,  physical_reads_delta,  cpu_centisecs_delta,  result_checksum,  started_at,  ended_atFROM sh23_bench_runsORDER BY run_id;

A lower single-session elapsed time is evidence only for this microbenchmark. Before promotion, test realistic concurrent sessions and application think time. If CPU is already saturated, a change that uses more CPU per request can improve one-user latency yet reduce total throughput.

7. Latency, throughput and saturation are separate axes

8. Build a bounded load profile from system deltas

A load profile describes how much useful/database work occurred during the benchmark window. Capture the same counters immediately before and after the steady interval, then divide deltas by elapsed seconds or transactions as appropriate. Never compare per-second rates from windows with different workload phases without noting the difference.

SELECT name,valueFROM v$sysstatWHERE name IN (  'user calls',  'user commits',  'execute count',  'session logical reads',  'physical reads',  'redo size')ORDER BY name;

Store T1/T2 values with the benchmark run. A change that improves latency by reducing work should usually be visible in one or more per-call/per-operation resource deltas; if not, investigate whether cache/background/concurrency effects explain the result.

9. Service-level database telemetry

SELECT  service_name,  begin_time,  end_time,  callspersec,  dbtimepercall,  cpupercallFROM v$servicemetricORDER BY end_time DESC,service_name;

Service metrics connect database time/CPU to the workload endpoint. They still do not replace client-side latency because network/middle-tier queues and think time lie outside the database.

10. RU 23.26.3 client request histogram awareness

RU 23.26.3 adds V$CLIENT_REQUEST_HISTOGRAM for service-level client request response-time distribution when compatible current Oracle drivers provide the telemetry. Use it to bridge database and client-request latency, but verify driver support and do not assume older clients populate it.

SELECT  s.name AS service_name,  h.bucket01_count,  h.bucket02_count,  h.bucket03_count,  h.bucket04_count,  h.bucket05_count,  h.bucket06_count,  h.bucket07_count,  h.bucket08_count,  h.bucket09_count,  h.bucket10_count,  h.bucket11_count,  h.bucket12_count,  h.bucket13_count,  h.bucket14_count,  h.con_idFROM v$client_request_histogram hJOIN v$services s  ON s.service_id=h.service_id AND s.name_hash=h.service_name_hashORDER BY s.name;

11. Cache state and warm-up must be explicit

Do not flush the shared pool/buffer cache on a shared production database merely to make a “cold cache” benchmark. Flushing can damage every workload and creates a synthetic state. For controlled test systems, define whether runs are cold/warm and how the state is established. Production regression tests should usually model the real warm steady state plus startup/recovery scenarios separately.

12. Capacity testing needs concurrency and steady state

A capacity test ramps users/workers, reaches a steady interval, then observes where throughput stops scaling and latency rises. Record connection pool size, worker concurrency, think time, request mix and hardware/container limits. Free's 2 CPU/2 GB ceiling is useful for learning saturation shape, not for extrapolating enterprise capacity linearly.

13. Regression gate and rollback

• Correctness: result/checksum/business invariants unchanged.
• Latency: agreed percentile thresholds under representative load.
• Throughput: meets target at target concurrency.
• Resources: CPU/I/O/PGA/TEMP/locks stay within approved headroom.
• Plan/cardinality: expected runtime plan and actual rows stable/understood.
• Operations: backup/maintenance/recovery windows not materially harmed.
• Rollback: index/schema/config/application rollback rehearsed before release.

14. Cleanup

DROP INDEX sh23_bench_tech_ix;DROP TABLE sh23_bench_runs PURGE;DROP TABLE sh23_bench_work PURGE;BEGIN  DBMS_APPLICATION_INFO.SET_MODULE(NULL,NULL);END;/

15. Production judgment and chapter close

Performance engineering is a loop: define SLO/workload → capture a baseline → identify the dominant mechanism → make one controlled change → repeat the same workload → compare latency/throughput/saturation/correctness → keep or roll back. Memory, I/O, contention and parallelism interact, so isolated folklore can simply move the bottleneck.

Current baseline remains 26ai RU 23.26.3, SQL Developer 26.2 and SQLcl 26.2.1. Free's hard 2-CPU/2-GB/12-GB resource envelope must be recorded with every result. Parallel query/DML is unavailable in Free; no chapter benchmark relies on it. V$CLIENT_REQUEST_HISTOGRAM is a 23.26.3-era capability requiring compatible current client telemetry. No COMPATIBLE change, hidden parameter or management pack is required for the mandatory benchmark.

Authoritative references

• Performance Tuning Guide — measurement/baseline methodology
• V$SERVICEMETRIC — service DB time/CPU/call metrics
• V$SESSMETRIC — session resource metrics
• July 2026 RU 23.26.3 New Features — client-request histogram feature
• Licensing Information — parallel/edition feature boundaries