SQL Tuning Advisor in Oracle Database | How to get SQL Advisor Against one SQL_ID

Whenever we have any performance issue with any problematic codes or requests we should try to narrow down the issue and isolate the problematic codes.

For this Oracle has one tool called SQL Tuning Advisor. SQL Tuning Advisor can be used to provide suggestion or recommendation about certain SQL statement. It analyzes high volume SQL statements and offer tuning recommendation.

When you run SQL tuning advisor it will give you few recommendations to improve the performance of problematic sql_id.

It will give your following:

•  A potentially better execution plan was found for this statement.

• By creating one or more indices

• By creating recommended indexes

• Creating SQL Profile

How to create sql advisory:

 1. Get the sql_id for a problematic code. This you can get from OEM, gv$session

       2.  Incase of the RAC database get the inst_id

     select SQL_ID,STATUS, inst_id from gv$session where sql_id='d2pxyu3m307qg';

        3. Create tuning task:

DECLARE

v_tune_taskid  VARCHAR2(100);

BEGIN

v_tune_taskid := dbms_sqltune.create_tuning_task (

sql_id      => 'd2pxyu3m307qg',

scope       => dbms_sqltune.scope_comprehensive,

time_limit  => 30,

task_name   => 'd2pxyu3m307qg',

description => 'Tuning task sql_id d2pxyu3m307qg');

dbms_output.put_line('taskid = ' || v_tune_taskid);

END;

/

       4. Compile the tuning task :

           In this case out tuning task name is “d2pxyu3m307qg”

       5.  Get the tuning advisor report

             exec dbms_sqltune.execute_tuning_task(task_name => 'd2pxyu3m307qg');

       6 .   Get the output and recommendation in readable format:

set long 10000;

set pagesize 1000

set linesize 220

set pagesize 24

select dbms_sqltune.report_tuning_task('d2pxyu3m307qg') as output from dual;

  7.       Fetch the list of tuning task in the database

 

select task_name, status from dba_advisor_log where owner = 'SYS';

   8.     Drop the tuning task

 

exec DBMS_SQLTUNE.DROP_TUNING_TASK(task_name => ' d2pxyu3m307qg ');

Action item after getting the SQL Tuning Advisory Report:

Based on the report you can go ahead and execute the recommendation. As for example if tuning report says to create and profile , or create indexes then login to database and execute the recommendation. Post execution you will get the better performance of the query.

Happy Learning….