Analizando o comportamento dunha BD con problemas de rendemento nun cliente, atopo que a principal actividade é xerada por unha ú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|      |      |       |
---------------------------------------------------------------------------------------------------

Trátase dunha consulta simple que se completa cunha operación Full Table Scan (operación con ID 4 TABLE ACCESS FULL). Esta operación FTS non é posible eliminala nin coa existencia dun índice na columna ID. O motivo é que estamos a buscar un valor que non é indexado por Oracle, NULL. Trátase dun comportamento normal e esperado dos índices en Oracle, os valores NULL son ignorados e polo tanto non se engaden nos índices (trátase literalmente como un valor que non existe).

Isto fai que aínda que creemos un índice simple nesa columna, a condición “CALLE IS NULL” será resolta necesariamente sempre cun FTS porque non podemos identificar valores NULL a través do índice, sendo preciso acudir á táboa directamente a revisar cada unha das filas para averiguar cales cumpren o criterio.

Neste caso, ocorre que a aplicación repite esta consulta de xeito moi frecuente, para manter actualizados datos na aplicación en tempo real, facendo que gran parte deo tempo unha sesión estea a facer User I/O na BD unha e outra vez. No momento de revisión, as execucións chegaban a superar os 150 segundos de duración, debido a que a táboa accedida tiña un tamaño duns 16GiBs. Estamos a traballar nun servidor pequeno, con SO Windows de 32bits, e tentando precisament resolver un problema relacionado co uso de memoria e a limitación de 2GiBs por proceso (até 3GiBs se configuramos para elo Windows).

A solución aplicada foi a de xerar un índice que inclúa valores NULL, o que en 11g (versión desta BD) podemos facer mediante un índice composto sendo unha das claves unha constante no canto de ser unha columna. Probamos inicialmente na nosa sesión cun índice virtual se o plan obtido é o que esperamos:
create index idx_nc_virt on cds(calle,0) nosegment;

alter session set "_use_nosegment_indexes" = true;
E unha vez validado, aplicamos a nivel global na BD cun índice real:
drop index idx_nc_virt;

create index idx_nc on cds(calle,0);
O resultado obtido foi o seguinte plan, moito máis ó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 executarse nun tempo de 0,25″.

Esta é unha alternativa interesante ó emprego dun índice funcional que resolva a situación (por exemplo empregando a función NVL). A vantaxe desta alternativa é que non foi preciso modificar a aplicación para que o SQL se adapte a este índice. Cun índice funcional, precisaríamos modificar adicionalmente o filtro WHERE para igualalo á función do índice. Deste xeito, xeramos un índice composto no que un dos compoñentes é a columna con NULLs e o outro é un valor constante. Isto fará que tódalas entradas NULL desa columna na táboa sexan indexadas, e convertirá no noso caso o FTS nun INDEX RANGE SCAN moito máis eficiente.

 

 

Agradecementos:

Fotografía: Alicia Constela, Vera Pereiro e Alberto Pereiro. Clube de Esgrima 100Tolos.

Revisións: Alicia Constela.