Oracle 12c R2 - Mejoras a APROXIMATE QUERY

APPROX_COUNT_DISTINCT en Oracle 12.1

En muchos escenarios de negocio, se requiere conocer información acumulada (total de ventas, cantidad de clientes nuevos, etc. etc.) con un grado de certeza importante, pero sin llegar a la necesidad de que el resultado sea 100% exacto.

Por ejemplo, la gerencia de una cadena de supermercados puede decidir dejar de abrir los días domingos, basándose en la cantidad de operaciones realizadas en dichos días y comparándolo con los otros días de la semana. En dicha situación, conocer el  número exacto de operaciones de venta por cada día de la semana no es una necesidad, sino que con un resultado aproximado es posible tomar una decisión.

En Oracle 12c, se introdujo la función de agregación aproximada APPROX_COUNT_DISTINCT que permite obtener resultados aproximados en forma mas rápida y consumiendo menos recursos.

El principal inconveniente de esta nueva funcionalidad radica en la necesidad de modificar el código existente para utilizar esta nueva función, como podemos ver en el siguiente ejemplo:

SELECT APPROX_COUNT_DISTINCT(manager_id) AS "Active Managers"
  FROM employees;

Nuevos parámetros de Oracle 12.2

Oracle 12c Release 2 introduce tres nuevos parámetros que permiten que las funciones de agregación "normales" (aquellas que venimos utilizando en nuestro código) realicen los cálculos por aproximación, sin necesidad de modificar las consultas existentes.

Los mismos son:

approx_for_aggregation
approx_for_count_distinct
approx_for_percentile

Ejemplo

Si ejecutamos una sentencia con COUNT(DISTINCT) en nuestra base de datos, sin habilitar el uso de funciones de aproximación, y consultamos el resultado y el plan de ejecución, obtenemos lo siguiente:

SQL> SELECT name, value
FROM   v$ses_optimizer_env
WHERE  sid = SYS_CONTEXT('USERENV','SID')
AND    name LIKE '%approx%';

NAME                                     VALUE
---------------------------------------- -------------------------
approx_for_aggregation                   false
approx_for_count_distinct                false
approx_for_percentile                    none

SQL>  SELECT COUNT(DISTINCT id) AS data_count
  2  FROM   t2;

DATA_COUNT
----------
   1010000

SQL> SET AUTOTRACE TRACE EXPLAIN
SQL> SELECT COUNT(DISTINCT id) AS data_count
FROM   t2;

Execution Plan
----------------------------------------------------------
Plan hash value: 4170058314

-----------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |     1 |    13 |       |  4472   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE      |          |     1 |    13 |       |            |          |
|   2 |   VIEW               | VW_DAG_0 |  1010K|    12M|       |  4472   (1)| 00:00:01 |
|   3 |    HASH GROUP BY     |          |  1010K|  4931K|    11M|  4472   (1)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T2       |  1010K|  4931K|       |  1390   (1)| 00:00:01 |
-----------------------------------------------------------------------------------------

Analizando el plan de ejecución, podemos ver que que la consulta generó una vista agrupando por un HASH, para poder calcular el resultado acumulado, la cual consumió 11 Mb. de espacio temporal. El costo total de la consulta es de 4.472, correspondiendo 3.082 de ellos a dicha vista temporal.

Al habilitar el uso de funciones de agregación aproximadas por defecto (sin necesidad de cambiar la funcion COUNT() por la nueva función APROX_COUNT_DISTINCT(), la misma consulta devuelve otro resultado y nos genera el siguiente plan de ejecución:

SQL> ALTER SESSION SET approx_for_aggregation = TRUE;
Session altered.

SQL> SELECT name, value
FROM   v$ses_optimizer_env
WHERE  sid = SYS_CONTEXT('USERENV','SID')
AND    name LIKE '%approx%';

NAME                                     VALUE
---------------------------------------- -------------------------
approx_for_aggregation                   true
approx_for_count_distinct                true
approx_for_percentile                    all

SQL>  SELECT COUNT(DISTINCT id) AS data_count
  2  FROM   t2;

DATA_COUNT
----------
    979478

SQL> SET AUTOTRACE TRACE EXPLAIN
SQL> SELECT COUNT(DISTINCT id) AS data_count
FROM   t2;

Execution Plan
----------------------------------------------------------
Plan hash value: 3321871023

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |     5 |  1390   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE APPROX|      |     1 |     5 |            |          |
|   2 |   TABLE ACCESS FULL   | T2   |  1010K|  4931K|  1390   (1)| 00:00:01 |
------------------------------------------------------------------------------

Podemos ver que la segunda consulta devuelve un resultado (979.478) que difiere con el resultado exacto (1.010.000), con una diferencia del 3.02% respecto al resultado exacto.

Pero en este caso, el plan de ejecución nos muestra que Oracle no necesitó agrupar los datos en una vista, reduciendo el costo total en de 4.472 a 1.390 y sin consumir los 11 Mb. de espacio temporal.

En este ejemplo, se logra una reducción del 69% del costo de la consulta sacrificando un 3% de exactitud, lo cual puede ser un valor aceptable para muchos casos.