Take a
look at using array fetches to retrieve rows more efficeintly. It appears
you are fetching 1 row at a time. Which can be grossly
inefficient.
Another helpful thing would to generate the TKPROF with Explan plans so
you can see the rows per step and the paths chosen.
Also,
consider of bulk transaction use for the updates, which seem to be touching alot
of blocks.
"Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes."
Christopher R. Spence
Oracle DBA
Phone: (978) 322-5744
Fax: (707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
-----Original Message-----
From: Robertson Lee - lerobe [mailto:[EMAIL PROTECTED]]
Sent: Thursday, August 30, 2001 11:56 AM
To: Multiple recipients of list ORACLE-L
Subject: Tkprof outputApologies for the length of the mail.This query is running for a mad amount of time, anyone any ideas.Code and tkprof out put shown below.Huge TIALee (who must learn more about such things !!!)DECLARE CURSOR TEMP_CDS IS
SELECT ACXIOM_CUSTOMER_KEY,
VERSION_NO,
ADDRESS_OCCUPANCY_KEY
FROM CUSTOMER_DETAIL_SOURCE
WHERE VISIBLE=1;COUNTER NUMBER(8);BEGIN
COUNTER:=0;
FOR I IN TEMP_CDS
LOOPUPDATE &SCHEMA..SINGLE_CUSTOMER SC
SET VISIBLE = 1
WHERE ACXIOM_CUSTOMER_KEY=I.ACXIOM_CUSTOMER_KEY
AND VERSION_NO =I.VERSION_NO;UPDATE &SCHEMA..SINGLE_CUSTOMER_HISTORY SCH
SET VISIBLE = 1
WHERE ACXIOM_CUSTOMER_KEY=I.ACXIOM_CUSTOMER_KEY
AND VERSION_NO =I.VERSION_NO;UPDATE &SCHEMA..ADDRESS_OCCUPANCY AO
SET VISIBLE = 1
WHERE ADDRESS_OCCUPANCY_KEY = I.ADDRESS_OCCUPANCY_KEY;COUNTER := COUNTER + 1;
IF (COUNTER = 50000)
THEN
COUNTER:=0;
COMMIT;
END IF;
END LOOP;
COMMIT;Sort options: prsela exeela fchela
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************UPDATE VM_LIVE.SINGLE_CUSTOMER SC SET VISIBLE=1
WHERE
ACXIOM_CUSTOMER_KEY = :b1 AND VERSION_NO = :b2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 39562 15.51 398.98 56555 181085 40672 39562
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 39562 15.51 398.98 56555 181085 40672 39562Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 39 (VM_LIVE) (recursive depth: 1)Rows Execution Plan
------- ---------------------------------------------------
0 UPDATE STATEMENT GOAL: CHOOSE
0 UPDATE OF 'SINGLE_CUSTOMER'
0 TABLE ACCESS (BY INDEX ROWID) OF 'SINGLE_CUSTOMER'
0 INDEX (UNIQUE SCAN) OF 'SINGLE_CUSTOMER_PK' (UNIQUE)********************************************************************************UPDATE VM_LIVE.ADDRESS_OCCUPANCY AO SET VISIBLE=1
WHERE
ADDRESS_OCCUPANCY_KEY = :b1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 39562 12.57 186.88 57285 124038 40726 39562
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 39562 12.57 186.88 57285 124038 40726 39562Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 39 (VM_LIVE) (recursive depth: 1)Rows Execution Plan
------- ---------------------------------------------------
0 UPDATE STATEMENT GOAL: CHOOSE
0 UPDATE OF 'ADDRESS_OCCUPANCY'
0 INDEX (UNIQUE SCAN) OF 'I_ADDRESS_OCCUPANCY_I4' (UNIQUE)********************************************************************************UPDATE VM_LIVE.SINGLE_CUSTOMER_HISTORY SCH SET VISIBLE=1
WHERE
ACXIOM_CUSTOMER_KEY = :b1 AND VERSION_NO = :b2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 39562 4.55 7.22 10897 118687 1 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 39562 4.55 7.22 10897 118687 1 1Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 39 (VM_LIVE) (recursive depth: 1)Rows Execution Plan
------- ---------------------------------------------------
0 UPDATE STATEMENT GOAL: CHOOSE
0 UPDATE OF 'SINGLE_CUSTOMER_HISTORY'
0 INDEX (UNIQUE SCAN) OF 'SINGLE_CUSTOMER_HISTORY_PK' (UNIQUE)********************************************************************************SELECT ACXIOM_CUSTOMER_KEY,VERSION_NO,ADDRESS_OCCUPANCY_KEY
FROM
CUSTOMER_DETAIL_SOURCE WHERE VISIBLE = 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 39562 1.51 2.04 392 39618 0 39562
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 39562 1.51 2.04 392 39618 0 39562Misses in library cache during parse: 0
Parsing user id: 39 (VM_LIVE) (recursive depth: 1)********************************************************************************OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTScall count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0Misses in library cache during parse: 0
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTScall count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 118686 32.63 593.08 124737 423810 81399 79125
Fetch 39562 1.51 2.04 392 39618 0 39562
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 158248 34.14 595.12 125129 463428 81399 118687Misses in library cache during parse: 0
Misses in library cache during execute: 14 user SQL statements in session.
0 internal SQL statements in session.
4 SQL statements in session.
3 statements EXPLAINed in this session.
********************************************************************************
Trace file: ora_349778.trc
Trace file compatibility: 7.03.02
Sort options: prsela exeela fchela
1 session in tracefile.
4 user SQL statements in trace file.
0 internal SQL statements in trace file.
4 SQL statements in trace file.
4 unique SQL statements in trace file.
3 SQL statements EXPLAINed using schema:
VM_LIVE.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
158286 lines in trace file.
The information contained in this communication is
confidential, is intended only for the use of the recipient
named above, and may be legally privileged. If the reader
of this message is not the intended recipient, you are
hereby notified that any dissemination, distribution or
copying of this communication is strictly prohibited.
If you have received this communication in error, please
re-send this communication to the sender and delete the
original message or any copy of it from your computer
system.