sql_id_details

col PARSE_SCHEMA for a15
col ELATIME for a15
col db_name for a15
col sqlplan for a15
col CPUTIME for a15
set lin 450

select sys_context('userenv','DB_NAME')||'-'|| ins DB_NAME, sql_id, executions, plan_hash_value, parse_schema, plans, bufRdPerExe "BufRd-1Exe", PhyRdPerExe, SmartIO, (case when elatime/1000000 > 1 then round(elatime/1000000,0) ||' Sec'
--    when  elatime/1000000   > 60  then to_char(to_date(elatime/1000000),'SSSSS', 'MI "Minutes" SS "secs"'')
else round(elatime/1000,2) || ' Ms' end ) ElaTime, (case when cputime/1000000 > 1 then round(cputime/1000000,0) ||' Sec' else round(cputime/1000) || ' Ms' end ) cputime, version ver_cnt ,sqlplan
from (
select sql_id, inst_id ins, executions , plan_hash_value, PARSING_SCHEMA_NAME parse_schema, (
select count(distinct plan_hash_value)
from gv$sql_plan gvp
where sql_id = '&&sql_id' and gvp.inst_id = gvs.inst_id) Plans,
--( select count(*) from gv$sqlarea gva  where plan_hash_value = gvs.plan_hash_value and gva.inst_id = gvs.inst_id) sqlids,
round(buffer_gets/decode(executions,0,1,executions),0) BufRdPerExe, round(disk_reads/decode(executions,0,1,executions),0) PhyRdPerExe, round(IO_CELL_OFFLOAD_ELIGIBLE_BYTES/decode(executions,0,1,executions)/1024/1024,0) SmartIO, elapsed_time/decode(executions,0,1,executions) elatime, round(cpu_time/decode(executions,0,1,executions)) cputime , round(parse_calls/decode(executions,0,1,executions) * 100,0) parse_per, VERSION_COUNT Version,decode(sql_profile,null,SQL_PLAN_BASELINE,sql_profile) sqlplan
from gv$sqlarea gvs
where sql_Id = '&&sql_id' order by inst_id, last_active_time desc )