Sunday, September 4, 2016

Useful Queries for Long-Running Operations using v$session_longops and gv$session_longops

I have compiled a few useful queries to estimate the elapsed and remaining time for Long Running operations using using v$session_longops and gv$session_longops.
A description of v$session_longops and gv$session_longops:
SQL> desc v$session_longops;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SID                                                NUMBER
 SERIAL#                                            NUMBER
 OPNAME                                             VARCHAR2(64)
 TARGET                                             VARCHAR2(64)
 TARGET_DESC                                        VARCHAR2(32)
 SOFAR                                              NUMBER
 TOTALWORK                                          NUMBER
 UNITS                                              VARCHAR2(32)
 START_TIME                                         DATE
 LAST_UPDATE_TIME                                   DATE
 TIMESTAMP                                          DATE
 TIME_REMAINING                                     NUMBER
 ELAPSED_SECONDS                                    NUMBER
 CONTEXT                                            NUMBER
 MESSAGE                                            VARCHAR2(512)
 USERNAME                                           VARCHAR2(30)
 SQL_ADDRESS                                        RAW(8)
 SQL_HASH_VALUE                                     NUMBER
 SQL_ID                                             VARCHAR2(13)
 SQL_PLAN_HASH_VALUE                                NUMBER
 SQL_EXEC_START                                     DATE
 SQL_EXEC_ID                                        NUMBER
 SQL_PLAN_LINE_ID                                   NUMBER
 SQL_PLAN_OPERATION                                 VARCHAR2(30)
 SQL_PLAN_OPTIONS                                   VARCHAR2(30)
 QCSID                                              NUMBER

SQL> desc gv$session_longops;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 INST_ID                                            NUMBER
 SID                                                NUMBER
 SERIAL#                                            NUMBER
 OPNAME                                             VARCHAR2(64)
 TARGET                                             VARCHAR2(64)
 TARGET_DESC                                        VARCHAR2(32)
 SOFAR                                              NUMBER
 TOTALWORK                                          NUMBER
 UNITS                                              VARCHAR2(32)
 START_TIME                                         DATE
 LAST_UPDATE_TIME                                   DATE
 TIMESTAMP                                          DATE
 TIME_REMAINING                                     NUMBER
 ELAPSED_SECONDS                                    NUMBER
 CONTEXT                                            NUMBER
 MESSAGE                                            VARCHAR2(512)
 USERNAME                                           VARCHAR2(30)
 SQL_ADDRESS                                        RAW(8)
 SQL_HASH_VALUE                                     NUMBER
 SQL_ID                                             VARCHAR2(13)
 SQL_PLAN_HASH_VALUE                                NUMBER
 SQL_EXEC_START                                     DATE
 SQL_EXEC_ID                                        NUMBER
 SQL_PLAN_LINE_ID                                   NUMBER
 SQL_PLAN_OPERATION                                 VARCHAR2(30)
 SQL_PLAN_OPTIONS                                   VARCHAR2(30)
 QCSID                                              NUMBER

How to estimate the elapsed and remaining time for a long-running operation in Oracle?
Note the only difference in the two queries is v$session_longops VS gv$session_longops and the addition of the inst_id column to indicate the ID of the RAC instance.
RAC environments:
SELECT 
        serial#,
        inst_id,
        sid,
        opname,
        sofar,
        (totalwork) - (sofar) remaining_work,
        start_time,
        round(elapsed_seconds/60) elapsed_time_in_mins,
        round(time_remaining/60)  remaining_time_in_mins
   FROM gv$session_longops
 WHERE time_remaining > 0

 and SOFAR <> TOTALWORK;
 order by 6 desc;



Non-RAC environments:
SELECT 
        serial#,
        sid,
        opname,
        sofar,
        (totalwork) - (sofar) remaining_work,
        start_time,
        round(elapsed_seconds/60) elapsed_time_in_mins,
        round(time_remaining/60)  remaining_time_in_mins
   FROM v$session_longops
 WHERE time_remaining > 0

 and SOFAR <> TOTALWORK;
 order by 6 desc;

Very similarly, how to estimate the elapsed and remaining time for a long-running RMAN operation in Oracle?




RAC environments:
SELECT 
        serial#,
        inst_id,
        sid,
        opname,
        sofar,
        (totalwork) - (sofar) remaining_work,
        start_time,
        round(elapsed_seconds/60) elapsed_time_in_mins,
        round(time_remaining/60)  remaining_time_in_mins
   FROM gv$session_longops
 WHERE time_remaining > 0
 and opname like '%RMAN%'
 order by 6 desc;

Non-RAC environments:
SELECT 
        serial#,
        inst_id,
        sid,
        opname,
        sofar,
        (totalwork) - (sofar) remaining_work,
        start_time,
        round(elapsed_seconds/60) elapsed_time_in_mins,
        round(time_remaining/60)  remaining_time_in_mins
   FROM gv$session_longops
 WHERE time_remaining > 0
 and opname like '%RMAN%'

 and SOFAR <> TOTALWORK;
 order by 6 desc;

How to estimate the estimated remaining time for a long-running SQL query?
RAC:
select sql_id, totalwork, sofar, elapsed_seconds,time_remaining from gv$session_longops where sql_id in ('INSERT_SQL_ID_IN_HERE');

Non-RAC:
select sql_id, totalwork, sofar, elapsed_seconds,time_remaining from v$session_longops where sql_id in ('INSERT_SQL_ID_IN_HERE');





No comments:

Post a Comment