martes, 11 de septiembre de 2007

Clustering Factor

El Clustering Factor nos dice que tan ordenado se encuentran los registros de la tabla en base a los valores del índice.

Respecto al valor que obtenemos del Clustering Factor, podemos hacer 2 observaciones:

- Si el valor se acerca a la cantidad de bloques de la tabla, entonces se dice que la tabla está muy bien ordenada. En ese caso, el contendido del índice de un leaf block, tiende a apuntar a registros del mismo bloque de datos.
- Si el valor se acerca a la cantidad de registros de la tabla, entonces se dice que la tabla está muy mal ordenada. En ese caso, es improbable que el contendido del índice de un leaf block, tienda a apuntar a registros del mismo bloque de datos.

Bien, pero como afectaría el Clustering Factor en nuestra consulta?

Veamos un ejemplo:

Creamos una tabla llamada "ordenada":

SQL_9iR2> CREATE TABLE ordenada AS
2 SELECT level x, rpad(dbms_random.random,100,'*') y
3 FROM dual
4 CONNECT BY level <= 100000 ;

Table created.

SQL_9iR2> ALTER TABLE ordenada
2 ADD CONSTRAINT ord_pk
3 PRIMARY KEY(x) ;

Table altered.

SQL_9iR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDENADA', cascade => true) ;

PL/SQL procedure successfully completed.

Creamos una tabla llamada "desordenada":

SQL_9iR2> CREATE TABLE desordenada AS
2 SELECT x, y
3 FROM ordenada
4 ORDER BY y ;

Table created.

SQL_9iR2> ALTER TABLE desordenada
2 ADD CONSTRAINT desord_pk
3 PRIMARY KEY(x) ;

Table altered.

SQL_9iR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'DESORDENADA', cascade => true) ;

PL/SQL procedure successfully completed.

Ya tenemos nuestras 2 tablas creadas, analizadas y con sus respectivos índices.

Veamos un poco los valores que hay en cada una de las tablas...

SQL_9iR2> SELECT x, y
2 FROM ordenada
3 WHERE rownum <= 5 ;

X Y
------ --------------------------------------------------
1 1546142627****************************************
**************************************************

2 -1607493826***************************************
**************************************************

3 -1823003438***************************************
**************************************************

4 -385107593****************************************
**************************************************

5 -478367392****************************************
**************************************************

5 rows selected.

SQL_9iR2> SELECT x, y
2 FROM desordenada
3 WHERE rownum <= 5 ;

X Y
------ --------------------------------------------------
50230 -1000006552***************************************
**************************************************

37976 1000011102****************************************
**************************************************

71202 1000046989****************************************
**************************************************

54512 -1000054026***************************************
**************************************************

73252 -1000056784***************************************
**************************************************

5 rows selected.

Pueden notar que la columna X (donde creamos las 2 primary key) está ordenada en la tabla "ordenada" y desordenada en la tabla "desordenada".

Veamos el Clustering Factor de los índices de cada tabla:

SQL_9iR2> SELECT a.index_name, b.num_rows, b.blocks, a.clustering_factor
2 FROM dba_indexes a, dba_tables b
3 WHERE a.index_name IN ('ORD_PK','DESORD_PK')
4 AND a.table_name = b.table_name ;

INDEX_NAME NUM_ROWS BLOCKS CLUSTERING_FACTOR
------------------------------ ---------- ---------- -----------------
DESORD_PK 100000 1539 99932
ORD_PK 100000 1539 1539


2 rows selected.

Para el índice DESORD_PK podemos ver que el valor del CLUSTERING_FACTOR se acerca al valor de NUM_ROWS, esto nos quiere decir que la tabla se encuentra muy mal ordenada y que es improbable que el contendido del leaf block de éste índice, tienda a apuntar a registros del mismo bloque de datos.

Para el índice ORD_PK podemos ver que el valor del CLUSTERING_FACTOR es igual al valor de BLOCKS, esto nos quiere decir que la tabla se encuentra muy bien ordenada y que el contendido del leaf block de éste índice, tiende a apuntar a registros del mismo bloque de datos.

Qué consecuencias puede tener el Clustering Factor en la ejecución de nuestra consulta?

Supongamos la siguiente consulta en la tabla "ordenada":

SQL_9iR2> explain plan for
2 SELECT *
3 FROM ordenada
4 WHERE x BETWEEN 55000 AND 60000 ;

Explained.

SQL_9iR2> @explains

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5002 | 512K| 89 |
| 1 | TABLE ACCESS BY INDEX ROWID| ORDENADA | 5002 | 512K| 89 |
|* 2 | INDEX RANGE SCAN | ORD_PK | 5002 | | 12 |
---------------------------------------------------------------------------

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

2 - access("ORDENADA"."X">=55000 AND "ORDENADA"."X"<=60000)

SQL_9iR2> SET AUTOTRACE TRACEONLY STATISTICS

Statistics
---------------------------------------------------
0 recursive calls
0 db block gets
757 consistent gets
0 physical reads

0 redo size
562801 bytes sent via SQL*Net to client
4159 bytes received via SQL*Net from client
335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5001 rows processed

Vemos que el CBO optó por utilizar el índice ORD_PK para realizar un range scan. El CBO sabe, gracias al valor del Clustering Factor, que el índice de ésta tabla se encuentra ordenado; y como nosotros colocamos la condición "BETWEEN 55000 AND 60000", Oracle puede caminar en forma horizontal a través de los leaf blocks sin necesidad de realizar demasiadas lecturas de bloques ya que los valores se encuentran ordenados; y como se encuentran ordenados, el contenido de los leaf blocks tienden a apuntar a registros del mismo bloque de datos.

Ahora veamos que sucede si ejecutamos la misma consulta pero en la tabla "desordenada":

SQL_9iR2> explain plan for
2 SELECT *
3 FROM desordenada
4 WHERE x BETWEEN 55000 AND 60000 ;

Explained.

SQL_9iR2> @explains

---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5002 | 512K| 150 |
|* 1 | TABLE ACCESS FULL | DESORDENADA | 5002 | 512K| 150 |
---------------------------------------------------------------------

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

1 - filter("DESORDENADA"."X">=55000 AND "DESORDENADA"."X"<=60000)

SQL_9iR2> SET AUTOTRACE TRACEONLY STATISTICS

Statistics
---------------------------------------------------
0 recursive calls
0 db block gets
1876 consistent gets
1539 physical reads

0 redo size
562801 bytes sent via SQL*Net to client
4159 bytes received via SQL*Net from client
335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5001 rows processed

En éste caso, el CBO sabe, gracias al valor del Clustering Factor, que el índice de ésta tabla se encuentra desordenado. Entonces, opta por realizar un full scan ya que si accede por el índice DESORD_PK, va a tener que leer mayor cantidad de bloques que si realiza un barrido completo de la tabla.

Hay personas fanáticas de los índices y tratan de evitar a toda costa el acceso por full scan. Bien, veamos que sucede si fuerzo al CBO a que utilice el índice de la tabla desordenada:

SQL_9iR2> explain plan for
2 SELECT /*+ INDEX(desordenada desord_pk) */ *
3 FROM desordenada
4 WHERE x BETWEEN 55000 AND 60000 ;

Explained.

SQL_9iR2> @explains

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5002 | 512K| 5011 |
| 1 | TABLE ACCESS BY INDEX ROWID| DESORDENADA | 5002 | 512K| 5011 |
|* 2 | INDEX RANGE SCAN | DESORD_PK | 5002 | | 12 |
----------------------------------------------------------------------------

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

2 - access("DESORDENADA"."X">=55000 AND "DESORDENADA"."X"<=60000)

SQL_9iR2> SET AUTOTRACE TRACEONLY STATISTICS

Statistics
---------------------------------------------------
0 recursive calls
0 db block gets
5344 consistent gets
1495 physical reads

0 redo size
562801 bytes sent via SQL*Net to client
4159 bytes received via SQL*Net from client
335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5001 rows processed

Observamos que si utilizamos el índice de la tabla desordenada, leemos muchos más bloques de datos que si dejamos al CBO hacer lo que mejor sabe hacer; que en éste caso para la tabla desordenada, es realizar un full scan de la tabla.

11 comentarios:

Anónimo dijo...

Muy buenos artículos, y muy buen blog. Sigue así.

Un saludo desde España.

Hector Gabriel Ulloa Ligarius dijo...

Hola Leonardo

Muy buen artículo del Clustering Factor

Sólo una consulta, cuando indicas que todos los datos que lee el índice se encuentran en el mismo bloque de datos y que la búsqueda se hará de forma horizontal cuando el factor de clustering es cercana a la cantidad de bloques de la tabla, con esto quieres decir que Oracle no tiene necesidad de ir a leer a disco otros bloques de datos

Me llama la atención, que a pesar de que se este leyendo un índice en los datos que muestras no haya lecturas a disco.

Atte
Hector Gabriel Ulloa Ligarius
Santiago de Chile
http://ligarius.wordpress.com

Leonardo Horikian dijo...

Hector, Oracle no lee todos los registros del mismo bloque de datos. Lee todos los bloques necesarios para encontrar los registros que necesita.

Recordá lo siguiente: Podemos crear índices ascendentes (default) o descendentes. El orden que indicamos al crear el índice, es el orden en el que van a estar ordenadas las entradas de los bloques del índice, no de la tabla. Entonces, el índice queda ordenado como nosotros lo indicamos, pero cuando recorremos el índice, podemos encontrarnos con que estamos leyendo más bloques de los necesarios; y ésto es debido a que si los datos de la tabla estan desordenados, tengamos que hacer muchos saltos de bloques (lecturas) para satisfascer el orden que impusimos en el índice.

Con el Clustering Factor, Oracle sabe si le conviene o no acceder por índice, porque quizas (como en nuestro ejemplo), realizar un full scan de la tabla es menos costoso (leemos menos bloques de datos) que el acceso por índice.

La lectura de bloques se realiza de forma horizontal (en éste ejemplo) porque se está realizando un INDEX RANGE SCAN.

En la primer consulta que ejecuté (en donde se accede por índice), no es que jamas hubo lecturas a disco. Si ejecutamos esa consulta por primera vez y los bloques no se encuentran en el data buffer, vamos a tener que leer a disco por lo menos la primera vez que ejecutamos la consulta. Yo SIEMPRE ejecuto las consultas 2 veces cuando lo que me interesa son las estadísticas (del autotrace) de la consulta. Porque hago esto? Porque supongamos que ejecuto una consulta y me muestra 1000 bloques leidos desde disco. Si ejecuto esa misma consulta otra vez mas, puede darse 2 casos:
1) que no lea más bloques desde disco porque ya se encuentran en memoria.
2) que sigamos leyendo bloques desde disco (lo cual puede estar indicándonos un problema).
Es por esto que ejecuto todas mis consultas 2 veces antes de ver las estadísticas. En este ejemplo, para la ejecución de la primer consulta, no hay accesos a disco porque cuando ejecuté la consulta por segunda vez, leyó los bloques desde memoria, no desde disco.

Hector Gabriel Ulloa Ligarius dijo...

Muchas gracias Leonardo

Allí encontré la explicación a mi duda.. Lo ejecutas 2 veces.

Bueno, quizás sea la forma más óptima de sacar estadísticas de la consulta

Muy bueno tu blog Leonardo, te felicito, ya está dentro de mis favoritos, un abrazo desde el otro lado de la cordillera

Regards

Atte
Hector Gabriel Ulloa Ligarius
http://ligarius.wordpress.com

Anónimo dijo...

Un blog estupendo y muy útil.

Saludos desde España.

Anónimo dijo...

Hola,

Muy buena y útil explicación.

Otra cosa:
¿Sabéis si se puede saber si un índice se ha usado alguna vez?

Saludos

Leonardo Horikian dijo...

Hola, para saber si un índice se usó alguna vez, tendrías que seguir éstos pasos:

Habilitar el monitoreo del índice:

SQL> ALTER INDEX nombre_indice MONITORING USAGE;

Ver información acerca del uso del índice:

SELECT index_name,
table_name,
monitoring,
used,
start_monitoring,
end_monitoring
FROM v$object_usage
WHERE index_name = 'NOMBRE_INDICE';

Deshabilitar el monitoreo del índice:

SQL> ALTER INDEX nombre_indice NOMONITORING USAGE;


Espero que te sea de utilidad.

Saludos

Anónimo dijo...

La información es de mucha utilidad, gracias por los ejemplos. Saludos desde México.

Luis dijo...

Hola Leonardo
en el caso de la tabla desordenada, para que la consulta use el indice como se debe y que los gets sean lo mas ligeors....como se puede reducir el factoring cluster? haciendo una reorganisacion de la tabla?

Muchas gracias
PD: Felicitacion por tu BLOG es genial. From Barcelona !

Leonardo Horikian dijo...

Hola Luis,

Para reorganizar la tabla y reducir el Clustering Factor, lo que se suele hacer es recrear la tabla reordenandola.

Ejemplo:


SQL> CREATE TABLE desordenada_nueva AS
2 SELECT *
3 FROM desordenada
4 ORDER BY x;

Tabla creada.

SQL> ALTER TABLE desordenada_nueva
2 ADD CONSTRAINT desord_pk_nueva
3 PRIMARY KEY(x) ;

Tabla modificada.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'DESORDENADA_NUEVA',cascade=>true) ;

Procedimiento PL/SQL terminado correctamente.

SQL> SELECT a.index_name, b.num_rows, b.blocks, a.clustering_factor
2 FROM dba_indexes a, dba_tables b
3 WHERE a.index_name IN ('ORD_PK','DESORD_PK','DESORD_PK_NUEVA')
4 AND a.table_name = b.table_name ;

INDEX_NAME NUM_ROWS BLOCKS CLUSTERING_FACTOR
--------------------------- ---------- ---------- -----------------
ORD_PK 100000 1190 1190
DESORD_PK 100000 1191 99919
ORD_PK_NUEVA 100000 1190 1190

2 rows selected.


Saludos

Luis dijo...

Hola Leonardo
ok muchas gracias

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.