REM
REM DBAToolZ NOTE:
REM	This script was obtained from DBAToolZ.com
REM	It's configured to work with SQL Directory (SQLDIR).
REM	SQLDIR is a utility that allows easy organization and
REM	execution of SQL*Plus scripts using user-friendly menu.
REM	Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM 
REM File:
REM 	s_pq_slave_sec.sql
REM
REM <SQLDIR_GRP>PQ</SQLDIR_GRP>
REM 
REM Author:
REM 	J.P.Lewis 
REM 
REM Purpose:
REM	<SQLDIR_TXT>
REM	WARNING:
REM	--------
REM		MUST BE RAN AS SYS creates a VIEW and public SYNONYM
REM	-------------------------------------------------------------------
REM	Improved view to replace v$pq_slave
rem     This script creates a view similar to the Oracle-supplied view
rem     v$pq_slave only a lot better.
rem	--
rem     It reports:
rem             messages sent and received split into local and remote
rem             times in seconds, and CPU time in hundredths
rem             the process address (v$process.addr) of the slave
rem
rem             the calling instance number
rem             the process (v$process.addr, v$session.paddr) of the caller
rem     
rem             An id for the slave
rem             the number of times that process has been started
rem             the number of times that pmon has cleaned up the process
rem             the number of times the process has been used
rem	--
rem     Note:
rem     Some of the stats (e.g. cpu_sec_cur) are not updated in real time.
rem     This means they are always zero.
rem	--
rem     Furthermore, some of the totals are per startup (cpu time), whilst
rem     some are for the full lifetime of the instance (messages sent etc.)
REM	</SQLDIR_TXT>
REM	
REM Usage:
REM	s_pq_slave_sec.sql
REM 
REM Example:
REM	s_pq_slave_sec.sql
REM
REM
REM History:
REM	03-13-1998	J.P.Lewis	Created
REM	08-01-2001	VMOGILEV	Added to DBATOOLZ library
REM
REM

set echo on

create or replace view v$pq_slave_sec as
select
        kxfpdpnum                                               id,
        kxfpdpnam                                               name,
        kxfpdppro                                               process,
        decode(bitand(kxfpdpflg, 16), 0, 'BUSY', 'IDLE')        status,
        decode(bitand(kxfpdpflg, 8), 0, 'NO', 'YES')        active,
        kxfpdpsta                                               started,
        kxfpdpcln                                               cleaned,
        kxfpdpcin                                               calling_inst,
        kxfpdpcpr                                               calling_paddr,
        kxfpdpses                                               sessions,
        floor(kxfpdpcit / 100)                                  idle_sec_cur,
        floor(kxfpdpcbt / 100)                                  busy_sec_cur,
        round(kxfpdpcct / 100,2)                                cpu_sec_cur,
        kxfpdpclsnt                                             local_sent_cur,
        kxfpdpcrsnt                                             remote_sent_cur,
        kxfpdpclrcv                                             local_recd_cur,
        kxfpdpcrrcv                                             remote_recd_cur,
        floor((kxfpdptit + kxfpdpcit) / 100)                    idle_sec_total,
        floor((kxfpdptbt + kxfpdpcbt) / 100)                    busy_sec_total,
        round((kxfpdptct + kxfpdpcct) / 100,2)                  cpu_sec_total,
        kxfpdptlsnt + kxfpdpclsnt                               local_sent_tot,
        kxfpdptrsnt + kxfpdpcrsnt                               remote_sent_tot,
        kxfpdptlrcv + kxfpdpclrcv                               local_recd_tot,
        kxfpdptrrcv + kxfpdpcrrcv                               remote_recd_tot
from x$kxfpdp
where bitand(kxfpdpflg, 8) != 0
;

create public synonym v$pq_slave_sec for sys.v$pq_slave_sec;