UNSOLVED
Monitoring Oracle Long Running Operations
Long running sessions in oracle indicates the operations that run for longer than 6 seconds (in absolute time). They include many backup and recovery functions, statistics gathering, query execution etc. Based on newer oracle version many operations are added gradually.
To monitor long running operations in oracle the following two conditions must met.
1) Set the initialization parameter TIMED_STATISTICS or SQL_TRACE parameters to true.
2) Gather statistics for your objects with the ANALYZE statement or the DBMS_STATS package.
After you have met above two conditions you can easily monitor your long running operations by querying V$SESSION_LONGOPS view.
Thanks for your sharing, Jason!
These 2 views enabled by default for long running statements if parameter CONTROL_MANAGEMENT_PACK_ACCESS set to:
“DIAGNOSTIC+TUNING” and STATISTICS_LEVEL=ALL|TYPICAL
They can be enabled at statement level as well with /*+ MONITOR */ hint or disabled with /*+ NO_MONITOR */ hint.
zhaos2
2 Intern
•
643 Posts
682
0
Posted May 21st, 2012 18:00
Apart from backup job, each new version of Oracle adds several new types of built in long operations that are shown in v$session_longops. Some of them are: