Como minimizar problemas luego de un upgrade de versión de Oracle (Caso1: Ordenamiento implicito de GROUP BY en versiones anteriores a 10g R2)

Esta nota pretende ser la primera de una serie de notas en las que voy a comentarles problemas que se pueden dar luego de un upgrade de versión de base de datos. Este año estuve involucrado en unos cuantos upgrades de versiones, siempre hacia version 10g R2 pero partiendo desde distintas versiones base (8i, 9i R2 y 10g R1). Aqui en Argentina todavia son muy pocos los que migraron a 11g ya que es una practica habitual, y en mi opinión muy acertada, esperar al segundo release para upgradear. Al momento de esta nota acaba de salir el 11g R2, pero solo para linux, asi que estimo que en poco tiempo tendremos disponible el nuevo release para las plataformas unix "grandes", tales como solaris, aix, hp-ux y tambien para la familia de SO's de windows.

Es sabido que cada nueva versión de Oracle introduce nuevos features, y en especial features que cambian el comportamiento del optimizador y que producen cambios en los paths de los planes de ejecución. Los cambios en el optimizador son generalmente para mejorar el acceso a los datos y por ende reducir el tiempo de respuesta. Esta mejora en la "inteligencia" del optimizador no debería ocacionar cambios de comportamiento, a menos que no se cumplan las Buenas Practicas de confección de sentencias sql. Una mala práctica, y por desgracia bastante común, es confiar en el ordenamiento implicito que se da, por ejemplo, al usar distint/unique o en el ordenamiento que se produce con el group by. Este último es a veces innecesario y agrega un path implicito para ordenar que suma un tiempo mas antes de retornar la respuesta.

A partir de 10g R2 cambió el path SORT GROUP BY por el path HASH GROUP BY mejorando el rendimiento dado que no se infiere la necesidad de retornar el resultado ordenado. Todas las unidades de código que "confiaban" en este ordenamiento implicito y que necesitan por negocio un cierto orden van a comenzar a devolver resultados erroneos al upgradear a 10g R2 o versión superior, recordemos que las "Best Practices" dictan usar siempre ORDER BY cuando debe haber un orden ya que el comportamiento no esta garantizado a futuro.

Ahora, como suelo hacer, les voy a mostrar el ejemplo del group by, en próximas notas le voy a mostrar otros "issues" que pueden causar fuertes dolores de cabeza cuando no se detectan a tiempo.

Voy a usar mi conocida, y nunca bien ponderada, tablita de ejemplo T

rop@TEST10G> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production


rop@TEST10G> create table t as select * from dba_objects;

Tabla creada.

rop@TEST10G> exec dbms_stats.gather_table_Stats(user,'T');

Procedimiento PL/SQL terminado correctamente.


rop@TEST10G> select object_type,count(1)
2         from t
3         group by object_type

OBJECT_TYPE           COUNT(1)
------------------- ----------
INDEX                    22538
JOB CLASS                    2
CONTEXT                      5
TABLE SUBPARTITION          18
TYPE BODY                  174
INDEXTYPE                   10
PROCEDURE                  252
RESOURCE PLAN                4
RULE                         4
JAVA CLASS               16417
SCHEDULE                     1
TABLE PARTITION            625
WINDOW                       2
WINDOW GROUP                 1
JAVA RESOURCE              770
TABLE                    22631
TYPE                      1941
VIEW                      3804
LIBRARY                    150
FUNCTION                   329
TRIGGER                    565
PROGRAM                     12
MATERIALIZED VIEW            3
DATABASE LINK                5
CLUSTER                     10
SYNONYM                  23307
PACKAGE BODY               807
QUEUE                       27
CONSUMER GROUP               6
EVALUATION CONTEXT          14
RULE SET                    19
DIRECTORY                   16
UNDEFINED                    6
OPERATOR                    57
JAVA DATA                  306
DIMENSION                    5
SEQUENCE                  2759
LOB                        713
PACKAGE                    866
JOB                         18
INDEX PARTITION            617
LOB PARTITION                1
XML SCHEMA                  26

43 filas seleccionadas.

El listado salio desordenado, veamos el plan que genera:

rop@TEST10G>  explain plan for
2   select object_type,count(1)
3   from t
4   group by object_type;

Explicado.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2963600285

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    26 |   208 |   339   (7)| 00:00:05 |
|   1 |  HASH GROUP BY     |      |    26 |   208 |   339   (7)| 00:00:05 |
|   2 |   TABLE ACCESS FULL| T    | 99843 |   780K|   324   (2)| 00:00:04 |
---------------------------------------------------------------------------

9 filas seleccionadas.

El path es HASH GROUP BY en reemplazo de SORT HASH GROUP

Ahora, voy a usar, el truco mas rápido para salir del paso, tipo de solución "quick and dirty", pero solución al fin, que me ha salvado varias veces cuando se pasó por alto algún nuevo mecanismo y se comienzan a ver los problemas en plena hora pico o cuando cancelan procesos baths al dia siguiente del upgrade. Podemos setear a nivel sesion el parámetro "optimizer_features_enable", tambien se puede setear a nivel de hint con opt_param(param,valor), para hacer un flashback al comportamiento de un versión anterior. Como el caso de esta nota se da a partir de 10g R2, como estrategia siempre tomo la decisión de ir al upgrade anterior mas próximo donde funciona como antes, para asi estabilizar el comportamiento y no tener que retornar al release original. Como dije antes, en este caso la solución definitiva sera disparar un requerimiento de cambio de código y que el sector de desarrollo agregue el order by en las sentencias que necesitan del ordenamiento para funcionar correctamente.

rop@TEST10G> alter session set optimizer_features_enable = '10.1.0.5';

Sesión modificada.


rop@TEST10G>  select object_type,count(1)
2           from t
3           group by object_type;

OBJECT_TYPE           COUNT(1)
------------------- ----------
CLUSTER                     10
CONSUMER GROUP               6
CONTEXT                      5
DATABASE LINK                5
DIMENSION                    5
DIRECTORY                   16
EVALUATION CONTEXT          14
FUNCTION                   329
INDEX                    22538
INDEX PARTITION            617
INDEXTYPE                   10
JAVA CLASS               16417
JAVA DATA                  306
JAVA RESOURCE              770
JOB                         18
JOB CLASS                    2
LIBRARY                    150
LOB                        713
LOB PARTITION                1
MATERIALIZED VIEW            3
OPERATOR                    57
PACKAGE                    866
PACKAGE BODY               807
PROCEDURE                  252
PROGRAM                     12
QUEUE                       27
RESOURCE PLAN                4
RULE                         4
RULE SET                    19
SCHEDULE                     1
SEQUENCE                  2759
SYNONYM                  23307
TABLE                    22631
TABLE PARTITION            625
TABLE SUBPARTITION          18
TRIGGER                    565
TYPE                      1941
TYPE BODY                  174
UNDEFINED                    6
VIEW                      3804
WINDOW                       2
WINDOW GROUP                 1
XML SCHEMA                  26

43 filas seleccionadas.

Cambié la version del optimizador y el resultado fue el esperado, salió ordenado. Miremos el nuevo plan:

rop@TEST10G>  explain plan for
2  select object_type,count(1)
3   from t
4   group by object_type;

Explicado.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3156910365

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    26 |   208 |   337   (6)| 00:00:05 |
|   1 |  SORT GROUP BY     |      |    26 |   208 |   337   (6)| 00:00:05 |
|   2 |   TABLE ACCESS FULL| T    | 99843 |   780K|   322   (2)| 00:00:04 |
---------------------------------------------------------------------------

9 filas seleccionadas.

Ahora uso en antiguo path SORT GROUP BY y en consecuencia la salida no altero el orden
Por ultimo, agregamos el order by y vemos como se agrega (ahora explicitamente) un path para ordenar el resultado antes de retornarlo.

rop@TEST10G> ed
Escrito file afiedt.buf

1   explain plan for
2  select object_type,count(1)
3   from t
4   group by object_type
5*  order by object_type
rop@TEST10G> /

Explicado.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3861070257

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    26 |   208 |   354  (11)| 00:00:05 |
|   1 |  SORT ORDER BY      |      |    26 |   208 |   354  (11)| 00:00:05 |
|   2 |   HASH GROUP BY     |      |    26 |   208 |   354  (11)| 00:00:05 |
|   3 |    TABLE ACCESS FULL| T    | 99843 |   780K|   324   (2)| 00:00:04 |
----------------------------------------------------------------------------

Este es un caso interesante de cambio de comportamiento que saca a la luz problemas de mala programación, que en versiones anteriores pasaban desapercibidas. Por tal motivo es muy importante tener código de calidad ,y que no solo funcione, para evitar sorpresas a futuro. En la próxima nota les voy a mostrar otro caso de cambio interesante, relacionado con cambios en el orden de evaluación de predicados.