DBMS_STATS Parte 3, continuación…

GATHER_TABLE_STATS

Continuando con gather_table_stats, ahora quiero ver la opción METHOD_OPT. Vamos a recordar que opciones podemos tener para este parámetro:

FOR ALL [INDEXED | HIDDEN] COLUMNS
[size_clause]

FOR COLUMNS [size clause] column|attribute [size_clause] [,column|attribute [size_clause]...]

size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}

-Integer : numéro de buckets de histogramas dentro del rango de 1 .. 254.

-Repeat: Calcula histogramas solamente en las columnas que ya tienen histogramas.

-Auto: Oracle determina a cuales columnas les debe de calcular histogramas basándose en la carga y distribución de datos.

-Skewonly: Oracle determina a cuales columnas debe calcular histogramas basándose en la distribución de datos.

El valor de DEFAULT es FOR ALL COLUMNS SIZE AUTO

Histogramas

Antes de pasar a escribir instrucciones vamos a ver que son los histogramas para comprender por que es útil la opción mencionada.

Cuando se calculan estadísticas en una tabla, DBMS_STATS obtiene información sobre la distribución de los datos en las columnas de la tabla. La información básica sobre la distribución puede ser el valor máximo y mínimo de una columna, pero esta información o este nivel de estadísticas pueden ser insuficientes si los datos dentro de la columna no están distribuidos uniformemente (skewed, no se como traducir esa palabra). Para distribuciones “no uniformes” se utilizan los histogramas y le dicen a Oracle como están distribuidos los datos en la columna.

Básicamente los histogramas son útiles cuando tenemos datos distribuidos de manera no uniforme, por ejemplo si tenemos una tabla con 1,000,000 de registros donde 950,000 tienen el valor ‘X’ y otros 50,000 tienen otros valores diferentes(Y, Z, A, B, C, etc). Cuando queremos obtener los datos que tienen X (where columna=’X’) obviamente no queremos que utilice un índice ya que va a tener un costo muy alto debido a que tiene que accesar casi TODA (FULL) la tabla + todo el índice, pero cuando queremos un valor especifico (una minoría) como Y (where columna=’Y’) entonces ahí si queremos que use un índice ya que no va a accesar toda la tabla si no una porción.

Oracle utiliza dos tipos de histogramas: Heigh-balanced y frecuencia.

Heigh-Balanced

En este tipo los valores de las columnas son divididos en bandas o grupos y cada grupo contiene aproximadamente el mismo número de registros. Tomemos por ejemplo una columna que tiene valores del 1 al 100 y un histograma con 10 grupos (buckets), entonces el histograma seria como sigue:

En este ejemplo el número de registros por grupo es 1/10 de todos los registros de la tabla, esto quiere decir 10 registros por cada grupo.

Si los datos no estuvieran uniformemente podría ser algo así:

Aquí la mayoría de los registros tienen un valor de 5 y por ejemplo los registros con un valor entre 60 y 100 son 1/10 de todos los registros de la tabla, esto quiere decir que pueden ser como máximo 10 registros en comparación con el ejemplo anterior que son 4/10 y que podrían llegar a ser 40 registros.

Frecuencia

En este tipo cada valor de la columna corresponde a un grupo (bucket) del histograma. Cada grupo contiene el número de ocurrencias/repeticiones de un valor.

5 20 5 6 10 5 3 23 234 12

a

b

c

d

e

f

g

h

i

x

Y pues de esta manera tenemos 10 grupos donde el grupo de “a” tiene 5 registros, el grupo de “e” tiene 10 repeticiones, etc.

Manos a la obra…

Para terminar con la teoría vamos a ver un ejemplo como funciona este rollo…

SQL> CREATE TABLE DEMO AS SELECT * FROM ALL_OBJECTS;
Table created.
SQL> CREATE INDEX STAT_IDX ON DEMO(STATUS);
Index created.
SQL> SET AUTOTRACE TRACEONLY EXPLAIN;
SQL> SELECT COUNT(*)
  2  FROM DEMO
  3  WHERE STATUS='INVALID';
Execution Plan
----------------------------------------------------------
Plan hash value: 3974250510
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |     5 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |          |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| STAT_IDX |    94 |   470 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("STATUS"='INVALID')
Note
-----
   - dynamic sampling used for this statement
SQL> SELECT COUNT(*)
  2  FROM DEMO
  3  WHERE STATUS='VALID';
Execution Plan
----------------------------------------------------------
Plan hash value: 2265447936
----------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |     5 |    32   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |          |     1 |     5 |            |          |
|*  2 |   INDEX FAST FULL SCAN| STAT_IDX | 50562 |   246K|    32   (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("STATUS"='VALID')
Note
-----
   - dynamic sampling used for this statement

Ahora vamos a calcular estadisticas con histogramas.

SQL> exec dbms_stats.gather_table_stats('SCOTT','DEMO',METHOD_OPT=>'FOR COLUMNS SIZE AUTO STATUS');
PL/SQL procedure successfully completed.
SQL> SELECT COUNT(*)
  2  FROM DEMO
  3  WHERE STATUS='INVALID';
Execution Plan
----------------------------------------------------------
Plan hash value: 3974250510
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |     7 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |          |     1 |     7 |            |          |
|*  2 |   INDEX RANGE SCAN| STAT_IDX |   146 |  1022 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("STATUS"='INVALID')
SQL> SELECT COUNT(*)
  2  FROM DEMO
  3  WHERE STATUS='VALID';
Execution Plan
----------------------------------------------------------
Plan hash value: 2265447936
----------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |     7 |    31   (7)| 00:00:01 |
|   1 |  SORT AGGREGATE       |          |     1 |     7 |            |          |
|*  2 |   INDEX FAST FULL SCAN| STAT_IDX | 52655 |   359K|    31   (7)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("STATUS"='VALID')
SQL> SET AUTOT OFF;
SQL> SELECT COUNT(*)
  2  FROM DEMO
  3  WHERE STATUS='INVALID';
  COUNT(*)
----------
        94
SQL> SELECT COUNT(*)
  2  FROM DEMO
  3  WHERE STATUS='VALID';
  COUNT(*)
----------
     52711

Aquí podemos que el explain plan no cambio mucho, “al parecer” salió peor que sin histogramas, por ejemplo con el valor INVALID tenemos rows 94 vs 146 al final, con la otra opción tenemos 50562 vs 52655 lo cual esta un poco alejado al numero de registros que existen. Esto se debe a que le dijimos a Oracle que hiciera el cálculo de manera AUTOmatica.

Vamos ahora a decirle un número de buckets.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','DEMO',METHOD_OPT=>'FOR COLUMNS SIZE 100 STATUS');
PL/SQL procedure successfully completed.
SQL> CONNECT SCOTT/ORACLE;
Connected.
SQL> SET AUTOT TRACEONLY EXPLAIN
SQL> SELECT COUNT(*)
  2  FROM DEMO
  3  WHERE STATUS='INVALID';
Execution Plan
----------------------------------------------------------
Plan hash value: 3974250510
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |     7 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |          |     1 |     7 |            |          |
|*  2 |   INDEX RANGE SCAN| STAT_IDX |    99 |   693 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("STATUS"='INVALID')
SQL> SELECT COUNT(*)
  2  FROM DEMO
  3  WHERE STATUS='VALID';
Execution Plan
----------------------------------------------------------
Plan hash value: 2265447936
----------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |     7 |    31   (7)| 00:00:01 |
|   1 |  SORT AGGREGATE       |          |     1 |     7 |            |          |
|*  2 |   INDEX FAST FULL SCAN| STAT_IDX | 52702 |   360K|    31   (7)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("STATUS"='VALID')

Orale, ahora vemos que la información que tiene Oracle es mas exacta respecto al numero de registros, por ejemplo INVALID 94 vs 99 y VALID 50562 vs 52702, cuando los valores reales son 94 y 52711. Así que el numero de bandas (buckets) que le especifiquemos a Oracle que tiene que crear influyen bastante en como Oracle va a identificar los diferentes rangos.

Cuando debo calcular histogramas.

Bueno igual que en todo los casos de performance tunning no existe una regla que aplique a todos los casos, según lo que he leído por ejemplo muchas personas recomiendan calcular histogramas en sistemas de datawarehouse y no “siempre” en OLTP (aunque hay excepciones), si se conoce muy bien la aplicación y se sabe que alguna(s) columna(s) tiene una distribución de datos no uniforme por ejemplo de 1 millón de registros solo 1000 tienen valores diferentes y el resto tiene el valor X, tal vez esa columna sea una candidata para histogramas. Una vez observe que un query tenia un explain plan “perfecto” accesaba índices, el costo era bajo, etc. Sin embargo para algunos casos se tardaba “mas” tiempo que en otros casos y querían saber cual era la razón, estuvimos probando con varias opciones y nos dimos cuenta que una de las columnas usadas en el query caia en el esquema mencionado anteriormente, calculamos histogramas (200 buckets) en esa columna y el query mejoro bastante su ejecución.

Aunque esto se aplica a las columnas yo no recomendaría calcular histogramas a TODAS las columnas debido a que no siempre será benéfico o no tendrá un gran impacto en la ejecución y pues además el calculo implica mas tiempo al obtener estadísticas con gather_table_stats.

Conclusión

Quería mencionar esta opción de gather_table_stats debido a que puede ayudar muchísimo a la ejecución de un query, le da mas información a Oracle sobre la distribución de la información y eso ayuda a obtener un mejor explain plan. Aunque recomiendo hacer pruebas antes de implementarlo en un ambiente de producción.

Hasta la próxima…a lo mejor continuo con gather_schema_Stats

  1. #1 by Jean Pierre on 16 January, 2009 - 18:27

    Una pregunta se bloquea las tablas o los esquemas cuando corro estas estadisticas????

  2. #2 by Paula on 22 May, 2009 - 22:47

    hola!
    gracias por todos los post enfocados a estadísticas
    ojalá puedas complementar con gather_schema_stats
    gracias nuevamente,
    saludos

  3. #3 by delfinonunez on 24 May, 2009 - 15:58

    Gracias por pasar por el blog, buena idea sobre gather_Schema_Stats, no es muy complicado ya que si entiendes los de las tablas el de schema es para todo el usuario, pero si lo voy a poner en la lista de pendientes…desafortunadamente por el momento no tengo computadora propia para poder hacer pruebas y bloggear pero voy a tratar de hacerlo en ratos libres en el trabajo.

    Saludos.

  4. #4 by beto on 9 July, 2009 - 17:53

    que onda!!

    esto de las estadisticas en oracle esta muy chido pero … ya no has publicado nada sobre las estadisiticas de schema, diccionario de datos ya te dio hueva????

    saludos desde el defectuso distrito federal !!!!

  5. #5 by delfinonunez on 9 July, 2009 - 18:22

    jajajaja, es cierto Beto esta chido y tambien es cierto que me ha dado hueva en el… ultimo año. Me he entretenido en otras cosas, espero volver a escribir…las ganas si las tengo, pero como dices, me da mucha hueva…jejejejeje

    Pero si voy a volver a escribir al respecto, no prometo cuando porque ahorita ando muy desganado.

  6. #6 by Alexis on 24 March, 2010 - 20:59

    Te felicito por las 3 partes de dbms_stats, realmente han sido muy buenas y me dejaron todo bastante claro

    saludos

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: