viernes, 13 de marzo de 2009

Dynamic Sampling en acción

El muestreo dinámico, introducido por primera vez en 9i R2, es un mecanismo que permite "ayudar" al optimizador por costos (CBO) a obtener un mejor plan de acceso a los datos, recordemos que CBO es el unico soportado desde 10g. El CBO usa información tal como: cardinalidad, selectividad, constraints definidas, velocidad de transferencia i/o, latencia, velocidad de cpu, etc. Es evidente que cuanto mas información pueda recolectar mejor será el plan que arme. Una de las métricas mas importantes es la cardinalidad, que nos da la cantidad de filas estimadas que devolverá nuestra consulta. Si la cardinalidad estimada es cercana a la real podemos asegurar que nuestra consulta funcionará bien.
Un problema recurrente que salió a la luz con el CBO (desde sus primeras versiones en Oracle 7.1.xx) fue y es la falta o desactualización de estadísticas en los segmentos involucrados en las sentencias parseadas, convengamos que CBO basa todo su análisis en información estadística (nro de filas, tamaño promedio de filas, clustering_factor de un índice, cantidad de hojas de un indice,etc). Para minimizar este problema se agregó un mecanismo denominado "dynamic sampling" que tiene 11 niveles posibles (desde 0 al 10, ver: Dynamic Sampling Levels) y que permite, tal como su nombre lo indica, realizar en el momento del hard parse un muestreo para las tablas que no posean estadísticas. En 9i R2 esta configurado con nivel 1 y en 10g con nivel 2, posiblemente en versiones futuras tome un valor aún mayor. Este nuevo parámetro resulta muy util para las tablas temporales ya que sobre este tipo de tablas no se pueden recolectar estadísticas (si se les puede setear con dbms_stats.set_table_stats).

Los dos parámetros relativos al muestreo dinámico son:

OPTIMIZER_DYNAMIC_SAMPLING:
se utiliza cuando se quiere setear a nivel de instancia o a nivel de sesion.
DYNAMIC_SAMPLING :
se usa como hint en la sentencias.


Ahora voy a mostrarles algunas pruebas y conclusiones, utilizando el hint DYNAMIC_SAMPLING para "jugar" con los distintos niveles y ver como es estimada la cardinalidad en cada caso

rop@DESA10G> create table t as select * from dba_objects;

Tabla creada.

rop@DESA10G> select count(1) from t;

COUNT(1)
----------
102002

La tabla tiene 102002 filas en total.
Voy a activar el modo trace en el sqlplus para ver los planes:

rop@DESA10G>set autotr trace exp

Ahora realizo una consulta sin filtro con dynamic sampling en 0 sobre la tabla T sin estadísticas, de forma tal de simular el comportamiento en versiones inferiores a 9i R2:

rop@DESA10G>select /*+ dynamic_sampling(t 0) */ * from t

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   120K|    20M|   333   (1)| 00:00:08 |
|   1 |  TABLE ACCESS FULL| T    |   120K|    20M|   333   (1)| 00:00:08 |
--------------------------------------------------------------------------

La estimación fue de 120 mil contra los 102 mil reales. Pruebo como se comportaría en 9i R2, ya que el nivel default es 1:

rop@DESA10G> select /*+ dynamic_sampling(t 1) */ * from t;

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 72365 |    12M|   332   (1)| 00:00:08 |
|   1 |  TABLE ACCESS FULL| T    | 72365 |    12M|   332   (1)| 00:00:08 |
--------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

La estimación cambió bastante, ahora es menor a la real y dió alrededor de 72 mil filas

Ahora pruebo con el valor default del dynamic_sampling en 10g:

rop@DESA10G> select /*+ dynamic_sampling(t 2) */ * from t;

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   101K|    17M|   333   (1)| 00:00:08 |
|   1 |  TABLE ACCESS FULL| T    |   101K|    17M|   333   (1)| 00:00:08 |
--------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

Como observamos la cardinalidad estimada fue muy cercana a la realidad.

Por último pruebo con el nivel más alto posible de dynamic_sampling:

rop@DESA10G> select /*+ dynamic_sampling(t 10) */ * from t;

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   102K|    17M|   333   (1)| 00:00:08 |
|   1 |  TABLE ACCESS FULL| T    |   102K|    17M|   333   (1)| 00:00:08 |
--------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

Se puede ver que ahora la estimación fue precisa, pero tuvimos que usar el nivel mas alto, lo cual implica una sampling muy agresivo que va demorar un tiempo considerable para tablas grandes.

En la siguiente prueba voy a analizar que pasa cuando la tabla posee estadísticas (utilizo analyze por simplicidad ya que es solo una prueba, pero todos sabemos que hay que usar siempre dbms_stats)

rop@DESA10G> analyze table t compute statistics;

Tabla analizada.

rop@DESA10G>  select * from t;

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   102K|  8865K|   333   (1)| 00:00:08 |
|   1 |  TABLE ACCESS FULL| T    |   102K|  8865K|   333   (1)| 00:00:08 |
--------------------------------------------------------------------------

En el plan se nota que no fue necesario usar dynamic_sampling y tal como esperaba la estimación fue precisa.

Trunco la tabla para mostrar que sucede cdo no hay estadísticas y no usa muestreo:

rop@DESA10G> truncate table t;

Tabla truncada.

rop@DESA10G> select /*+ dynamic_sampling(t 0) */ * from t;

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   102K|  8865K|   333   (1)| 00:00:08 |
|   1 |  TABLE ACCESS FULL| T    |   102K|  8865K|   333   (1)| 00:00:08 |
--------------------------------------------------------------------------

En realidad no hay filas pero la estimación me dio 102 mil filas, imaginen que puede pasar con una sentencia similar si el optimizador arma un plan con tal error de cardinalidad.


rop@DESA10G> select /*+ dynamic_sampling(t 1) */ * from t;

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    89 |   331   (1)| 00:00:08 |
|   1 |  TABLE ACCESS FULL| T    |     1 |    89 |   331   (1)| 00:00:08 |
--------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

Con tan solo elevar un solo nivel el sampling la estimación cambia sensiblemente y refleja la realidad
de los datos.

En la siguiente voy a crear una tabla con dos columnas, de forma tal de que nunca tengan valores iguales. Esto lo hago para mostrar que aunque se use sampling default, si no hay estadísticas el CBO no estima bien la cantidad de filas a retornar:

Voy a crear una tabla con 1M de filas de forma tal que la columna c2 = c1 +1, y voy a realizar un select de forma tal de buscar la filas cuyas columnas c1 y c2 sean iguales, obviamente dada la lógica que se uso para llenar la tabla deberá devolver 0 filas:

rop@DESA10G> create table t as select rownum c1,rownum+1 c2 from dual connect by rownum <= 1000000;  Tabla creada.


Para dynamic_sampling 0:

rop@DESA10G> set autotr trace exp


rop@DESA10G> select /*+ dynamic_sampling(t 0) */ * from t
2          where c1 = 1 and c2 =1;

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    17 |   442 |   476   (1)| 00:00:11 |
|*  1 |  TABLE ACCESS FULL| T    |    17 |   442 |   476   (1)| 00:00:11 |
--------------------------------------------------------------------------

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

1 - filter("C1"=1 AND "C2"=1)


La cardinalidad fue de 17 filas

rop@DESA10G>  select /*+ dynamic_sampling(t 2) */ * from t
2           where c1 = 1 and c2 =1;

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    88 |  2288 |   486   (3)| 00:00:11 |
|*  1 |  TABLE ACCESS FULL| T    |    88 |  2288 |   486   (3)| 00:00:11 |
--------------------------------------------------------------------------

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

1 - filter("C1"=1 AND "C2"=1)

Note
-----
- dynamic sampling used for this statement

extrañamente para nivel default en 10g de dynamic_sampling la estimación fue aun peor, 88 filas.

Con el valor de muetreo máximo:

rop@DESA10G> ed
Escrito file afiedt.buf

1   select /*+ dynamic_sampling(t 10) */ * from t
2*          where c1 = 1 and c2 =1
rop@DESA10G> /

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    26 |   487   (3)| 00:00:11 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    26 |   487   (3)| 00:00:11 |
--------------------------------------------------------------------------

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

1 - filter("C1"=1 AND "C2"=1)

Note
-----
- dynamic sampling used for this statement


Con nivel 10 la estimación fue correcta, no probé pero tal vez la cardinalidad correcta se alcance con un nivel menor, pero mayor a 2.

Actualizo las estádisticas:

rop@DESA10G> analyze table t compute statistics;

Tabla analizada.

rop@DESA10G> select /*+ dynamic_sampling(t 2) */ * from t
2  where c1 = 1 and c2 =1;

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     8 |   487   (3)| 00:00:11 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |     8 |   487   (3)| 00:00:11 |
--------------------------------------------------------------------------

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

1 - filter("C2"=1 AND "C1"=1)

Con dynamic_sampling default la cardinalidad estimada es la real.

La última prueba que voy a hacer es usando binding, sin estadísticas:


rop@DESA10G> variable x number;

rop@DESA10G> exec :x := 1;

Procedimiento PL/SQL terminado correctamente.


rop@DESA10G> analyze table t delete statistics;

Tabla analizada.

rop@DESA10G>  select /*+ dynamic_sampling(t 2) */ * from t
2   where c1=:x and c2=:x;

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    88 |  2288 |   491   (4)| 00:00:11 |
|*  1 |  TABLE ACCESS FULL| T    |    88 |  2288 |   491   (4)| 00:00:11 |
--------------------------------------------------------------------------

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

1 - filter("C1"=TO_NUMBER(:X) AND "C2"=TO_NUMBER(:X))

Note
-----
- dynamic sampling used for this statement

La estimación fue la misma que cuando usamos literales.

Veamos que pasa si elevamos el nivel a 4:

rop@DESA10G> ed
Escrito file afiedt.buf

1  select /*+ dynamic_sampling(t 4) */ * from t
2*         where c1=:x and c2=:x
rop@DESA10G> /

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    97 |  2522 |   493   (5)| 00:00:11 |
|*  1 |  TABLE ACCESS FULL| T    |    97 |  2522 |   493   (5)| 00:00:11 |
--------------------------------------------------------------------------

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

1 - filter("C1"=TO_NUMBER(:X) AND "C2"=TO_NUMBER(:X))

Note
-----
- dynamic sampling used for this statement

La estimación es mas lejana a la realidad.

Con nivel 10:

rop@DESA10G> ed
Escrito file afiedt.buf

1  select /*+ dynamic_sampling(t 10) */ * from t
2*         where c1=:x and c2=:x
rop@DESA10G> /

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100 |  2600 |   493   (5)| 00:00:11 |
|*  1 |  TABLE ACCESS FULL| T    |   100 |  2600 |   493   (5)| 00:00:11 |
--------------------------------------------------------------------------

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

1 - filter("C1"=TO_NUMBER(:X) AND "C2"=TO_NUMBER(:X))

Note
-----
- dynamic sampling used for this statement


Sorpresivamente, por lo menos para mi, la estimación con dynamic_sampling en 10 es de 100 filas.
Con solo analizar la tabla, vemos que la cardinalidad del plan es la correcta:

rop@DESA10G> analyze table t compute statistics;

Tabla analizada.

rop@DESA10G> select * from t
2  where c1=:x and c2=:x;

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     8 |   493   (5)| 00:00:11 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |     8 |   493   (5)| 00:00:11 |
--------------------------------------------------------------------------

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

1 - filter("C1"=TO_NUMBER(:X) AND "C2"=TO_NUMBER(:X))

rop@DESA10G>

En conclusión se ve que cuando usamos binding el dynamic_sampling no parece ser muy útil, lo cual no estaría tan mal ya que este parámetro fue diseñado pensando en sistemas DW y no OLTP ya que la idea del sampling es para sistemas que ejecutan pocas consultas largas (tipicamente sistemas de DataWarehouse) y no para sistemas OLTP donde se ejecutan muchas veces consultas cortas. En sistemas OLTP es recomendable usar profiles (10g r1 en adelante) ya que permiten persistir en el diccionario información estadística de las consultas (sampling) para ayudar y complementar a las estadísticas típicas de los objetos. También podemos pensar que en ciertos casos, aunque estemos en sistemas OLTP, el muestreo puede minimizar el sesgo con la realidad para tablas temporales o tablas normales con estadísticas nulas y así lograr un mejor plan.

Ref 1: Database Performance & Tuning Guide (10g)
Ref 2: On Dynamic Sampling
Publicado por en
Etiquetas: ,

No hay comentarios:

Publicar un comentario

Suscribirse a: Enviar comentarios (Atom)