Cuando tenemos problemas de rendiemiento cualquier ayuda es poca, Oracle Database ofrece el SQL Tuning Advisor  (STA) que nos ayudará ofreciendo recomendaciones para mejora el rendimiento del entorno, indices nuevos, planes de ejecución, etc..  de un SQL Tuning Set (entre otras posibles entradas)

En esta entrada vamos a ver como utilizar el SQL Tuning Advisor, que ofrecerá recomendaciones que podemos aplicar para mejorar el rendimiento de la BD.

La documentación oficial está aqui.

Partimos de un SQL Tuning Set (STS) creado en la entrada SQL Tuning Set Oracle Database.

1. Lo primero que tenemos que hacer es crear una tarea de tuning pasando como parámetro el STS.
   

   Source

   --Creamos la tuning task llamada SH_STA
   DECLARE
   my_task_name VARCHAR2(30);
   BEGIN
   my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK (sqlset_name => 'sh_workload', task_name => 'SH_STA', time_limit => '3600' , sqlset_owner => 'SYS');
   DBMS_OUTPUT.PUT_LINE(my_task_name);
   END;
   /
   --podemos verla con la consulta
   COL TASK_ID FORMAT 999999
   COL TASK_NAME FORMAT a25
   COL STATUS_MESSAGE FORMAT a33
   SELECT TASK_ID, TASK_NAME, STATUS, STATUS_MESSAGE FROM   USER_ADVISOR_LOG WHERE TASK_NAME='SH_STA';

2. Ejecutamos la task SH_STA.
   

   Source

   exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name=>'SH_STA');
   --podemos ver el estado de la ejecución con, debemos esperar hasta que esté "COMPLETED"
   COL TASK_ID FORMAT 999999
   COL TASK_NAME FORMAT a25
   COL STATUS_MESSAGE FORMAT a33
   SELECT TASK_ID, TASK_NAME, STATUS, STATUS_MESSAGE FROM   USER_ADVISOR_LOG WHERE TASK_NAME='SH_STA';TASK_ID TASK_NAME                 STATUS      STATUS_MESSAGE
   ------- ------------------------- ----------- ---------------------------------
   168 SH_STA                    COMPLETED

3. Report de la tareas con las recomendaciones incluidas (ver el fichero sta_report.txt)
   

   Source

   SET LONG 1000
   SET LONGCHUNKSIZE 1000
   SET LINESIZE 100
   SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'SH_STA' ) FROM   DUAL;

4. Obtener un script de las con las recomenadiones:
   

   Source

   SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK('SH_STA') FROM dual;
   -----------------------------------------------------------------
   -- Script generated by DBMS_SQLTUNE package, advisor framework --
   -- Use this script to implement some of the recommendations    --
   -- made by the SQL tuning advisor.                             --
   --                                                             --
   -- NOTE: this script may need to be edited for your system     --
   --       (index names, privileges, etc) before it is executed. --
   -----------------------------------------------------------------
   execute dbms_sqltune.accept_sql_profile(task_name => 'SH_STA', object_id => 2, REPLACE => TRUE);
   CREATE INDEX SH.IDX$$_00A90001 ON SH.TIMES("CALENDAR_QUARTER_DESC","TIME_ID");

5. Se podrían también aceptar las recomendaciones y aplicarlas
   

   Source

   exec DBMS_SQLTUNE.IMPLEMENT_TUNING_TASK('SH_STA');

Podemos borrar todo lo creado con:

Source

--borrar tuning task
exec DBMS_SQLTUNE.DROP_TUNING_TASK( 'SH_STA' )
--borrar sqlset
exec DBMS_SQLTUNE.DROP_SQLSET (sqlset_name  => 'sh_workload')

Espero os sea de utilidad para solucionar problemas de rendimiento.