/*
* Title : awr_tps_hist.sql
* Author : EXADIME LLC
* Created: 20-Apr-2021
* Updated: 21-May-2021
* Release: 1.0.0
* Purpose: Shows TPS history captured in awr.
* History: N/A
*/

start oracle.ini

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

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

column	sno	format	9999	heading	"#"
column	snid	format	999999	heading	"SnapId"
column	time	format	a15	heading	"Begin Time"
column	name	format	a45	heading	"Name"
column	aval	format	99,999,999	heading	"Average"
column	mval	format	99,999,999	heading	"Maximum"

accept p_bsnap prompt "Enter Begin Snap Id: "
accept p_esnap prompt "Enter End Snap Id: "
accept p_order prompt "Enter sort order [asc|desc]: "

spool &&rpt_dir/awr_tps_hist.rpt

select
row_number() over(order by snap_id &&p_order) sno,
snap_id snid,
to_char(begin_interval_time,'yyyymmdd hh24:mi') time,
metric_name name,
avalue aval,
mvalue mval
from
(
select
s.snap_id,
s.begin_interval_time,
t.metric_name,
avg(value) avalue,
max(value) mvalue
from
dba_hist_snapshot s, dba_hist_sysmetric_history t
where
s.snap_id = t.snap_id
and s.instance_number = t.instance_number
and s.dbid = t.dbid
and s.instance_number = (select instance_number from v$instance)
and s.dbid = (select dbid from v$database)
and t.metric_name = 'User Transaction Per Sec'
and s.snap_id between &p_bsnap and &p_esnap
group by
s.snap_id,
s.begin_interval_time,
t.metric_name
order by
s.snap_id desc
);

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

/*
* Title : asm_free.sql
* Author : EXADIME LLC
* Created: 16-Nov-2020
* Updated: 16-Nov-2020
* Release: 1.0.0
* Purpose: Shows free space in ASM disk groups.
* History: N/A
*/

start oracle.ini

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

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

column	dummy	noprint
column	sno	format	9999	heading	"#"
column	name	format	a30	heading	"Name"
column	total	format	99,999,999,999	heading	"Total"
column	used	format	99,999,999,999	heading	"Used"
column	free	format	99,999,999,999	heading	"MB|Free"
column	pct_used	format	999.99	heading	"%|Used"

compute sum of total on report
compute sum of used on report
compute sum of free on report

break on report

accept p_order char prompt "Enter order by column {1|2|3|4|5|6} [asc|desc] " default 6

spool &&rpt_dir/asm_free.rpt

select
row_number() over(order by &&p_order) sno,
name name,
total_mb total,
total_mb-free_mb used,
free_mb free,
round(((total_mb-free_mb)/total_mb)*100,2) pct_used
from(
select
name,
total_mb,
free_mb
from
v$asm_diskgroup
)
order by
&&p_order
/

spool off

clear columns
clear computes

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

/*
* Title : awr_base_line.sql
* Author : EXADIME LLC
* Created: 29-Mar-2018
* Updated: 29-Mar-2018
* Release: 1.0.0
* Purpose: To create baseline for good SQL plan captured in AWR.
* History: N/A
*/

start oracle.ini

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

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

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

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

accept p_bsnap char prompt "Enter Begin Snap Id: "
accept p_esnap char prompt "Enter End Snap Id: "
accept p_sqlid char prompt "Enter SQL Id: "
accept p_plan_hash char prompt "Enter Plan Hash Value: "

spool &&rpt_dir/awr_base_line.rpt

DECLARE
cur sys_refcursor;
int pls_integer;
BEGIN
--1.Create SQL Tuning Set.
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'awr_base_line_&&p_sqlid',
description => 'SQL Tuning Set for loading plan into SQL Plan Baseline (SQL Id: &&p_sqlid).'
);

--2.Load SQL from AWR into SQL Tuning Set.
OPEN cur FOR
SELECT VALUE(P)
FROM TABLE(
dbms_sqltune.select_workload_repository(begin_snap=>&&p_bsnap, end_snap=>&&p_esnap,basic_filter=>'sql_id = ''&&p_sqlid''',attribute_list=>'ALL')
) p;
DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'awr_base_line_&&p_sqlid', populate_cursor=>cur);
CLOSE cur;

--3.Filter good SQL based on plan has value and create baseline.
int:=DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
sqlset_name => 'awr_base_line_&&p_sqlid',
basic_filter=>'plan_hash_value = ''&&p_plan_hash'''
);

if int=1 then
dbms_output.put_line('Baseline created.');
else
dbms_output.put_line('Baseline creation failed!');
end if;
END;
/

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

/*
* Title : run_tune_task.sql
* Author : EXADIME LLC
* Created: 29-Mar-2018
* Updated: 29-Mar-2018
* Release: 1.0.0
* Purpose: To run SQL Tuning Advisor and report recommendations.
* History: N/A
*/

start oracle.ini

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

set linesize 200
set pagesize 1000
set pause off
set long 65536

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

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

accept p_sql_id char prompt "Enter SQL Id "

spool &&rpt_dir/run_tune_task.rpt

declare
sql_tune_task_id varchar2(100);
begin
sql_tune_task_id := dbms_sqltune.create_tuning_task(
sql_id => '&&p_sql_id',
scope => dbms_sqltune.scope_comprehensive,
time_limit => 1000,
task_name => 'sql_tune_task_&&p_sql_id',
description => 'Tuning task for statement &&p_sql_id');
dbms_output.put_line('SQL Tune Task Id: ' || sql_tune_task_id);
end;
/

exec dbms_sqltune.execute_tuning_task(task_name => 'sql_tune_task_&&p_sql_id');

select
dbms_sqltune.report_tuning_task('sql_tune_task_&&p_sql_id')
from
dual;

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