RE: Tkprof output

From: Koivu, Lisa <lisa.koivu_at_efairfield.com>
Date: Thu, 30 Aug 2001 09:19:43 -0700
Message-ID: <F001.0037D302.20010830084715@fatcity.com>

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:lerobe_at_acxiom.co.uk]
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. Received on Thu Aug 30 2001 - 11:19:43 CDT