Knowing ETA for Parallel Queries in Oracle

 While running heavy, long-running and critical  production queries on the production Oracle databases especially in the cloud, knowing an approx ETA is a must. This is specially true in the case of parallel queries on large datasets.

Following query can help to ascertain that ETA:

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

/