===== Parallel Query im RAC Umfeld =====

Oracle 10g \\

Per default dürfen Parallel Queries sich über das ganze Cluster erstecken. So können Parallel Slave Prozesse auf anderen Knoten auftauchen.\\ Hier tauchen oft dann erhebliche Performance Problem bzgl. der masiven Kommunikation über den Interconnet auf.\\

Mit Parallel Groups kann diese Verhalten gesteuert werden.\\
Pro Knoten im Cluster, im Beispiel 3 Stück, wird eine Instance group definiert und die Ausführung von parallelen Slave Prozesen auf diese Gruppe eingeschränkt.

Auszug aus der init.ora:
<code>
 GPI1.instance_groups=IGroupGPI1
 GPI1.parallel_instance_group=IGroupGPI1

 GPI2.instance_groups=IGroupGPI2
 GPI2.parallel_instance_group=IGroupGPI2

 GPI3.instance_groups=IGroupGPI3
 GPI3.parallel_instance_group=IGroupGPI3
</code>
Script für die Analyse:
<code parallel.sql>

col username for a12
col "QC SID" for A6
col "SID" for A6
col "QC/Slave" for A8
col "Req. DOP" for 9999
col "Actual DOP" for 9999
col "Slaveset" for A8
col "Slave INST" for A9
col "QC INST" for A6

set pages 300 lines 300

col wait_event format a30

SELECT   DECODE (px.qcinst_id
               , NULL, username
               , ' - ' || LOWER (SUBSTR (pp.server_name
                                       , LENGTH (pp.server_name) - 4
                                       , 4
                                        ))
                ) "Username"
       , DECODE (px.qcinst_id
               , NULL, 'QC'
               , '(Slave)'
                ) "QC/Slave"
       , TO_CHAR (px.server_set) "SlaveSet"
       , TO_CHAR (s.SID) "SID"
       , TO_CHAR (px.inst_id) "Slave INST"
       , DECODE (sw.state
               , 'WAITING', 'WAIT'
               , 'NOT WAIT'
                ) AS state
       , CASE sw.state
            WHEN 'WAITING'
               THEN SUBSTR (sw.event
                          , 1
                          , 30
                           )
            ELSE NULL
         END AS wait_event
       , DECODE (px.qcinst_id
               , NULL, TO_CHAR (s.SID)
               , px.qcsid
                ) "QC SID"
       , TO_CHAR (px.qcinst_id) "QC INST"
       , px.req_degree "Req. DOP"
       , px.DEGREE "Actual DOP"
    FROM gv$px_session px, gv$session s, gv$px_process pp, gv$session_wait sw
   WHERE px.SID = s.SID(+)
     AND px.serial# = s.serial#(+)
     AND px.inst_id = s.inst_id(+)
     AND px.SID = pp.SID(+)
     AND px.serial# = pp.serial#(+)
     AND sw.SID = s.SID
     AND sw.inst_id = s.inst_id
ORDER BY DECODE (px.qcinst_id
               , NULL, px.inst_id
               , px.qcinst_id
                )
       , px.qcsid
       , DECODE (px.server_group
               , NULL, 0
               , px.server_group
                )
       , px.server_set
       , px.inst_id
/

set pages 300 lines 300
col wait_event format a30

SELECT   sw.SID AS rcvsid
       , DECODE (pp.server_name
               , NULL, 'A QC'
               , pp.server_name
                ) AS rcvr
       , sw.inst_id AS rcvrinst
       , CASE sw.state
            WHEN 'WAITING'
               THEN SUBSTR (sw.event
                          , 1
                          , 30
                           )
            ELSE NULL
         END AS wait_event
       , DECODE (BITAND (p1, 65535)
               , 65535, 'QC'
               , 'P' || TO_CHAR (BITAND (p1, 65535), 'fm000')
                ) AS sndr
       , BITAND (p1, 16711680) - 65535 AS sndrinst
       , DECODE (BITAND (p1, 65535)
               , 65535, ps.qcsid
               , (SELECT SID
                    FROM gv$px_process
                   WHERE server_name = 'P' || TO_CHAR (BITAND (sw.p1, 65535), 'fm000')
                     AND inst_id = BITAND (sw.p1, 16711680) - 65535)
                ) AS sndrsid
       , DECODE (sw.state
               , 'WAITING', 'WAIT'
               , 'NOT WAIT'
                ) AS state
    FROM gv$session_wait sw, gv$px_process pp, gv$px_session ps
   WHERE sw.SID = pp.SID(+)
     AND sw.inst_id = pp.inst_id(+)
     AND sw.SID = ps.SID(+)
     AND sw.inst_id = ps.inst_id(+)
     AND p1text = 'sleeptime/senderid'
     AND BITAND (p1, 268435456) = 268435456
ORDER BY DECODE (ps.qcinst_id
               , NULL, ps.inst_id
               , ps.qcinst_id
                )
       , ps.qcsid
       , DECODE (ps.server_group
               , NULL, 0
               , ps.server_group
                )
       , ps.server_set
       , ps.inst_id
/

set pages 300 lines 300

col "Username" for a12
col "QC/Slave" for A8
col "Slaveset" for A8
col "Slave INST" for A9
col "QC SID" for A6
col "QC INST" for A6
col "operation_name" for A30
col "target" for A30

SELECT   DECODE (px.qcinst_id
               , NULL, username
               , ' - ' || LOWER (SUBSTR (pp.server_name
                                       , LENGTH (pp.server_name) - 4
                                       , 4
                                        ))
                ) "Username"
       , DECODE (px.qcinst_id
               , NULL, 'QC'
               , '(Slave)'
                ) "QC/Slave"
       , TO_CHAR (px.server_set) "SlaveSet"
       , TO_CHAR (px.inst_id) "Slave INST"
       , SUBSTR (opname
               , 1
               , 30
                ) operation_name
       , SUBSTR (target
               , 1
               , 30
                ) target
       , sofar
       , totalwork
       , units
       , start_time
       , TIMESTAMP
       , DECODE (px.qcinst_id
               , NULL, TO_CHAR (s.SID)
               , px.qcsid
                ) "QC SID"
       , TO_CHAR (px.qcinst_id) "QC INST"
    FROM gv$px_session px, gv$px_process pp, gv$session_longops s
   WHERE px.SID = s.SID AND px.serial# = s.serial# AND px.inst_id = s.inst_id AND px.SID = pp.SID(+) AND px.serial# = pp.serial#(+)
ORDER BY DECODE (px.qcinst_id
               , NULL, px.inst_id
               , px.qcinst_id
                )
       , px.qcsid
       , DECODE (px.server_group
               , NULL, 0
               , px.server_group
                )
       , px.server_set
       , px.inst_id
/ 
</code>


==== Weitere Informationen ====

  * http://scn.sap.com/community/oracle/blog/2014/02/10/oracle-troubleshooting-parallel-executions-px--what-the-heck-why-is-the-sql-not-executed-in-parallel


MetaLink:

  * Oracle Support Document 444164.1 (Tracing Parallel Execution with _px_trace. Part I)