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)

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14377/viewspace-2286979/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14377/viewspace-2286979/