martes, 3 de febrero de 2009

Por que el optimizador no usa mi índice cuando arma el plan de ejecución (Clustering Factor)

Es común que los desarrolladores se frusten al ver que el optimizador por costos (CBO) no usa el índice que recién crearon. No es casualidad que le haya llevado varios años a Oracle convencer a sus clientes que el optimizador por reglas (RBO) no servía mas (de hecho se dejó de actualizar en 1992). Es cierto que versión tras versión el CBO fue haciéndose mas sofisticado (de hecho se lo considera una de las piezas de software mas complejas que existe) y también es verdad que en sus primeras versiones tenia algunos problemas, pero eso fue cambiando. En mi opinión, el gran salto se dio a partir de 10g ya que despues de muchas promesas durante los años anteriores, fue recien en 10g donde se desoportó a RBO, aunque se podía seguir utilizando.
Uno de los motivos mas frecuentes de que el optimizador no escogiera el plan ideal era la falta o desactualizacion de estadísticas de los objetos involucrados en las sentencias. Para mi las dos principales innovaciones de 10g fueron: a) la automatización de la recolección de estadísticas mediante las ventanas de mantenimiento, que se activan por default, y b) el denominado dynamic sampling por el cual si se realiza una pseudo recolección de estadísticas "on the fly" (al vuelo) para los segmentos que aun no cuenten con información estadística. Sin embargo, hay casos donde las estadísticas son "frescas" pero de todas formas nos frustamos al ver que no se arma el plan que pretendemos, no se usa nuestro índice.
Cuando comencé con el tema de tuning sentí esa misma frustración muchas veces y con el tiempo me di cuenta que en la mayoría de las casos era por desconocimiento, no sabia que pasaba y entonces recurría a una técnica muy frecuente y efectiva para salir del paso, el uso de un hints. No voy a ahondar demasiado en el tema de hints, pero es sabido que excepto algunos tipos de hints en ciertos casos especificos, no es una práctica recomendada. Uno de esos desconocimientos fue no conocer que era el clustering_factor. El clustering_factor es un número (una parte de información recolectada en la toma de estadísticas de los índices) que compara el orden de un índice con el grado de desorden en la tabla que indexa. Ese número puede ser tan pequeño como la cantidad de bloques de la tabla indexada o tan grande como el número de filas indexadas. Si ese valor tiende a ser igual a la cantidad de bloques decimos que el clustering_factor es bajo y si el valor tiende a ser igual a la cantidad de filas decimos que el clustering_factor es alto. En este ultimo caso es cuando el optimizador tiende a evitar el uso del índice para consultas del tipo RANGE SCAN. Cuanto mas alto sea el clustering_factor menos probable será que el optimizador escoja el índice para generar su plan.Ahora voy a intentar ejemplificar como funciona el clustering_factor:

Con sqlplus creo una tabla T con datos en cierto orden para que al indexar por la columna X me queden ordenados (clustering factor bajo) y al indexar por la columna Y me queden desordenados (clustering factor alto)

rop@DESA10G> create table t
2 as
3 select trunc(rownum/10000) x,
4 trunc(dbms_random.value(0,9999)) y,
5 dbms_random.string('a',10) z
6 from dual
7 connect by rownum <= 1000000;

Tabla creada.



rop@DESA10G> create index t_x on t(x);

Índice creado.

rop@DESA10G> create index t_y on t(y);

Índice creado.

rop@DESA10G> begin
2 dbms_stats.gather_table_stats(ownname => user,tabname => 'T',cascade => true);
3 end;
4 /

Procedimiento PL/SQL terminado correctamente.


Vamos a ver la cantidad de filas y bloques estimadas para la tabla T:

rop@DESA10G> select num_rows,blocks
2 from user_tab_statistics
3 where table_name = 'T';

NUM_ROWS BLOCKS
---------- ----------
997144 3243

rop@DESA10G> select index_name,clustering_factor,leaf_blocks,num_rows
2 from user_ind_statistics
3 where table_name = 'T';

INDEX_NAME CLUSTERING_FACTOR LEAF_BLOCKS NUM_ROWS
------------------------------ ----------------- ----------- ----------
T_X 3183 1952 1000000
T_Y 984472 2090 1000000


Como resultado de la recolección estadistica se ve que el indice T_X tiene un clustering factor similar a la cantidad de bloques y que el indice T_Y tiene un clustering factor similar a la cantidad de filas.

Una vez armado el escenario, vamos a ver como cambia el plan en concordancia con el factor de aglutinamiento (clustering factor) de acuerdo a la columna que se usa para filtrar en el predicado


rop@DESA10G> select avg(length(z)) from t where x between 90 and 100;

AVG(LENGTH(Z))
--------------
10


Plan de Ejecución
----------------------------------------------------------
Plan hash value: 288193465

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 523 (1)| 00:00:12 |
| 1 | SORT AGGREGATE | | 1 | 14 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 100K| 1374K| 523 (1)| 00:00:12 |
|* 3 | INDEX RANGE SCAN | T_X | 100K| | 200 (1)| 00:00:05 |
-------------------------------------------------------------------------------------

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

3 - access("X">=90 AND "X"<=100)


Estadísticas
----------------------------------------------------------
1 recursive calls
0 db block gets
518 consistent gets
0 physical reads
0 redo size
250 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


El optimizador eligió ir por el indice ya que es mas barato dado que tiene el dato de que el clustering factor es bajo.

Ahora probamos de filtrar por la columna Y, cuyo indexacion tiene clustering factor elevado:


rop@DESA10G> select avg(length(z)) from t where y between 90 and 100;

AVG(LENGTH(Z))
--------------
10


Plan de Ejecución
----------------------------------------------------------
Plan hash value: 1842905362

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 740 (3)| 00:00:17 |
| 1 | SORT AGGREGATE | | 1 | 15 | | |
|* 2 | TABLE ACCESS FULL| T | 1197 | 17955 | 740 (3)| 00:00:17 |
---------------------------------------------------------------------------

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

2 - filter("Y"<=100 AND "Y">=90)


Estadísticas
----------------------------------------------------------
1 recursive calls
0 db block gets
3190 consistent gets
0 physical reads
0 redo size
250 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

rop@DESA10G>


Tal cual se esperaba el optimizador optó por hacer un full scan basandose en la dispersión de los datos en los bloques de la tabla.

9 comentarios:

  1. Esclarecedor, muy claro como siempre. Gracias Pablo !

    ResponderEliminar
  2. Acabo de encontrar tu blog... y ya estoy suscrito para leerlo cada ves que publiques.

    Esta buenisimo este y otros articulos que he leido. Voy a recomendarte con otros compañeros de la oficina.

    gracias por tu excelente artículo.

    saludos!

    ResponderEliminar
  3. Gracias Carcasco, me alegro que te haya gustado, espero continuar escribiendo notas que sean de utilidad.

    ResponderEliminar
  4. Estimado Pablo, muy valioso este articulo, gracias por compartirlo!. Pregunta, porque el índice Pk de mi tabla no posee un clustering_factor?, quizás esté tomando mal las stats?
    Desde ya muchas gracias!
    Fernando

    ResponderEliminar
  5. Si, seguramente no estes analizando ese índice. Que version de Oracle estas usando?, la columna num_rows la tienes vacia tambien?. Que comando utilizas para analizar?

    ResponderEliminar
  6. Pablo, creo que use el package para tablas sin el cascade, ahora estoy utilizando el package para index. Cualquier novedad te comento.
    Gracias!
    Fernando

    ResponderEliminar
  7. Hola Pablo, antes que nada te dejo mis felicitaciones por estos artículos tan bien explicados, yo hace un año que estoy trabajando con Oracle y seguramente me serán de gran ayuda.

    Con respecto a este artículo particular te quería preguntar si existe alguna manera de ordenar la tabla física por alguna columna en particular, para lograr que el clustering factor disminuya.

    Muchas gracias.

    ResponderEliminar
  8. Hola Facundo, gracias por leer mi notas. Con respecto a tu pregunta no le veo mucho sentido organizar la tabla de forma tal de obtener un mejor CF. Generalmente las tablas tienen mas de un indice definido y obviamente solo se podrá organizar respecto a uno solo. El CF es un valor de descripción que sirve para dar mayor información al optimizador respecto a la mejor forma de acceder los datos, y tener un dato mas de decisión para determinar si es mejor acceder por indice o por full scan.

    ResponderEliminar
  9. Hola Pablo,

    Probe en mi base de datos, pero en ambos casos utiliza el indice. Incluso aumente la cantidad de registros a 10,000,000, pensando que era muy pequeña la tabla, pero igual.
    Se copio los resultados.

    select num_rows,blocks
    from user_tab_statistics
    where table_name = 'T';
    10000000 8195

    select index_name,clustering_factor,leaf_blocks,num_rows
    from user_ind_statistics
    where table_name = 'T';

    T_X 12418 5319 10465248
    T_Y 9516903 5172 10115201


    select avg(length(z)) from t where x between 90 and 100;

    select avg(length(z)) from t where y between 90 and 100;


    explain plan SET STATEMENT_ID = 'X' for select avg(length(z)) from t where x between 90 and 100;
    explain plan SET STATEMENT_ID = 'Y' for select avg(length(z)) from t where y between 90 and 100;


    select statement_id, plan_id, timestamp, remarks, operation, options, object_name
    from plan_table
    order by timestamp desc, id

    Y 12958 11/12/2012 5:55:56 PM (Null) SELECT STATEMENT (Null) (Null)
    Y 12958 11/12/2012 5:55:56 PM (Null) SORT AGGREGATE (Null)
    Y 12958 11/12/2012 5:55:56 PM (Null) TABLE ACCESS BY INDEX ROWID T
    Y 12958 11/12/2012 5:55:56 PM (Null) INDEX RANGE SCAN T_Y
    X 12957 11/12/2012 5:54:55 PM (Null) SELECT STATEMENT (Null) (Null)
    X 12957 11/12/2012 5:54:55 PM (Null) SORT AGGREGATE (Null)
    X 12957 11/12/2012 5:54:55 PM (Null) TABLE ACCESS BY INDEX ROWID T
    X 12957 11/12/2012 5:54:55 PM (Null) INDEX RANGE SCAN T_X

    En ambos casos realiza el Index Range Scan.

    ResponderEliminar