126 lines
4.9 KiB
SQL
126 lines
4.9 KiB
SQL
@@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
|