Use SQL Access Advisor

1. Documentación en Tahiti -> Oracle Database, 11g Release 2 (11.2) -> Masters Book List -> Performance Tuning Guide -> 18 SQL Access Advisor

2. SQL Access Advisor funciona de forma similar a SQL Tuning Advisor. Recibe como input una serie de sentencias o carga de trabajo y realiza unas recomendaciones para crear índices, vistas materializadas, … y otras estructuras para optimizar el acceso a los datos.

SQL Access Advisor tiene dos modos de funcionamiento: Comprehensive y Limited. En función del modo escogido, el Advisor puede realizar más o menos recomendaciones. En la siguiente tabla se pueden ver las diferentes. Hay que tener en cuenta que la ejecución del Advisor en modo Comprehensive puede durar mucho más tiempo que el modo Limited.

Recommendation                                                             Comprehensive   Limited
----------------------------------------------------------------------     -------------   -------
Add new (partition) index on table or materialized view                          YES         YES
Drop and unused index                                                            YES         NO
Modify an existing index by changing the index type                              YES         NO
Modify an existing index by adding columns at the end                            YES         YES
Add a new (partitioned) materializez view                                        YES         YES
Drop an unused materialized view (log)                                           YES         NO
Add a new materialized view log                                                  YES         YES
Modify an existing materialized view log to add new columns or clauses           YES         YES
Partition an existing unpartitioned table or index                               YES         YES

3. La mejor forma de utilizar SQL Access Advisor es a través del Enteprise Manager. En el siguiente ejercicio vamos a ejecutar una serie de consultas sobre el esquema SH. Capturaremos esas sentencias para generar un SQL Tuning Set (STS). Y utilizaremos dicho STS como entrada para el SQL Access Advidor

-- Nos conectamos al esquema SH
CONN SH/sh

-- Deshabilitamos la salida de las consultas que vamos a ejecutar
SET AUTOTRACE TRACEONLY

-- Lanzamos varias consultas con un identificador que nos permite buscarlas facilmente en la la Shared Pool
SELECT /* TESTADVISOR01 */
 CH.CHANNEL_CLASS,
 C.CUST_CITY,
 T.CALENDAR_QUARTER_DESC,
 SUM(S.AMOUNT_SOLD) SALES_AMOUNT
  FROM SH.SALES S, SH.TIMES T, SH.CUSTOMERS C, SH.CHANNELS CH
 WHERE S.TIME_ID = T.TIME_ID
   AND S.CUST_ID = C.CUST_ID
   AND S.CHANNEL_ID = CH.CHANNEL_ID
   AND C.CUST_STATE_PROVINCE = 'CA'
   AND CH.CHANNEL_DESC IN ('Internet', 'Catalog')
   AND T.CALENDAR_QUARTER_DESC IN ('1999-01', '1999-02')
 GROUP BY CH.CHANNEL_CLASS, C.CUST_CITY, T.CALENDAR_QUARTER_DESC;

SELECT /* TESTADVISOR02 */
 CH.CHANNEL_CLASS,
 C.CUST_CITY,
 T.CALENDAR_QUARTER_DESC,
 SUM(S.AMOUNT_SOLD) SALES_AMOUNT
  FROM SH.SALES S, SH.TIMES T, SH.CUSTOMERS C, SH.CHANNELS CH
 WHERE S.TIME_ID = T.TIME_ID
   AND S.CUST_ID = C.CUST_ID
   AND S.CHANNEL_ID = CH.CHANNEL_ID
   AND C.CUST_STATE_PROVINCE = 'CA'
   AND CH.CHANNEL_DESC IN ('Internet', 'Catalog')
   AND T.CALENDAR_QUARTER_DESC IN ('1999-03', '1999-04')
 GROUP BY CH.CHANNEL_CLASS, C.CUST_CITY, T.CALENDAR_QUARTER_DESC;

SELECT /* TESTADVISOR03 */
 C.COUNTRY_ID, C.CUST_CITY, C.CUST_LAST_NAME
  FROM SH.CUSTOMERS C
 WHERE C.COUNTRY_ID IN (52790, 52798)
 ORDER BY C.COUNTRY_ID, C.CUST_CITY, C.CUST_LAST_NAME;

-- Desactivamos el modo AUTOTRACE
SET AUTOTRACE OFF

-- Localizamos las consultas que hemos ejecutado en la Shared Pool
SELECT SQL_ID, SQL_TEXT
  FROM TABLE(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('SQL_TEXT LIKE ''SELECT /* TESTADVISOR%'''))
  ORDER BY SQL_ID;

-- Nos conectamos como SYSDBA para generar el STS
CONN / AS SYSDBA
EXEC DBMS_SQLTUNE.CREATE_SQLSET('STS_TEST');

-- Creamos el STS con las consultas anteriores
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P)
    FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('SQL_TEXT LIKE ''SELECT /* TESTADVISOR%''')) P;
  DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'STS_TEST', populate_cursor => cur);
  CLOSE cur;
END;
/

El siguiente paso lo realizamos desde EM, ya que el tiempo es una cuestión importante en el examen, y así podemos evitar muchos errores humanos que se cometen al utilizar PL/SQL.

En la página principal de la instancia OCM ->
-> Click “Advisor Central” ->
-> Click “SQL Advisors” ->
-> Click “SQL Access Advisors” ->
-> Seleccionamos “Inherit Options from a previously saved Task or Template” ->
-> Seleccionamos “SQLACCESS_WAREHOUSE” ->
-> Click “Continue” ->
-> Click “Use an existing SQL Tuning Set” ->
-> Click icono de Linterna ->
-> Seleccionamos el SQl Tuning Set “STS_TEST” ->
-> Click “Select” ->
-> Click “Next” ->
-> Seleccionamos los tres tipos de recomendaciones “Indexes”, “Materialized Views” y “Partitioning” ->
-> Seleccionamos “Comprehensive” ->
-> Click “Next” ->
-> Task Name = “SQL_ACCESS_ADVISOR_TEST” ->
-> Time Zone = “(UTC+01:00) Madrid” ->
-> Click “Next” ->
-> Click “Submit” ->
-> Nos lleva a la página “Advisor Central” ->
-> En unos minutos nuestro trabajo (Ej. SQLACCESS5419481) aparece con el estado “COMPLETED” ->
-> Click “View Result” ->
-> Nos fijamos en la mejora muy significativa en coste I/O ->
-> Click “Recommendations” ->
-> Click “Recommendation Details” ->
-> Se recomienda la creación de particiones, MVIEW, índices y pasar estadísticas ->
-> También nos fijamos en la SQL afectados por las recomendaciones ->
-> Click “OK” ->
-> Click “Schedule Implementation” ->
-> OEM nos advierte de que hay particionados que requiere implementación manual ->
-> Click “Show SQL” ->
-> Guardamos la salida en el fichero sqladvisor_deploy.sql ->
-> Click “Done” ->
-> Click “Cancel”

No hemos realizado las implantaciones porque ese script nos sirve perfectamente para la práctica que tenemos que realiza con SPA en el objetivo de “Use SQL Performance Analyzer“.

4. Veamos la implementación del ejercicio anterior con el paquete DBMS_ADVISOR.

-- Ya tenemos generado el STS, luego no tenemos porque volver a crearlo (STS_TEST)
-- Revisamos que todavia lo tenemos
SELECT NAME, OWNER, STATEMENT_COUNT FROM DBA_SQLSET;

-- Otra cosa que hemos hecho durante el asistente es utilizar una plantilla para Data Warehouse
-- Podemos ver todas las plantillas que tenemos
SELECT * FROM DBA_ADVISOR_TEMPLATES;

-- Create una tarea para el ADVISOR
VARIABLE task_id NUMBER;
VARIABLE task_name VARCHAR2(255);
EXEC :task_name := 'SQLACCESS_TEST';
EXEC DBMS_ADVISOR.CREATE_TASK ('SQL Access Advisor', :task_id, :task_name, template => 'SQLACCESS_WAREHOUSE');

-- Obtenemos el Id de la tarea para posibles busquedas
PRINT :TASK_ID;

-- Añadimos el STS de referencia
EXEC DBMS_ADVISOR.ADD_STS_REF ('SQLACCESS_TEST', 'SYS', 'STS_TEST');

-- Ejecutamos el Advisor
EXEC DBMS_ADVISOR.EXECUTE_TASK('SQLACCESS_TEST');

-- Vemos las recomendaciones
SELECT REC_ID, RANK, BENEFIT FROM USER_ADVISOR_RECOMMENDATIONS WHERE TASK_NAME = :task_name;

-- Vemos las acciones de cada recomendación
SELECT rec_id, action_id, SUBSTR(command,1,30) AS command
FROM user_advisor_actions WHERE task_name = :task_name
ORDER BY rec_id, action_id;

-- Podemos guardar el script de las recomendaciones a un fichero
EXEC DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT('SQLACCESS_TEST'), 'DATA_PUMP_DIR', 'sqladvisor_deploy.sql');

-- Vemos el script que ha generado SQL Access Advisor
cat /u01/app/oracle/admin/OCM/dpdump/sqladvisor_deploy.sql