dba:rac_parallel_query
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Beide Seiten der vorigen RevisionVorhergehende Überarbeitung | Nächste ÜberarbeitungBeide Seiten der Revision | ||
dba:rac_parallel_query [2010/10/05 11:44] – gpipperr | dba:rac_parallel_query [2010/10/05 11:49] – gpipperr | ||
---|---|---|---|
Zeile 19: | Zeile 19: | ||
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 34: | ||
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 91: | ||
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 152: | ||
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