Quick answer: Diagnose a slow query using its actual execution plan and runtime statistics. Compare estimated and actual row counts, scan types, join operations and filter selectivity before suggesting indexes or hints.
Why guesses fail
A slow query is not always missing an index. Outdated statistics, skewed data, a large join result or high network transfer can be more important than whether a table is scanned.
Inspect a cursor execution plan
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
NULL, NULL, 'ALLSTATS LAST'
));When permitted, this helps compare actual rows with estimates. The required privileges, plan availability and statistics collection vary by environment.
A systematic tuning sequence
- Capture SQL ID, elapsed time, row counts and wait symptoms.
- Inspect access paths, join order and estimated versus actual rows.
- Check filters, bind values and table/index statistics.
- Measure an isolated change with representative test data.
- Document tradeoffs and validate correctness before deployment.
Practical caution
A full table scan may be efficient for large result sets. Index creation affects write cost and storage, so it should be driven by measured access patterns, not by rule of thumb.