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

From: Daniel Fink <daniel.fink_at_optimaldba.com>
Date: Tue, 03 Nov 2009 12:20:25 -0700
Message-ID: <4AF08279.8090106_at_optimaldba.com>



One thing that strikes me is that the c and e values are almost identical. This tells me that almost all (99.999%) of the time was spent in CPU. Brandon and Tim both have great information, but we need more details to rule out some issues.

Could you post the all relevant entries of the trace file? (You can remove the sql statement or change it so that you select "column a from table a")
Are there any WAITs between the PARSE and EXEC? What are the entries for the FETCHes?

Regards,
Daniel Fink

-- 
Daniel Fink

OptimalDBA    http://www.optimaldba.com
Oracle Blog   http://optimaldba.blogspot.com

Lost Data?    http://www.ora600.be/



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
>
-- Daniel Fink OptimalDBA http://www.optimaldba.com Oracle Blog http://optimaldba.blogspot.com Lost Data? http://www.ora600.be/ -- http://www.freelists.org/webpage/oracle-l
Received on Tue Nov 03 2009 - 13:20:25 CST

Original text of this message