set linesize 999
set pagesize 50000
set feedback 1
set trimspool on
set trimout on

select instance_name,round((sysdate-startup_time),1) up_days
from v$instance
/

col sql_text format a700 trunc
col pct_total format 990
compute sum of sql_statements on report
break on report

select cpu_time_log10,sql_statements,cpu_time_rounded,round(cpu_time) cpu_time
      ,100*ratio_to_report(cpu_time) over() pct_total,round(sum(cpu_time) over (order by cpu_time_log10)) running_cpu_time
from (
        select trunc(log(10,((case when cpu_time <= 0 then 1 else cpu_time end))/1000000)) cpu_time_log10,count(*) sql_statements,power(10,trunc(log(10,((case when cpu_time <= 0 then 1 else cpu_time end))/1000000))) cpu_time_rounded,sum((case when cpu_time <= 0 then 1 else cpu_time end)/1000000) cpu_time
        from v$sqlarea
        where upper(sql_text) not like 'BEGIN%'
        and upper(sql_text) not like 'DECLARE%'
        group by trunc(log(10,((case when cpu_time <= 0 then 1 else cpu_time end))/1000000))
     ) a
order by a.cpu_time_log10
/

compute sum of cpu_seconds on report
break on report

select trunc(cpu_time/1000000) cpu_seconds,trunc(elapsed_time/1000000) eplapsed_seconds,round((sysdate-to_date(first_load_time,'rrrr-mm-dd/hh24:mi:ss'))*24) hours_in_cache,executions,sql_text
from v$sqlarea
where upper(sql_text) not like 'BEGIN%'
and upper(sql_text) not like 'DECLARE%'
and cpu_time/1000000 > 100
order by 1,2
/