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

From: Tim Gorman <tim@xxxxxxxxx>
To: Freek.DHooge@xxxxxxxxx
Date: Tue, 03 Nov 2009 11:40:37 -0700

D'Hooge,

GROUP BY, ORDER BY, or FOR UPDATE OF clauses could easily cause hugetimes for the EXEC phase in a SELECT. Each of these clauses forces asignificant amount of processing before the first row is fetched. Thefirst two (GROUP/ORDER BY) might require large portions of the resultset to be stored in temporary segments, and FOR UPDATE OF could wait ona number of things, including indefinite waits on conflicting locks...


Not a complete list, but you get the idea... ;-)

Hope this helps...

-Tim



D'Hooge Freek wrote:

Hi,

When investigating a performance problem using sql trace for a client, I came 
across a (huge) select statement, for which the elap value in the exec call was 
more then 4 seconds.

EXEC 
#39:c=4263352,e=4204874,p=0,cr=66,cu=0,mis=1,r=0,dep=0,og=1,tim=1226849067232845

Does somebody know what can cause this? Are there any know bugs which can cause this behaviour (during tracing)?The tracing was done with both waits and binds enabled. The statement only had 2 bind variables.

The db version is 10.2.0.4 on OEL 4.7


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



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

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

References:
- what could cause a high elap value for the exec system call (for a select statement)?
  - From: D'Hooge Freek

Other related posts:

» what could cause a high elap value for the exec system call (for a select statement)?- D'Hooge Freek
» RE: what could cause a high elap value for the exec system call (for a select statement)?- Allen, Brandon
» Re: what could cause a high elap value for the exec system call (for a select statement)? - Tim Gorman
» Re: what could cause a high elap value for the exec system call (for a select statement)?- Daniel Fink
» RE: what could cause a high elap value for the exec system call (for a select statement)?- D'Hooge Freek
» RE: what could cause a high elap value for the exec system call (for a select statement)?- Mark W. Farnham
» RE: what could cause a high elap value for the exec system call (for a select statement)?- D'Hooge Freek
» Re: what could cause a high elap value for the exec system call (for a select statement)?- Cary Millsap
» RE: what could cause a high elap value for the exec system call (for a select statement)?- D'Hooge Freek
» Re: what could cause a high elap value for the exec system call (for a select statement)?- Tim Gorman
» RE: what could cause a high elap value for the exec system call (for a select statement)?- D'Hooge Freek
» RE: what could cause a high elap value for the exec system call (for a select statement)?- Mark W. Farnham
» RE: what could cause a high elap value for the exec system call (for a select statement)?- Ric Van Dyke
» Re: what could cause a high elap value for the exec system call (for a select statement)?- Tim Gorman
» RE: what could cause a high elap value for the exec system call (for a select statement)?- D'Hooge Freek
» RE: what could cause a high elap value for the exec system call (for a select statement)?- D'Hooge Freek