DBMS_SQLTUNE – Profile

References:

https://docs.oracle.com/database/121/TGSQL/tgsql_profiles.htm#TGSQL612

1.) Identify the SQL_IF of your command, you can use the V$SQL for example:
SQL_ID: 0th9yu2b4ad7k

2.) Create a Tuning Task: DBMS_SQLTUNE.CREATE_TUNING_TASK
3.) Set some specific Parameters , if needed, there are some cases where 
the command can take too much time, so you need to increase the time for analysis.
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(task_name => stmt_task, 
parameter => 'TIME_LIMIT', value => 15000);

4.) Execute the Tuning Task:
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => stmt_task);

The anonymous block below will execute all steps:
SET SERVEROUTPUT ON
declare
stmt_task VARCHAR2(40);
begin
stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '0th9yu2b4ad7k');
DBMS_OUTPUT.put_line('task_id: ' || stmt_task );
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(task_name => stmt_task, parameter => 'TIME_LIMIT', value => 15000);
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => stmt_task);
end;
/

This command will return the NAME of the task: TASK_9931
5.) After the execution, you need to check the outcome of the analysis:
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_9931') AS recommendations FROM dual;

6.) If you agree with the suggestion, you can apply the PROFILE:
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_9931', 
task_owner => 'SYS', replace => TRUE);

7.) If you want to disable the PROFILE:
You can find the name on this view DBA_SQLTUNE_PLANS
begin
DBMS_SQLTUNE.ALTER_SQL_PROFILE('<NAME>','STATUS','DISABLED');
end;
/

8.) If you want to DROP the PROFILE:
BEGIN
  DBMS_SQLTUNE.DROP_SQL_PROFILE (  name => '<NAME>' );
END;
/

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s