RE: Tkprof output

From: Christopher Spence <cspence_at_FuelSpot.com>
Date: Thu, 30 Aug 2001 10:51:13 -0700
Message-ID: <F001.0037DADB.20010830105900@fatcity.com>

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.
<FONT face=Arial color=#0000ff
size=2>Another helpful thing would to generate the TKPROF with Explan plans so you can see the rows per step and the paths chosen. <FONT face=Arial color=#0000ff
size=2> 
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 <FONT

face="Comic Sans MS" size=2>Oracle DBA <FONT face="Comic Sans MS" 
size=2>Phone: (978) 322-5744 <FONT face="Comic Sans MS" 
size=2>Fax:    (707) 885-2275 

Fuelspot <FONT

face="Comic Sans MS" size=2>73 Princeton Street <FONT 
face="Comic Sans MS" size=2>North, Chelmsford 01863 <FONT 
face="Comic Sans MS" size=2>  

  

  <FONT
  face=Tahoma size=2>-----Original Message-----From: Robertson Lee -   lerobe [mailto:lerobe_at_acxiom.co.uk] Sent: Thursday, August 30, 2001   11:56 AMTo: Multiple recipients of list ORACLE-LSubject:   Tkprof output
  Apologies for the
  length of the mail.
 

  This query is
  running for a mad amount of time, anyone any ideas.   <SPAN
  class=078354714-30082001> 
  Code and tkprof
  out put shown below.
  <SPAN
  class=078354714-30082001> 
  Huge
  TIA
  <SPAN
  class=078354714-30082001> 
  Lee (who must
  learn more about such things !!!)
  <SPAN
  class=078354714-30082001> 
  DECLARE CURSOR
  TEMP_CDS ISSELECT
  ACXIOM_CUSTOMER_KEY,      
  VERSION_NO,      
  ADDRESS_OCCUPANCY_KEYFROM  
  CUSTOMER_DETAIL_SOURCEWHERE  VISIBLE=1;
 

  COUNTER
  NUMBER(8);
 

  <SPAN
  class=078354714-30082001>BEGIN   COUNTER:=0;   FOR   I IN TEMP_CDS   LOOP
 

  <SPAN
  class=078354714-30082001>     UPDATE
  &SCHEMA..SINGLE_CUSTOMER
  SC            SET
  VISIBLE =
  1            WHERE
  ACXIOM_CUSTOMER_KEY=I.ACXIOM_CUSTOMER_KEY              AND   VERSION_NO        
  =I.VERSION_NO;
 

  <SPAN
  class=078354714-30082001>     UPDATE
  &SCHEMA..SINGLE_CUSTOMER_HISTORY
  SCH            SET
  VISIBLE =
  1            WHERE
  ACXIOM_CUSTOMER_KEY=I.ACXIOM_CUSTOMER_KEY              AND   VERSION_NO        
  =I.VERSION_NO;
 

  <SPAN
  class=078354714-30082001>     UPDATE
  &SCHEMA..ADDRESS_OCCUPANCY
  AO            SET
  VISIBLE =
  1            WHERE
  ADDRESS_OCCUPANCY_KEY = I.ADDRESS_OCCUPANCY_KEY;
 

  <SPAN
  class=078354714-30082001>      COUNTER := COUNTER +

  1;      IF (COUNTER = 
  50000)      
  THEN          

  COUNTER:=0;         
  COMMIT;      END IF;   END
  LOOP;   COMMIT;
  <SPAN
  class=078354714-30082001> 
  Sort options:
  prsela  exeela  fchela 

  ********************************************************************************count    

• number of times OCI procedure was executedcpu      = cpu time in seconds executing elapsed  = elapsed time in seconds executingdisk     = number of physical reads of buffers from diskquery    = number of buffers gotten for consistent readcurrent  = 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
   
  <SPAN class=078354714-30082001>call     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        0      0.00       0.00          0          0          0           0Execute  39562     15.51     398.98      56555     181085      40672       39562Fetch        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: 0Misses in library cache during execute: 1Optimizer goal: CHOOSEParsing user id: 39  (VM_LIVE)   (recursive depth: 1)
   
  <SPAN class=078354714-30082001>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)
   
  <SPAN class=078354714-30082001>********************************************************************************
   
  UPDATE VM_LIVE.ADDRESS_OCCUPANCY AO SET VISIBLE=1 WHERE ADDRESS_OCCUPANCY_KEY = :b1
   
  <SPAN class=078354714-30082001>call     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        0      0.00       0.00          0          0          0           0Execute  39562     12.57     186.88      57285     124038      40726       39562Fetch        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: 0Optimizer goal: CHOOSEParsing user id: 39  (VM_LIVE)   (recursive depth: 1)
   
  <SPAN class=078354714-30082001>Rows     Execution Plan-------  ---------------------------------------------------      0  UPDATE STATEMENT   GOAL: CHOOSE      0   UPDATE OF 'ADDRESS_OCCUPANCY'      0    INDEX (UNIQUE SCAN) OF 'I_ADDRESS_OCCUPANCY_I4' (UNIQUE)
   
  <SPAN class=078354714-30082001>********************************************************************************
   
  UPDATE VM_LIVE.SINGLE_CUSTOMER_HISTORY SCH SET VISIBLE=1 WHERE ACXIOM_CUSTOMER_KEY = :b1  AND VERSION_NO = :b2
   
  <SPAN class=078354714-30082001>call     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        0      0.00       0.00          0          0          0           0Execute  39562      4.55       7.22      10897     118687          1           1Fetch        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: 0Optimizer goal: CHOOSEParsing user id: 39  (VM_LIVE)   (recursive depth: 1)
   
  <SPAN class=078354714-30082001>Rows     Execution Plan-------  ---------------------------------------------------      0  UPDATE STATEMENT   GOAL: CHOOSE      0   UPDATE OF 'SINGLE_CUSTOMER_HISTORY'      0    INDEX (UNIQUE SCAN) OF 'SINGLE_CUSTOMER_HISTORY_PK' (UNIQUE)
   
  <SPAN class=078354714-30082001>********************************************************************************
   
  SELECT ACXIOM_CUSTOMER_KEY,VERSION_NO,ADDRESS_OCCUPANCY_KEY   FROM CUSTOMER_DETAIL_SOURCE  WHERE VISIBLE = 1
   
  <SPAN class=078354714-30082001>call     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        0      0.00       0.00          0          0          0           0Execute      0      0.00       0.00          0          0          0           0Fetch    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: 0Parsing user id: 39  (VM_LIVE)   (recursive depth: 1)
     
    <SPAN class=078354714-30082001> 
     
    <SPAN class=078354714-30082001>********************************************************************************
     
    OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
     
    <SPAN class=078354714-30082001>call     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        0      0.00       0.00          0          0          0           0Execute      0      0.00       0.00          0          0          0           0Fetch        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
     
    <SPAN class=078354714-30082001>call     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        0      0.00       0.00          0          0          0           0Execute 118686     32.63     593.08     124737     423810      81399       79125Fetch    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: 0Misses in library cache during execute: 1
     
    <SPAN class=078354714-30082001>    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.trcTrace file compatibility: 7.03.02Sort 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 isconfidential, is intended only for the use of the recipientnamed above, and may be legally privileged. If the reader of this message is not the intended recipient, you arehereby notified that any dissemination, distribution orcopying 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 computersystem.

Received on Thu Aug 30 2001 - 12:51:13 CDT