products-service

Improving the performance of a stored procedure in Oracle involves a combination of SQL tuning, PL/SQL optimization, efficient data access, and proper use of database features. Here’s a comprehensive list of techniques:

---

1. Minimize Context Switching

• Every time PL/SQL calls SQL and vice versa, context switching occurs.
• Solution: Use bulk operations like FORALL, BULK COLLECT to reduce switching overhead.

-- Inefficient
FOR i IN (SELECT * FROM employees) LOOP
  INSERT INTO log_table VALUES (i.emp_id, i.name);
END LOOP;

-- Efficient
DECLARE
  TYPE emp_tab IS TABLE OF employees%ROWTYPE;
  emps emp_tab;
BEGIN
  SELECT * BULK COLLECT INTO emps FROM employees;
  FORALL i IN 1..emps.COUNT
    INSERT INTO log_table VALUES emps(i).emp_id, emps(i).name;
END;

---

2. Avoid Unnecessary SQL Queries

• Cache values in variables if used repeatedly instead of re-querying the DB.
• Avoid nested loops that perform DML/SQL statements repeatedly.

---

3. Use Bind Variables

• Avoid dynamic SQL with hardcoded values to benefit from SQL statement reuse.

  -- Good
  EXECUTE IMMEDIATE 'DELETE FROM employees WHERE id = :id' USING emp_id;
  

---

4. Optimize SQL Inside Procedures

• Use EXPLAIN PLAN or SQL Trace to analyze queries.
• Add indexes or hints where needed.
• Rewrite complex queries with WITH clause or temporary tables.

---

5. Limit Rows Processed

• Use ROWNUM, FETCH FIRST, or WHERE clause to avoid scanning entire tables.

  SELECT * FROM employees WHERE department_id = 10 AND ROWNUM < 100;
  

---

6. Use Deterministic Functions

If your procedure calls functions with the same inputs frequently, declare them as DETERMINISTIC to allow result caching.

---

7. Use Collections Instead of Cursors Where Appropriate

• Cursor loops are slower than collection-based bulk processing.

---

8. Parallel Execution (if applicable)

• Consider Oracle parallel features when working on large datasets, especially in data warehouse environments.

---

9. Use NOCOPY for OUT Parameters

• Reduces copying overhead, especially for large OUT parameters.

  PROCEDURE process_data(p_data IN OUT NOCOPY CLOB);
  

---

10. Analyze and Tune Dependencies

• Tune the tables, indexes, and views the procedure relies on.
• Gather statistics using DBMS_STATS regularly.

---

11. Avoid Unnecessary Logging

• Remove or limit DBMS_OUTPUT.PUT_LINE in production code.

---

12. Compile with Optimization

• Compile with native code (PLSQL_CODE_TYPE = 'NATIVE').

  ALTER SYSTEM SET plsql_code_type = 'NATIVE';
  

---

13. Profile and Benchmark

Use tools like:

• DBMS_PROFILER
• PL/SQL Hierarchical Profiler
• AUTOTRACE

---

Let me know if you’d like a working example to apply these concepts or want to analyze a specific procedure for performance.

Great! Let’s walk through a working example to improve a stored procedure’s performance by applying some of the techniques discussed.

---

Problem Statement

You have a procedure that processes employee bonuses by:

1. Selecting high-performing employees,
2. Giving them a bonus,
3. Logging each action.

Initial inefficient version:

CREATE OR REPLACE PROCEDURE process_bonuses AS
  CURSOR emp_cur IS SELECT emp_id, salary FROM employees WHERE performance_rating = 'HIGH';
  v_bonus NUMBER;
BEGIN
  FOR emp IN emp_cur LOOP
    v_bonus := emp.salary * 0.10;

    UPDATE employees
    SET bonus = bonus + v_bonus
    WHERE emp_id = emp.emp_id;

    INSERT INTO bonus_log(emp_id, bonus_given, log_date)
    VALUES (emp.emp_id, v_bonus, SYSDATE);
  END LOOP;
END;

---

Problems Here

• Multiple context switches (UPDATE and INSERT per loop).
• Uses a cursor (less efficient).
• No bulk processing.

---

Optimized Version using BULK COLLECT and FORALL

CREATE OR REPLACE PROCEDURE process_bonuses_optimized AS
  TYPE emp_tab IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
  l_emps emp_tab;

  TYPE bonus_tab IS TABLE OF bonus_log%ROWTYPE INDEX BY PLS_INTEGER;
  l_logs bonus_tab;

  v_bonus NUMBER;
BEGIN
  -- Bulk collect employee records
  SELECT * BULK COLLECT INTO l_emps
  FROM employees
  WHERE performance_rating = 'HIGH';

  FOR i IN 1 .. l_emps.COUNT LOOP
    v_bonus := l_emps(i).salary * 0.10;

    -- Prepare updates using FORALL
    UPDATE employees
    SET bonus = bonus + v_bonus
    WHERE emp_id = l_emps(i).emp_id;

    -- Prepare log
    l_logs(i).emp_id := l_emps(i).emp_id;
    l_logs(i).bonus_given := v_bonus;
    l_logs(i).log_date := SYSDATE;
  END LOOP;

  -- Bulk insert logs
  FORALL i IN INDICES OF l_logs
    INSERT INTO bonus_log(emp_id, bonus_given, log_date)
    VALUES l_logs(i);
END;

---

Key Improvements

• Bulk Collect avoids row-by-row fetching.
• FORALL reduces context switching for both UPDATE and INSERT.
• This structure scales better for thousands or millions of rows.

---

Let me know if you want to simulate this on a sample table, or convert this logic to PostgreSQL, Java JDBC, or using Oracle SQL Developer tools!