dba:rac_parallel_query
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Beide Seiten der vorigen RevisionVorhergehende ÜberarbeitungNächste Überarbeitung | Vorhergehende ÜberarbeitungLetzte ÜberarbeitungBeide Seiten der Revision | ||
dba:rac_parallel_query [2010/10/05 11:44] – gpipperr | dba:rac_parallel_query [2012/03/21 21:49] – gpipperr | ||
---|---|---|---|
Zeile 1: | Zeile 1: | ||
===== Parallel Query im RAC Umfeld ===== | ===== 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.\\ | 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.\\ | ||
Zeile 19: | Zeile 21: | ||
Script für die Analyse: | Script für die Analyse: | ||
<code parallel.sql> | <code parallel.sql> | ||
+ | |||
col username for a12 | col username for a12 | ||
col "QC SID" for A6 | col "QC SID" for A6 | ||
Zeile 33: | Zeile 36: | ||
col wait_event format a30 | col wait_event format a30 | ||
- | select | + | SELECT |
- | decode(px.qcinst_id, | + | , NULL, username |
- | ' - '||lower(substr(pp.SERVER_NAME, | + | , ' - ' || LOWER (SUBSTR |
- | length(pp.SERVER_NAME)-4,4) ) )" | + | , LENGTH |
- | decode(px.qcinst_id, | + | , 4 |
- | to_char( px.server_set) " | + | |
- | to_char(s.sid) " | + | |
- | to_char(px.inst_id) "Slave INST", | + | , DECODE |
- | decode(sw.state,' | + | , NULL, ' |
- | case | + | , ' |
- | decode(px.qcinst_id, | + | |
- | to_char(px.qcinst_id) "QC INST", | + | , TO_CHAR |
- | px.req_degree "Req. DOP", | + | , TO_CHAR |
- | px.degree | + | , TO_CHAR |
- | from gv$px_session px, | + | , DECODE |
- | gv$session s , | + | , ' |
- | gv$px_process pp, | + | , 'NOT WAIT' |
- | gv$session_wait sw | + | |
- | where px.sid=s.sid (+) | + | , CASE sw.state |
- | and px.serial# | + | |
- | and px.inst_id = s.inst_id(+) | + | THEN SUBSTR |
- | and px.sid = pp.sid (+) | + | |
- | and px.serial# | + | |
- | and sw.sid = s.sid | + | ) |
- | and sw.inst_id = s.inst_id | + | |
- | order by | + | END AS wait_event |
- | decode(px.QCINST_ID, NULL, px.INST_ID, px.QCINST_ID), | + | , DECODE |
- | px.QCSID, | + | , NULL, TO_CHAR |
- | | + | , px.qcsid |
- | px.SERVER_SET, | + | |
- | px.INST_ID | + | , TO_CHAR |
+ | , px.req_degree "Req. DOP" | ||
+ | , px.DEGREE | ||
+ | | ||
+ | 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 | ||
+ | , NULL, px.inst_id | ||
+ | , px.qcinst_id | ||
+ | | ||
+ | , px.qcsid | ||
+ | , DECODE | ||
+ | , NULL, 0 | ||
+ | , px.server_group | ||
+ | | ||
+ | , px.server_set | ||
+ | , px.inst_id | ||
/ | / | ||
Zeile 69: | Zeile 93: | ||
col wait_event format a30 | col wait_event format a30 | ||
- | select | + | SELECT |
- | | + | , DECODE |
- | | + | , NULL, 'A QC' |
- | NULL, 'A QC', | + | , pp.server_name |
- | pp.server_name) | + | |
- | sw.inst_id | + | , sw.inst_id |
- | case | + | , CASE sw.state |
- | | + | |
- | | + | THEN SUBSTR |
- | ' | + | |
- | | + | |
- | | + | ) |
- | | + | |
- | | + | END AS wait_event |
- | sid | + | , DECODE |
- | from | + | , 65535, ' |
- | | + | , ' |
- | where | + | |
- | | + | , BITAND |
- | inst_id = bitand(sw.p1, 16711680) - 65535) | + | , DECODE |
- | ) as SNDRSID, | + | , 65535, ps.qcsid |
- | decode(sw.state,' | + | , (SELECT SID |
- | from | + | |
- | gv$session_wait sw, | + | WHERE server_name = ' |
- | | + | AND inst_id = BITAND |
- | | + | ) AS sndrsid |
- | where | + | , DECODE |
- | | + | , ' |
- | sw.inst_id = pp.inst_id (+) and | + | , 'NOT WAIT' |
- | sw.sid = ps.sid (+) and | + | ) AS state |
- | sw.inst_id = ps.inst_id (+) and | + | |
- | p1text | + | WHERE sw.SID = pp.SID(+) |
- | | + | AND sw.inst_id = pp.inst_id(+) |
- | order by | + | AND sw.SID = ps.SID(+) |
- | decode(ps.QCINST_ID, NULL, ps.INST_ID, ps.QCINST_ID), | + | AND sw.inst_id = ps.inst_id(+) |
- | ps.QCSID, | + | AND p1text = ' |
- | | + | AND BITAND |
- | ps.SERVER_SET, | + | ORDER BY DECODE |
- | ps.INST_ID | + | , NULL, ps.inst_id |
+ | , ps.qcinst_id | ||
+ | | ||
+ | , ps.qcsid | ||
+ | , DECODE | ||
+ | , NULL, 0 | ||
+ | , ps.server_group | ||
+ | | ||
+ | , ps.server_set | ||
+ | , ps.inst_id | ||
/ | / | ||
Zeile 121: | Zeile 154: | ||
col " | col " | ||
- | select | + | SELECT |
- | decode(px.qcinst_id, | + | , NULL, username |
- | ' - '||lower(substr(pp.SERVER_NAME, | + | , ' - ' || LOWER (SUBSTR |
- | length(pp.SERVER_NAME)-4,4) ) )" | + | , LENGTH |
- | decode(px.qcinst_id, | + | , 4 |
- | to_char( px.server_set) " | + | |
- | to_char(px.inst_id) "Slave INST", | + | |
- | substr(opname, | + | , DECODE |
- | substr(target, | + | , NULL, ' |
- | sofar, | + | , ' |
- | totalwork, | + | |
- | units, | + | , TO_CHAR |
- | start_time, | + | , TO_CHAR |
- | timestamp, | + | , SUBSTR |
- | decode(px.qcinst_id, | + | , 1 |
- | to_char(px.qcinst_id) "QC INST" | + | , 30 |
- | from gv$px_session px, | + | |
- | gv$px_process pp, | + | , SUBSTR |
- | gv$session_longops s | + | , 1 |
- | where px.sid=s.sid | + | , 30 |
- | and px.serial# | + | |
- | and px.inst_id = s.inst_id | + | |
- | and px.sid = pp.sid (+) | + | |
- | and px.serial# | + | |
- | order by | + | |
- | decode(px.QCINST_ID, NULL, px.INST_ID, px.QCINST_ID), | + | |
- | px.QCSID, | + | , DECODE |
- | | + | , NULL, TO_CHAR |
- | px.SERVER_SET, | + | , px.qcsid |
- | px.INST_ID | + | |
+ | , TO_CHAR | ||
+ | | ||
+ | WHERE px.SID = s.SID AND px.serial# = s.serial# | ||
+ | ORDER BY DECODE | ||
+ | , NULL, px.inst_id | ||
+ | , px.qcinst_id | ||
+ | | ||
+ | , px.qcsid | ||
+ | , DECODE | ||
+ | , NULL, 0 | ||
+ | , px.server_group | ||
+ | | ||
+ | , px.server_set | ||
+ | , px.inst_id | ||
/ | / | ||
</ | </ |
dba/rac_parallel_query.txt · Zuletzt geändert: 2014/04/02 12:56 von gpipperr