/*
 * Title  : ash_top_sql.sql
 * Author : EXADIME LLC
 * Created: 16-Sep-2025
 * Updated: 16-Sep-2025
 * Release: 1.0.0
 * Purpose: Shows top SQL by elapsed time including SQL text captured in ASH.
 * Comment: Adjust _ash_size to flush ASH data into historical tables.
 * History: N/A
 */

start oracle.ini

set echo off
set feedback off
set serverout on
set termout on

set linesize 250
set pagesize 0
set pause off
set long 80

set heading on
set headsep on
set underline on
set colsep " "

set define on
set verify off
set escape off
set embedded on

set wrap off

col rownum		format 999		heading "#"
col time		format a18		heading "Time"
col inst_id		format 999		heading "Instance"
col username		format a20		heading "Username"
col sql_id              format a13		heading "SQL Id"
col session_id		format 9999999		heading "Session"
col seconds_in_wait     format 999,999		heading "Wait"
col elapsed_seconds     format 999,999		heading "Seconds|Elapsed"

accept p_sample_time_begin char prompt "Enter begin sample time in format [yyyymmdd hh24:mi:ss]: "
accept p_sample_time_end char prompt "Enter end sample time in format [yyyymmdd hh24:mi:ss]: "

spool &&rpt_dir/ash_top_sql.rpt

select
	rownum,
	time,
	username,
	session_id,
	sql_id,
	seconds_in_wait,
	elapsed_seconds
from(
	select 
		to_char(s.sample_time,'yyyymmdd hh24:mi:ss') time,
		s.session_id session_id,
		s.session_serial# session_serial#,
		(select substr(username,1,20) from dba_users where user_id=s.user_id) username,
		(select object_name from dba_objects where object_id=s.current_obj#) current_object,
		s.sql_id,
		s.time_waited/1000000 seconds_in_wait,
		s.delta_time/1000000 elapsed_seconds
	from 
		dba_hist_active_sess_history s,
		dba_users u
	where 
		s.user_id=u.user_id
	and	s.user_id<>0 
	and	u.username not in ('DBSNMP')
	and     s.sample_time between to_date('&&p_sample_time_begin','yyyymmdd hh24:mi:ss') 
	and 	to_date('&&p_sample_time_end','yyyymmdd hh24:mi:ss')
	order by
		s.delta_time desc
)
where
	rownum<=10;

spool off

clear columns

set embedded off
set escape off
set verify on
set define on

set colsep " "
set underline on
set headsep on
set heading on

set pause off
set pagesize 24
set linesize 80
set long 80

set termout on
set serverout off
set feedback on
set echo off
