Sunday, February 27, 2022

Parallel Sessions Quandary in Oracle Database

 Whether should I use parallel query or not? I have this many CPUs or CPU cores or OCPUs, this much memory what should be the level of parallelism? These and many other confusions exist when it comes to parallel executions in oracle database. Following query can help in answering some of the questions:

col sid for 999999

col QC_SID for 999999

col QC_INST for 9

col username for a10

col operation_name for a20

col target for a20

col units for a10

col start_time for a18

Select

px.sid,

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,

decode(totalwork,0,0,round(sofar/totalwork*100)) pct_done,

units,

start_time,

round(totalwork/(sofar/((sysdate - start_time)*1440))) eta_min,

decode(px.qcinst_id, NULL ,s.sid ,px.qcsid) QC_SID,

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#(+)

and sofar <> totalwork

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

/


No comments: