===== 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:
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
Script für die Analyse:
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
/
==== 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)