/*
 * Title  : awr_sql_list.sql
 * Author : EXADIME LLC
 * Created: 29-Jul-2017
 * Updated: 19-Sep-2022
 * Release: 1.0.0
 * Purpose: Shows captured SQL in awr.
 * Comment: To capture full SQL, execute dbms_workload_repository.modify_snapshot_settings(topnsql=>'MAXIMUM').
 * History: N/A
 */

start oracle.ini

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

set linesize 150
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

column sno      format 99999            heading "#"
column snapid   format 9999999          heading "Snap Id"
column snaptime	format a15		heading "Snap Time"
column sqlid    format a15              heading "SQL Id"
column hash	format 9999999999	heading "Hash Value"
column text	format a60		heading "SQL Text"
column exec	format 9999999999	heading "Executions"
column elapsed	format 9999999999	heading "Seconds|Elapsed"

accept p_schema char prompt "Enter schema name: "
accept p_search char prompt "Enter search string: "
accept p_stsnap char prompt "Enter begin snap id: "
accept p_spsnap char prompt "Enter end snap id: "
accept p_order	char prompt "Enter sort order {1|6|7 [asc|desc]}: " default snapid

spool &&rpt_dir/awr_sql_list.rpt

select
	row_number() over(order by &&p_order)				sno,
	snapid,
	snaptime,
	sqlid,
	hash,
	text,
	exec,
	elapsed
from (
	select
		stat.snap_id						snapid,
		to_char(snap.end_interval_time,'yyyymmdd hh24:mi') 	snaptime,
		txt.sql_id						sqlid,
		stat.plan_hash_value					hash,
		substr(trim(sql_text),1,60)				text,
		executions_delta					exec,
		elapsed_time_delta/1000/1000				elapsed
	from     
		dba_hist_sqlstat stat,
		dba_hist_sqltext txt,
		dba_hist_snapshot snap
	where     
		stat.sql_id=txt.sql_id
	and	stat.snap_id=snap.snap_id
	and	snap.snap_id>=&p_stsnap
	and	snap.snap_id<=&p_spsnap
	and	parsing_schema_name=upper('&&p_schema')
	and	lower(sql_text) like lower('%&&p_search%')
	order by 
		&&p_order
);

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

set wrap on
