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
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" ?
Oracle Database Administrator
tel +32(0)3 451 23 82
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)?
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.
Method R Corporation