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;