As a DBA, We need to tune many sql for performans issue. Its very good option to use SQL Tuning Advisor(also SQL repair advisor) from OEM.

In this post, We will use sqlplus for SQL Tuning

Here is the steps.

1. Create user(If neccessary):

CONN sys/password AS SYSDBA

–create user
GRANT ADVISOR TO scott;

–grant user
GRANT CREATE SESSION TO scoot;
GRANT ADMINISTER SQL MANAGEMENT OBJECT TO scott;

–connect db by scoot
CONN scott/tiger

2. Create Tuning TASK

To create tuning task, We need to use CREATE_TUNING_TASK function. The statements to be analyzed can be retrieved from the Automatic Workload Repository (AWR), the cursor cache, a SQL tuning set or specified manually.

A. By AWR interval

SET SERVEROUTPUT ON

— Tuning task created for specific a statement from the AWR.
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
begin_snap => 7830,
end_snap => 7832,
sql_id => ‘3c8gs332cuugy’,
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 1800,
task_name => ‘MY_AWR_tuning_task_for_3c8gs332cuugy’,
description => ‘Tuning task for 3c8gs332cuugy’);
DBMS_OUTPUT.put_line(‘l_sql_tune_task_id: ‘ || l_sql_tune_task_id);
END;
/

B. By Cursor cache

— Tuning task created for specific a statement from the cursor cache.
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => ‘3c8gs332cuugy’,
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 1800,
task_name => ‘3c8gs332cuugy_tuning_task’,
description => ‘Tuning task for 3c8gs332cuugy.’);
DBMS_OUTPUT.put_line(‘l_sql_tune_task_id: ‘ || l_sql_tune_task_id);
END;
/

C. By SQL text

DECLARE
my_task_name VARCHAR2(80);
my_sqltext VARCHAR2(80);
BEGIN
my_sqltext := ‘select /*+ no_index(test test_idx) */ * from test where n=1’;
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text=> my_sqltext,
time_limit => 60,
task_name => ‘my_sql_tuning_task_gunes’,
description => ‘Task to tune a query on a specified table’);
END;
/

D. By SQL_id

DECLARE
my_task_name VARCHAR2(80);
my_sql_id VARCHAR2(80);
BEGIN
my_sql_id := ’90qyvkh4xb3n6′;
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => my_sql_id,
time_limit => 1800,
task_name => ‘my_sql_tuning_task_gunes’,
description => ‘Task to tune a query on a specified table’);
END;
/

3. Create Task

SQL> exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => ‘my_sql_tuning_task_gunes’);

SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task(‘my_sql_tuning_task_gunes’) AS recommendations FROM dual;
SET PAGESIZE 24
4. Run profile

With NO Force match:
SQL> execute dbms_sqltune.accept_sql_profile(task_name =>’my_sql_tuning_task_gunes’, replace => TRUE);

PL/SQL procedure successfully completed.

With Force match:
SQL> execute dbms_sqltune.accept_sql_profile(task_name =>’my_sql_tuning_task_gunes’, replace => TRUE, force_match=>true);

Referans:
How To Use SQL Profiles for Queries Using Different Literals Using the Force_Match Parameter of DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (Doc ID 1253696.1)

Advertisements