Simulated environment. All schemas, metrics, and run histories are synthetic; no live Oracle instance, credentials, or customer data are used.

Oracle Secure Data Engineering Lab

Tuning & diagnostics

Performance analysis

How I approach a slow workload: quantify where database time goes, read the plan rather than guess, change one thing, and measure again. The statements below are fabricated case studies written to mirror problems I have actually solved.

Wait-class profile
Share of simulated database time.
DB CPU42%
User I/O27%
Log file sync13%
Concurrency9%
Cluster5%
Other4%
Throughput by hour
Thousands of rows loaded, with p95 batch latency.
210K00
168K03
262K06
448K09
512K12
496K15
388K18
274K21

Peak window 12:00–15:00 UTC drives the tuning priorities below.

Tuning case studies

Monthly billing aggregate
sql_id 7fk2xq9mzab41
96 executions · 8,940,112 buffer gets · 15,980ms CPU

Execution plan (before)

SELECT STATEMENT
  HASH GROUP BY
    HASH JOIN
      TABLE ACCESS FULL DIM_CUSTOMER
      PARTITION RANGE ALL
        TABLE ACCESS FULL FACT_BILLING

Finding

Predicate on BILL_MONTH was wrapped in TRUNC(), disabling partition pruning across 28 partitions.

Remediation

Rewrote the predicate as a half-open range so the optimiser prunes to a single partition; added a local index on CUSTOMER_SK.

Before

18.42s

After

1.24s

Speedup

14.9x

Customer SCD2 merge
sql_id b3qq7t1ldd902
24 executions · 2,210,455 buffer gets · 4,120ms CPU

Execution plan (before)

MERGE STATEMENT
  MERGE DIM_CUSTOMER
    VIEW
      NESTED LOOPS OUTER
        TABLE ACCESS FULL STG_CUSTOMER
        INDEX RANGE SCAN UX_DIM_CUSTOMER_CURRENT

Finding

Row-by-row nested loops on a 60k-row staging set; stale statistics on the staging table.

Remediation

Gathered stats after load and hinted a hash outer join, converting the merge to a set-based operation.

Before

9.31s

After

1.87s

Speedup

5x

Telemetry hourly upsert
sql_id c9zw04prv6t18
288 executions · 640,220 buffer gets · 2,510ms CPU

Execution plan (before)

MERGE STATEMENT
  MERGE AGG_DEVICE_HOURLY
    HASH JOIN OUTER
      TABLE ACCESS FULL STG_TELEMETRY
      PARTITION RANGE SINGLE
        INDEX UNIQUE SCAN PK_AGG_DEVICE_HOURLY

Finding

Acceptable, but commit frequency per micro-batch caused log file sync waits.

Remediation

Single commit per micro-batch and larger array size reduced redo waits.

Before

3.08s

After

1.42s

Speedup

2.2x