dba:rac_parallel_query
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Beide Seiten der vorigen RevisionVorhergehende ÜberarbeitungNächste Überarbeitung | Vorhergehende ÜberarbeitungNächste ÜberarbeitungBeide Seiten der Revision | ||
dba:rac_parallel_query [2010/10/05 11:40] – gpipperr | dba:rac_parallel_query [2010/10/05 11:49] – gpipperr | ||
---|---|---|---|
Zeile 16: | Zeile 16: | ||
| | ||
| | ||
+ | </ | ||
+ | Script für die Analyse: | ||
+ | <code parallel.sql> | ||
+ | |||
+ | col username for a12 | ||
+ | col "QC SID" for A6 | ||
+ | col " | ||
+ | col " | ||
+ | col "Req. DOP" for 9999 | ||
+ | col " | ||
+ | col " | ||
+ | col "Slave INST" for A9 | ||
+ | col "QC INST" for A6 | ||
+ | |||
+ | set pages 300 lines 300 | ||
+ | |||
+ | col wait_event format a30 | ||
+ | |||
+ | SELECT | ||
+ | , NULL, username | ||
+ | , ' - ' || LOWER (SUBSTR (pp.server_name | ||
+ | , LENGTH (pp.server_name) - 4 | ||
+ | , 4 | ||
+ | )) | ||
+ | ) " | ||
+ | , DECODE (px.qcinst_id | ||
+ | , NULL, ' | ||
+ | , ' | ||
+ | ) " | ||
+ | , TO_CHAR (px.server_set) " | ||
+ | , TO_CHAR (s.SID) " | ||
+ | , TO_CHAR (px.inst_id) "Slave INST" | ||
+ | , DECODE (sw.state | ||
+ | , ' | ||
+ | , 'NOT WAIT' | ||
+ | ) AS state | ||
+ | , CASE sw.state | ||
+ | WHEN ' | ||
+ | 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 " | ||
+ | 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 | ||
+ | , DECODE (pp.server_name | ||
+ | , NULL, 'A QC' | ||
+ | , pp.server_name | ||
+ | ) AS rcvr | ||
+ | , sw.inst_id AS rcvrinst | ||
+ | , CASE sw.state | ||
+ | WHEN ' | ||
+ | THEN SUBSTR (sw.event | ||
+ | , 1 | ||
+ | , 30 | ||
+ | ) | ||
+ | ELSE NULL | ||
+ | END AS wait_event | ||
+ | , DECODE (BITAND (p1, 65535) | ||
+ | , 65535, ' | ||
+ | , ' | ||
+ | ) AS sndr | ||
+ | , BITAND (p1, 16711680) - 65535 AS sndrinst | ||
+ | , DECODE (BITAND (p1, 65535) | ||
+ | , 65535, ps.qcsid | ||
+ | , (SELECT SID | ||
+ | FROM gv$px_process | ||
+ | WHERE server_name = ' | ||
+ | AND inst_id = BITAND (sw.p1, 16711680) - 65535) | ||
+ | ) AS sndrsid | ||
+ | , DECODE (sw.state | ||
+ | , ' | ||
+ | , '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 = ' | ||
+ | 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 " | ||
+ | col " | ||
+ | col " | ||
+ | col "Slave INST" for A9 | ||
+ | col "QC SID" for A6 | ||
+ | col "QC INST" for A6 | ||
+ | col " | ||
+ | col " | ||
+ | |||
+ | SELECT | ||
+ | , NULL, username | ||
+ | , ' - ' || LOWER (SUBSTR (pp.server_name | ||
+ | , LENGTH (pp.server_name) - 4 | ||
+ | , 4 | ||
+ | )) | ||
+ | ) " | ||
+ | , DECODE (px.qcinst_id | ||
+ | , NULL, ' | ||
+ | , ' | ||
+ | ) " | ||
+ | , TO_CHAR (px.server_set) " | ||
+ | , 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 | ||
+ | / | ||
</ | </ |
dba/rac_parallel_query.txt · Zuletzt geändert: 2014/04/02 12:56 von gpipperr