HELP -- Insert's Take Forever! (code attached)

From: contrapositive <contrapositive_at_hotmail.com>
Date: Thu, 23 May 2002 22:51:52 -0400
Message-ID: <3cedaeb5_5@nopics.sjc>

Hi.

HELP! I'm working with a PL/SQL program that combines or consolidates orders from a database. My problem is that the inserts are SLOW AS DEATH!

I've attached the code, but I've had to "mask" it and simplify it in order to post it, so there may be some syntactical "bugs". Still the framework and all the elements are the same. In the code, I've identified THREE potential problem inserts and what they do. (I believe No. 2 is the culprit, but I'm not sure.) I've tried these with and without the append hint. No difference. I wonder, since the query in my cursor is kind of slow, if it's not running the query each time through the loop. It's not a speedy query. Really, I'm not sure where the bottleneck lies, but it takes about 30 seconds to process each iteration.

If someone has some general advice, or more detailed hints for me from looking at the code, I'd be grateful. Thanks in advance. (And please let me know if I've left anything out.)

-jk

PRELIMINARIES
This is an order invoicing application database. Orders have multiple line items, line items have one or more "charges" associated with them. The table names should be obvious. Orders typically have 10 to 20 line items (though this varies greatly), and line_items typically have just one "charge". The orders and line_items tables are large; upwards of 50,000 orders and maybe 300,000 line_items. This is an Oracle 8i database running in serial mode. No statistics are kept.

WHAT THE PROGRAM DOES
All open orders for customers must be combined into a single new order. This new order has all the same line items from the original orders. Each day the program looks to see what customers are scheduled to have their orders combined that day and then builds a single new CONSOLIDATED order from all of that customer's open orders. The sum of the line items from the original order should equal the number of line items on the new order (excluding "deleted" lines; status = -99). The original orders are then closed.

HOW IT DOES IT
Orders and customers are brought back in the c_headers cursor, ordered by customer. The cursor iterates through each order and when it detects a new customer ID, a new order header is built. During each iteration, line items for the current order are added under the new order header. Charges associated with the line item are "copied over".

CREATE OR REPLACE PROCEDURE CONSOLIDATE(   p_region IN VARCHAR2(8)
) IS

v_customer_id NUMBER := 0;
v_consolidated_no VARCHAR2(8);
v_consolidated_id NUMBER;
v_last VARCHAR2(9);
v_pad VARCHAR2(1);
v_size NUMBER
v_lines NUMBER;
v_shipped NUMBER;
v_id NUMBER;
v_is_new BOOLEAN;
v_line_no_offset NUMBER := 0;
v_row_count NUMBER := 0;
v_has_lines BOOLEAN := FALSE;

CURSOR c_get_order_no(p IN VARCHAR2) IS
  SELECT x, y, z
  FROM t
  WHERE a = 'A'
  AND b = p
  AND c = 0
  FOR UPDATE; CURSOR c_all_orders(p_region IN varchar2, p_date IN date) IS

• About the query: There seems to be some extraneous stuff here but it's all needed, as
  --there are some other things the program does not shown here (but
  negligible in terms of
  --performance). The query takes about 30 seconds to one minute to bring
  back the rows--a
  --kind of long time. The query can bring back up to 1500 rows but
  typically brings back
  --between 500 and 1000.
  SELECT /*+ ordered */ x.customer_id, x.monthly_ind, t.type_name, o.id, o.region, o.date, t2.type FROM (SELECT 1 AS monthly_ind, m.customer_id FROM customers_to_schedules ms, schedules s, customers m WHERE m.customer_id = ms.customer_id AND ms.schedule_id = s.schedule_id AND trunc(s.schedule_date) = trunc(p_date) AND s.schedule_date <= p_date AND m.invoice_ind = 1 UNION SELECT 0, customer_id FROM customers WHERE invoice_ind = 0) x, log_table h, orders_table o, order_types t, order_types t2, policies p WHERE o.region = p_region AND o.region = p.region AND o.type = t.type AND ( (t.type_name = 'TYPE1' AND o.status = 0) or (t.type_name = 'TYPE2' AND o.status = 20)) AND t.language_id = t2.language_id AND t2.type_name = 'CONS' AND o.id = h.id(+) AND h.type(+) = 'T3' AND h.id is null -- ONLY extract orders NOT in the log table; done here with an outer join AND o.bill_to = x.customer_id;

BEGIN FOR r_header IN c_all_orders(p_region, SYSDATE) LOOP

  v_is_new := (v_customer_id != r_header.customer_id);

  IF v_is_new THEN
    IF NOT v_has_lines THEN
      ROLLBACK; --b/c we created a combined order with no detail lines; this will revert the order

    ELSE
      COMMIT; --commit all others
    END IF;
    v_line_no_offset := 0;
    v_has_lines := FALSE;
  END IF;   v_customer_id := r_header.customer_id;   v_id := r_header.id;

  IF v_is_new THEN

    --new id of consolidated order/new order no     v_consolidated_id := my_function('get_new_id');     OPEN c_get_order_no(r_header.region);       FETCH c_get_order_no INTO v_last, v_pad, v_size;

      UPDATE system_table_1
      SET a = a + 1,
        b = b + 1
      WHERE CURRENT OF c_get_order_no;

    CLOSE c_get_order_no;
    v_consolidated_no := ltrim(lpad(v_last, v_size, v_pad));

• *** PROBLEM INSERT NUMBER 1 ***
• Creates an order "header" - this is the new consolidated order. Most of the values
• inserted are taken from the cursor values: "r_header.column_name" INSERT INTO /*+ APPEND */ orders_table ( type, number
  • ... 40 or so more columns deleted ) VALUES ( r_header.type_name, v_consolidated_no, 0 ); END IF;
• *** PROBLEM INSERT NUMBER 2 ***
• This query grabs all the line items and copies them into the same table
• as part of the new order INSERT INTO /*+ APPEND */ line_item_table ( id, line_no, lineitem_id, invoice_id
• 80 or so more columns here... ) SELECT v_consolidated_id, rownum + v_line_no_offset, my_function('get_new_lineitem_id'), d.invoice_id
  • ... FROM line_item_table d, WHERE d.id = v_id AND d.status != -99 AND d.exception = 'NO';

  v_row_count := SQL%RowCount;
  v_line_no_offset := v_line_no_offset + v_row_count;

  IF v_row_count > 0 THEN
    v_has_lines := TRUE;
    COMMIT; --needed to enable the following query since it selects from the rows inserted into the line items table above

• *** PROBLEM INSERT NUMBER 3 ***
• This query copies all charges associated with original orders into the same table
• thus associating them with the new combined order INSERT INTO /*+ APPEND */ charges_table ( invoice_id, lineitem_id, value_id, linevalue_id
• ... 15 or so more columns delete ) SELECT i.invoice_id, d.lineitem_id, i.value_id, my_function('get_new_linevalue_id')
• ... FROM line_item_table d, charges_table i, line_item_table d2 WHERE d.id = v_consolidated_id AND d.original_order_id = d2.lineitem_id AND d2.lineitem_id = i.lineitem_id AND d2.id = v_id AND d2.status != -99 AND d2.exception = 'NO';

    INSERT INTO log_table (

      id,
      type,
      consolidated_id,
      date
    ) VALUES (
      v_id,
      'T3',
      v_consolidated_id,
      SYSDATE

    );

    UPDATE line_item_table
    SET status = 99,

      changed_by = 'user99',
      change_date = SYSDATE

    WHERE id = v_id
    AND status != -99;

    UPDATE orders_table
    SET status = 99,

      changed_by = 'user99',
      change_date = SYSDATE

    WHERE id = v_id;
  END IF; END LOOP; IF not v_has_lines then
  ROLLBACK; --b/c we created a combined order with no detail lines; this will revert the order
ELSE
  COMMIT; --commit all others
END IF; END CONSOLIDATE; Received on Thu May 23 2002 - 21:51:52 CDT