SLQ Profiling best practices.

--To Check for SQL_ID Execution Plan in the DB--
set lines 201 trimout on trimspool on pages 0
col PLAN_TABLE_OUTPUT format A200
set serveroutput off

alter session set statistics_level = all;

select plan_table_output from table(dbms_xplan.display_cursor('&sql_id',null,'advanced')) ;

--To Check for Existing SQL Profiles in the DB--

select name,category,status from dba_sql_profiles where name like '%&SQL_ID%' ;

-- Tuning task created for specific a statement from the cursor cache--

DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => '&sql_id',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          --time_limit  => 60,
                          task_name   => '&<sql_id>_tuuning_task',
                          description => 'Tuning task for statement awhvk2mgm7fxu.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

-- Executing tunning task --
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '&<sql_id>_tuuning_task');

-- To check Output of tunning task--
SET LONG 999999;
SET PAGESIZE 1000
SET LINESIZE 800
SET PAGESIZE 24
SELECT DBMS_SQLTUNE.report_tuning_task('&<sql_id>_tuuning_task') AS recommendations FROM dual;


-- To Set SQL Profile for test run --
begin
  dbms_sqltune.accept_sql_profile (
    task_name   => '&<sql_id>_tuuning_task',
    name        => '&<sql_id>_<ADBA_member_initials>',
    description => 'Test',
    category    => 'ADBA_TEST',
    task_owner  => 'ADBM',
    replace     => TRUE,
    force_match => TRUE
  );
end;
/

-- To change SQL Profile to DEFAULT Category --

begin
  dbms_sqltune.alter_sql_profile ('&<sql_id>_<ADBA_member_initials>', 'CATEGORY', 'DEFAULT');
end;
/


-- TO Bind SQL profile with Hash plan --
execute dbms_sqltune.create_sql_plan_baseline(task_name =>'&<sql_id>_tuuning_task', owner_name => 'ADBM', plan_hash_value => 2281968314);


-- To drop the tunning task --
BEGIN
  DBMS_SQLTUNE.drop_tuning_task (task_name => '&<sql_id>_tuuning_task');
END;
/