La funcionalidad de muestreo dinámico o Dynamic Sampling

La funcionalidad de muestreo dinámico (Dynamic Sampling) estuvo por primera vez disponible para la release 2 de la bases de datos Oracle 9i. Esta funcionalidad posibilita que el optimizador SQL y PL/SQL basado en costes (CBO) muestree las tablas que utiliza una consulta (query) durante la fase de parsing duro, para mejorar los valores estadísticos que utiliza el optimizador al incluir en dichas estadísticas los segmentos no analizados con anterioridad. Como ya he indicado, este muestro sólo ocurre durante la fase de parsing duro y se utiliza para mejorar las estadísticas utilizadas por el optimizador PLSQL, de ahí el nombre de muestro dinámico.

El optimizador de las bases de datos Oracle usa una gran variedad de datos de entradas para generar los planes de ejecución. Utiliza todos los índices y restricciones (constraints) definidos en las tablas, las estadísticas del sistema (velocidad de entrada/salida de los servidores, velocidad de CPU), y las estadísticas recogidas de los segmentos involucrados en la ejecución de la consulta. El optimizador utiliza estas estadísticas para estimar el número de registros involucrados en cada uno de los pasos de los que consta un plan de ejecución específico, siendo este volumen de registros la variable principal a la hora de calcular el coste de ejecución de una consulta. Cuando el optimizador calcula incorrectamente el volumen de registros, probablemente escogerá un plan de ejecución ineficiente causando problemas de rendimiento en la base de datos. De hecho, la principal razón por la que el optimizador Oracle puede decidirse por un plan de ejecución incorrecto, es precisamente por esta causa, es decir, una estimación incorrecta del volumen de registros a procesar en cada paso del plan de ejecución.

En conclusión, el principal motivo que existe detrás de utilizar la funcionalidad del muestreo dinámico, es ayudar al optimizador a estimar correctamente el volumen de registros a procesar, alimentándole con mayor cantidad de datos y una información más exacta relativa a la ejecución de la consulta que se necesita ejecutar en ese mismo momento.

Formas de trabajar del muestreo dinámico o Dynamic Sampling

El tipo de muestreo dinámico puede establecerse de dos formas diferentes:

• Con el parámetro OPTIMIZER_DYNAMIC_SAMPLING puede establecerse el tipo de muestreo dinámico, bien a nivel de instancia de la base de datos, bien a nivel de sesión mediante la utilización del comando ALTER SESSION.
• También se puede utilizar el hint de SQL o PL/SQL denominado DYNAMIC_SAMPLING para establecer es tipo de muestreo a nivel de consulta

A continuación os mostraré un ejemplo en el que podréis ver algunas diferencias entre un plan de ejecución generado por el optimizador Oracle sin utilizar muestreo dinámico, y otro plan en el que si que se utiliza dicha funcionalidad. En el ejemplo debemos considerar que la tabla mitabla consta de 60.000 registros:

SQL> set autotrace traceonly explain
SQL> select /*+ dynamic_sampling(mit 0) */ * 
SQL> from mitabla mit;

Execution Plan
------------------------------------------------------
Plan hash value: 1601036213
------------------------------------------------------
|Id|Operation        |Name|Rows |Cost (%CPU)|Time    |
------------------------------------------------------
| 0|SELECT STATEMENT |    |15034| 52     (0)|00:00:01|
| 1|TABLE ACCESS FULL|T   |15034| 52     (0)|00:00:01|
------------------------------------------------------

SQL> select * from mitabla mit;

Execution Plan
------------------------------------------------------
Plan hash value: 1601036213
------------------------------------------------------
|Id|Operation        |Name|Rows |Cost (%CPU)|Time    |
------------------------------------------------------
| 0|SELECT STATEMENT |    |64222| 54     (2)|00:00:01|
| 1|TABLE ACCESS FULL|T   |64222| 54     (2)|00:00:01|
------------------------------------------------------

En el ejemplo vemos un primer caso de plan de ejecución en el que hemos deshabilitado el muestreo dinámico utilizando el hint DYNAMIC_SAMPLING, mientras que el segundo plan de ejecución, puesto que por defecto dicha funcionalidad está habilitada y no hemos utilizado ningún hint en la consulta, se ha generado utilizando el muestro dinámico. Como podemos ver, los planes de ejecución arrojan datos bastante dispares. Al deshabilitar el muestreo dinámico, el optimizador utiliza los valores estadísticos por defecto que típicamente no son muy fiables, ya que el número de registros se estima en base a número de bloques de datos en la tabla y al tamaño medio de los registros de dicha tabla. Así, al deshabilitar el muestreo dinámico, el optimizador deduce que la tabla cuenta con tan sólo 15.034 registros frente a los 60.000 que tiene en realidad. Por otro lado, al activar el muestreo dinámico, el optimizador da un valor mucho más cercano a la realidad, 64.222 registros.

La inexactitud se hace todavía más patente si borramos totalmente el contenido de la tabla mitabla. En este caso, el plan de ejecución si deshabilitamos el muestreo dinámico seguirá devolviéndonos el valor de 15.034 registros, mientras que el plan de ejecución con el dynamic sampling habilitado no dirá que el número de registros a procesar es tan sólo 1.

El muestreo dinámico es más eficaz cuando en una consulta estamos utilizando una tabla que ha sido creada y poblada con datos, pero que todavía no ha sido analizada mediante el paquete estándar PLSQL DBMS_STATS, paquete que se utiliza para generar los datos estadísticos de las tablas. Hay que considerar que desde la primera release de la base de datos Oracle 10g, ya sólo esta soportado el optimizador basado en costes, y que dicho optimizador necesita datos estadísticos exactos para funcionar correctamente. Por lo tanto, si una tabla existe y sus estadísticas no han sido recogidas todavía, el muestreo dinámico ayuda sobremanera a que el optimizador genere planes de ejecución adecuados.

Por último terminaré indicando que la funcionalidad de muestreo dinámico ofrece once niveles de dynamic sampling, de 0 (deshabilitado) a 10. En la release 2 de Oracle 9i el valor por defecto es 1, mientras que en la release 2 de Oracle 10g y superiores, el valor por defecto es 2. Si queréis informaros acerca del significado e implicaciones que supone utilizar un nivel de muestreo dinámico u otro, os emplazo a que visitéis este enlace: Dynamic Sampling Levels.

Artículos relacionados: Hints PLSQL para el modo de optimización. Planes de ejecución Oracle.