Benutzer-Werkzeuge

Webseiten-Werkzeuge


dba:rac_parallel_query

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

Cookies helfen bei der Bereitstellung von Inhalten. Durch die Nutzung dieser Seiten erklären Sie sich damit einverstanden, dass Cookies auf Ihrem Rechner gespeichert werden. Weitere Information
"Autor: Gunther Pipperr"
dba/rac_parallel_query.txt · Zuletzt geändert: 2014/04/02 12:56 von Gunther Pippèrr