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;
复制