JL Computer Consultancy

PQ Slaves - a better view of v$pq_slave

Pre- Aug 1997


Update: 11th June 1999: If you have parallel_min_servers less than parallel_max_servers, then some PQ_Slaves will end up shutting down. However, they leave their entries behind them in the X$ structure. I have modified the view by making the active/inactive value visible so that you can now review the work done by PQ slaves that are no longer visible.

To view information about the activity of currently active Parallel Query slaves, Oracle offers the view v$pq_slave. Unfortunately this is deficient in two or three ways. Some of the statistics (e.g. CPU time used) are reported to the minute, and there is no information built in to the view to help you connect parallel query slaves to the sessions (v$session) that are calling them.

There is no reason for this inadequacy, since all the relevant information is in the X$ object that underpins v$pq_slave, and the following script offers an alternative, more informative, layer on top of that object. The bad news is that the script has to be run by SYS because of the X$ object.

This view was created originally on Oracle 7.3.3, and has not been reviewed for Oracle 8.0

Update: 11th June 1999: If you have parallel_min_servers less than parallel_max_servers, then some PQ_Slaves will end up shutting down. However, they leave their entries behind them in the X$ structure. I have modified the view by making the active/inactive value visible so that you can now review the work done by PQ slaves that are no longer visible.

Back to Main Index of Topics


rem
rem     Script:        pq_slave_sec.sql
rem     Author:        J.P.Lewis
rem     Dated:         13-Mar-1998
rem     Purpose:       Improved view to replace v$pq_slave
rem
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     
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;

Back to Main Index of Topics