Re: Slow UPDATE
- From: "Juan Cachito Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 15 May 2004 18:14:06 -0400
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
