Browse Category: Performance

Join Method II

CHOOSING THE RIGHT JOIN METHOD

En muchas ocasiones dejamos al optimizador que decida que join utilizar. Sin embargo, en muchas ocasiones, ya sea por la mala elección del optimizador o por nuestros conocimientos de los datos nos permite una mejor toma de decisiones.

SORT-MERGE/HASH VERSUS NESTED LOOPS

Sort-merge join y hash join pueden ser considerados de la misma “familia” de joins (proporcionan un buen rendimiento bajo condiciones similares, mientras que los nested loops se adaptan a una categoría diferente de queries. Por lo tanto cuando determines que tipo de join usar, hay que plantearse si un nested loop es apropiada.

La decisión entre hash/sort-merge y nested loop debería estar basada en:

  • Necesidad de rendimiento vs necesidad de tiempo de respuesta. Nested Loop habitualmente ofrece mejor tiempo de respuesta, pero hash/sort-merge pueden ofrecer mejor rendimiento.
  • La proporción de tablas en el join. Un mayor subconjunto de filas procesadas puede ser más rápido con hash/sort-merge.
  • Disponibilidad de índices para soportar el join. Un nested loop es más efectivo cuando un índice puede ser usado en las tablas del join.
  • Memoria y CPU disponible para ordenaciones. Grandes ordenaciones pueden consumir una cantidad excesiva de recursos y puede ralentizar la ejecución.
  • Hash join podría conseguir grandes beneficios en ejecuciones paralelas y operaciones orientadas a partición. Aunque nested loop y sort-merge también pueden ser usadas en paralelización.

Table 1 Determining the Optimal Join Method

WHEN JOINING A TO B (IN THAT ORDER)  CONSIDER SORT-MERGE OR HASH JOIN?  CONSIDER NESTED LOOPS USING AN INDEX ON B?
Both A and B are small. Yes. Maybe, depending on the selectivity of the index and the size of the outer table.
Only selecting a small subset of rows from B (and B has an index). No. Performing a table scan of B will be cost-inefficient. Yes. The index will reduce the number of IOs on B.
Want the first row as quickly as possible. No. The first row won’t bereturned until both A and B are scanned, sorted, and merged or until the hash table has been built. Yes. Rows can be returned as soon as they are fetched using the index.
Want to get all rows as quickly as possible. Maybe. Nested loops might still get all rows before sort-merge or hash join if other conditions apply. Maybe. Nested loops might still get all rows before sort merge if other conditions apply.
Doing a full table scan of A and want to use parallel query. Yes. Yes. Nested loops can be resolved in parallel if the outer (first) table in the join is retrieved via a full table scan.
Memory is limited, especially PGA memory. Maybe not. Large sorts can have significant overhead, especially if memory for sorts is limited. Hash joins can also require more memory than nested loops. Yes. The nested loops join avoids sorting and is, therefore, less affected by memory limitations.

 SORT-MERGE VERSUS HASH JOINS
Sort-merge está disponible desde versiones antiguas de Oracle, mientras que el hash join es más reciente. Hash join puede ser empleado en muchos, pero no todos los caso donde puede ser empleado el sort-merge. Cuando ambos son directamente comparados, el hash join tiende a un mejor rendimiento que un sort-merge join.

Sin embargo, sort-merge join tiene una más amplia aplicabilidad. Hash join puede ser ejecutado sólo cuando la condición join es una condición de igualdad, y los sort-merge pueden ser utilizados para resolver condiciones de no igualdad.

El sort-merge requiere más memoria y para ejecutar eficientemente y más CPU para durante las ordenaciones.

Aunque el hash join habitualmente tiene un rendimiento superior que el sort-merge, si los datos de entrada ya están ordenados, las operaciones de ordenación serán menos costosas y el sort-merge será más efectivo.

Referencias: Oracle Performance Survival Guide (Guy Harrison)

Notas: La Table 1 he preferido no traducirla, ya que los conceptos pueden ser más sencillos de entender.

Join Methods I

Esta es la primera de una serie de entradas en las que quiero hablar sobre los métodos Join de acceso a datos.  Independientemente de la categoría lógica de join, Oracle tiene tres tipos de algoritmos join: nested loops join, short-merge join y hash join. Cada join es óptimo en diferentes circunstancias.

NESTED LOOPS JOIN

Oracle realiza una búsqueda de la segunda tabla (o inner) por cada fila encontrada en la primera tabla (o outer). Sin un índice en la tabla inner, se necesitará escanear la inner table una vez por cada fila en la outer table. Esto puede provocar que nested table scan tenga un incremento exponencial en cuanto a sobrecarga y tiempo de ejecución dependiendo del crecimiento de la tabla.

nestedloop

El optimizador habitualmente seleccionará una operación nested loops sólo si hay un índice en la inner table. Si se debe acceder a la mayoría de los datos de la inner table un nested loop suele ser menos efectivo que un sort-merge o hash join.

SORT- MERGE JOIN

Cuando se ejecuta un merge-join, Oracle ordena cada tabla (o result set) por el valor de la columna join. Una vez ordenadas, los dos conjuntos de datos son fusionados.

sortmerge

En este tipo de join se suelen leer la mayoría de datos de las tablas o no hay disponible un acceso por índice en la inner table. El hash join (lo veremos más adelante) suele ser más efectivo en los casos donde pueden ser considerados ambos. Sin embargo hay casos donde podemos emplear sort-merge join y no hash join (non-equi join).

HASH JOIN

Cuando ejecutamos un hash join Oracle construye una hash table para una de las dos tablas comprendidas en el join. Esta hash table es utilizada para encontrar registros coincidentes en una forma algo similar a la utiliza en los nested loop con índices.

hashjoin

Como comentamos anteriormente el hash join es habitualmente más efectivo que sort-merge join en circunstancias en las que ambos son aplicables y pudiera ser más efectivo que nested loop donde una gran proporción de registros de la tabla son incluidos.

Referencias: Oracle Performance Survival Guide (Guy Harrison)

Indexing Guidelines

Para cuando nos preguntemos en qué casos debería crear un índice, por qué debería crearlo o por qué no, presentaré un pequeño checklist que nos puede ayudar a decidirnos que hacer:

Guideline Razón
Crear tantos índices como necesites, pero intentando mantener un número mínimo. Añade índices de forma juiciosa. Comprueba primero para tener medidas por las que guiarte. Los índices incrementan el performance, pero también consumen espacio en disco y recursos (CPU, memoria, I/O). No añadas índices innecesariamente.
El performance requerido de una query sobre una tabla debe ser la base de la estrategia de indexaxión. Indexar columnas usadas en queries SQL ayudarán al performance de dicha query.
Considerar usar SQL Tuning Advisor o SQL Access Advisor para recomendaciones de índices. Estas herramientas ofrecen recomendaciones y una importante ayuda en tu decisión de indexación.
Crea PK (Primary Key) constraints para todas las tablas. Esto automáticamente creará un índice B-Tree (si las columnas en la PK no están previamente indexadas).
Crea unique key constraints donde sea apropiado. Esto automáticamente creará un índice B-Tree (si las columnas en la PK no están previamente indexadas).
Crea índices en las columnas que tengan una FK (Foreign Key). Las columnas con FK son habitualmente incluidas en cláusulas WHERE con JOIN de tablas y esto mejorará el performance de sentencias SQL SELECT. Creando un índice B-Tree en la columna FK también reducirá bloqueos cuando actualices e insertes en las tablas hijas.
Seleccionar con cuidado y probar los índices en tablas pequeñas. Incluso en tablas pequeñas un índice puede mejorar el performance de la SQL.
Uso correcto del tipo de índices. Seleccionar el tipo de índice correcto para cada caso puede mejorar el performance.
Usar un índice B-Tree si no se tiene un aumento de performance verificado con un tipo diferente de índice. Los índices B-Tree son adecuados para muchas aplicaciones donde se tiene una alta cardinalidad en los valores de columnas.
Considerar usar índices Bitmap en entornos Data Warehouse. Este tipo de índices son ideales para columnas con baja cardinalidad donde los valores no son actualizados a menudo. Índices Bitmap trabajan bien en columnas con FK donde tú a menudo ejecutas queries con condiciones JOIN usando AND y OR.
Considerar usar tablespace separados para índices (separarlos de las tablas) Los datos de tablas e índices deben tener diferente almacenamiento. Usando diferentes tablespace la administración de los índices se hará de forma separada a la de tablas.
Que el índice herede sus propiedades de almacenamiento desde el tablespace. Esto hará más sencillo de administrar el índice.
Uso de nombres estándar y consistentes. Esto hará el mantenimiento y la resolución de problemas más sencilla.
No haga rebuild de índices a menos que tengas una sólida razón para ello. Realizar un rebuild de índice es habitualmente innecesario salvo que un índice esté corrupto o tú quieras mover el índice a diferente tablespace.
Monitorizar los índices y borrar los que no se utilizan. Esta operación libera espacio y mejora el performance de las operaciones DML.
Antes de borrar un índice, considera marcarlo como unusable o invisible. Esto permitirá determinar la mejora si el no hay pérdida de rendimiento al borrar el índice. Podrás volver a rehacer o realizar rebuild del índice sin requerir ejecutar sentencia DDL para la creación del índice.

Referencias: “Expert Indexing in Oracle Database 11g”

Clustering Factor

Oracle toma las I/O por bloques. Por lo tanto la decisión del optimizador de realizar TABLE FULL SCAN  está influenciado por la cantidad de bloques accedidos, no de filas. Esto es conocido como index cluatering factor. Si los bloques contienen una fila, entonces, las filas y bloques accedidos son los mismos.

Por todo esto, el clustering factor es una característica muy importante para la toma de decisiones del CBO, mas concretamente, a la hora de elegir el coste asociado a una operación mediante un INDEX RANGE SCAN o un TABLE FULL SCAN.

Con esta característica podemos observar la ordenación del índice con los datos en la tabla. Una tabla con los datos mal ordenados tendrá un clustering factor alto o cercano al número de registros, lo cual llevará a que el CBO acceda a los datos mediante FULL TABLE SCAN. Por el contrario si los datos están bien ordenados en la tabla , tendremos un clustering factor bajo o cercano al numero de bloques de datos, y el CBO llevara a cabo un INDEX RANGE SCAN.

Cuando un índice puede ser usado, Oracle revisa la información del Clustering Factor del índice, el cuál le dice a Oracle el número de bloques que deben ser leídos para conseguir los datos necesarios por la condición de la query.

Evidentemente podemos ver que con un clustering factor bueno, reducimos el número de I/O lógicas requeridas.

Algo importante a tener en cuenta es que no vamos a reducir el clustering factor llevando a cabo un rebuild del índice, ya que el clustereing factor está asociado con la ordenación de los datos dentro de la tabla y no del índice. La única forma de cambiar esto es cambiar el índice o el orden de la tabla.

La siguiente query es un ejemplo de como podemos obtener el clustering factor, entre otros valores, del índice dado:

SELECT a.index_name, b.num_rows, b.blocks, a.clustering_factor
FROM dba_indexes a, dba_tables b
WHERE a.index_name = ‘nombre_indice’
AND a.table_name = b.table_name ;