A script showing explain plan for currently running queries

Pachot Franck http://www.dba-village.com/village/dvp_scripts.ScriptDetails?ScriptIdA=2182

Script:
SET linesize 1000 pagesize 0 feedback OFF

SELECT /* tag F354R334A56N47C687K645P6A628C7638H608O658758T8 */
DECODE(id,0,'
=== SID,SERIAL: ('||sid||','||serial#||') USER: '||username||' , ROWS_PROCESSED: '||rows_processed||' , BUFFER_GETS: '||buffer_gets||'
=== PROGRAM: '||program ||' , MODULE: ' || s.MODULE||'
'||'
'||sql_text|| '
'||'
EXPLAIN PLAN: ',LPAD(''||depth||'.'||position||') ',6+2*depth,' '))||
INITCAP(operation||DECODE(options,NULL,'',' '||options||'')) ||
DECODE(object_name,NULL,'',' '||object_owner||'.'||object_name)||
DECODE(OBJECT#,NULL,'',DECODE(optimizer,'ANALYZED','',' not analyzed'))||
DECODE(partition_start,NULL,'',' partition '||partition_start||'->'||partition_stop||' ')||
DECODE(cardinality,NULL,'',' card='||DECODE(SIGN(cardinality-1000), -1, cardinality||'',DECODE(SIGN(cardinality-1000000), -1, ROUND(cardinality/1000)||'K',DECODE(SIGN(cardinality-1000000000), -1, ROUND(cardinality/1000000)||'M',ROUND(cardinality/1000000000)||'G')))) ||
DECODE(cost,NULL,' ',' cost='||DECODE(SIGN(cost-10000000), -1, cost||'',DECODE(SIGN(cost-1000000000), -1, ROUND(cost/1000000)||'M',ROUND(cost/1000000000)||'G'))) ||
DECODE(bytes,NULL,' ',' bytes='||DECODE(SIGN(bytes-1024), -1, bytes||'',DECODE(SIGN(bytes-1048576), -1, ROUND(bytes/1024)||'K',DECODE(SIGN(bytes-1073741824), -1, ROUND(bytes/1048576)||'M',ROUND(bytes/1073741824)||'G'))))||
DECODE(cpu_cost,NULL,' ',' cpu_cost='||DECODE(SIGN(cpu_cost-10000000), -1, cpu_cost||'',DECODE(SIGN(cpu_cost-1000000000), -1, ROUND(cpu_cost/1000000)||'M',ROUND(cpu_cost/1000000000)||'G'))) ||
DECODE(io_cost,NULL,' ',' io_cost='||DECODE(SIGN(io_cost-10000000), -1, io_cost||'',DECODE(SIGN(io_cost-1000000000), -1, ROUND(io_cost/1000000)||'M',ROUND(io_cost/1000000000)||'G'))) ||
DECODE(temp_space,NULL,' ',' temp='||DECODE(SIGN(temp_space-1024), -1, temp_space||'',DECODE(SIGN(temp_space-1048576), -1, ROUND(temp_space/1024)||'K',DECODE(SIGN(temp_space-1073741824), -1, ROUND(temp_space/1048576)||'M',ROUND(temp_space/1073741824)||'G'))))||
'' text
FROM v$session s,v$sql q,v$sql_plan p
WHERE s.sql_hash_value=q.hash_value AND q.users_executing>0 AND q.hash_value=p.hash_value AND q.child_number=p.child_number
AND sql_text NOT LIKE '%F354R334A56N47C687K645P6A628C7638H608O658758T8%'
ORDER BY buffer_gets,s.sid,s.serial#,p.hash_value,p.child_number,p.id;

Sample Output:
=== SID,SERIAL: (90,33387) USER: APP , ROWS_PROCESSED: 0 , BUFFER_GETS: 152
=== PROGRAM: sqlplus.exe , MODULE: test.sql

INSERT /*+ append nologging */ INTO FACTS subpartition (DWH_P_333_NLGROC) (dwh_tpr_id, dwh_sho_id, ...

EXPLAIN PLAN: Insert Statement cost=267970

1.1) Load As Select
2.1) View card=22M cost=267970 bytes=18G
3.1) Window Sort card=22M cost=267970 bytes=18G io_cost=267970 temp=42G
4.1) View card=22M cost=3693 bytes=18G
5.1) Union-All
6.1) Table Access Full APP.FACTS partition 1179->1179 card=14M cost=1895 bytes=1G io_cost=1895
6.2) Table Access Full APP.FACTS partition 1173->1173 card=8M cost=1798 bytes=321M io_cost=1798