并行执行计划脚本

Parallel Work

译文如下（原文附后）：
如果您可以执行一个查询，那么您可以通过查询dynamic performance view v$pq_tqstat(在您执行执行了一些并行工作的语句之后，该视图由会话填充)来发现并行执行从器之间的工作分布情况。下面是一个简单的脚本转储内容:

rem
rem Script:     pq_tqstat.sql
rem Dated:      March 2001
rem Author:     Jonathan Lewis
rem Purpose:    Report PX message passing after the event
rem
rem Last tested 
rem     12.1.0.2
rem     11.2.0.4
rem     10.2.0.5
rem      9.2.0.8
rem      8.1.7.4
rem
rem Notes:
rem Run your parallel query, then run this query immediately afterwards.
rem
 
set linesize 156
set trimspool on
set pagesize  24
set arraysize 25
 
column server_type format a15
column process     format a15
column instance    format 999
 
column row_share  format 990.00
column data_share format 990.00
 
break on dfo_number skip 1 on tq_id skip 1 on server_type
 
set null n/a
 
spool pq_tqstat
 
select
        dfo_number, tq_id, server_type, instance, process,
        num_rows, bytes,
        round(
                100 * ratio_to_report(num_rows) over (
                        partition by dfo_number, tq_id, server_type, instance
                ),2
        )       row_share,
        round(
                100 * ratio_to_report(bytes)    over (
                        partition by dfo_number, tq_id, server_type, instance
                ),2
        )       data_share,
        waits,
        timeouts,
        avg_latency
from
        v$pq_tqstat
order by
        dfo_number,
        tq_id,
        server_type desc,
        instance,
        process
;
 
spool off
复制

server_type上的顺序非常重要——在表队列中，它在“生产者”之前得到“巡游者”，在“消费者”之前得到“生产者”，而这正是活动必须执行的顺序。(有些版本的Oracle会有点混乱，将所有游骑兵分配到表队列0中;在旧版本中，Oracle填充结构的方式还有其他缺陷。)。
同样在我的网站上有更多的评论和一个示例的输出从一个旧版本的脚本,并在博客中最近的一个例子,使用相同的旧版本查询的调查的一个特征“TOP N”并行运行在12c。

原文：
Oracle Scratchpad
Parallel Work
If you can execute a query you can find out the distribution of work across parallel execution slaves by querying the dynamic performance view v$pq_tqstat which is populated by your session after you’ve executed a statement that did some parallel work. Here’s a simple script dumping the contents:

rem
rem Script: pq_tqstat.sql
rem Dated: March 2001
rem Author: Jonathan Lewis
rem Purpose: Report PX message passing after the event
rem
rem Last tested
rem 12.1.0.2
rem 11.2.0.4
rem 10.2.0.5
rem 9.2.0.8
rem 8.1.7.4
rem
rem Notes:
rem Run your parallel query, then run this query immediately afterwards.
rem

set linesize 156
set trimspool on
set pagesize 24
set arraysize 25

column server_type format a15
column process format a15
column instance format 999

column row_share format 990.00
column data_share format 990.00

break on dfo_number skip 1 on tq_id skip 1 on server_type

set null n/a

spool pq_tqstat

select
dfo_number, tq_id, server_type, instance, process,
num_rows, bytes,
round(
100 * ratio_to_report(num_rows) over (
partition by dfo_number, tq_id, server_type, instance
),2
) row_share,
round(
100 * ratio_to_report(bytes) over (
partition by dfo_number, tq_id, server_type, instance
),2
) data_share,
waits,
timeouts,
avg_latency
from
v$pq_tqstat
order by
dfo_number,
tq_id,
server_type desc,
instance,
process
;

spool off
The ordering on server_type is quite important – it gets “Rangers” before “Producers”, and “Producers” before “Consumers” in a table queue, and that’s exactly the order in which the activity has to go. (Some versions of Oracle will get a little messed up and assign all the Rangers to table queue zero; and there are other defects in the way Oracle populate the structure in older versions.)
There a similar note on my old website with a few more comments and a sample of output from an older version of the script, and a recent example on the blog of using the same older version of the query to investigate a feature of “Top N” running in parallel on 12c.