RE: what could cause a high elap value for the exec system call (for a select statement)?

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Wed, 4 Nov 2009 10:57:10 -0500
Message-ID: <D5AB6602290048069E728298FFC4EB8B_at_rsiz.com>



well great usual answer. and pages approximately 98-101 of Cary's book with Jeff Holt takes you through "walking the clock."  

now, you are in fact talking about Oracle's extended trace output, and the right way to look at it is exactly the way Cary put it. If you want to dig deeper I think you have to write out your own harness in oci for what sqlplus usually does for you. In that context (and hey, I think I last wrote an oci program in 1990 and they had already changed all the calls since I was really good at it in 1986) I'm pretty sure if you toss your sql through an oparse (or whatever they've renamed it), then oexec (or whatever they've renamed that), Oracle will run off processing. If you never do an ofetch
(owtrt), the oexec will still do all the work to fill the first buffer area
you've given it. How the lines in the extended trace correlate is the way Cary describes it (or at least it was last time I looked, and that was before there were WAIT lines.). And unless you write an oci harness, I don't think you have a way to do the oexec without automagically bundling at least the first ofetch. But the way I think of it is that the oexec does all the work except delivering the rows back from the resultset. If you deferred the ofetch call until a while after the resultset had been dumped into the buffer area, then its elapsed time would be short - but I'm not sure how that would translate into extended trace lines. So while I'd be inclined to answer your question no and say the exec call does lots of the work you're thinking of as fetch, the way Cary has described it is the way to look at extended sql trace lines. (And don't forget, they may have changed the oexec/ofetch relationship too. I'm not going to look it up until I have to write an oci program.)  

mwf  


From: Cary Millsap [mailto:cary.millsap_at_method-r.com] Sent: Wednesday, November 04, 2009 9:17 AM To: Freek.DHooge_at_uptime.be
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://method-r.com/> 
http://carymillsap.blogspot.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
Received on Wed Nov 04 2009 - 09:57:10 CST

Original text of this message