cpu instensive query

Hi all,

I want to know which sql query in the database is consuming more cpu cycles resource ?

can any one help me out ?

thanks in advance
shashi

What OS?
What Oracle version?

It's pretty easy to drill down with OEM, i'm assuming you do not have OEM?

MH

high CPU usage SQLs are those which has high

parse_calls
buffer_gets

v$sql shows that (you might want to divide buffer_gets per executions since buffer_gets is cumulative)

hi, if in oracle 9i,
there is cpu_time in v$sql, and cpu% in statspack report.

Also you can monitor it from unix, like prstat/top/ps find the spid and check the sql.

Thanks all for the inputs

OS sun 2.7 E3500
Ram 2 GB
Dual CPU


Bit still I am not convienced yet