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

GROUP BY, ORDER BY, or FOR UPDATE OF clauses could easily cause huge times for the EXEC phase in a SELECT. Each of these clauses forces a significant amount of processing before the first row is fetched. The first two (GROUP/ORDER BY) might require large portions of the result set to be stored in temporary segments, and FOR UPDATE OF could wait on a 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_at_uptime.be
> tel +32(0)3 451 23 82
> http://www.uptime.be
> disclaimer: www.uptime.be/disclaimer
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
>

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