The Scenario
The application team reports: *'The customer search page takes 10 seconds, and sometimes the whole page just hangs.'* You will find both problems.
Part 1: Build the Test Data
Part 2: Reproduce the Slow Query
The plan shows TABLE ACCESS FULL on BIG_ORDERS and reads 1,000,000 rows to return about 50. That is the problem.
Part 3: Compare the Work Done
Part 4: Fix It
The plan now uses INDEX RANGE SCAN. Buffer gets drop from thousands of blocks to a few dozen, and the query finishes in milliseconds.
| Before | After | |
|---|---|---|
| Access path | TABLE ACCESS FULL | INDEX RANGE SCAN |
| Rows read | 1,000,000 | About 50 |
| Buffer gets | Thousands | Few dozen |
Part 5: The Hanging Page (Blocking Session)
- Session A (a forgotten, uncommitted change):
UPDATE big_orders SET note = 'LOCKED' WHERE order_id = 1; - Session B (the application):
UPDATE big_orders SET note = 'X' WHERE order_id = 1;It hangs.
Now be the DBA in Session C:
The blocker is INACTIVE: someone updated a row and did not commit. Ask the owner to COMMIT or ROLLBACK. If you cannot reach them and the business impact is high:
Part 6: Write the Findings
| Item | Notes |
|---|---|
| Problem 1 | Missing index on customer_id caused a full scan of 1M rows |
| Fix 1 | Created idx_big_orders_cust, gathered statistics |
| Problem 2 | Uncommitted update held a row lock |
| Fix 2 | Session owner committed; advised the team to commit promptly |
| Prevention | Add monitoring for blockers over 60 seconds |
Part 7: Clean Up
Conclusion
You found a full table scan from the execution plan, fixed it with an index and fresh statistics, then traced a hanging page to a forgotten commit. This is the heart of daily DBA troubleshooting.

