Apologies 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
TIA
Lee (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;
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
LOOP
COUNTER:=0;
FOR I IN TEMP_CDS
LOOP
UPDATE
&SCHEMA..SINGLE_CUSTOMER
SC
SET VISIBLE = 1
WHERE ACXIOM_CUSTOMER_KEY=I.ACXIOM_CUSTOMER_KEY
AND VERSION_NO =I.VERSION_NO;
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;
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;
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;
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
********************************************************************************
********************************************************************************
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
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 39562
Misses 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)
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)
------- ---------------------------------------------------
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
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 39562
Misses in library
cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 39 (VM_LIVE) (recursive depth: 1)
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)
------- ---------------------------------------------------
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
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 1
Misses in library
cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 39 (VM_LIVE) (recursive depth: 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_HISTORY'
0 INDEX (UNIQUE SCAN) OF 'SINGLE_CUSTOMER_HISTORY_PK' (UNIQUE)
------- ---------------------------------------------------
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
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 39562
Misses in library
cache during parse: 0
Parsing user id: 39 (VM_LIVE) (recursive depth: 1)
Parsing user id: 39 (VM_LIVE) (recursive depth: 1)
********************************************************************************
OVERALL TOTALS FOR
ALL NON-RECURSIVE STATEMENTS
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 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
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 0
Misses in library
cache during parse: 0
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call
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 118687
------- ------ -------- ---------- ---------- ---------- ---------- ----------
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 118687
Misses in library
cache during parse: 0
Misses in library cache during execute: 1
Misses in library cache during execute: 1
4 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.
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.