Como minimizar problemas luego de un upgrade de versión de Oracle (Caso2: Cambio del orden de evaluación de predicados)

En esta nueva nota les voy a contar un problema con el que se pueden encontrar al migrar desde version 8i hacia 9i o superior. El principal tema radica en la evolución constante que va teniendo el optimizador para estimar el costo de acceso a los datos de las sentencias. En la versión 7, donde apareció por primera vez el optimizador por costos, el costo se calculaba simplemente ponderando por la cantidad de requerimientos de lectura a disco. Esto, como es sabido, provocó un rechazo a cambiar de RBO a CBO bastante generalizado en su momento, dado que los planes de ejecución comenzaban a hacer cosas extrañas, causando importantes problemas de rendimiento generalizado. Por tal motivo, la mayoria de las compañias continuaron usando el optimizador por reglas, ya que les garantizaba que no se alteraran los planes y que no se destabilizaran las aplicaciones. El principal problema de usar solo los read request para generar el costo en Oracle 7 fue no considerar el caching que existe en distintos niveles.

A partir de 8i, se comenzó a ponderar por tipo de lectura (single reads y multiblock reads) y por tamaño y tiempo de lectura. Esto mejoró bastante la calidad de los planes generados y dió mayor confianza a las empresas para animarse a cambiar a CBO, mas que nada porque Oracle Corporation comenzó a incentivar fuertemente a salir de RBO, discontinuado a partir de 7.x (año 1992) y desoportado desde 10g. Si bien el comportamiento en 8i fue mucho mas estable faltaba tomar en cuenta algo muy importante, el tiempo de cpu.

Recien a partir de 9i se incluyó en el calculo del costo el tiempo insumido en procesamiento de cpu, anteriormente la formula para calcular solo tomaba en cuenta caracteristicas de i/o. En consecuencia con esta nueva variable de ponderación el optimizador puede cambiar el orden de evaluación de los predicados si estima que con ese nuevo orden se minimiza el tiempo de cpu. Este reordenamiento puede causar que ciertas consultas fallen en 9i+ y no en 8i. Este fallo generalmente se debe a una inconsistencia de datos, que antes quedaba "tapada" y que ahora al evaluar en otro orden genera un error, por ejemplo con una conversión implicita.

Vayamos a los ejemplos para graficar mejor este tema:

Primero voy a crear la siguiente tabla:

rop@DESA10G> create table t as
 2  select to_char(mod(rownum,30)) c1,
 3         rownum n1,
 4         mod(rownum,30) n2
 5  from dual
 6  connect by rownum <= 5000;  Tabla creada. 

La tabla T tiene 3 columnas:
C1: solo tendrá valores 30 posibles valores (de 0 a 29) y es de tipo varchar2.
N1: tiene valores distintos del 1 al 5000.
N2: tiene los mismos valores de C1 pero es de tipo number.

Para simular estar en 8i, voy a cambiar el parametro de session "optimizer_features_enable" para que el optimizador se comporte con un optimizador de 8i.

rop@DESA10G> alter session set optimizer_features_enable = '8.1.7';

Sesión modificada.

Ahora, ejecuto una sentencia que cuenta la cantidad de filas filtrando la tabla
por los tres campos.

rop@DESA10G> ed
Escrito file afiedt.buf

 1  explain plan for
 2  select count(1) from t
 3* where c1 = 1 and n1 = 1111 and n2 = 1
rop@DESA10G> /

Explicado.

rop@DESA10G> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1842905362

-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    48 |     2 |
|   1 |  SORT AGGREGATE    |      |     1 |    48 |       |
|*  2 |   TABLE ACCESS FULL| T    |     1 |    48 |     2 |
-----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - filter(TO_NUMBER("C1")=1 AND "N1"=1111 AND "N2"=1)

Note
-----
  - cpu costing is off (consider enabling it)

18 filas seleccionadas.

Mirando la información predicados (Predicate Information) notamos dos cosas interesantes. Primero hay una nota que nos advierte que el costo por cpu esta en off. Lo cual es lógico, por lo que comenté mas arriba respecto a que 8i no ponderaba por cpu y si bien estamos en 10g, recordemos que cambié el comportamiento del optimizador a 8i. Segundo vemos que los predicados se evaluaron en orden y que hubo una conversión implicita (TO_NUMBER()).

Vuelvo a poner el optimizador a su valor default y me fijo evaluo el plan:

rop@DESA10G> alter session set optimizer_features_enable = '10.2.0.4';

Sesión modificada.

rop@DESA10G> explain plan for
 2  select count(1) from t
 3  where c1 = 1 and n1 = 1111 and n2 = 1;

Explicado.

rop@DESA10G> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1842905362

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    48 |     5   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    48 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |     1 |    48 |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - filter("N1"=1111 AND "N2"=1 AND TO_NUMBER("C1")=1)

Note
-----
  - dynamic sampling used for this statement

18 filas seleccionadas.

El plan no cambió, se hizo sampleo dinamico ya que no habia recolectado estadisticas, pero lo mas importante a destacar es el cambio en la evaluación de los filtros. Notemos que ahora la conversión implicita se dejó para lo ultimo. Por que hizo eso el optimizador?, bien, pensemos que una conversión implica ciclos de cpu y entonces, porque no mejor evaluarlo al final cuando seguramente queden menos filas, ya que se van filtrando con los dos predicados o filtros anteriores, y asi minimizar la cantidad de conversiones, no?. Como ya dijimos el optimizador en versiones 9i+ se preocupa por el costo de procesamiento de cpu y por lo tanto puede realizar ciertos ajustes (reordenamiento de predicados, merge de subqueries, etc) si con eso se reduce la utilización de cpu.

Analicemos mas en detalle el ejemplo y como se evalua con cpu costing en ON y en OFF


Con CPU Costing en OFF (8i)

filter(TO_NUMBER("C1")=1 AND "N1"=1111 AND "N2"=1)

El primer predicado (TO_NUMBER("C1")=1) evalua 5000 filas y como resultado de ese filtro se queda con 167 filas.
El segundo predicado (N1=1111) evalua 167 filas y se queda con 1.
El tercer predicado (N2=1) evalua 1 y devuelve 1 (el resultado del count()).

Con CPU Costing en ON (9i+)

filter("N1"=1111 AND "N2"=1 AND TO_NUMBER("C1")=1)

El primer predicado (N1=1111) evalua 5000 filas y solo devuelve 1 fila.
El segundo predicado (N2=1) compara la fila y se la pasa al siguiente predicado.
El tercer predicado (TO_NUMBER("C1")=1) evalua una sola fila y devuelve el resultado.

En base al ejemplo analizado se ve claramente que con cpu costing apagado se deben realizar 5000 operaciones implicitas contra 1 operacion implicita cuando se tiene activado el costeo por cpu. Con este ejemplo sencillo, pero representativo, se puede ver lo importante del ordenamiento de la evaluación para minimizar el uso de recurso de cpu y por ende mejorar el tiempo de respuesta general.


Ahora veamos como fijar el orden de evaluación sin cambiar el comportamiento general de la sesion usando el hint "ordered_predicates":

rop@DESA10G> ed
Escrito file afiedt.buf

 1  explain plan for
 2  select /*+ ordered_predicates */ count(1) from t
 3* where c1 = 1 and n1 = 1111 and n2 = 1
rop@DESA10G> /

Explicado.

rop@DESA10G>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1842905362

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    48 |     5   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    48 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |     1 |    48 |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - filter(TO_NUMBER("C1")=1 AND "N1"=1111 AND "N2"=1)

Note
-----
  - dynamic sampling used for this statement

18 filas seleccionadas.

Con el hint la evaluación es ordenada, tal cual hubiese sido en 8i por default.
En el plan no se muestró el costo de cpu, pero consultando la tabla de soporte PLAN_TABLE podemos ver que costos fueron asignados de acuerdo al orden de evaluación.

rop@DESA10G> ed
Escrito file afiedt.buf

 1  select filter_predicates,cpu_cost from plan_table
 2* where filter_predicates is not null
rop@DESA10G> /

FILTER_PREDICATES                                    CPU_COST
-------------------------------------------------- ----------
"N1"=1000 AND "N2"=1 AND TO_NUMBER("C1")=1           1302275
TO_NUMBER("C1")=1 AND "N1"=1111 AND "N2"=1           1802225
rop@DESA10G>

El costo de cpu al evaluar en 10g fue de 1302275 (con reordenamiento) y el costo de cpu sin ordenamiento fue de 1802225 (8i)

Como demostré con el ejemplo, el optimizador evalua los predicados de forma tal de optimizar el uso de cpu. Este cambio podría hacer fallar ciertos codigos que antes funcionaban y que tenian inconsistencias de datos, como por ejemplo, tener valores no numericos sobre campos varchar que deben tener numeros, y entonces al convertir implicitamente se genere un error de invalid number (ORA-01722). Queda claro que si ocurre esto es debido a un mal diseño, ya que una columna que aloja solo números no debería ser de tipo varchar, ya que de esta forma se promueve, por un lado inconsistencias en los datos y por otro lado, se afecta el rendimiento debido a las conversiones implicitas.