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. For more topics- https://beingpiyush.blogspot.in/