Using Perl to Monitor v$session_longops
Submitted by dave on Sat, 10/11/2008 - 21:25
Description:This script continuously watches and interprets the contents of the v$Session_LongOps view. One of the redeeming qualities of the Java Oracle Enterprise Manager is the progress bar that comes up when you are viewing the long operations of a session. I wanted this functionality but didn't want to wake up the big lug to get this info (it takes a long time to start up and navigate to the screen to see the single long operations progress bar). So I decided to write a short script to emulate that functionality but take it one step further and show me info about _all_ the longops currently running on an instance. This can be useful when you are performing long operations on your database instance like: o Exporting o Rebuilding indexes o Long running updates o Long running queries From Oracle MetaLink Note 180924.1: This view displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution, and more operations are added for every Oracle release. To monitor query execution progress, you must be using the cost-based optimizer and you must: o Set the TIMED_STATISTICS or SQL_TRACE parameter to TRUE o Gather statistics for your objects with the ANALYZE statement or the DBMS_STATS package
Running the Script:
perl WatchLongops.pl username/password@tnsnameHit Ctrl-c to stop the script.