Sql Tunning

-- Find sql
SELECT sql_id,child_number,plan_hash_value FROM v$sql where sql_id='7rgqxq78pmptc';

-- Create SQL Tuning Set (STS)
EXEC DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => 'STSDSPACE01', description => 'STS for sql_id 7rgqxq78pmptc');

-- There are 3 ways load STS
-- SELECT_WORKLOAD_REPOSITORY - Retrieves statements from the Automatic Workload Repository (AWR).
-- SELECT_CURSOR_CACHE - Retrieves statements from the cursor cache.
-- SELECT_SQLSET - Retrieves statements from another SQL tuning set.
-- Load SQL by SQL ID to SQL TUNNING SET from CURSOR_CACHE

DECLARE
  cursor1 DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
  OPEN cursor1 FOR SELECT VALUE(p)
  FROM TABLE(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''7rgqxq78pmptc'' and plan_hash_value=''2801298299''')) p; 
  DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'STSDSPACE01', populate_cursor => cursor1);
  CLOSE cursor1;
END;
/

-- Check Sql Tunning Set
SELECT sql_id, plan_hash_value FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET('STSDSPACE01'));

-- Create SQL Tunning Task for sql_id
DECLARE
  task_name VARCHAR2(30);
BEGIN
  task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK ( sql_id => '7rgqxq78pmptc', scope => 'COMPREHENSIVE', time_limit => 1800, task_name => 'SQLTUNE_7rgqxq78pmptc_0317_01');
  DBMS_OUTPUT.PUT_LINE('Task Name: ''' || task_name || ''' Tuning Task Created');
END;
/

-- Execute Task
BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK ( task_name => 'SQLTUNE_7rgqxq78pmptc_0317_01');
END;
/

-- Check Status
SELECT STATUS FROM DBA_ADVISOR_TASKS WHERE TASK_NAME = 'SQLTUNE_7rgqxq78pmptc_0317_01';

-- Reporting
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'SQLTUNE_7rgqxq78pmptc_0317_01' ) FROM DUAL;

-- SQL Access Advisor
DECLARE
  task_id NUMBER := 0;
  task_name VARCHAR2(30) := 'SAADSPACE01';
  sts_name VARCHAR2(30) :='STSDSPACE01';
BEGIN
  DBMS_ADVISOR.CREATE_TASK('SQL Access Advisor', task_id, task_name, 'Task for sql_id 7rgqxq78pmptc');
  DBMS_ADVISOR.ADD_STS_REF(task_name, 'SYS', sts_name);
  DBMS_ADVISOR.EXECUTE_TASK(task_name);
END;
/

-- Reporting
SELECT DBMS_ADVISOR.get_task_script('SAADSPACE01') AS script FROM   dual;

-- If the recommendations of the SQL tuning advisor include a suggested profile you can choose to accept it using the ACCEPT_SQL_PROFILE procedure.

-- Accepting New SQL Profile
exec dbms_sqltune.accept_sql_profile(task_name => 'SQLTUNE_21zq47mj49f7w_0105_01', task_owner => 'SYS', replace => TRUE, name => 'sql_profile_for_21zq47mj49f7w');

-- Check new profile status
select name, category, status from dba_sql_profiles;