SQL Tuning Advisor (STA) can provide some recommendations for a sql_id to improve its performance. Today in this article, we will learn how to run SQL Tuning Advisor in Oracle. Let’s go through the steps :
Table of Contents
Run SQL Tuning Advisor Using DBMS_SQLTUNE :
First, you need the sql_id, suppose the sql_id is 434a4nn8cx6uc.
1. Create a Tuning Task :
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id =>'434a4nn8cx6uc',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 1800, -- Time limit in seconds
task_name => 'sql_tuning_434a4nn8cx6uc',
description => 'Tuning task for statement sql_tuning_434a4nn8cx6uc');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
Output:
2. Execute the Tuning task:
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'sql_tuning_434a4nn8cx6uc');
Output :
3. Now get the STA Report and recommendations :
SET PAGESIZE 1000
set long 65536
set longchunksize 65536
set linesize 200
SELECT DBMS_SQLTUNE.report_tuning_task('sql_tuning_434a4nn8cx6uc') AS recommendations FROM dual;
Run SQL Tuning Advisor Using OEM :
1. Login to OEM:
Open your OEM console in a web browser and log in with your DBA credentials.
2. Navigate to SQL Tuning Advisor:
Go to the “Performance” tab and select SQL from the menu.
Click on SQL Tuning Advisor (or a similar option depending on your version).
Select the SQL Statement
3. Provide the SQL_ID :
If you have a specific SQL statement to tune, you can paste it into the text box.
4. Start Tuning Process :
After selecting the SQL statement, click on “Tuning” or “Start Advisor” (the exact label may vary depending on your version).
Oracle will analyze the SQL statement and provide tuning recommendations like indexing.
5. Go through the Recommendations :
Once the analysis is done, you will receive a report with some recommendations including changes to SQL execution plans and possible indexes to create if required.
Hope this helps!! Also, you can get more information from the Oracle Doc as well.
How to get a list of tuning task present in the database :
Issue the below command :
SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG;
Drop a SQL Tuning Task :
execute dbms_sqltune.drop_tuning_task('sql_tuning_434a4nn8cx6uc');
Output :
Related Oracle Related Articles :
Pingback: How to find top SQL in library cache: Easy Guide