| How to interpret the tkprof output [message #382623] |
Fri, 23 January 2009 04:26  |
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 #382816 is a reply to message #382623] |
Sun, 25 January 2009 05:32  |
michael_bialik
Messages: 621
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 |
 |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
