Start with data, not guesses
Performance tuning without measurement is guessing. Always start by identifying the actual bottleneck:
AWR (Automatic Workload Repository): provides SQL statements consuming the most resources over a time period. Access via Enterprise Manager or:
SELECT sql_id, elapsed_time/1000000 AS elapsed_sec, executions,
elapsed_time/NULLIF(executions,0)/1000000 AS avg_sec
FROM v$sqlstats ORDER BY elapsed_time DESC FETCH FIRST 20 ROWS ONLY;
TKPROF: trace a specific session to get detailed per-statement timing:
ALTER SESSION SET sql_trace = TRUE;
-- run your process
ALTER SESSION SET sql_trace = FALSE;
-- then run tkprof on the trace file
The top 5 PL/SQL performance killers
1. Row-by-row DML in loops — replace with BULK COLLECT + FORALL. Covered in the bulk operations article.
2. SELECT inside a loop — fetching one row per iteration of a loop. Replace with a single JOIN query before the loop.
3. Implicit type conversion — comparing a VARCHAR2 column to a NUMBER causes a full scan:
WHERE employee_id = '101' -- VARCHAR2 = NUMBER: implicit conversion
WHERE employee_id = 101 -- NUMBER = NUMBER: index can be used
4. Functions on indexed columns in WHERE — WHERE UPPER(email) = :val ignores an index on email. Create a function-based index or store data in consistent case.
5. Unnecessary COMMIT inside loops — each COMMIT causes a redo log write. Batch your commits every N rows instead.
SQL Tuning Advisor
Oracle’s built-in advisor analyses a specific statement and recommends actions:
DECLARE
l_task_name VARCHAR2(100);
BEGIN
l_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'your_sql_id');
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => l_task_name);
DBMS_OUTPUT.PUT_LINE(DBMS_SQLTUNE.REPORT_TUNING_TASK(l_task_name));
END;
Recommendations typically include index creation, statistics gathering, or SQL restructuring.
Indexing strategy review
Verify indexes are actually being used:
SELECT index_name, num_rows, distinct_keys, last_analyzed
FROM dba_indexes WHERE table_name = 'ORDERS';
-- Check for unused indexes (wasting maintenance overhead):
SELECT * FROM v$object_usage WHERE used = 'NO';
Connection pooling and PL/SQL overhead
For VBCS and OIC integrations calling PL/SQL: each call that opens a new database session has connection overhead (100-300ms). Use connection pooling (available in ATP and standard Oracle DB) and batch multiple logical operations into single stored procedure calls.