We have an Informatica job that is crawling along attempting to update
about
400K rows. There's nothing else running in the database. The job
takes
over 17 hours to update all 400K records. The following is a tkprof
from about
15 minutes of tracing. I'm at a loss to explain the physical and
logical
I/O when the access path is via the PK. Any ideas? There's no
chaining
and there are no other indexes.
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
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0
0
Execute 200 578.44 789.45 1852160 1936484 218
200
Fetch 0 0.00 0.00 0 0 0
0
------- ------ -------- ---------- ---------- ---------- ----------
total 200 578.44 789.45 1852160 1936484 218
200
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 84 (IA2_USER)
Rows Execution Plan
------- ---------------------------------------------------
0 UPDATE STATEMENT GOAL: CHOOSE
0 UPDATE OF 'OD_CUST_LOCS'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'SYS_C0035425'
(UNIQUE)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total
Waited
- Waited ----------
db file scattered read 254177 1.66
251.52
db file sequential read 9514 0.17
1.43
SQL*Net message to client 200 0.00
0.00
SQL*Net message from client 200 0.00
0.29
Jeffery D Thomas
DBA
Thomson Information Services
Thomson, Inc.
Email: jeff.thomas_at_thomson.net
Indy DBA Master Documentation available at:
http://gkmqp.tce.com/tis_dba
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 - 09:50:59 CDT
