Diagnose a slow or inflated shipment report

Understand the likely causes

• Independent child collections can multiply rows before aggregation. Implicit conversion or a function on a column can change available access paths. Skew and correlation can mislead estimates, while client fetching or network transfer can dominate time outside the database calculation.

Prove the row meaning first

Write “one row per shipment” or the actual required unit beside each query stage. Compare a tiny fixture containing one shipment with three scans and two invoice lines: a raw join creates six combinations. If a shipment charge is summed there, no index can repair the inflated value. Aggregate independent children to the joining key or use EXISTS for a pure presence question. Keep an empty-child shipment in the fixture to expose outer-join filters that remove valid parents.

Inspect types and boundaries

Compare bound parameter types with column types and inspect any implicit conversion in approved plan predicates. A string formatted as a date is not a typed date bind. For a DATE column, the example selects a local operational day using a half-open interval, avoiding formatting the stored column. Timestamp-with-zone data requires separately agreed instant boundaries. Do not rewrite the report until its missing-value and timezone rules are understood.

SELECT shipment_id, charge FROM shipments
WHERE customer_id = :customer_id
 AND booked_at >= DATE '2026-10-01'
 AND booked_at < DATE '2026-10-02'
ORDER BY booked_at, shipment_id;

Identify which plan is being discussed

EXPLAIN PLAN is an estimate in the explain environment and does not execute the report. Existing cursor evidence may reflect the actual bind types and optimizer environment, but actual row statistics exist only when collected through an appropriate authorized path. Label the evidence source before comparing estimated and observed cardinalities. A full scan is not automatically wrong, especially for a wide population. The display example reads an existing plan-table entry and makes no runtime claim.

SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'BASIC +PREDICATE'));

Test one justified hypothesis

If the correct query repeatedly selects one customer and a time range, review a composite index matching that workload. If estimates are wrong, investigate skew, correlated columns and statistics provenance first. Any index or statistics change can affect writes and other plans, so the owner should evaluate representative small and large customers plus write overhead in an independent lab. Check Diagnostics and Tuning Pack entitlements before using controlled facilities such as AWR, ASH or tuning advisors. A visible tool is not proof of permission.

Keep it from recurring

• Keep grain annotations, nullable-child fixtures, typed bind contracts and representative parameter populations with report reviews. Approve indexes from workload evidence and verify both correctness and total operational cost after adoption.