Thursday, May 29, 2008

Oracle longops

Oracle has so many amazing features, and one of them I learned about yesterday was longops. It keeps track of all long operations within a database. You can view them like this (with the associated SQL):


select
vl.*, vsql.sql_fulltext
from
v$session_longops vl,
v$sqlarea vsql
where
vl.sql_id = vsql.sql_id
AND
vl.sql_id = vsql.sql_id
ORDER BY
vl.elapsed_seconds desc
;



Now if you want to create an alert to let you know when there are current long operations in your system you can do something like this:


select
vl.*, vsql.sql_fulltext
from
v$session_longops vl,
v$sqlarea vsql
where
vl.sql_id = vsql.sql_id
AND
vl.username = 'SOMEUSER'
AND
vl.time_remaining > 30
AND
vl.elapsed_seconds > 30
;



This will tell you if you have any current long operations that have been going for longer than 30 seconds, and have more than 30 seconds left to go.

You will likely want to use the vl.username where statement to filter out system events.

Thanks to Russ for this query.

3 comments:

Anonymous said...

> Little tidbits of technical
> knowledge that I do not want
> to loose.

Not Loose. Lose. :-)

Tor Flatebo said...

Ouch. It's funny how much I correct my wife's grammar and I make a mistake like that! Thanks for the heads up.

Anonymous said...

That anonymous guy is very clearly a 'looser' :).. oops, i meant 'loser' :)