Performance tuning tip for slow running sql

The below script will help in finding out if any index creation/tuning is required on a oracle table for a long running sql script.

Using the sid, fetch the sql_id from gv$sql , before running this script ( sql_id is the input for the script):



set echo on
set verify on
set serveroutput on
set linesize 120
undefine sql_id
spool sta.out
execute dbms_workload_repository.create_snapshot(flush_level=>'ALL');
select min(snap_id) from dba_hist_snapshot;
select max(snap_id) from dba_hist_snapshot;
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK(
begin_snap=>&beginsnap,
end_snap=>&endsnap,
sql_id=>'&&sql_id',
scope=>'COMPREHENSIVE',
time_limit=>3600,
task_name=>'&&sql_id._tuning_task',
description=>'Tuning task for statement ' || '&&sql_id');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => '&&sql_id._tuning_task');
END;
/

SET LONG 10000
SET LONGCHUNKSIZE 10000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( '&&sql_id._tuning_task') from DUAL;
--
--
-- exec dbms_sqltune.drop_tuning_task(task_name=>'&&sql_id._tuning_task');
--
--
spool off