Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Tkprof output

Tkprof output

From: Robertson Lee - lerobe <lerobe_at_acxiom.co.uk>
Date: Thu, 30 Aug 2001 08:28:12 -0700
Message-ID: <F001.0037D0FC.20010830075534@fatcity.com> 







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


 

    



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 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 - 10:28:12 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US