74 lines
2.9 KiB
SQL
74 lines
2.9 KiB
SQL
-- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
|
|
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.
|
|
|
|
COL px_qcsid HEAD QC_SID FOR A13
|
|
|
|
PROMPT Show current Parallel Execution sessions in RAC cluster...
|
|
|
|
WITH sq AS (
|
|
SELECT
|
|
pxs.qcsid||','||pxs.qcserial# px_qcsid
|
|
, pxs.qcinst_id
|
|
, ses.username
|
|
, ses.sql_id
|
|
, pxs.degree
|
|
, pxs.req_degree
|
|
, COUNT(*) slaves
|
|
, COUNT(DISTINCT pxs.inst_id) inst_cnt
|
|
, CASE WHEN pxs.inst_id = 1 THEN 1 ELSE NULL END i01
|
|
, CASE WHEN pxs.inst_id = 2 THEN 1 ELSE NULL END i02
|
|
, CASE WHEN pxs.inst_id = 3 THEN 1 ELSE NULL END i03
|
|
, CASE WHEN pxs.inst_id = 4 THEN 1 ELSE NULL END i04
|
|
, CASE WHEN pxs.inst_id = 5 THEN 1 ELSE NULL END i05
|
|
, CASE WHEN pxs.inst_id = 6 THEN 1 ELSE NULL END i06
|
|
, CASE WHEN pxs.inst_id = 7 THEN 1 ELSE NULL END i07
|
|
, CASE WHEN pxs.inst_id = 8 THEN 1 ELSE NULL END i08
|
|
, CASE WHEN pxs.inst_id = 9 THEN 1 ELSE NULL END i09
|
|
, CASE WHEN pxs.inst_id = 10 THEN 1 ELSE NULL END i10
|
|
, CASE WHEN pxs.inst_id = 11 THEN 1 ELSE NULL END i11
|
|
, CASE WHEN pxs.inst_id = 12 THEN 1 ELSE NULL END i12
|
|
, CASE WHEN pxs.inst_id = 13 THEN 1 ELSE NULL END i13
|
|
, CASE WHEN pxs.inst_id = 14 THEN 1 ELSE NULL END i14
|
|
, CASE WHEN pxs.inst_id = 15 THEN 1 ELSE NULL END i15
|
|
, CASE WHEN pxs.inst_id = 16 THEN 1 ELSE NULL END i16
|
|
-- , LISTAGG ( TO_CHAR(pxs.inst_id) , ' ' ) WITHIN GROUP (ORDER BY pxs.inst_id) instances
|
|
FROM
|
|
gv$px_session pxs
|
|
, gv$session ses
|
|
, gv$px_process p
|
|
WHERE
|
|
ses.sid = pxs.sid
|
|
AND ses.serial# = pxs.serial#
|
|
AND p.sid = pxs.sid
|
|
AND pxs.inst_id = ses.inst_id
|
|
AND ses.inst_id = p.inst_id
|
|
--
|
|
AND pxs.req_degree IS NOT NULL -- qc
|
|
GROUP BY
|
|
pxs.qcsid||','||pxs.qcserial#
|
|
, pxs.qcinst_id
|
|
, ses.username
|
|
, ses.sql_id
|
|
--, pxs.inst_id
|
|
, pxs.degree
|
|
, pxs.req_degree
|
|
, CASE WHEN pxs.inst_id = 1 THEN 1 ELSE NULL END
|
|
, CASE WHEN pxs.inst_id = 2 THEN 1 ELSE NULL END
|
|
, CASE WHEN pxs.inst_id = 3 THEN 1 ELSE NULL END
|
|
, CASE WHEN pxs.inst_id = 4 THEN 1 ELSE NULL END
|
|
, CASE WHEN pxs.inst_id = 5 THEN 1 ELSE NULL END
|
|
, CASE WHEN pxs.inst_id = 6 THEN 1 ELSE NULL END
|
|
, CASE WHEN pxs.inst_id = 7 THEN 1 ELSE NULL END
|
|
, CASE WHEN pxs.inst_id = 8 THEN 1 ELSE NULL END
|
|
, CASE WHEN pxs.inst_id = 9 THEN 1 ELSE NULL END
|
|
, CASE WHEN pxs.inst_id = 10 THEN 1 ELSE NULL END
|
|
, CASE WHEN pxs.inst_id = 11 THEN 1 ELSE NULL END
|
|
, CASE WHEN pxs.inst_id = 12 THEN 1 ELSE NULL END
|
|
, CASE WHEN pxs.inst_id = 13 THEN 1 ELSE NULL END
|
|
, CASE WHEN pxs.inst_id = 14 THEN 1 ELSE NULL END
|
|
, CASE WHEN pxs.inst_id = 15 THEN 1 ELSE NULL END
|
|
, CASE WHEN pxs.inst_id = 16 THEN 1 ELSE NULL END
|
|
)
|
|
/
|
|
|