RE: what could cause a high elap value for the exec system call (for a select statement)?
Date: Wed, 4 Nov 2009 15:47:42 +0100
Message-ID: <4814386347E41145AAE79139EAA398980D33E9FC33_at_ws03-exch07.iconos.be>
Cary,
I did a summation of the e values for all fetch and exec calls done between the parsing of the problem statement and its execution call, and it was only 1.5 ms. So, if I understand correctly, the problem would be in the exec itself.
Is it normal that I don't see separate childs for the select statements inside the "select clause" ?
Regards,
Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge_at_uptime.be
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer
From: Cary Millsap [mailto:cary.millsap_at_method-r.com] Sent: woensdag 4 november 2009 15:17
To: D'Hooge Freek
Cc: mwf_at_rsiz.com; Daniel Fink; tim_at_evdbt.com; Brandon.Allen_at_OneNeck.com; Oracle-L_at_freelists.org Subject: Re: what could cause a high elap value for the exec system call (for a select statement)?
Freek,
The stuff you commented out from your trace file excerpt may contain your answer. Any dbcalls in there that are executed at recursive depth of 1 or greater (dep>0) are calls executed as children of the EXEC call you're worried about. It's in those trace file lines that you'll be able to determine whether the EXEC was expensive because of its own work, or if it's expensive because of the work performed by its children. All of the c and e time logged by those children are going to be rolled up (that is, double-counted) into the c and e time logged by that EXEC #39 (p, cr, and cu roll up, too). Whether it's the EXEC itself or its children is the first thing you need to know before you dig deeper.
Cary Millsap
Method R Corporation
http://method-r.com http://carymillsap.blogspot.com http://twitter.com/cary_millsap
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Nov 04 2009 - 08:47:42 CST