CPU

CPU 

set pagesize 299
set lines 299
select STAT_NAME,to_char(VALUE) as VALUE  ,COMMENTS from v$osstat where stat_name  IN ('NUM_CPUS','NUM_CPU_CORES','NUM_CPU_SOCKETS')
union
select STAT_NAME,VALUE/1024/1024/1024 || ' GB'  ,COMMENTS from v$osstat where stat_name  IN ('PHYSICAL_MEMORY_BYTES')

++++
Session consuming lot of CPU:
set pagesize 299
set lines 299
select STAT_NAME,to_char(VALUE) as VALUE  ,COMMENTS from v$osstat where stat_name  IN ('NUM_CPUS','NUM_CPU_CORES','NUM_CPU_SOCKETS')
union
select STAT_NAME,VALUE/1024/1024/1024 || ' GB'  ,COMMENTS from v$osstat where stat_name  IN ('PHYSICAL_MEMORY_BYTES')

++++
Execution history of SQLID:
select a.instance_number inst_id, a.snap_id,a.plan_hash_value, to_char(begin_interval_time,'dd-mon-yy hh24:mi') btime, abs(extract(minute from (end_interval_time-begin_interval_time)) + extract(hour from (end_interval_time-begin_interval_time))*60 + extract(day from (end_interval_time-begin_interval_time))*24*60) minutes,
executions_delta executions, round(ELAPSED_TIME_delta/1000000/greatest(executions_delta,1),4) "avg duration (sec)" from dba_hist_SQLSTAT a, dba_hist_snapshot b
where sql_id='&sql_id' and a.snap_id=b.snap_id
and a.instance_number=b.instance_number
order by snap_id desc, a.instance_number;