Re: Slow UPDATE

From: Juan Cachito Reyes Pacheco <jreyes_at_dazasoftware.com>
Date: Sat, 15 May 2004 18:14:06 -0400
Message-ID: <005201c43ac9$f2f163c0$2501a8c0@dazasoftware.com>

Hi Thomas if most of that data don't need to be updated you can try adding

WHERE
NOT CURR_KEY = :1 AND NOT CUSTOMER_ID = :2 AND NOT CREATED_BY_ID =

:3,........................

I optimized in that way an update from 3 minutes to 10 seconds. But I don't know if this is the same situation in you. Give I try if this don't solve your problem maybe I can give you another idea.

Better if you get an statpacks reports, this gives more information about the current worload you have.
If you don't have an oltp
try to get use
start statpacks (before the events you want to monitor)

EXECUTE STATSPACK.SNAP(i_snap_level=>7);

After that events happens stop statpacs

EXECUTE STATSPACK.SNAP(i_snap_level=>7); This gives the most informatoin but can take some resources.

Maybe the reason for the time is anothe process, not the update process.

Can't you do all in only one update, I saw a 200 executions? Can you post your code.

UPDATE OD_CUST_LOCS SET CURR_KEY = :1, CUSTOMER_ID = :2, CREATED_BY_ID = :3,
CHANGED_BY_ID = :4, CREATED_ON_DT = :5, CHANGED_ON_DT = :6, PSTL_GEO_LOC_ID

:7, ADDRESS_NUM = :8, LOCATION_TYPE = :9, STREET_NUM = :10, STREET_NAME =
:11, POST_OFFICE_BOX = :12, CITY_CODE = :13, CITY_NAME = :14,
STATE_CODE =
:15, STATE_NAME = :16, REGION_CODE = :17, REGION_NAME = :18,
COUNTRY_CODE =
:19, COUNTRY_NAME = :20, COUNTY_CODE = :21, COUNTY_NAME = :22,
POSTAL_CODE = :23, PHONE_NUM = :24, FAX_NUM = :25, EMAIL_ADDRESS = :26, WEB_ADDRESS =
:27, AUTO_ROUTING_CD = :28, ADDR_LATITUDE = :29, ADDR_LONGITUDE = :30,
PRMRY_CONTCT_NAME = :31, CLOC_ATTR1_CODE = :32, CLOC_ATTR1_NAME = :33, CLOC_ATTR2_CODE = :34, CLOC_ATTR2_NAME = :35, CLOC_ATTR3_CODE = :36, CLOC_ATTR3_NAME = :37, CLOC_ATTR4_CODE = :38, CLOC_ATTR4_NAME = :39, CLOC_ATTR5_CODE = :40, CLOC_ATTR5_NAME = :41, CLOC_ATTR6_CODE = :42, CLOC_ATTR6_NAME = :43, CLOC_ATTR7_CODE = :44, CLOC_ATTR7_NAME = :45, CLOC_ATTR8_CODE = :46, CLOC_ATTR8_NAME = :47, CLOC_ATTR9_CODE = :48, CLOC_ATTR9_NAME = :49, CLOC_ATTR10_CODE = :50, CLOC_ATTR10_NAME = :51, CLOC_ATTR1_TEXT = :52, CLOC_ATTR2_TEXT = :53, CLOC_ATTR3_TEXT = :54, CLOC_ATTR4_TEXT = :55, CLOC_ATTR5_TEXT = :56, X_CLOC_ATTR1_TEXT = :57, IA_INSERT_DT = :58, IA_UPDATE_DT = :59, EFFECTIVE_FROM_DT = :60, EFFECTIVE_TO_DT = :61, SRC_EFF_TO_DT = :62, DELETE_FLAG = :63, IA_COPYRIGHT
:64, X_LOAD_DT = :65 WHERE SRC_EFF_FROM_DT = :66 AND KEY_ID = :67 AND SOURCE_ID = :68

Juan Carlos Reyes Pacheco
OCP
Database 9.2 Standard Edition

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Sat May 15 2004 - 17:15:46 CDT