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;
| Column | Look for |
elapsed_sec | Total time spent. Start with the biggest. |
sec_per_exec | Slow per run? |
buffer_gets | Logical reads. Very high means lots of work. |
disk_reads | Physical 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.