jueves, 10 de septiembre de 2009

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.

1 comentario:

  1. Pablo:
    Felicitaciones, es un articulo claro y bien concreto, muy util.

    ResponderEliminar