JL Computer Consultancy

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.

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;