How to use SQL tuning advisory in Oracle

SQL Tuning Advisory is a tool in Oracle designed to optimize SQL queries. It offers suggestions to enhance performance by gathering statistics, creating indexes, and modifying execution plans.

Steps to run the SQL Tuning Advisory for SQL Query

1. Create the SQL Tuning Advisory Task
Enter the SQL text below for the SQL advisory:

DECLARE
  my_sqltext CLOB;
  task_name VARCHAR2(30);
BEGIN
  my_sqltext := 'SELECT name,job_title FROM EMP';
  my_sqltext := my_sqltext || ' where emp_no=100';
  task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_text => my_sqltext,
               bind_list => sql_binds(anydata.ConvertNumber(100)),
               user_name => 'HR',
               scope => 'COMPREHENSIVE',
               time_limit => 60,
               task_name => 'sql_tuning_task1');
END;
/

Note: If you have the SQL ID, you can use this script to create an SQL Advisory Task. You can find the SQL ID in the V$sqlarea view.

DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => '9nnmc2mquh0gb',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'SQL_TUNING_TASK1',
                          description => 'Tuning task for statement 19v5guvsgcd1v.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

2. Execute the SQL tuning Advisory task

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'SQL_TUNING_TASK1');

3. Get the Report of SQL tuning Advisory task

SET LONG 100000;
SET PAGESIZE 200
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('SQL_TUNING_TASK1') AS recommendations FROM dual;

3. You will receive the recommendation in the report when you run the SQL Tuning Advisory report package.

4. Drop the SQL tuning advisory task

exec DBMS_SQLTUNE.drop_tuning_task (task_name => 'SQL_TUNING_TASK1');

You received the following SQL tuning suggestions:
1. Improve the execution plan for the SQL query.
2. Create indexes on the tables.
3. Generate statistics for the table.

Example: For execution plan recommendation got from SQL Tunning package

Note: If advisory is suggest for creating the different execution plan in accept state which will increase the performance as example given below:

1. Created the accept SQL Profile as mentioned:

BEGIN
  DBMS_SQLTUNE.accept_sql_profile(
    task_name => 'tune_sql_4f8s1k9u3xw2g',
    name      => 'sqlprof_4f8s1k9u3xw2g');
END;
/

If you created the plan based on advisory reports, it will work for the same parameters as those in the SQL query tuning advisory. However, it may not work with different parameters. This depends on whether your application is using bind variables, allowing it to use the same plan for all parameters.