Lee,
This query seems suspect
UPDATE VM_LIVE.SINGLE_CUSTOMER_HISTORY SCH SET VISIBLE=1
WHERE
ACXIOM_CUSTOMER_KEY = :b1 AND VERSION_NO = :b2
because of this
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
It's reading a ton of blocks to operate on ONE record. What's the table structure here? What's the index structure? Cardinality?
The buffer gets in the other queries are suspect too. What's your blocksize? It's reading a ton of blocks to arrive at the result.
-----Original Message-----
From: Robertson Lee - lerobe [SMTP:[EMAIL PROTECTED]]
Sent: Thursday, August 30, 2001 11:56 AM
To: Multiple recipients of list ORACLE-L
Subject: Tkprof output
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;
�
COUNTER NUMBER(8);
�
BEGIN
�� 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;
�
���� 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������ 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)
�
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������ 39562
�
Misses 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���������� 1
�
Misses 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������ 39562
�
Misses in library cache during parse: 0
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
�
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
�
Misses in library cache during parse: 0
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.
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.
