Tune SQL, Optimizer Statistics, Memory, I/O, Parallelism, and Contention from Diagnostic Evidence

Learning outcomes

An operations team sees CPU pressure and immediately increases memory, enables parallel execution, and rebuilds indexes. The real problem is a selective tenant/time query doing excessive work because the matching composite index is missing, plus a long transaction blocking an interactive request. Performance engineering begins with a reproducible workload and measured row-source/wait/resource evidence.

1. Grow a reproducible workload table

INSERT /*+ APPEND */ INTO servicehub_owner.sh29_work_orders(  customer_id,tenant_code,status_code,priority_code,  description,amount,created_at,updated_at)SELECT  CASE WHEN MOD(LEVEL,2)=0 THEN 1 ELSE 2 END,  CASE WHEN MOD(LEVEL,2)=0 THEN 'TENANT_A' ELSE 'TENANT_B' END,  CASE WHEN MOD(LEVEL,20)=0 THEN 'HOLD'       WHEN MOD(LEVEL,5)=0 THEN 'CLOSED'       ELSE 'OPEN' END,  CASE WHEN MOD(LEVEL,17)=0 THEN 'HIGH' ELSE 'NORMAL' END,  'Synthetic capstone work order '||LEVEL,  MOD(LEVEL*19,5000)/10,  TIMESTAMP '2026-01-01 00:00:00'    + NUMTODSINTERVAL(MOD(LEVEL,220000),'SECOND'),  SYSTIMESTAMPFROM dualCONNECT BY LEVEL <= 50000;COMMIT;BEGIN  DBMS_STATS.GATHER_TABLE_STATS(    ownname => 'SERVICEHUB_OWNER',    tabname => 'SH29_WORK_ORDERS',    cascade => TRUE,    method_opt => 'FOR ALL COLUMNS SIZE AUTO'  );END;/

Fifty thousand small rows fit comfortably inside the Free learning cap and are enough to make access paths observable. They are not an enterprise benchmark.

2. Create a timing/evidence table

BEGIN EXECUTE IMMEDIATE  'DROP TABLE servicehub_owner.sh29_perf_run PURGE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;/CREATE TABLE servicehub_owner.sh29_perf_run (  run_name VARCHAR2(50) PRIMARY KEY,  iterations NUMBER NOT NULL,  elapsed_centiseconds NUMBER NOT NULL,  measured_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,  note_text VARCHAR2(500));

3. Establish the baseline with the useful index temporarily removed

Lesson 2 created the desired composite index. To create an educational before/after comparison, drop it in this disposable capstone and recreate it afterward.

DROP INDEX servicehub_owner.sh29_wo_tenant_created_ix;BEGIN  DBMS_STATS.GATHER_TABLE_STATS(    'SERVICEHUB_OWNER','SH29_WORK_ORDERS',    cascade=>TRUE  );END;/

DECLARE  l_start PLS_INTEGER;  l_count NUMBER;  l_iterations CONSTANT PLS_INTEGER := 100;BEGIN  l_start := DBMS_UTILITY.GET_TIME;  FOR i IN 1..l_iterations LOOP    SELECT COUNT(*)    INTO l_count    FROM servicehub_owner.sh29_work_orders    WHERE tenant_code='TENANT_A'      AND created_at >= TIMESTAMP '2026-01-03 00:00:00'      AND created_at <  TIMESTAMP '2026-01-03 00:10:00';  END LOOP;  INSERT INTO servicehub_owner.sh29_perf_run(    run_name,iterations,elapsed_centiseconds,note_text  ) VALUES(    'BEFORE_INDEX',l_iterations,    DBMS_UTILITY.GET_TIME-l_start,    'Same selective tenant/time count executed repeatedly'  );  COMMIT;END;/

Elapsed centiseconds are local-lab evidence only. Repeat runs, warm/cold cache, concurrent load and OS scheduling change them; the runtime plan and logical/physical work explain why.

4. Capture actual row-source plan

SELECT /*+ gather_plan_statistics */       COUNT(*)FROM servicehub_owner.sh29_work_ordersWHERE tenant_code='TENANT_A'  AND created_at >= TIMESTAMP '2026-01-03 00:00:00'  AND created_at <  TIMESTAMP '2026-01-03 00:10:00';SELECT *FROM TABLE(  DBMS_XPLAN.DISPLAY_CURSOR(    NULL,NULL,'ALLSTATS LAST +PREDICATE +NOTE'  ));

Look at the chosen access path, estimated rows (E-Rows), actual rows (A-Rows), starts and buffers. If the optimizer still chooses a full scan in this small synthetic table, that is valid evidence; do not force an index merely to make the tutorial screenshot look different.

5. Repair the access path and re-measure

CREATE INDEX servicehub_owner.sh29_wo_tenant_created_ixON servicehub_owner.sh29_work_orders(  tenant_code,created_at,work_order_id);BEGIN  DBMS_STATS.GATHER_TABLE_STATS(    'SERVICEHUB_OWNER','SH29_WORK_ORDERS',    cascade=>TRUE  );END;/

DECLARE  l_start PLS_INTEGER;  l_count NUMBER;  l_iterations CONSTANT PLS_INTEGER := 100;BEGIN  l_start := DBMS_UTILITY.GET_TIME;  FOR i IN 1..l_iterations LOOP    SELECT COUNT(*)    INTO l_count    FROM servicehub_owner.sh29_work_orders    WHERE tenant_code='TENANT_A'      AND created_at >= TIMESTAMP '2026-01-03 00:00:00'      AND created_at <  TIMESTAMP '2026-01-03 00:10:00';  END LOOP;  INSERT INTO servicehub_owner.sh29_perf_run(    run_name,iterations,elapsed_centiseconds,note_text  ) VALUES(    'AFTER_INDEX',l_iterations,    DBMS_UTILITY.GET_TIME-l_start,    'Same query after composite index and fresh stats'  );  COMMIT;END;/SELECT  run_name,  iterations,  elapsed_centiseconds,  ROUND(elapsed_centiseconds/iterations,3)    AS centiseconds_per_executionFROM servicehub_owner.sh29_perf_runORDER BY measured_at;

The measured delta belongs only to this environment. The correct conclusion is whether the access path reduced row-source work and met the SLO under representative concurrency—not a universal percentage improvement.

6. Re-capture the runtime plan after the change

SELECT /*+ gather_plan_statistics */       COUNT(*)FROM servicehub_owner.sh29_work_ordersWHERE tenant_code='TENANT_A'  AND created_at >= TIMESTAMP '2026-01-03 00:00:00'  AND created_at <  TIMESTAMP '2026-01-03 00:10:00';SELECT *FROM TABLE(  DBMS_XPLAN.DISPLAY_CURSOR(    NULL,NULL,'ALLSTATS LAST +PREDICATE +NOTE'  ));

If an index range scan is chosen, compare actual buffers/rows with baseline. If not, inspect selectivity/statistics/table size before using hints. A cost-based optimizer is allowed to choose a full scan when it is cheaper.

7. Use time model and waits to identify where database time goes

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;

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;

Cumulative instance totals need a before/after interval or monitoring time series. A high historical wait total does not prove the current incident cause.

8. SQL-level evidence complements the current cursor plan

SELECT  sql_id,  plan_hash_value,  executions,  rows_processed,  buffer_gets,  disk_reads,  cpu_time,  elapsed_timeFROM v$sqlstatsWHERE sql_text LIKE  'SELECT /*+ gather_plan_statistics */%SH29_WORK_ORDERS%'ORDER BY last_active_time DESCFETCH FIRST 20 ROWS ONLY;

Normalize per-execution/row only when the executions represent comparable work. One SQL_ID can also have multiple child cursors/plans; Chapter 10's cursor concepts still apply.

9. Memory: inspect pressure before resizing

SELECT name,bytes,resizeableFROM v$sgainfoORDER BY name;SELECT name,value,unitFROM v$pgastatORDER BY name;SELECT name,valueFROM v$parameterWHERE name IN (  'memory_target','sga_target',  'pga_aggregate_target',  'pga_aggregate_limit',  'vector_memory_size')ORDER BY name;

Free's 2-GB RAM cap makes “increase memory” especially constrained. PGA over-allocation, sorts/spills, vector-pool demand and SGA working-set misses have different mechanisms; do not tune one aggregate hit ratio.

10. I/O and temporary work are workload evidence

SELECT  file#,  phyrds,  phywrts,  readtim,  writetimFROM v$filestatORDER BY file#;SELECT  tablespace_name,  used_blocks,  free_blocksFROM v$temp_space_header;

Database file counters do not diagnose storage latency alone. Correlate with OS/device/cloud-storage latency/queue evidence and the SQL doing the reads/writes.

11. Deliberate concurrency failure: a long transaction blocks interactive work

UPDATE servicehub_owner.sh29_work_ordersSET amount=amount+1WHERE work_order_id=1;-- Keep this transaction open for the exercise.

SELECT work_order_id,status_codeFROM servicehub_owner.sh29_work_ordersWHERE work_order_id=1FOR UPDATE WAIT 2;-- Expected after the bounded wait:-- ORA-30006: resource busy; acquire with WAIT timeout expired

SELECT  sid,serial#,username,status,  event,blocking_session,sql_idFROM v$sessionWHERE blocking_session IS NOT NULL   OR sid IN (      SELECT blocking_session      FROM v$session      WHERE blocking_session IS NOT NULL   )ORDER BY sid;

The problem is transaction/lock ownership, not missing SGA or a need for parallelism.

12. Repair contention by shortening/ordering the transaction

COMMIT;

SELECT work_order_id,status_codeFROM servicehub_owner.sh29_work_ordersWHERE work_order_id=1FOR UPDATE WAIT 2;ROLLBACK;

Production applications should keep transactions short, lock rows in consistent order where multiple rows are involved, and use bounded retry/idempotency from Chapter 27. Do not hide systemic contention with infinite retry loops.

13. Parallelism is not a generic speed switch

Parallel execution can improve large scans/aggregations on suitable systems but consumes more workers/CPU/memory and can harm OLTP. Free has only two processing cores; forcing parallel query cannot create additional CPU capacity. Inspect the plan and workload class before using PARALLEL hints/table attributes.

SELECT name,valueFROM v$parameterWHERE name IN (  'parallel_degree_policy',  'parallel_max_servers',  'parallel_min_servers')ORDER BY name;

14. AWR/ASH/ADDM and Tuning Pack licensing is offering-specific

Current 26ai licensing lists Diagnostics Pack and Tuning Pack as available in Free, while on EE/EE-ES they are extra-cost packs (Tuning also requires Diagnostics). That means a Free learner can study Automatic Workload Repository (AWR), Active Session History (ASH), Automatic Database Diagnostic Monitor (ADDM) and SQL tuning tooling without assuming the same entitlement on commercial EE. This mandatory lab uses V$SQLSTATS, V$SYS_TIME_MODEL, waits and DBMS_XPLAN so the diagnostic method remains valid even where packs are disabled/unlicensed.

SHOW PARAMETER control_management_pack_access

15. Production judgment

Performance tuning is a loop: define the workload/SLO → capture interval evidence → identify the dominant SQL/wait/resource mechanism → change one causal element → rerun the same workload → compare plans/work and tail latency → retain or roll back. Never tune from one cache ratio or invented benchmark percentage.

No restart or hidden parameter is used. The composite index is restored at lesson end. Lesson 4 now protects the tuned system with backups, recovery evidence, DR design, auditing, monitoring and maintenance runbooks.

Authoritative references

• SQL Tuning Guide — Execution Plans — DBMS_XPLAN runtime plans
• Oracle AI Database Reference — V$SQLSTATS/time model/waits/resource views
• Optimizer Statistics Concepts — statistics collection and cardinality
• Database Performance Tuning Guide — wait/time/resource diagnosis
• Licensing Information — Diagnostics/Tuning Pack and Resource Manager offering rules