-- E. Nossova, Product TuTool : www.tutool.de set heading on set pagesize 1000 set linesize 1000 set verify off set feedback off col "Sum. Sec. in Wait" format 99999999990.99 justify right col "Avg. Sec. in Wait" format 99999999990.99 justify right col "Min. Sec. in Wait" format 99999999990.99 justify right col "Max. Sec. in Wait" format 99999999990.99 justify right col "Event" format a64 /* shows wait statistics for parallel query sessions, input parameters: qcsid - SID of query coordinator, qcinst_id - instance id of query coordinator, default: current instance, requirement: TIMED_STATISTICS=TRUE */ define qcsid='&qcsid' define qcinst_id='&qcinst_id' select /*+ no_merge use_nl */ event "Event", sum(time_waited) / 100 "Sum. Sec. in Wait", avg(time_waited) / 100 "Avg. Sec. in Wait", min(time_waited) / 100 "Min. Sec. in Wait", max(time_waited) / 100 "Max. Sec. in Wait" from sys.gv_$session_event where (sid, inst_id) in (select sid, inst_id from sys.gv_$px_session where qcsid='&qcsid' and nvl(qcinst_id, inst_id) = nvl('&qcinst_id', userenv('instance')) and sid != qcsid or (sid = qcsid and inst_id != nvl(qcinst_id, inst_id))) group by event having sum(time_waited) > 0 order by 2 desc, 3 desc / undefine qcsid undefine qcinst_id set linesize 80