Home » RDBMS Server » Performance Tuning » How to interpret the tkprof output (Oracle 9.2.0.8.0 - 64bit on HP-UX B.11.11 U)
How to interpret the tkprof output [message #382623] Fri, 23 January 2009 04:26 Go to next message
madugular
Messages: 3
Registered: February 2008
Location: India
Junior Member
I am running Oracle Warehouse Builder mapping in one of our servers. The map execution is taking very long time(approximately it loaded 250000 recs in 20 hours). It is suppose to load 2.3 million records. So I asked DBA to capture trace on the PID. DBA capture the trace for 35 mins. After running tkprof utility on the trace file below is the information from tkprof output.


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 8 0.00 0.00 0 0 0 0
Execute 449 72.33 344.23 525456 1337592 175 155
Fetch 441 0.05 0.07 0 1728 8 441
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 898 72.38 344.31 525456 1339320 183 596

Can any one help me in understanding this trace information?

I am attaching the entire tkprof output.
Re: How to interpret the tkprof output [message #382632 is a reply to message #382623] Fri, 23 January 2009 05:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you read the documentation about TKPROF and its output?

Regards
Michel
Re: How to interpret the tkprof output [message #382682 is a reply to message #382623] Fri, 23 January 2009 10:11 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
It shouldn't be that hard to interpret a report composed by just a few lines.

Just find the meaning of each word in the report and make some logic deductions considering your environment capabilities and your business process behavior.

Bye Alessandro
Re: How to interpret the tkprof output [message #382816 is a reply to message #382623] Sun, 25 January 2009 05:32 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Do you understand what is the meaning of hints used in your update statement and what is the result?

UPDATE /*+ APPEND PARALLEL(S_AUDIT_ITEM, DEFAULT, DEFAULT) */ "S_AUDIT_ITEM" 
  SET "CREATED" = :B17 , "CREATED_BY" = :B16 , "LAST_UPD" = :B15 , 
  "LAST_UPD_BY" = :B14 , "MODIFICATION_NUM" = :B13 , "CONFLICT_ID" = :B12 , 
  "BUSCOMP_NAME" = :B11 , "FIELD_NAME" = :B10 , "OPERATION_CD" = :B9 , 
  "RECORD_ID" = :B8 , "USER_ID" = :B7 , "OPERATION_DT" = :B6 , "NEW_VAL" = 
  :B5 , "OLD_VAL" = :B4 , "ODS_LAST_UPD_DTM" = :B3 , "ODS_LAST_UPD_BY" = :B2 
WHERE
 "ROW_ID" = :B1 RETURNING ROWID INTO :B0
?



Re: How to interpret the tkprof output [message #382826 is a reply to message #382816] Sun, 25 January 2009 07:29 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
michael_bialik wrote on Sun, 25 January 2009 12:32
Do you understand what is the meaning of hints used in your update statement and what is the result?

UPDATE /*+ APPEND PARALLEL(S_AUDIT_ITEM, DEFAULT, DEFAULT) */ "S_AUDIT_ITEM" 
  SET "CREATED" = :B17 , "CREATED_BY" = :B16 , "LAST_UPD" = :B15 , 
  "LAST_UPD_BY" = :B14 , "MODIFICATION_NUM" = :B13 , "CONFLICT_ID" = :B12 , 
  "BUSCOMP_NAME" = :B11 , "FIELD_NAME" = :B10 , "OPERATION_CD" = :B9 , 
  "RECORD_ID" = :B8 , "USER_ID" = :B7 , "OPERATION_DT" = :B6 , "NEW_VAL" = 
  :B5 , "OLD_VAL" = :B4 , "ODS_LAST_UPD_DTM" = :B3 , "ODS_LAST_UPD_BY" = :B2 
WHERE
 "ROW_ID" = :B1 RETURNING ROWID INTO :B0
?







Yes what would be the reason of a parallel hint on a single rowid access?

And by the way what could be the other logic of selecting rowids first ( I suppose that just because a query would be the only source for such information ) and then update each record one at the time.

Did you put any attention on your application design on database interaction? I don't really think so and that could be the reason why it's not performing as you want.

Before beginning the development of applications that make use of database connectivity it would be necessary to have at least a sort of basic background on that field.

I suggest you to read some manuals to learn and understand some basic requirements on database developing. If you'll do that there won't be a single database not performing as you want.

Bye Alessandro
Re: How to interpret the tkprof output [message #382849 is a reply to message #382826] Sun, 25 January 2009 19:42 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Looks to me like an update within a cursor loop. Take a look at this article, it explains why this is a bad idea for batch PL/SQL.

http://www.orafaq.com/node/1399

It also looks as though this code may have been generated from some other tool. Are you able to customise the code? Or is is system generated.

Ross Leishman
Previous Topic: high water mark (merged)
Next Topic: Not sure what is causing the slow performance
Goto Forum:
  


Current Time: Fri Dec 09 19:21:59 CST 2016

Total time taken to generate the page: 0.17616 seconds