Automatic Indexing - Oracle Database 19c

Por Francisco Riccio
Publicado en Abril 2019

Introducción

Uno de los mayores desafíos de los Administradores de Base de Datos es mantener una base de datos con el mejor desempeño posible, manteniendo las sentencias SQL debidamente optimizadas. Las sentencias SQL se han venido optimizando muchas veces de manera reactiva ante incidencias reportadas, pero hoy la versión de Oracle Database 19c, permite proactivamente mejorar cada sentencia SQL a través de la creación automática de índices sin alguna intervención del Administrador de Base de Datos. Esta nueva funcionalidad aplica para todos los tipos de base de datos especialmente OLTP.

La creación automática de índices trabaja de la siguiente manera en intervalos de 15 minutos:

Fase	Actividades Realizadas
Identificación	Identificación de índices candidatos basado en las sentencias SQL ejecutadas, las cuales han sido almacenadas en un historial. Esta captura incluye planes de ejecución, estadísticas, bind variables relacionados a las sentencias.
Creación	Los índices (solo metadata sin registros) son creados con la opción de invisible con la finalidad que no puedan ser utilizados.
Verificación	A través del optimizador, los índices son materializados y validados contra las sentencias SQL con la finalidad de obtener métricas necesarias.
Decisión	Si los índices logran obtener un beneficio en desempeño, cambian de invisible a visible, en caso contrario, estos son cambiados a unusable y registrados en una “lista negra” de manera que nunca más vuelvan ser considerados en el futuro.
Verificación en Línea	Solo una de las sesiones que ejecutan la sentencia SQL será permitida a utilizar el nuevo índice con la finalidad de validar los potenciales resultados esperados.
Monitoreo	Los índices creados son monitoreados durante todo el ciclo de vida de la base de datos con la finalidad de ser eliminados en el tiempo si dejan de ser usados.

Los índices creados automáticamente soportan:

Uno o varios campos.
Basado en funciones.
Compresión (Low)

Implementación

A continuación se presentará la implementación y validación de esta nueva funcionalidad.

>A. Preparación de la Prueba

Para validar la funcionalidad se creó una tabla TEST con 10 millones de filas y ejecutando en concurrencia una sentencia SQL cuyo plan de ejecución realiza un FULL SCAN a la tabla.

Carga de 10 millones de registros:

begin
 for i in 1..10000000 loop
  insert into test values(i,'T'||to_char(i));
 end loop;
 commit;
end;
/

Sentencia SQL:

declare
 v varchar(30);
begin
 for i in 1..10000000 loop
  select campo2 into v from test where campo1=i;
 end loop;
end;
/

B. Configuración de Automatic Indexing

Habilitar la Funcionalidad:

execute DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT|REPORT ONLY|OFF');

El valor del parámetro REPORT ONLY ejecuta todas las fases mencionadas pero no permite que los índices creados sean utilizados por las sentencias SQL.

Configurar el Tablespace utilizado para crear los Índices Automáticos:

execute DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE','<NOMBRE DEL TABLESPACE>');

Existen otros parámetros que podemos configurar como parte del paquete DBMS_AUTO_INDEX:

Parámetro	Objetivo
AUTO_INDEX_SCHEMA,<NOMBRE ESQUEMA>,FALSE\|TRUE	TRUE: Se considera la creación automática de índices en el esquema.
AUTO_INDEX_SPACE_BUDGET,%	Especifica el porcentaje de uso de espacio que puede ser utilizado para la creación de Índices Automáticos. Por defecto es: 50%.
AUTO_INDEX_RETENTION_FOR_AUTO,n días	Mantiene por un período de n días los índices en estado UNUSABLE. Por defecto es: 373 días
AUTO_INDEX_RETENTION_FOR_MANUAL,n días	Mantiene por un período de n días los índices que no han sido utilizados y fueron creados por el DBA manualmente. Por defecto Oracle no elimina los índices creados por el DBA que no tengan utilización.
AUTO_INDEX_REPORT_RETENTION,n días	Permite almacenar por una cantidad de días los análisis y beneficios que tuvieron los índices analizados durante la fase de validación. Por defecto, se almacena por 31 días.

Si se desea revisar toda la configuración, se puede consultar la siguiente vista: DBA_AUTO_INDEX_CONFIG

C. Revisión de los Índices Sugeridos

Cada 15 minutos se realiza la revisión de sentencias SQL que puedan tomar algún beneficio desempeño mediante índices, dichas tareas de revisión pueden ser consultadas a través de la siguiente vista: DBA_AUTO_INDEX_EXECUTIONS

Para revisar el detalle del análisis de una tarea específica, se consulta la vista DBA_AUTO_INDEX_STATISTICS. En este caso se revisará la última del reporte previo: SYS_AI_2019-03-21/23:42:41

Se puede revisar las acciones ejecutadas por la base datos sobre los índices creados automáticamente a través de la vista: DBA_AUTO_INDEX_IND_ACTIONS

Las verificaciones realizadas por la base de datos sobre un índice creado automáticamente se pueden visualizar en la siguiente vista: DBA_AUTO_INDEX_VERIFICATIONS

En este ejemplo se puede apreciar que el nuevo índice reduce los buffer gets de: 27,432.1425 a 83.

Acorde al escenario presentado, se puede apreciar que se ha creado un índice en el tablespace TBS_IDX_AUTO:

D. Obtención de Reportes de Automatic Indexing

Los reportes pueden ser obtenidos a través de los siguientes métodos:

DBMS_AUTO_INDEX.REPORT_ACTIVITY
DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY

El formato entregado puede ser: TEXT, XML y HTML.

Ejemplo:

Para este caso se obtendrá el reporte de los índices creados automáticamente en el intervalo de hace 3 días a la fecha actual menos 30 minutos.

Se puede apreciar que la consulta con el SQLID: cxg3jf34hgtts, tenía un costo de 7,513 y con la implementación automática del índice tiene un costo actual de: 4, donde esto generó un beneficio de 330.5x. Asimismo consultando el plan de ejecución de la sentencia SQL tenemos el mismo resultado:

Conclusión

Esta nueva funcionalidad que nos provee Oracle Database 19c permite liberar de horas de trabajo al DBA de una manera proactiva para mantener la base de datos con un tiempo de respuesta eficiente, manteniendo todos los niveles de control que permitan no afectar el rendimiento actual.

Francisco Riccio, actualmente se desempeña como Arquitecto de Soluciones en Oracle Perú y es instructor de cursos oficiales de certificación Oracle. Es un Oracle Certified Professional en productos de Oracle Application, Base de Datos, Cloud & Virtualización.

Este artículo ha sido revisado por el equipo de productos Oracle y se encuentra en cumplimiento de las normas y prácticas para el uso de los productos Oracle.