Project 4: Diagnose and Fix a Slow Query and a Blocking Session

Oracle DBA Certification 20 min min read Updated: Nov 16, 2026 Advanced
Project 4: Diagnose and Fix a Slow Query and a Blocking Session
Advanced Topic 4 of 4

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

sql CONNECT shop/ShopPwd1@localhost:1521/FREEPDB1 CREATE TABLE big_orders AS SELECT level AS order_id, MOD(level, 20000) AS customer_id, SYSDATE - MOD(level, 365) AS order_date, DBMS_RANDOM.STRING('U', 12) AS note FROM dual CONNECT BY level <= 1000000; EXEC DBMS_STATS.GATHER_TABLE_STATS('SHOP','BIG_ORDERS');

Part 2: Reproduce the Slow Query

sql SET TIMING ON SELECT /*+ GATHER_PLAN_STATISTICS */ COUNT(*), MAX(order_date) FROM big_orders WHERE customer_id = 4242; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST'));
Result

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

sql SELECT sql_id, executions, buffer_gets, disk_reads, ROUND(elapsed_time/1000) AS elapsed_ms FROM v$sql WHERE sql_text LIKE 'SELECT /*+ GATHER_PLAN_STATISTICS */ COUNT(*), MAX(order_date)%' AND sql_text NOT LIKE '%v$sql%';

Part 4: Fix It

sql CREATE INDEX idx_big_orders_cust ON big_orders (customer_id); EXEC DBMS_STATS.GATHER_TABLE_STATS('SHOP','BIG_ORDERS', cascade => TRUE); SELECT /*+ GATHER_PLAN_STATISTICS */ COUNT(*), MAX(order_date) FROM big_orders WHERE customer_id = 4242; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST'));
Result

The plan now uses INDEX RANGE SCAN. Buffer gets drop from thousands of blocks to a few dozen, and the query finishes in milliseconds.

BeforeAfter
Access pathTABLE ACCESS FULLINDEX RANGE SCAN
Rows read1,000,000About 50
Buffer getsThousandsFew dozen

Part 5: The Hanging Page (Blocking Session)

  1. Session A (a forgotten, uncommitted change): UPDATE big_orders SET note = 'LOCKED' WHERE order_id = 1;
  2. Session B (the application): UPDATE big_orders SET note = 'X' WHERE order_id = 1; It hangs.

Now be the DBA in Session C:

sql SELECT s.sid, s.serial#, s.username, s.blocking_session, s.event, s.seconds_in_wait FROM v$session s WHERE s.blocking_session IS NOT NULL; SELECT sid, serial#, username, status, machine, program, logon_time FROM v$session WHERE sid = <blocker_sid>;

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:

sql ALTER SYSTEM KILL SESSION '<sid>,<serial#>' IMMEDIATE;

Part 6: Write the Findings

ItemNotes
Problem 1Missing index on customer_id caused a full scan of 1M rows
Fix 1Created idx_big_orders_cust, gathered statistics
Problem 2Uncommitted update held a row lock
Fix 2Session owner committed; advised the team to commit promptly
PreventionAdd monitoring for blockers over 60 seconds

Part 7: Clean Up

sql DROP TABLE big_orders PURGE;

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.

Quick Summary: Plan shows full scan, add index and stats. blocking_session leads to the cause. Document and prevent.

Get Newsletter

Subscibe to our newsletter and we will notify you about the newest updates on Edugators