Automatic Indexing en Oracle Exadata desde 19c: qué es y cómo funciona
Guía práctica con caso de uso y scripts
Introducción
Oracle Exadata permite, desde la versión 19c de base de datos, usar la opción Automatic Indexing en Oracle Enterprise Edition sin necesidad de adquirir licencias adicionales.
Automatic Indexing es una característica de los Engineered Systems (Exadata) y Oracle Cloud. Por tanto, si estamos al menos en esta versión y en Exadata, puede delegarse la gestión de la mayoría de índices al motor de base de datos, tanto en lo que se refiere a su creación y mantenimiento como en la eliminación automática de índices creados manualmente y que no estén en uso.
Arquitectura
La base del Automatic Indexing está en el SQL Performance Analyzer y el Automatic SQL Tuning Set (ASTS).
Cuando está activado el Automatic Indexing, cada 15 minutos se levanta la tarea SYS_AUTO_INDEX_TASK y realiza 5 pasos:
Paso 1: Identificación y Captura.
De los datos capturados por el
ASTSde sentencias y su carga sobre columnas en tablas con estadísticas válidas no obsoletas, se identifican los índices candidatos.Paso 2: Creación.
Se crean los índices en estado
INVISIBLE y UNUSABLE, esto es se crean en el diccionario pero no tienen segmentos asociados, así no hay coste de mantenimiento. Solo se crearán índices B-tree. En tablas particionadas, solo índices locales. Pueden ser tanto de una sola columna o multi-columna. Los índices suelen tener el prefijoSYS_AI, para ser fácilmente identificables.Paso 3: Validación.
Se valida la carga de trabajo por el
SQL Performance Analyzer. En esta fase el índice se construye temporalmente y elSPAsimula la carga, comparando el rendimiento usando el índice con el rendimiento obtenido con la opción de no usarlo. El índice permaneceVALIDperoINVISIBLEpara el resto de usuarios.Paso 4: Toma de decisión.
Si el uso del índice conlleva una mejora de rendimiento, el índice se publica, se hace
VISIBLEyUSABLE, quedando disponible para el optimizador. Si se detectan consultas individuales que sufren regresión, se crea unSQL Plan Baselinepara prevenir la regresión.Si el rendimiento empeora o no mejora, el ínidice vuelve a marcarse como
UNUSABLEy se purgará en el paso 5.Paso 5: Purgado.
Se limpian los índices que se descartaron en el paso 3 o los índices que se hicieron visibles si no son usados por un periodo de tiempo (por defecto 373 días). También puede configurarse para que se borren los índices creados manualmente no usados, aunque el comportamiento por defecto es no borrar los índices creados manualmente.
Restricciones
Es necesario que los binarios de oracle hayan sido compilados sin excluir explícitamente la opción
Real Application Testing, esta es la configuración por defecto. No es necesaria la licenciaRATde forma independienteEs necesario estar en
OCIo enEngineered Systems Exadata.En
Exadata, hay que tener en cuenta que para que la creación del índice sea efectiva, no deben hacerse escaneos masivos, sino que deben extraerse pocas filas, escaneos masivos llevarán al uso delExadata Smart Scanque es más rápido que el acceso por índice y por tanto elSPAno seleccionará el índice como opción de mejor rendimiento, eso deja fuera índices sobre tablas muy pequeñas.Estadísticas actualizadas. Tablas sin estadísticas o con estadísticas obsoletas no serán tenidas en cuenta para el Automatic Indexing.
Indices Bitmap no serán candidatos a ser creados por Autmatic Indexing.
Indices globales en tablas particionadas tampoco.
Ni lo serán Índices de función, índices de clave inversa (Reverse Key Indexes).
Están excluidos los índices en tablas temporales y en tablas del sistema o propiedad de esquemas como
SYS,SYSTEM,AUDSYSo del diccionario de datos.No se crearán índices en columnas con tipo de dtaos no soportados, del tipo
LOBs,LONG,SDO_GEOMETRYo tipos de datos definidos por el usuario.
Implementación de la prueba
En un entorno aisliado de test, limitaremos la prueba al esquema deSCOTTy a su tablespace por defectoUSERS.
Los valores iniciales son los de por defecto:
La configuración se hace mediante el paquete DBMS_AUTO_INDEX.
- Habilitar el modo de implementación. Hay tres modos posibles,
OFF,REPORT ONLYeIMPLEMENT. En nuestra prueba, se pondrá enIMPLEMENTque creará y hará visibles los índices:
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
- Restricción al esquema
SCOTT. Pueden gestionarse listas de inclusión y exclusión, así como el uso de máscaras, para la prueba de concepto solo añadiremos aSCOTTa la lista de inclusión:
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'SCOTT', allow => TRUE);
- Retención a 30 días de los índices no usados, al ser un entorno de pruebas vamos a reducir el valor por defecto de 373 días:
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO', 30);
- Nos aseguramos que el esquema SCOTT tiene estadísticas:
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SCOTT', cascade => TRUE);
No se generarán índices automáticos sobre tablas que no tengan estadísticas actualizadas.
Modificamos la tabla emp para que las consultas sobre dicha tabla sean más óptimas por los índices que puedan crearse, lo hacemos quitando la Primary Key, cargando varios miles de registros y recalculando las estadísticas:
ALTER TABLE scott.emp DROP PRIMARY KEY CASCADE;
ALTER TABLE scott.emp MODIFY (empno NUMBER(10));
INSERT /*+ APPEND */ INTO scott.emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
SELECT
ROWNUM + 10000,
e.ename,
e.job,
e.mgr,
e.hiredate,
e.sal,
e.comm,
e.deptno
FROM
scott.emp e
CROSS JOIN
(SELECT 1 FROM DUAL CONNECT BY LEVEL <= 100000);
COMMIT;
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT', tabname => 'EMP', cascade => TRUE);
Para simular actividad, generamos un job que no pare de hacer consultas a la tabla SCOTT.EMP por empno:
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'SCOTT.POC_AUTO_INDEX_WORKLOAD',
job_type => 'PLSQL_BLOCK',
job_action => '
DECLARE
v_ename VARCHAR2(50);
BEGIN
FOR i IN 1..5000 LOOP
BEGIN
-- Buscamos por EMPNO. Selectividad del 100% (1 sola fila).
-- El valor de :1 iterara desde 10001 en adelante.
EXECUTE IMMEDIATE ''SELECT ename FROM scott.emp WHERE empno = :1''
INTO v_ename USING (i + 10000);
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
WHEN TOO_MANY_ROWS THEN NULL;
END;
END LOOP;
END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=MINUTELY; INTERVAL=2',
enabled => TRUE
);
END;
/
También se forzaron jobs similares que repetían consultas sobre otros campos de la tabla emp sin indexar.
¿Como ver los resultados del proceso de Automatic Indexing?
El proceso de automatic indexing se ejecuta cada 15 minutos, y podemos identificarlo como una tarea SYS_AUTO_INDEX_TASK dentro de la vista dba_advisor_executions.
SELECT
task_name,
execution_name,
TO_CHAR(execution_start, 'YYYY-MM-DD HH24:MI:SS') AS execution_start,
TO_CHAR(execution_end, 'YYYY-MM-DD HH24:MI:SS') AS execution_end,
status
FROM
dba_advisor_executions
WHERE
task_name = 'SYS_AUTO_INDEX_TASK'
ORDER BY
execution_start DESC
FETCH FIRST 20 ROWS ONLY;
También puede verse ver el último report con el paquete DBMS_AUTO_INDEX y en función de su tiempo, podrá calcularse la siguiente ejecución:
DECLARE
report CLOB := NULL;
BEGIN
report := DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY(
type => 'TEXT',
section => 'SUMMARY +INDEX_DETAILS +ERRORS',
level => 'BASIC'
);
DBMS_OUTPUT.PUT_LINE(report);
END;
/
La salida será:
Pasados al menos 15 minutos para que se ejecute el Automatic Indexing, debe haberse creado un índice INVISIBLE sobre el campo empno.
Para ver las ejecuciones en un rango de tiempo: (en github he subido chk_auto_indexing_report.sql)
Y el índice que ha creado tendrá la columna AUTO=YES en la DBA_INDEXES:
SELECT
o.owner,
i.index_name,
i.table_name,
i.index_type,
i.visibility,
c.column_name,
c.column_position,
TO_CHAR(i.last_analyzed, 'YYYY-MM-DD HH24:MI:SS') AS last_analyzed,
TO_CHAR(o.created, 'YYYY-MM-DD HH24:MI:SS') AS created,
TO_CHAR(o.last_ddl_time, 'YYYY-MM-DD HH24:MI:SS') AS last_ddl_time
FROM
dba_indexes i,
dba_objects o,
dba_ind_columns c
WHERE
i.index_name = o.object_name
AND i.owner = o.owner
AND o.object_type = 'INDEX'
AND i.index_name = c.index_name
AND i.owner = c.index_owner
AND i.table_name = c.table_name
AND i.auto = 'YES'
ORDER BY
i.table_name ASC,
i.index_name ASC,
c.column_position ASC;
Horas después, podemos verificar que ya hay mas índices creados automáticamente y el que se usa por el optimizador está en estado VISIBLE, esto indica que ha pasado la fase de validación indicada en el apartado de aquitectura.
Comparando planes de ejecución en awr, ver Comparar planes de ejecución en el AWR, he obtenido el plan antiguo y nuevo para la consulta a la tabla scott.emp filtrando por empno.
Y el nuevo plan con el índice:
Para borrar un índice creado con este método, no se puede aplicar el tradicional drop index, se borra automáticamente cuando el índice lleve sin usarse el tiempo configurado en el parametro AUTO_INDEX_RETENTION_FOR_AUTO, pero puede forzarse un borrado con la api, como ejemplo borraremos un índice de los creados pero todavia invisibles:
exec dbms_auto_index.drop_auto_indexes('SCOTT','"SYS_AI_1qg24s2ykdat4"',TRUE);
Con la opción TRUE, permitimos que vuelva a crearse automáticamente en un futuro.
Verificamos que ya no existe:
Si intento borrar un índice creado automáticamente de manera tradicional:
Si está permitido hacerle un rebuild o un coalesce, con lo que se puede cambiar de tablespace o sus parámetros de almacenamiento.
Pueden verse las últimas acciones ejecutadas por el Automatic Indexing con las vistas DBA_AUTO_INDEX_IND_ACTIONS y DBA_AUTO_INDEX_SQL_ACTIONS:
Licencias y versiones
Los únicos requisitos es estar en Exadata con Oracle Enterprise Edition 19c. Aunque el Automatic Indexing se basa en el Automatic SQL Tuning Set y en SQL Performance Analyzer, no es necesaria la licencia de Tuning & Diagnostic Pack.
Según las notas leídas en MOS, deberíamos estar mínimo en oracle 19c RU 19.7 (KB119960).
Resumen
Son muy pocos los requisitos que debe cumplir una base de datos 19c Enterprise Edition en exadata para beneficiarse del Automatic Indexing, con una compilación por defecto, solo es necesario que las tablas candidatas tengan las estadísticas recientes y ya puede configurarse esta funcionalidad.
El mantenimiento automático de índices puede ser una gran ventaja al liberar al DBA o al administrador de la aplicación de la pesada tarea de mantener índices obsoletos o proponer la creación de índices nuevos.
El riesgo de regresión por causa de los nuevos índices no existe ya que el SPA valida el impacto de cada índice antes de liberarlo, creando SQL Plan Baselines para consultas que sufireron regresiones.
Una vez activado el Automatic Indexing, la automatización del proceso es completa. Todo se realiza en segundo plano sin requerir intervención del dba.
Aplicabilidad
Antes de decidir el uso en producción de esta potente funcionalidad, puede probarse en un entorno de test controlado bajo simulaciones de carga reales. Sin usar RAT para capturar y reproducir el tráfico de producción puede forzarse esa carga con herramientas tipo JMeter o LoadRunner o scripts a medida. El Automatic Indexing creará los índices que considere necesarios, en modo IMPLEMENT, o los mostrará solo en modo INVISIBLE y NO DISPONIBLE si se ha configurado en modo REPORT ONLY.
Un caso particular que puede ser solventado con esta funcionalidad, es el problema que se genera cuando aparecen foreign keys in indexar en entornos OLTPS.
Scripts usados
Parametrización del Automatic Indexing:
Últimas ejecuciones de la tarea de Automatic Indexing:
Para obtener el informe para un número de días hacía atrás
Para ver los índices creados en el Automatic Indexing
Para ver las últimas acciones sobre los índices automáticos
Para ver las últimas acciones realizadas con índices automáticos sobre sqls.
Notas y bibliografia
El Automatic SQL Tuning Set (ASTS) captura de forma continua y sin intervención la carga de trabajo y almacena en el tablespace SYSAUX, las sentencias SQL, sus métricas de rendimiento, contextos y planes de ejecución. Es la base para la Indexación Automática y el Automatic SQL Plan Management.
KB119960 MOS Note: "Automatic SQL Tuning Sets (ASTS) 19c RU 19.7 Onwards" (Doc ID 2686869.1).
El SQL Performance Analyzer (SPA) es el componente del Real Application Testing (RAT) que evalua el impacto de los cambios del sistema en el rendimiento de las consultas antes de ser aplicadas en producción
Oracle Database Testing Guide 19
KB149029 SQL Performance Analyzer Summary
MOS Note: "SQL Performance Analyzer (SPA) White Paper and Links" (Doc ID 2102143.1)
Consideraciones
Las consultas que aparecen en el artículo se han elaborado a partir de la documentación oficial de Oracle y se ofrecen como ejemplos y herramientas de apoyo al diagnóstico. Se facilitan para su uso y adaptación, pero su aplicación en entornos productivos debe validarse previamente por el responsable del sistema o un DBA; cualquier modificación que afecte a la producción es responsabilidad del usuario. Para casos críticos o dudas, consulte la documentación oficial de Oracle y/o Oracle Support (MOS). Si detecta errores o desea proponer mejoras, agradeceremos sus informes o contribuciones mediante issues o pull requests.
Los problemas son reales, los datos no.
