Many times a sql may be running long and we may need to run sql tuning advisor to get the recommendation to resolve the long running issue.In this post, we would be looking at the steps to achieve it
Steps for running sql tuning advisor on the particular sql -id in the Cursor cache
Create sql Tuning Task
DECLARE my_task_name VARCHAR2(30); BEGIN my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK (sql_id => '<SQL_ID>', scope => 'COMPREHENSIVE', time_limit => 3600, task_name => '<task Name>', description => 'Tuning Task for <SQL-ID>'); end; / Example For SQL_ID=454nj462ha3n DECLARE my_task_name VARCHAR2(30); BEGIN my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_id => '454nj462ha3n', scope => 'COMPREHENSIVE', time_limit => 3600, task_name => '454nj462ha3n_task_1', description => 'Tune query using sql_id 454nj462ha3n'); end; /
The scope parameter above is one of the most important for this function. You can set this parameter to the following values:
LIMITED
SQL Tuning Advisor produces recommendations based on statistical checks, access path analysis, and SQL structure analysis. SQL profile recommendations are not generated.
COMPREHENSIVE
SQL Tuning Advisor carries out all the analysis it performs under limited scope plus SQL profiling.
The below query list all the tuning task in the database
COL TASK_ID FORMAT 999999 COL TASK_NAME FORMAT a25 COL STATUS_MESSAGE FORMAT a33 SELECT TASK_ID, TASK_NAME, STATUS, STATUS_MESSAGE FROM DBA_ADVISOR_LOG;
Execute sql Tuning Task
BEGIN DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => '<Task Name>'); end; / Example BEGIN DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => '454nj462ha3n_task_1'); end; /
You can monitor the processing of the tuning task with the statement
SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME = ‘454nj462ha3n_task_1’;
Once the tuning task is completed, we can generate the report
Get the summary of recommendation of sql tuning task
SET LONG 10000000 SET LONGCHUNKSIZE 1000 SET LINESIZE 100 set pagesize 100 SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( '<TASK NAME>') from DUAL; Example SET LONG 10000000 SET LONGCHUNKSIZE 1000 SET LINESIZE 100 set pagesize 100 SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( '454nj462ha3n_task_1') from DUAL;
Get the detailed recommendation of sql tuning task
SET LONG 10000000 SET LONGCHUNKSIZE 1000 SET LINESIZE 100 set pagesize 100 SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( '454nj462ha3n_task_1','TEXT','ALL','ALL') from DUAL;
How to accept the profile recommended by the sql tuning expert
execute dbms_sqltune.accept_sql_profile(task_name =>'454nj462ha3n_task_1', task_owner => 'SYS', replace => TRUE);
This will provide system generated name for the SQL Profile. If you want to give some name, then you can use below sql
DECLARE my_sqlprofile_id VARCHAR2(30); BEGIN my_sqlprofile_id := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE ( task_name => '454nj462ha3n_task_1', name => '454nj462ha3n_profile'); END; /
How to drop the sql tuning task.
begin
DBMS_SQLTUNE.DROP_TUNING_TASK('454nj462ha3n_task_1');
end;
/
Steps for running sql tuning advisor on the particular sql -id not in the Cursor cache
If the sql_id is not in the Cursor Cache, we can locate into the AWR tables and then run the sql tuning tasks
set lines 155 col execs for 999,999,999 col avg_etime for 999,999.999 col avg_lio for 999,999,999.9 col begin_interval_time for a30 col node for 99999 break on plan_hash_value on startup_time skip 1 select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value, nvl(executions_delta,0) execs, (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime, (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS where sql_id = nvl('&sql_id','<SQL_ID>') and ss.snap_id = S.snap_id and ss.instance_number = S.instance_number and executions_delta > 0 order by 1, 2, 3 /
Once we get to know the Snap_id , we can run the sql tuning advisor as below
SET SERVEROUTPUT ON DECLARE l_sql_tune_task_id VARCHAR2(100); BEGIN l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( begin_snap => <begin_Snap_id>, end_snap => <end_Snap_id>, sql_id => '<SQL_ID>', scope => DBMS_SQLTUNE.scope_comprehensive, time_limit => 1800, task_name => '<SQL_ID>_AWR_task', description => 'Tuning task for statement <SQL_ID> in AWR.'); DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); END; /
Then normal steps can be executed as same as above
Related Articles
Oracle Explain Plan : This is about Explain Plan in Oracle, How to read oracle explain plan for performance related issue, how to find the explain plan for query in cursor
Oracle Performance Tuning : Listing of Oracle performance tuning articles which will help you in solving the problems quickly
Sql Trace in Oracle: SQL trace or 10046 event is being used to trace the session activity in Oracle. The output generated by SQL trace is formatted using trcsess, tkprof utility
Oracle joins with examples : What are Oracle Joins with examples (Sql joins), Different type of joins , Inner Join,Outer join,left outer join,right outer join,cross join with examples
Hash join in Oracle :Check out this post for the detailed description of Hash join in Oracle, How it is different from Nested Loop join in oracle
v$active_session_history :Check out about Active Session History ,how it is configured,how to find performance bottleneck using ASH, ASH report generation,ASH queries
how to check sql profile in oracle :Check out this post on how to check sql profile in oracle, how to find the content of sql profile, how to drop the sql profile
https://docs.oracle.com/database/121/TGSQL/tgsql_sqltune.htm
Excellent. Most of the time tuning advisor gives the proper recommendation which really helps to fasten the query.