@@header

/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display Standby RFS archive log fetch details
*  Version    : 
*  Parameters :                
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  22-NOV-13  Vishal Gupta  Added following columns in output. 
*                           Also added the totals at the bottom.
*                            - SID
*                            - Client Machine 
*                            - Log Received so far
*                            - Total Received so far
*                            - Average Speed
*  22-NOV-13  Vishal Gupta  Created
*
*/


/************************************
*  INPUT PARAMETERS
************************************/

DEFINE THREAD="&&1"  

set term off
COLUMN  _THREAD          NEW_VALUE  THREAD             NOPRINT

SELECT UPPER(DECODE('&&THREAD','','%','&&THREAD'))   "_THREAD"
FROM DUAL;
set term on


Prompt
Prompt ***********************************************
Prompt *  RFS ArchiveLog Fetch Summary By Thread
Prompt ***********************************************

COLUMN inst_id             HEADING "I#"                             FORMAT 99
COLUMN SID                 HEADING "SID"                            FORMAT 99999
COLUMN process             HEADING "Process"                        FORMAT a7
COLUMN pid                 HEADING "PID"                            FORMAT 999999
COLUMN status              HEADING "Status"                         FORMAT a15
COLUMN delay_mins          HEADING "Delay|Mins"                     FORMAT 9999
COLUMN client_process      HEADING "Client|Process"                 FORMAT a10
COLUMN client_pid          HEADING "Client|PID"                     FORMAT a10
COLUMN machine             HEADING "Client|Machine"                 FORMAT a10 TRUNCATE
COLUMN thread#             HEADING "Th#"                            FORMAT 999
COLUMN sequence#           HEADING "Seq#"                           FORMAT 99999999
COLUMN block#              HEADING "Block#"                         FORMAT 9999999999
COLUMN blocks              HEADING "Blocks"                         FORMAT 9999999999
COLUMN received_log        HEADING "Current|Logs|Received|(MB)"     FORMAT 9,999,999
COLUMN received_total      HEADING "Total|Logs|Received|(MB)"       FORMAT 9,999,999
COLUMN speed               HEADING "Avg|Speed|(KB/s)"               FORMAT 9,999,999
COLUMN known_agents        HEADING "Known|Agents"                   FORMAT 999999
COLUMN active_agents       HEADING "Active|Agents"                  FORMAT 999999

BREAK ON REPORT    

COMPUTE SUM LABEL 'Total' OF speed           FORMAT 99,999,999   ON REPORT 
COMPUTE SUM LABEL 'Total' OF received_log    FORMAT 99,999,999   ON REPORT 
COMPUTE SUM LABEL 'Total' OF received_total  FORMAT 99,999,999   ON REPORT 

SELECT m.process
     , m.inst_id
     , m.thread#
     , SUM(((m.block# - 1) * 512)/power(1024,2))    received_log
     , SUM((ss.value/power(1024,2)))                received_total
     , SUM(ROUND((ss.value/power(1024,1))/((sysdate-s.logon_time)*24*60*60)))   speed
  FROM gv$managed_standby m
       JOIN gv$process p ON m.inst_id = p.inst_id AND m.pid = p.spid
       JOIN gv$session s ON s.inst_id = p.inst_id AND s.paddr = p.addr
       JOIN v$statname sn ON sn.name = 'bytes received via SQL*Net from client'
       JOIN gv$sesstat ss ON ss.inst_id = s.inst_id AND ss.sid = s.sid AND sn.statistic# = ss.statistic# 
 WHERE m.process in ('RFS')
    AND m.thread# <> 0
GROUP BY m.process, m.inst_id, m.thread#   
ORDER BY m.process, m.inst_id, m.thread#   
;
 
Prompt
Prompt *********************************
Prompt *  RFS ArchiveLog Fetch Details 
Prompt *********************************

SELECT m.process
     , s.sid
     , m.inst_id
     --, m.pid
     , m.status
     --, m.client_process
     --, m.client_pid
     , s.machine
     , m.thread#
     , m.sequence#
     , m.block#
     , m.blocks
     , ((m.block# - 1) * 512)/power(1024,2)    received_log
     , (ss.value/power(1024,2))                received_total
     , ROUND((ss.value/power(1024,1))/((sysdate-s.logon_time)*24*60*60))   speed
  FROM gv$managed_standby m
       JOIN gv$process p ON m.inst_id = p.inst_id AND m.pid = p.spid
       JOIN gv$session s ON s.inst_id = p.inst_id AND s.paddr = p.addr
       JOIN v$statname sn ON sn.name = 'bytes received via SQL*Net from client'
       JOIN gv$sesstat ss ON ss.inst_id = s.inst_id AND ss.sid = s.sid AND sn.statistic# = ss.statistic# 
 WHERE 1=1
   AND m.process NOT IN ('MRP0','ARCH')
   AND m.status NOT IN ('IDLE')
   AND m.thread# <> 0
   AND ( CASE WHEN m.process = 'RFS' THEN TO_CHAR(m.thread#) ELSE '&&THREAD' END) LIKE  '&&THREAD'
ORDER BY m.process
          --, inst_id
          , m.thread#
          , m.sequence#
;



@@footer