How to tune sql using DBMS_SQLTUNE

The DBMS_SQLTUNE package provides the interface to tune SQL statements.

You use the CREATE_TUNING_TASK Functions to create a tuning task for tuning a single statement or a group of SQL statements.

The EXECUTE_TUNING_TASK Procedure executes a previously created tuning task.

The REPORT_TUNING_TASK Function displays the results of a tuning task.

You use the SCRIPT_TUNING_TASK Function to create a SQL*PLUS script which can then be executed to implement a set of Advisor recommendation

SQL> variable stmt_task VARCHAR2(64);

SQL> variable sts_task VARCHAR2(64);

SQL> EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(begin_snap => 21965, end_snap => 21966, sql_id => 'at18ft6rsb6n4');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(:stmt_task);

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(:stmt_task);