Analizando en un cliente el comportamiento de una BD Oracle con problemas de rendimiento, encuentro que la principal actividad está generada por una única consulta:
SQL_ID 3x5fn7ptysps8, child number 0 ------------------------------------- SELECT * FROM (SELECT * FROM CDS C WHERE C.CALLE IS NULL OR C.CALLE = '' ORDER BY ID_CDS DESC) WHERE ROWNUM <=100 Plan hash value: 1018703595 --------------------------------------------------------------------------------------------------- | Id | Operatistartupon | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | O/1/M | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 584K(100) | | | | | |* 1 | COUNT STOPKEY | | | | | | | | | | 2 | VIEW | | 2 | 1292 | 584K (1) | 01:56:49 | | | | |* 3 | SORT ORDER BY STOPKEY| | 2 | 334 | 584K (1) | 01:56:49 | 2048 | 2048 | 13/0/0| |* 4 | TABLE ACCESS FULL | CDS | 2 | 334 | 584K (1) | 01:56:49 | | | | ---------------------------------------------------------------------------------------------------
Se trata de una consulta simple que se completa con una operación Full Table Scan (operación con ID 4 TABLE ACCESS FULL). No es posible eliminar esta operación FTS ni con la existencia de un índice en la columna CALLE. El motivo es que estamos buscando un valor que nunca será indexado por Oracle, NULL. Es un comportamiento normal y esperado de los índices en Oracle, los valores NULL se ignoran y por lo tanto no son añadidos en los índices (son tratados literalmente como un valor que no existe, concepto de NULL).
Esto hace que aunque creemos un índice simple sobre esta columna, la condición “CALLE IS NULL” será resuelta siempre con un FTS porque no podemos identificar valores NULL a través del índice, siendo necesario acudir a la tabla de manera directa para revisar cada una de las filas y averiguar cuáles cumplen el criterio.
En nuestro caso, la aplicación replite esta consulta con mucha frecuencia para mantener actualizados datos en tiempo real, haciendo que gran parte del tiempo una sesión esté haciendo User I/O sobre la BD de una y otra vez. En el momento del análisis, las ejecuciones llegaban a superar los 150 segundos de duración, debido a que la tabla accedida tenía un tamaño de unos 16GiBs. La BD está en un servidor Windows de 32 bits, donde precisamente intentábamos solucionar un problema relacionado con la limitación de 2GiBs de memoria por proceso en este tipo de entornos (3GiBs si configuramos para ello el sistema operativo).
La solución aplicada para esta consulta fue la de crear un índice que incluya valores NULL, lo que en una versión 11g (versión de esta BD) podemos hacer a través de un índice compuesto donde uno de los valores es una constante en lugar de ser otra columna. Probamos inicialmente en nuestra sesión con un índice virtual para verificar si el plan es el esperado:
create index idx_nc_virt on cds(calle,0) nosegment; alter session set "_use_nosegment_indexes" = true;
Y una vez validado, aplicamos a nivel global en la BD creando un índice real:
drop index idx_nc_virt; create index idx_nc on cds(calle,0);
El resultado obtenido fue el siguiente plan, mucho más óptimo:
Plan hash value: 3682771407 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 2 | 1292 | 4 (25)| 00:00:01 | |* 1 | COUNT STOPKEY | | | | | | | 2 | VIEW | | 2 | 1292 | 4 (25)| 00:00:01 | |* 3 | SORT ORDER BY STOPKEY | | 2 | 334 | 4 (25)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID| CDS | 2 | 334 | 3 (0) | 00:00:01 | |* 5 | INDEX RANGE SCAN | IDX_NC | 2 | | 2 (0) | 00:00:01 | ------------------------------------------------------------------------------
Pasando a ejecutarse en un tiempo de 0,25″.
Esta es una alternativa interesante al empleo de un índice funcional que resuelva la situación (por ejemplo utilizando la función NVL). La ventaja de esta alternativa es que no fue necesario modificar la aplicación para adaptar el código SQL a este índice. Con un índice funcional (y una edición Standard de BD 🙂 ), necesitaríamos modificar el filtro WHERE para igualarlos a la función del índice para que el optimizador evalúe su uso. Se este modo generamos un índice compuesto en el que uno de los componentes es una columna con los valores NULL y el otro es un valor constante. Esto hará que todas las entradas NULL de esta columna se incorporen al índice y se transformará nuestro FTS inicial en un INDEX RANGE SCAN mucho más eficiente.
Agradecimientos:
Fotografía: Alicia Constela, Vera Pereiro e Alberto Pereiro. Clube de Esgrima 100Tolos.
Revisiones: Alicia Constela.
