/* v$sqlarea/v$sql	*/
set lines 1500 pages 9999 
column sid format 9999 
column username format a15
column PARSING_SCHEMA_NAME format a15 
column SQL_EXEC_START for a21 
column sql_text format a50
column module format a35
select a.inst_id,a.sid,a.username,b.PARSING_SCHEMA_NAME,a.module,a.sql_id,a.sql_child_number child,b.plan_hash_value,to_char (a.sql_exec_start, 'dd-Mon-yyyy hh24:mi:ss') sql_exec_start,(sysdate-sql_exec_start)*24*60*60 SECS,b.rows_processed,a.status,substr(b.sql_text,1,50) sql_text  
from gv$session a,gv$sqlarea b 
where a.sql_hash_value = b.hash_value
and a.sql_address    = b.address
and  a.module not like '%emagent%'
and  a.module not like '%oraagent.bin%'
and  sql_text not like '%b.PARSING_SCHEMA_NAME%'
and a.username is not null 
order by a.status;

/* ASH	*/
column my_sid format 999
column my_ser format 99999
column my_state format a30
column my_blkr format 999
select to_char(a.sample_time, 'HH24:MI:SS') MY_TIME,a.session_id MY_SID,a.session_serial# MY_SER,
        DECODE(a.session_state, 'WAITING' ,a.event, a.session_state) MY_STATE,a.xid, a.sql_id,
        a.blocking_session MY_BLKR
from gv$active_session_history a, dba_users u
where u.user_id = a.user_id
and a.sql_id = '&sql_id'
and a.sample_time > SYSTIMESTAMP-(2/1440);

/* AWR	*/
set lines 1000 pages 9999
SELECT s.snap_id,TO_CHAR(s.begin_interval_time, 'DD-MON HH24:MI') snap_time,ss.sql_id,ss.plan_hash_value,  
 ss.ROWS_PROCESSED_TOTAL,
    ss.executions_delta execs,
   (ss.elapsed_time_delta/1000000)/DECODE(ss.executions_delta,0,1,ss.executions_delta) ela_per_exec,
   (ss.cpu_time_delta    /1000000)/DECODE(ss.executions_delta,0,1,ss.executions_delta) cpu_per_exec,
  ss.buffer_gets_delta  /DECODE(ss.executions_delta,0,1,ss.executions_delta) lio_per_exec,
  ss.disk_reads_delta   /DECODE(ss.executions_delta,0,1,ss.executions_delta) pio_per_exec
FROM dba_hist_snapshot s,
  dba_hist_sqlstat ss
WHERE ss.dbid          = s.dbid
AND ss.instance_number = s.instance_number
AND ss.snap_id         = s.snap_id
AND ss.sql_id          = nvl('&sql_id','4dqs2k5tynk61')
/* and ss.executions_delta > 0 */   
/* check executions_delta for 1 , if it is 0 just consider only rows proceesed and calculate total execution time = sum ( executions_delta 1 + executions_delta 0 ) */
ORDER BY s.snap_id;


select
s.sql_id,
sum(case
when begin_interval_time = to_date('14-nov-2017 1100','dd-mon-yyyy hh24mi') then s.executions_total
else 0
end) sum_after,
(sum(case
when begin_interval_time >= to_date('14-nov-2017 1100','dd-mon-yyyy hh24mi') then s.executions_total
else 0
end) -
sum(case
when begin_interval_time < to_date('14-nov-2017 1100','dd-mon-yyyy hh24mi') then s.executions_total
else 0
end)) difference
from 
   dba_hist_sqlstat s,
   dba_hist_snapshot sn
where 
   sn.begin_interval_time between to_date('05-nov-2017 0001','dd-mon-yyyy hh24mi') 
and 
   to_date('05-nov-2017 2359','dd-mon-yyyy hh24mi') 
and
   sn.snap_id=s.snap_id
group by 
   s.sql_id
order by 
   difference desc;
   
/* Time based	*/
select * from
(
   select 
      sql_id,
      sql_plan_hash_value,
      event,sql_exec_id,
      sql_exec_start,current_obj#,
      sql_plan_line_id, 
      sql_plan_operation,
      sql_plan_options,
      SUM (delta_read_io_requests) lio_read ,
      SUM (delta_read_io_bytes) pio_read , 
      count(*) count_1
   from 
      dba_hist_active_sess_history 
   where 
      sql_id='&sql_id'
   group by 
      sql_id,
      sql_plan_hash_value,
      event,sql_exec_id, 
      sql_exec_start,
      current_obj#,
      sql_plan_line_id, 
      sql_plan_operation,
      sql_plan_options
  )
  order by count_1 desc;