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.

Cary Millsap
Method R Corporation

http://method-r.com
http://carymillsap.blogspot.com
http://twitter.com/cary_millsap

On Wed, Nov 4, 2009 at 8:07 AM, D'Hooge Freek <Freek.DHooge_at_uptime.be>wrote:

> Mark,
>
> Am I correct to say that, for a select statement, the exec call includes
> all the work (except parsing) that needs to be done to construct the cursor
> and that the fetch call includes all the work that needs to be done to
> retrieve rows from that cursor?
>
>
> 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
>
>
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Mark W. Farnham
> Sent: woensdag 4 november 2009 14:47
> To: D'Hooge Freek; 'Daniel Fink'; tim_at_evdbt.com; Brandon.Allen_at_OneNeck.com
> Cc: 'Oracle-L_at_freelists.org'
> Subject: RE: what could cause a high elap value for the exec system call
> (for a select statement)?
>
> I'm pretty sure Tim meant that in the context a whole heck of a lotta work
> must occur before you know what the first row to return is.
>
> Another example is if you have a union (non-all) and the source datasets of
> the parts of the union don't have a provable joint subkey, then you have to
> do full projection of all the columns in the parts of the queries and sort
> the resultset for duplicate rejection before you return anything. Something
> like that with 1000 columns in the select list would be a really bad joke
> to
> play on a computer system.
>
> Please do correct me if I got that wrong, Tim.
>
> mwf
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l