Unsolved
This post is more than 5 years old
643 Posts
0
1464
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.
Anonymous
5 Practitioner
5 Practitioner
•
274.2K Posts
0
May 17th, 2012 01:00
with this view, at least you can have an estimate of the percent of work which has been completed so far. This is useful when I start an RMAN backup and would like to know the progress of the backup job.
zhaos2
643 Posts
0
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:
Anonymous
5 Practitioner
5 Practitioner
•
274.2K Posts
0
May 21st, 2012 19:00
below is a samlpe output of querying the v$session_longops view to monitor a analyzing schema job:
SQL> select OPNAME,SOFAR,TOTALWORK,START_TIME,ELAPSED_SECONDS,TIME_REMAINING,USERNAME,QCSID from v$session_longops where SOFAR<>TOTALWORK;
OPNAME SOFAR TOTALWORK START_TIM ELAPSED_SECONDS TIME_REMAINING USERNAME QCSID
---------------------------------------------------------------- ---------- ---------- --------- --------------- -------------- ------------------------------ ----------
Gather Schema Statistics 0 9 22-MAY-12 0 SOE2 0
Anonymous
5 Practitioner
5 Practitioner
•
274.2K Posts
0
May 21st, 2012 20:00
A quick update that there are 2 valuable views in 11g: V$SQL_MONITOR and V$SQL_PLAN_MONITOR which provide many statistics for a long run-time query/operations.
zhaos2
643 Posts
0
May 21st, 2012 22:00
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.