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 YES3. 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