Oracle Sql & Plsql performance Tips

I'm this blog post I will outline some real time use cases related to performance tuning on oracle sql and plsql. 💡💻🚀

Below are examples demonstrating the best practices for Oracle SQL & PL/SQL performance tuning.

1. SQL Performance Tuning Examples

a. Avoid SELECT * and Fetch Only Required Columns

❌ Bad Practice

SELECT * FROM employees WHERE department_id = 10;

✅ Optimized Version

SELECT employee_id, first_name, salary FROM employees WHERE department_id = 10;

Why? Fetching only necessary columns reduces memory usage and improves performance.

b. Use EXISTS Instead of IN

❌ Bad Practice (Subquery with IN)

SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 100);

✅ Optimized Version (EXISTS is usually faster)

SELECT * FROM employees e WHERE EXISTS ( SELECT 1 FROM departments d WHERE d.department_id = e.department_id AND d.location_id = 100 );

Why? EXISTS stops searching as soon as it finds a match, whereas IN may check all rows.

c. Use Proper Indexing

✅ Creating an Index on Frequently Queried Columns

CREATE INDEX idx_emp_dept ON employees(department_id);

✅ Using a Composite Index for Multi-Column Queries

CREATE INDEX idx_emp_name_dept ON employees(last_name, department_id);

Why? Indexes speed up lookups but should be used carefully, as too many indexes can slow down INSERT/UPDATE/DELETE.

d. Avoid Functions on Indexed Columns

❌ Bad Practice (Function on an Indexed Column Prevents Index Usage)

SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';

✅ Optimized Version (Function-Based Index)

CREATE INDEX idx_upper_lastname ON employees(UPPER(last_name)); SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';

Why? Normal indexes don’t work when a function is applied to a column, so a function-based index is required.

e. Use UNION ALL Instead of UNION

❌ Bad Practice (Using UNION unnecessarily)

SELECT first_name FROM employees WHERE department_id = 10 UNION SELECT first_name FROM employees WHERE department_id = 20;

✅ Optimized Version (UNION ALL is Faster)

SELECT first_name FROM employees WHERE department_id = 10 UNION ALL SELECT first_name FROM employees WHERE department_id = 20;

Why? UNION removes duplicates, which requires sorting. If duplicates are not a concern, UNION ALL is much faster.

f. Analyzing Execution Plan

✅ Check Execution Plan for Query Performance

EXPLAIN PLAN FOR SELECT FROM employees WHERE department_id = 10; SELECT FROM TABLE(DBMS_XPLAN.DISPLAY);

Why? Helps understand how Oracle processes queries and find bottlenecks.

2. PL/SQL Performance Tuning Examples

a. Use BULK COLLECT Instead of Row-by-Row Fetching

❌ Bad Practice (Row-by-Row Processing)

DECLARE CURSOR emp_cursor IS SELECT employee_id, salary FROM employees; v_employee_id employees.employee_id%TYPE; v_salary employees.salary%TYPE;

BEGIN

FOR emp_rec IN emp_cursor LOOP -- Process each record one by one DBMS_OUTPUT.PUT_LINE(emp_rec.employee_id || ' - ' || emp_rec.salary);

END LOOP;

END;

✅ Optimized Version Using BULK COLLECT

DECLARE TYPE emp_table IS TABLE OF employees%ROWTYPE; emp_list emp_table; BEGIN

SELECT * BULK COLLECT INTO emp_list FROM employees; FOR i IN emp_list.FIRST .. emp_list.LAST LOOP DBMS_OUTPUT.PUT_LINE(emp_list(i).employee_id || ' - ' || emp_list(i).salary); END LOOP; END;

Why? BULK COLLECT reduces context switching between PL/SQL and SQL engines, improving performance.

b. Use FORALL Instead of FOR Loop for DML Operations

❌ Bad Practice (Row-by-Row Insert - Slow)

DECLARE

CURSOR emp_cursor IS SELECT employee_id, salary FROM employees;

BEGIN FOR emp_rec IN emp_cursor LOOP INSERT INTO employee_backup VALUES (emp_rec.employee_id, emp_rec.salary); END LOOP;

COMMIT;

END;

✅ Optimized Version Using FORALL

DECLARE

TYPE emp_table IS TABLE OF employees%ROWTYPE;

emp_list emp_table;

BEGIN

SELECT * BULK COLLECT INTO emp_list FROM employees;

FORALL i IN 1..emp_list.COUNT INSERT INTO employee_backup VALUES emp_list(i); COMMIT;

END;

Why? FORALL performs batch processing, reducing context switching and improving speed.

c. Use PL/SQL Function Result Caching

✅ Enable Function Result Cache

CREATE OR REPLACE FUNCTION get_employee_name (p_emp_id NUMBER) RETURN VARCHAR2 RESULT_CACHE IS v_name employees.first_name%TYPE;

BEGIN

SELECT first_name INTO v_name FROM employees WHERE employee_id = p_emp_id; RETURN v_name;

END;

Why? The RESULT_CACHE stores function results in memory, reducing repetitive database calls.

3. Database-Level Tuning Examples

a. Gathering Table Statistics

✅ Update Table Statistics for the Optimizer

EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');

Why? Helps Oracle’s optimizer make better execution plan decisions.

b. Monitoring Database Performance with AWR Report

✅ Generate AWR Report to Analyze Performance Issues

SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(10, 20));

Why? AWR reports help identify slow queries, CPU bottlenecks, and inefficient execution plans.

Final Thoughts

Implementing these best practices can significantly improve the performance of Oracle SQL & PL/SQL workloads.

Hope this helps . Happy learning.