set linesize 10000

define sql_id = '&sqlid'
define child_number='&childnr'


select to_char(id,'9990')||' '||decode(level,1,'     ',to_char(level-2,'9990'))||' '||lpad(' ',2*(level-1))||p.operation||
decode(p.options, null,'',' ('||p.options||')')||
decode(p.object_name, null, '', ' OF '||p.object_name)||
decode(p.id,0,decode(p.optimizer, null, '',' Optimizer='||p.optimizer))||
decode(nvl(sys.dbms_lob.instr(p.other_xml,'dynamic_sampling'),0),0,'',decode(extractvalue(xmltype(p.other_xml),'/*/info[@type = "dynamic_sampling"]'),'','',' (Dynamic Sampling used)'))||
decode(nvl(sys.dbms_lob.instr(p.other_xml,'peeked_bind'),0),0,'',' (Bind Peeking used)')||
decode(nvl(sys.dbms_lob.instr(p.other_xml,'cardinality_feedback'),0),0,'',' (Cardinality Feedback used)')||
decode(p.cost, null, '',' (Cost='||p.cost||decode(p.cardinality, null, '',' Card='||p.cardinality)||decode(p.bytes, null, '',' Bytes='||p.bytes)||decode(p.cpu_cost,null,'',' CPU_Cost='||p.cpu_cost)||decode(p.io_cost,null,'',' IO_Cost='||p.io_cost)||decode(p.temp_space,null,'',' Temp_Space='||p.temp_space)|| decode(p.time,null,'',' Time='||p.time)||')')||
decode(p.distribution, null, '', ' '||p.distribution)||
decode(p.partition_id, null, '',' Part. Id: '|| p.partition_id)||
decode(p.partition_start, null, '',' Part. Range: '||p.partition_start||' - '||p.partition_stop)||
decode(s.starts,null,'',' (Runtime Stats - ')||
decode(s.starts,null,'','starts:'||s.starts)||
decode(s.elapsed_time,null,'',', ela:'||ltrim(to_char(round(s.elapsed_time/1000000,2),'999999999990.99')))||
decode(s.disk_reads,null,'',', dr:'||s.disk_reads)||
decode(s.disk_writes,null,'',', dwr:'||s.disk_writes)||
decode(s.cu_buffer_gets,null,'',', bg:'||(s.cu_buffer_gets + s.cr_buffer_gets))||
decode(s.output_rows,null,'',', rows:'||s.output_rows||')')||
decode(p.other_tag, null,decode(p.object_node, null,'',' ['||p.object_node||']'),' ['||p.other_tag||' -> '||p.object_node||']')||
decode(p.other, null, '',' '||decode('&truncate_other_to','',p.other,substr(p.other,1,to_char('&truncate_other_to'))))||
decode(w.last_memory_used,null,'',' (WA Stats - Policy:'||w.policy||', Last Mem(K):'||trunc(w.last_memory_used/1024)||
decode(w.estimated_optimal_size,null,'',', Opt Mem(K):'||trunc(w.estimated_optimal_size/1024))||
decode(w.estimated_onepass_size,null,'',', 1 Pass Mem(K):'||trunc(w.estimated_onepass_size/1024))||
decode(w.optimal_executions,null,'',', Opt/1/Mult:'||w.optimal_executions||'/'||w.onepass_executions||'/'||w.multipasses_executions)||
decode(w.max_tempseg_size,null,'',', Max Temp Seg(K):'||trunc(w.max_tempseg_size/1024))||
decode(w.last_tempseg_size,null,'',', Last Temp Seg(K):'||trunc(w.last_tempseg_size/1024))||')') plan_string
from sys.v_$sql_workarea w, sys.v_$sql_plan_statistics s, ( select address, hash_value, child_number, id, parent_id, cost, other_tag, operation, options, object_name, object_node, distribution, partition_id, partition_start, partition_stop, optimizer, cardinality, bytes, cpu_cost, io_cost, temp_space, other, time, other_xml from sys.v_$sql_plan
where
sql_id='&sql_id' and
child_number = '&child_number'
order by id) p -- due to Oracle problems
where 
p.address = w.address (+) and
p.hash_value=w.hash_value(+) and
p.id=w.operation_id(+) and
p.child_number = w.child_number(+) and
p.address = s.address (+) and
p.hash_value=s.hash_value(+) and
p.id=s.operation_id(+) and
p.child_number = s.child_number(+)
start with p.id = 0
connect by prior p.id = p.parent_id 
order siblings by p.id;

undefine sql_id
undefine child_number