nums

set pages 1000
WITH nums as (
Select 'C'||Rownum rn
From dual Connect By Rownum < 24 )
select decode (rn, 'C1', 'Inst Id : ' || vs.inst_id ||' - '|| SYS_CONTEXT ('USERENV','DB_NAME'), 'C2', 'Module : ' || MODULE, 'C3', 'SQL Text : ' || substr(sql_text, 1, 40) , 'C4', 'No.of Plans : ' ||
(select count(distinct plan_hash_value)
from gv$sql_plan gvp
where sql_id= '&&sql_id' ), 'C5', 'Parsing Schema : ' || parsing_schema_name, 'C6', 'Remote Parse - Obj : ' || decode(remote,'Y','Yes') ||(
select decode(object_node,null,'NO','YES- ' ||object_node)
from v$sql_plan
where sqL_id ='&&sql_id' and operation='REMOTE' and rownum < 2), 'C7', 'Version Count : ' || vs.version_count, 'C8', 'Child Cursors : ' || (
select count(*)
from v$sql_shared_cursor
where sql_id ='&&sql_id' ), 'C9', 'Plan Hash : ' || vs.plan_hash_value, 'C10', 'Executions : ' || decode(vs.executions,0,vs.executions||' - Incomplete',vs.executions), 'C11', 'Invalidation : ' || vs.invalidations, 'C12','BufferRead : ' || round(buffer_gets/decode(executions,0,1,executions),2), 'C13','Disk Read : ' || round(disk_reads/decode(Executions,0,1,executions),2), 'C14','Parse Calls : ' || vs.parse_calls || ' - Exectution/Parse % = ' ||round((parse_calls/decode(executions,0,1,executions)) * 100,0), 'C15','First Load Time : ' || vs.FIRST_LOAD_TIME, 'C16','Last Active Time : ' || to_char(vs.last_active_time,'DD-MON-YYYY HH24:MI:SS'), 'C17','SQL_PROFILE : ' || SQL_PROFILE, 'C18','Smart Scans : ' || to_char(IO_CELL_OFFLOAD_ELIGIBLE_BYTES/1024/1024,'999,999')||' MB', 'C19','USER IO WAIT_TIME : ' || round((user_io_wait_time/decode(executions,0,1,executions))/1000000,0) || ' ('|| round(((user_io_wait_time/elapsed_time)*100),2)|| ' % of Total Wait)' , 'C20','CLUSTER_WAIT_TIME : ' || round((CLUSTER_WAIT_TIME/decode(executions,0,1,executions))/1000000,0) || ' ('|| round(((CLUSTER_WAIT_TIME/elapsed_time)*100),2)|| ' % of Total Wait)' , 'C21','CPU WAIT_TIME : ' || round((cpu_time/decode(executions,0,1,executions))/1000000,0) || ' ('|| round(((cpu_time/elapsed_time)*100),2)|| ' % of Total Wait)' ,
--'C18','Sessions Running    : ' || ( select wm_concat(sid) from v$session where sql_id ='&&sql_id' group by sql_id ),
'C22','Elapsed Time : ' || round((elapsed_time/decode(executions,0,1,executions))/(1000000),0), 'C23','Total Counted : ' || round((JAVA_EXEC_TIME+ PLSQL_EXEC_TIME + user_io_wait_time + CLUSTER_WAIT_TIME +CONCURRENCY_WAIT_TIME+ APPLICATION_WAIT_TIME +cpu_time )/ decode(executions,0,1,executions)/ (1000000),0) ) "DETAILS - SQL : &&sql_id"
from gv$sqlarea vs, nums
where vs.sql_id = '&&sql_id'
--and executions > 0
--and inst_id = userenv('INSTANCE')