SQL Tuning: Find and Fix Slow SQL

Oracle DBA Certification 15 min min read Updated: Nov 16, 2026 Advanced
SQL Tuning: Find and Fix Slow SQL
Advanced Topic 3 of 7

Tune the Right Thing

Do not guess. Tune the SQL that uses the most total time, because a fix there helps the whole system. A query that runs once for 5 seconds matters less than one that runs a million times for 50 ms.

Step 1: Find Top SQL

sql SELECT sql_id, executions, ROUND(elapsed_time/1e6) AS elapsed_sec, ROUND(elapsed_time/1e6/NULLIF(executions,0), 3) AS sec_per_exec, buffer_gets, disk_reads, SUBSTR(sql_text, 1, 60) AS sql_text FROM v$sql ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;
ColumnLook for
elapsed_secTotal time spent. Start with the biggest.
sec_per_execSlow per run?
buffer_getsLogical reads. Very high means lots of work.
disk_readsPhysical reads from disk

Step 2: Look at the Plan

sql SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'ALLSTATS LAST'));

Step 3: Apply the Common Fixes

1. Use bind variables

Literal values make every statement look different, so Oracle must parse each one from scratch (a hard parse). It wastes CPU and fills the shared pool.

sql -- bad: a new SQL for every customer SELECT * FROM shop.orders WHERE customer_id = 17; -- good: one shared SQL SELECT * FROM shop.orders WHERE customer_id = :cust_id;

2. Do not wrap indexed columns in functions

sql -- bad: index on order_date cannot be used SELECT * FROM shop.orders WHERE TRUNC(order_date) = DATE '2026-11-10'; -- good: a range keeps the index usable SELECT * FROM shop.orders WHERE order_date >= DATE '2026-11-10' AND order_date < DATE '2026-11-11';

3. Select only what you need

sql -- avoid SELECT * on wide tables SELECT order_id, status FROM shop.orders WHERE customer_id = :c;

4. Match data types

Comparing a number column to a string (WHERE customer_id = '17') forces a conversion and can stop the index from being used.

5. Index the join and filter columns

Add indexes for columns that appear in WHERE and JOIN, especially foreign keys.

6. Fresh statistics

Bad plans often come from missing or old statistics. Gather them first.

Hints: Use Rarely

sql SELECT /*+ INDEX(o idx_orders_status) */ * FROM shop.orders o WHERE status = 'CANCELLED';
Careful: A hint forces the optimizer's hand. It can become wrong when the data grows. Use it for testing or as a last resort.

The SQL Tuning Advisor

sql DECLARE l_task VARCHAR2(100); BEGIN l_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '&sql_id', time_limit => 60); DBMS_SQLTUNE.EXECUTE_TUNING_TASK(l_task); DBMS_OUTPUT.PUT_LINE(l_task); END; / SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('&task_name') FROM dual;
Licence: The SQL Tuning Advisor is part of the Oracle Tuning Pack, a paid option on Enterprise Edition. Check before using it in production.

Conclusion

Find the top SQL by total time, read its plan and fix the cause: bind variables, usable indexes, fresh statistics and fewer columns.

Quick Summary: Tune the biggest consumer first. Binds, no functions on indexed columns, good statistics.

Get Newsletter

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