Oracle 11g PL/SQL存储过程性能优化咨询

Great catch identifying those per-row table queries as the bottleneck—those repeated SELECT calls inside your loop are almost certainly responsible for most of that 3-hour runtime. Let's break down concrete, actionable optimizations to get this running in minutes instead of hours:

1. Replace Per-Row Lookups with Bulk Data Retrieval #

Instead of querying table3, table4, table5 once per record in your loop, fetch all required values in single bulk operations using your l_data collection as a filter. This cuts down on expensive database round-trips drastically.

Example Implementation: #

First, you’ll need to treat your my_record_table collection as a relational table (Oracle 11g supports this with schema-level collection types or CAST). Here’s how to rewrite the computation block:

-- First, define schema-level types if you haven't already:
-- CREATE TYPE my_record IS RECORD(key1 NUMBER, key2 NUMBER, key3 NUMBER, key4 NUMBER);
-- CREATE TYPE my_record_table IS TABLE OF my_record;

-- Inside your procedure, after fetching l_data:
DECLARE
  TYPE value_set IS RECORD(
    key1 NUMBER, key2 NUMBER, key3 NUMBER, key4 NUMBER,
    myValue1 NUMBER, myValue2 NUMBER, myValue3 NUMBER
  );
  TYPE value_table IS TABLE OF value_set;
  l_bulk_values value_table;
BEGIN
  -- Fetch all required values in one bulk query
  SELECT
    t.key1, t.key2, t.key3, t.key4,
    COALESCE(t3.max_amount, 0) AS myValue1,
    COALESCE(t4.amount, 0) AS myValue2,
    COALESCE(t5.amount, 0) AS myValue3
  BULK COLLECT INTO l_bulk_values
  FROM TABLE(CAST(l_data AS my_record_table)) t
  LEFT JOIN (
    SELECT key1, key2, key3, key4, MAX(amount) AS max_amount 
    FROM table3 
    GROUP BY key1, key2, key3, key4
  ) t3 ON t.key1 = t3.key1 AND t.key2 = t3.key2 AND t.key3 = t3.key3 AND t.key4 = t3.key4
  LEFT JOIN table4 t4 ON t.key1 = t4.key1 AND t.key2 = t4.key2 AND t.key3 = t4.key3 AND t.key4 = t4.key4
  LEFT JOIN table5 t5 ON t.key1 = t5.key1 AND t.key2 = t5.key2 AND t.key3 = t5.key3 AND t.key4 = t5.key4;

  -- Update l_data with bulk-fetched values (use index-by tables for even faster lookups)
  FOR indx IN 1 .. l_data.COUNT LOOP
    FOR val_indx IN 1 .. l_bulk_values.COUNT LOOP
      IF l_bulk_values(val_indx).key1 = l_data(indx).key1 
         AND l_bulk_values(val_indx).key2 = l_data(indx).key2 
         AND l_bulk_values(val_indx).key3 = l_data(indx).key3 
         AND l_bulk_values(val_indx).key4 = l_data(indx).key4 THEN
        l_data(indx).place_holder1 := l_bulk_values(val_indx).myValue1;
        l_data(indx).place_holder2 := someFunction(l_bulk_values(val_indx).myValue2, l_data(indx).p1);
        l_data(indx).place_holder3 := l_bulk_values(val_indx).myValue3 * l_data(indx).p2;
        EXIT;
      END IF;
    END LOOP;
  END LOOP;
END;

For even faster matching, replace the inner loop with an index-by table (associative array) keyed on the composite (key1, key2, key3, key4) to avoid linear searches.

2. Optimize the Initial Cursor Query #

Your cursor c includes two repeated subqueries for p1 and p2—these run once per row in mytable, which is unnecessary since param_id=1 and param_id=2 are fixed values. Fetch these parameters once at the start of the procedure:

procedure long_runnig_task is 
  -- ... existing type declarations ...
  l_p1 NUMBER;
  l_p2 NUMBER;
  cursor c is select key1, key2, key3, key4, 0 place_holder1, 0 place_holder2, 0 place_holder3 
              from mytable where myflag=4;
begin
  -- Fetch params once upfront instead of per-row
  SELECT param INTO l_p1 FROM paramtable WHERE param_id=1;
  SELECT param INTO l_p2 FROM paramtable WHERE param_id=2;

  open c;
  loop
    begin
      fetch c bulk collect into l_data limit 1000;
      savepoint mysp;

      -- Assign p1/p2 to all records in l_data
      FOR indx IN 1 .. l_data.COUNT loop
        l_data(indx).p1 := l_p1;
        l_data(indx).p2 := l_p2;
      END LOOP;

      -- ... rest of your bulk computation and DML ...

3. Adjust Bulk Collection Limit #

Your current limit is 1000—test increasing this to 5000 or 10000 (adjust based on your database’s memory constraints). Larger batches reduce the number of loop iterations and context switches between PL/SQL and SQL.

4. Add Targeted Indexes #

Ensure all tables involved have composite indexes on (key1, key2, key3, key4) to speed up joins and lookups:

• For table3: Create an index including amount to avoid table access:

  CREATE INDEX idx_table3_keys ON table3(key1, key2, key3, key4) INCLUDE(amount);
  

• For table4 and table5: Create unique indexes if the key combination is unique (which it appears to be, since you’re selecting a single amount):

  CREATE UNIQUE INDEX idx_table4_keys ON table4(key1, key2, key3, key4);
  CREATE UNIQUE INDEX idx_table5_keys ON table5(key1, key2, key3, key4);
  

• For table6: Add an index on the key columns to speed up the UPDATE operation.

5. Fix Typographical Errors #

I noticed a few typos in your code that could cause errors or unexpected behavior:

• l_data(indx).kay1 → should be l_data(indx).key1 (same for kay2, kay3, kay4)
• l_data(indx).palce_holder3 → should be l_data(indx).place_holder3

Fixing these will prevent unnecessary exceptions and ensure your logic runs as intended.

Final Notes #

• Test these changes in a staging environment first, and use tools like DBMS_PROFILER or SQL Trace to verify performance improvements.
• If someFunction is a PL/SQL function, consider inlining it or converting it to a SQL function if possible, to avoid per-row PL/SQL calls.

内容的提问来源于stack exchange，提问作者PKey