mercredi 7 septembre 2016

DBLink performance issue after upgrading to 11.2.0.4 writing to SQL Server

We have been writing information to a MS Sql Server database (v2005) for a few years now without much difficulty. After upgrading our Oracle db to 11.2.0.4 (from 11.1.0.7) this past weekend we are finding the process extremely slow. For example, to write 100 records used to take 5 seconds and now takes 50. With almost 30,000 records, it's going to be a problem if we can't figure out how to do it better. The destination table is truncated before beginning. Here is the code we used for the 100 record test. Any tips you have to speed this up are appreciated.

BEGIN
  FOR rec IN
  (SELECT * FROM my_orders_v WHERE  item_number like  '52548%'
  )
  LOOP
    INSERT
INTO bill_test_so@sqldatabase
  (
    ORDER_NUMBER ,
    item_number ,
    ITEM_DESCRIPTION ,
    QUANTITY ,
    uom_code,
    ORDER_TYPE_TEXT ,
    NEW_DUE_DATE ,
    ACTION ,
    PLANNER_CODE ,
    ORGANIZATION_ID ,
    INVENTORY_ITEM_ID ,
    LAST_UPDATE_DATE ,
    LAST_UPDATED_BY ,
    CREATED_BY ,
    CREATION_DATE ,
    TRANSACTION_ID
  )
  VALUES
  (
    rec.ORDER_NUMBER ,
    rec.item_number ,
    rec.DESCRIPTION ,
    rec.QUANTITY ,
    rec.uom_code,
    rec.ORDER_TYPE_TEXT ,
    rec.NEW_DUE_DATE ,
    rec.ACTION ,
    rec.PLANNER_CODE ,
    rec.ORGANIZATION_ID ,
    rec.INVENTORY_ITEM_ID ,
    rec.LAST_UPDATE_DATE ,
    rec.LAST_UPDATED_BY ,
    rec.CREATED_BY ,
    rec.CREATION_DATE ,
    rec.TRANSACTION_ID
  );
  END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
  retcode := 2;
  errbuf  := SQLERRM;
  ROLLBACK;
END ;

Thanks for your help! Bill

Aucun commentaire:

Enregistrer un commentaire