Como optimizar consultas en Oracle con el parámetro optimizer_index_cost_adj

Es muy común que los DBAs traten dentro de los ambientes OLTP evitar este tipo de accesos a los datos. Sabemos que este acceso es lento para tablas grandes, pero también sabemos que es muy usado en tablas pequeñas. Veamos como podemos sacar provecho de la eliminación o uso del no muy bien ponderado full table scan.

Vamos a empezar a ver algún ejemplo práctico: Vamos a crear una tabla mas o menos masiva a partir de otra ya existente:

[oracle@antares ~]$ sqlplus “/as sysdba”

SQL*Plus: Release 9.2.0.8.0 – Production on Tue Mar 3 17:06:31 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Release 9.2.0.8.0 – Production
JServer Release 9.2.0.8.0 – Production

SQL> create table tabla_test as select * from obj$;

Table created.

SQL> select count(*) from tabla_test;

COUNT(*)
———-
66084

SQL>

Una vez que tengamos creada nuestra tabla, vamos a realizar una simple consulta, para ver el tipo de acceso que tenemos (Obviamente tendremos un full table scan en las tablas de este tipo de volumen):

SQL> set autotrace traceonly
SQL> select * from tabla_test where owner#=1;

2834 rows selected.
Execution Plan
———————————————————-
Plan hash value: 1026990376

——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————–
| 0 | SELECT STATEMENT | | 24608 | 30M| 174 (3)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| TABLA_TEST | 24608 | 30M| 174 (3)| 00:00:03 |
——————————————————————————–
Statistics
———————————————————-
0 recursive calls
0 db block gets
2212 consistent gets
0 physical reads
0 redo size
1093152 bytes sent via SQL*Net to client
16470 bytes received via SQL*Net from client
1457 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2834 rows processed

SQL>

Ahora bien, un buen administrador de base de datos diría que para eliminar el full table scan es necesario crear un índice. Veamos los resultados luego de indexar (sin olvidar de actualizar estadística):

SQL> create index index_1_test on tabla_test(owner#);

Index created.

SQL> EXEC DBMS_STATS.gather_index_stats(‘sys’, ‘index_1_test’, estimate_percent => 100);

PL/SQL procedure successfully completed.

SQL> select * from tabla_test where owner#=1;

21834 rows selected.
Execution Plan
———————————————————-
Plan hash value: 1026990376

——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————–
| 0 | SELECT STATEMENT | | 24608 | 30M| 174 (3)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| TABLA_TEST | 24608 | 30M| 174 (3)| 00:00:03 |
——————————————————————————–
Statistics
———————————————————-
0 recursive calls
0 db block gets
2319 consistent gets
0 physical reads
0 redo size
1093152 bytes sent via SQL*Net to client
16470 bytes received via SQL*Net from client
1457 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
2834 rows processed

SQL>

¿Que pasó? ¡Aún después de haber creado un índice y actualizar estadísticas se sigue accediendo a la tabla por full table scan!. Bueno esto puede suceder a causa de decisiones que toma el CBO, estamos leyendo una gran cantidad de datos de la tabla, el CBO piensa según los datos que él posee que es mucho mejor para la base de datos acceder a la tabla completa que utilizar un índice. ¿Pero será tan así esto?

Para asegurarnos que las consultas que hagamos utilicen realmente los índices existe un parámetro de base de datos que se llama optimizer_index_cost_adj. Con ayuda de este parámetro podemos hacer que las decisiones de acceso del optimizador hacia los datos favorezcan el uso de índices antes que del uso de FTS. Oracle recomienda una formula para asignarle un valor a este parámetro (por defecto está en 100):

optimizer_index_cost_adj = Costo FTS de la consulta/Costo con hint usando el índice*100

Ya conocemos cual es el valor del costo por FTS, 174. Ahora averiguaremos el valor del costo utilizando como ayuda el hint del índice que creamos:

SQL> select /*+ index(tabla_test index_1_test) */ * from tabla_test where owner#=1;

2834 rows selected.
Execution Plan
———————————————————-
Plan hash value: 1582054771

——————————————————————————–
————

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time |

——————————————————————————–
————

| 0 | SELECT STATEMENT | | 24608 | 30M| 499 (1)
| 00:00:06 |

| 1 | TABLE ACCESS BY INDEX ROWID| TABLA_TEST | 24608 | 30M| 499 (1)
| 00:00:06 |

|* 2 | INDEX RANGE SCAN | INDEX_1_TEST | 24608 | | 42 (3)
| 00:00:01 |

——————————————————————————–
————
Statistics
———————————————————-
0 recursive calls
0 db block gets
367 consistent gets
0 physical reads
0 redo size
2126037 bytes sent via SQL*Net to client
16470 bytes received via SQL*Net from client
1457 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2834 rows processed

Como se puede ver el costo es bastante más alto, aún así utilizando el índice. Apliquemos la fórmula y asignemos un nuevo valor a nivel de sesión para el optimizer_index_cost_adj:

costo FTS/costo indice hint * 100

174/499*100 = 34,8

Asignaremos este nuevo valor al parámetro:

SQL> alter session set optimizer_index_cost_adj = 34;

Session altered.

SQL> select * from tabla_test where owner#=1;

2834 rows selected.
Execution Plan
———————————————————-
Plan hash value: 1582054771

——————————————————————————–
————

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time |

——————————————————————————–
————

| 0 | SELECT STATEMENT | | 24608 | 30M| 100 (1)
| 00:00:02 |

| 1 | TABLE ACCESS BY INDEX ROWID| TABLA_TEST | 24608 | 30M| 100 (1)
| 00:00:02 |

|* 2 | INDEX RANGE SCAN | INDEX_1_TEST | 24608 | | 8 (0)
| 00:00:01 |

——————————————————————————–
————
Statistics
———————————————————-
13 recursive calls
0 db block gets
367 consistent gets
0 physical reads
0 redo size
2126037 bytes sent via SQL*Net to client
16470 bytes received via SQL*Net from client
1457 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
21834 rows processed

Como podemos ver luego de la modificación de este parámetro el costo y los consisten gets disminuyeron sin necesidad de recurrir a la utilización de un hint, además de eliminar el FTS.

Como Observación es bastante común por lo menos para mí observar el valor de optimizer_index_cost_adj entre 20 y 30 en las bases de datos. Es importante recalcar que el DBA siempre debe optimizar sus resultados en base a las lecturas más que por el costo. Recordemos que el Costo es un valor que Oracle representa para un plan de ejecución, es algo así como un puntaje. El verdadero afinamiento debe estar orientado a las estadisticas que te entrega el plan como por ejemplo los consistent gets.

En esta nota metalink 243269.1 “How does Parameter OPTIMIZER_INDEX_COST_ADJ influence Index Access” está la información original, además del comentario de otros parámetros a los cuales ponerle atención. No olvidar establecer el valor de parámetro desde el archivo SPFILE o PFILE de Oracle, sólo para efectos de prueba lo declaramos a nivel de sesión.

Originalmente escrito en: www.dbagroup.cl blog de la empresa donde presto servicios de consultor de empresas.