/* Sql_monitor report */
set pagesize 0 echo off timing off linesize 1000 trimspool on trim on long 2000000 longchunksize 2000000
select
DBMS_SQLTUNE.REPORT_SQL_MONITOR(
   sql_id=>'&sql_id',
   report_level=>'ALL',
   type=>'TEXT')
from dual;

/* Elapsed/CPU/Read/Write MB */
SELECT *
     FROM
       (SELECT status,
         --username,
         sql_id,
         sql_exec_id,
         TO_CHAR(sql_exec_start,'dd-mon-yyyy hh24:mi:ss') AS sql_exec_start,
         ROUND(elapsed_time/1000000)                      AS ""Elapsed (s)"",
         ROUND(cpu_time    /1000000)                      AS ""CPU (s)"",
         buffer_gets,
         ROUND(physical_read_bytes /(1024*1024)) AS ""Phys reads (MB)"",
         ROUND(physical_write_bytes/(1024*1024)) AS ""Phys writes (MB)""
       FROM gv$sql_monitor where sql_id='&sql_id' and inst_id=&inst_id
       ORDER BY elapsed_time DESC
       )
     WHERE rownum<=20; 

/* Each Layer time spend */
SELECT ROUND(elapsed_time    /1000000)     AS ""Elapsed (s)"",
     ROUND(cpu_time             /1000000,3)   AS ""CPU (s)"",
     ROUND(queuing_time         /1000000,3)   AS ""Queuing (s)"",
     ROUND(application_wait_time/1000000,3)   AS ""Appli wait (s)"",
     ROUND(concurrency_wait_time/1000000,3)   AS ""Concurrency wait (s)"",
     ROUND(cluster_wait_time    /1000000,3)   AS ""Cluster wait (s)"",
     ROUND(user_io_wait_time    /1000000,3)   AS ""User io wait (s)"",
     ROUND(physical_read_bytes  /(1024*1024)) AS ""Phys reads (MB)"",
     ROUND(physical_write_bytes /(1024*1024)) AS ""Phys writes (MB)"",
     buffer_gets                              AS ""Buffer gets"",
     ROUND(plsql_exec_time/1000000,3)         AS ""Plsql exec (s)"",
     ROUND(java_exec_time /1000000,3)         AS ""Java exec (s)""
FROM gv$sql_monitor
WHERE sql_id='&sql_id' and inst_id=&inst_id;

/*  Explain Plan waiting steps */
col PLAN FOR a150
SELECT
     RPAD('(' || p.plan_line_ID || ' ' || NVL(p.plan_parent_id,'0') || ')',8) || '|' ||
     RPAD(LPAD (' ', 2*p.plan_DEPTH) || p.plan_operation || ' ' || p.plan_options,60,'.') ||
     NVL2(p.plan_object_owner||p.plan_object_name, '(' || p.plan_object_owner|| '.' || p.plan_object_name || ') ', '') ||
     NVL2(p.plan_COST,'Cost:' || p.plan_COST,'') || ' ' ||
     NVL2(p.plan_bytes||p.plan_CARDINALITY,'(' || p.plan_bytes || ' bytes, ' || p.plan_CARDINALITY || ' rows)','') || ' ' ||
     NVL2(p.plan_partition_start || p.plan_partition_stop,' PStart:' ||  p.plan_partition_start || ' PStop:' || p.plan_partition_stop,'') ||
     NVL2(p.plan_time, p.plan_time || '(s)','') AS PLAN
     FROM gv$sql_plan_monitor p
     WHERE sql_id='&sql_id'
     ORDER BY p.plan_line_id, p.plan_parent_id;