RAC中的並行查詢 DOP(Degree of Parallelism)

以下查詢顯示在執行並行查詢的 session, 和 DOP (degree of parallelism)

WITH px_session AS

  (SELECT qcsid,

    qcserial#,

    MAX (degree) degree,

    MAX (req_degree) req_degree,

    COUNT ( * ) nb_of_processes

  FROM gv$px_session p

  GROUP BY qcsid,

    qcserial#

  )

SELECT

  s.INST_ID,

  s.sid,

  s.username,

  degree,

  req_degree,

  nb_of_processes,

  s.sql_id,

  SUBSTR(sql_text,1,100)

FROM gv$session s

JOIN px_session p

ON (s.sid    = p.qcsid

AND s.serial# = p.qcserial#)

JOIN gv$sql SQL

ON (sql.sql_id      = s.sql_id

AND sql.child_number = s.sql_child_number);

To get an idea on what event the PX servers are waiting you can look at the EVENT column in V$SESSION or ASH views (V$ACTIVE_SESSION_HISTORY / DBA_HIST_ACTIVE_SESS_HISTORY) if the query is not running.

You can retrieve all the PX sessions for a statement by filtering on the Query Coordinator SID (QC_SESSION_ID column)