Overview of Oracle SQL-Tuning Adviser

SQL tuning adviser introduced in 10G

SQL Tuning adviser is replacing the manual tuning SQL tuning process.

It will be providing advice for increasing the SQL query performance.

Tuning adviser script is complete analysis for the SQL query and given detailed reports.

Tuning Adviser Recommendation below

Find the missing statics

Find the index

Find the SQL Profile

Find the better execution plan

Restructuring the SQL

Identify the high load SQL statements

How to run Tuning Adviser

Using OEM:

Step 1: Fins the SQL_D

Step2: Click Performance tabàSQLàSearch SQL

Step3: Enter the username and password

Step4: Place the SQL_ID for under filter condition tab and click search. It will show the sql_text, Plan hash value, Schema name and Elapsed time

Step5: Top of the tab showing the Actions tab choice sql_tuning_adviser and click GO button

Step6: It will show the tuning task name and click submit button

Step7: It will generate tuning adviser report.

Using Putty:

Go to below directory and execute sqltrpt.sql script pass the sql_id

cd /oracle/product/12.1.0.2/db/rdbms/admin/
SQL> @sqltrpt.sql

SQL Tuning Advisor Using sql command  

Create the Tuning Task

DECLARE 
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => '0y9hz0w9scvtg',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 500,
task_name => '0y9hz0w9scvtg_tuning_task11',
description => 'Tuning task1 for statement 0y9hz0w9scvtg');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/ 

Execute Tuning Task

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '0y9hz0w9scvtg_tuning_task11');

Get the tuning adviser report

 

set long 65536
set longchunksize 65536
set linesize 100
select dbms_sqltune.report_tuning_task('0y9hz0w9scvtg_tuning_task11') from dual;

 

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( '<SQL_Tuning_Task_Name>' ) FROM   DUAL;