Re: what could cause a high elap value for the exec system call (for a select statement)?
- From: "Tim Gorman" <tim@xxxxxxxxx>
- To: "D'Hooge Freek" <Freek.DHooge@xxxxxxxxx>, mwf@xxxxxxxx, "'Daniel Fink'" <daniel.fink@xxxxxxxxxxxxxx>, tim@xxxxxxxxx, Brandon.Allen@xxxxxxxxxxx
- Date: Wed, 04 Nov 2009 14:59:12 +0000
D'Hooge,
The answer to your question is largely "yes", except I'd word it as "exec call
includes all work (after parse call constructs the cursor) to construct the
result set for the cursor".
Rows cannot be retrieved (fetched) at the start of a GROUP BY operation or an
ORDER BY operation for example, they can only be retrieved after those
operations complete. If there is a HAVING clause then there is additional
filtering to perform before the fetch phase can begin, etc. Whether that
sorting or hashing occurs in private process memory (PGA) or in temporary
segments is only a matter of elapsed time, but the EXEC phase won't complete
until any such operations (as well as recursive operations in their entirety,
as Cary pointed out) complete.
Hope this helps...
-Tim
-----Original Message-----
From: D'Hooge Freek [mailto:Freek.DHooge@xxxxxxxxx]
Sent: Wednesday, November 4, 2009 07:07 AM
To: mwf@xxxxxxxx, ''Daniel Fink'', tim@xxxxxxxxx, Brandon.Allen@xxxxxxxxxxx
Cc: 'Oracle-L@xxxxxxxxxxxxx'
Subject: RE: what could cause a high elap value for the exec system call (for a
select statement)?
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@xxxxxxxxx tel +32(0)3 451 23 82
http://www.uptime.be disclaimer: www.uptime.be/disclaimer -----Original
Message----- From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Mark W. Farnham Sent:
woensdag 4 november 2009 14:47 To: D'Hooge Freek; 'Daniel Fink'; tim@xxxxxxxxx;
Brandon.Allen@xxxxxxxxxxx Cc: 'Oracle-L@xxxxxxxxxxxxx' 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
Other related posts:
