Estadísticas, estadísticas y más estadísticas Parte 1.

Últimamente hemos tenido algunos problemas de performance algunos de ellos han estado relacionados con la generación de estadísticas en las tablas, así que decidí escribir algo para recordarme que son, para que sirven y como funcionan.

  • Que son y para que sirven?

Primero lo primero, que son las famosas estadísticas. Bueno básicamente datos/información que describe en detalle los objetos dentro de la base de datos, regularmente las tablas y los indices. Estas estadísticas son utilizadas por el query optimizer que se encarga de seleccionar el plan de ejecución más optimo para un query.
Ejemplos de estadísticas son:

  • Tablas (numero de registros, numero de bloques de datos, tamaño promedio del registro).
  • Columnas (Numero de valores distintos, numero de valores NULOS, como esta distribuida la información en las columnas.
  • Indices (Numero de hojas, niveles, clustering factor.
  • Sistema (Utilización y desempeño de I/O y del CPU )

Toda esta información se guarda dentro del diccionario de datos de Oracle. Debido a la naturaleza cambiante(delete,update,insert) de los objetos (tablas, indices) se deben calcular las estadísticas de manera regular dependiendo de la carga de cambios que se tenga en la base de datos.

  • Como calculo las estadísticas?

Existen dos formas(instrucciones) para calcular estadísticas: ANALYZE TABLE y DBMS_STATS.
Hoy nada mas voy a ver ANALYZE TABLE, después entraré en detalle con DBMS_STATS ya que requiere más tiempo.

  • ANALYZE TABLE

Esta instrucción permite calcular estadísticas, validar la estructura de un indice o tabla, identificar en una tabla registros migrados o encadenados (migrated, chained).

Nota: Aunque esta instrucción todavía existe en la versión 10g no se recomienda usarla para calcular estadísticas desde la versión 9i. Así que solo es ilustrativa para saber que existe.

Requisitos
Se debe tener el privilegio ANALYZE ANY para poder analizar la tabla de otro usuario, pero si se desea analizar las tablas de su propio usuario entonces solo debe existir el objeto dentro de mi schema.

Sintaxis rápida

ANALYZETABLE   xxx COMPUTE STATISTICSINDEX       ESTIMATE STATISTICS  {SAMPLE nn [ROWS|PERCENT]}

CLUSTER     DELETE STATISTICS

Donde xxx es el nombre de la tabla o el indice.

Opciones
COMPUTE STATISTICS. Calcula las estadísticas de manera exacta, básicamente lee todos y cada unos de los registros con sus columnas para generar estadísticas, esto quiere decir que si tengo una tabla con 100 millones de registros esta opción leerá todos los registros, esta opción pude ser la más tardada debido a eso pero es la más exacta.

ESTIMATE STATISTICS. Como su nombre lo indica esta opción estima las estadísticas, regularmente esta opción es la más utilizada debido a que es acertada(no en todos los casos) y además toma mucho menos tiempo que la otra. Se puede especificar SAMPLE nn donde nn puede ser el numero de registros(ROWS) o el porcentaje de la tabla (PERCENT).

Ejemplos,ejemplos y más ejemplos…

Primero vamos a crear una tabla e insertarle datos.

scott@DBA> create table test as 
select * from all_objects;
scott@DBA> 
insert into test select * from all_objects;

48073 rows created.

scott@DBA> insert into test select * from all_objects;

48073 rows created.

scott@DBA> insert into test select * from all_objects;

48073 rows created.

scott@DBA> insert into test select * from all_objects;

48073 rows created.

scott@DBA> commit;

Commit complete.

scott@DBA> select count(*) from test;

COUNT(*)

----------

240365

Después revisamos si la tabla tiene estadísticas.

scott@DBA> select table_name,
num_rows,
blocks,
avg_space,
chain_cnt,
avg_row_len,
sample_Size 
from user_tables 
where table_name ='TEST';
TABLE_NAME                       NUM_ROWS     BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE
------------------------------ ---------- ---------- ---------- ---------- ----------- -----------

TEST

Aqui podemos confirmar que la tabla no tiene información estadística. Ahora vamos a calcular estadísticas y luego verificamos que es lo que sucede.

scott@DBA> analyze table test compute statistics;
Table analyzed.Elapsed: 00:00:08.34

Aqui podemos ver que tarda 8 segundos y medio en terminar de calcular todas las estadísticas, ahora vamos a revisar que información guardo.

scott@DBA> select table_name,
num_rows,
blocks,
avg_space,
chain_cnt,
avg_row_len,
sample_Size 
from user_tables 
where table_name ='TEST';TABLE_NAME                       
NUM_ROWS     BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE
------------------------------ ---------- ---------- ---------- ---------- ----------- -----------
TEST                               240365       3350        904          0          97      240365

Elapsed: 00:00:00.01

Aquí se muestra que ahora Oracle tiene información respecto a la tabla.
Ahora vamos a calcular las estadísticas con sample.

Primero hay que borrar las estadísticas de la tabla, esto se realiza con la opción DELETE STATISTICS.

scott@DBA> analyze table test delete statistics;
Table analyzed.Elapsed: 00:00:00.18

scott@DBA> select table_name,num_rows,blocks,avg_space,chain_cnt,avg_row_len,sample_Size from user_tables where table_name ='TEST';

TABLE_NAME                       NUM_ROWS     BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE
------------------------------ ---------- ---------- ---------- ---------- ----------- -----------
TEST

Elapsed: 00:00:00.01

scott@DBA> analyze table test estimate statistics;

Table analyzed.

Elapsed: 00:00:00.06

scott@DBA> select table_name,num_rows,blocks,avg_space,chain_cnt,avg_row_len,sample_Size from user_tables where table_name ='TEST';

TABLE_NAME                       NUM_ROWS     BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE
------------------------------ ---------- ---------- ---------- ---------- ----------- -----------
TEST                               242821       3350        873          0          97         993

Elapsed: 00:00:00.01

Como podemos observar el calculo tardo muchísimo menos tiempo que el calculo completo (6 centésimas de seg VS 8.5 segs), ademas que la información estimada es diferente al calculo completo.

Vamos a probar con 10%, 20% y 80% de SAMPLE.

scott@DBA> analyze table test delete statistics;
Table analyzed.Elapsed: 00:00:00.20

scott@DBA> analyze table test estimate statistics sample 10 percent;

Table analyzed.

Elapsed: 00:00:00.71

scott@DBA> select table_name,num_rows,blocks,avg_space,chain_cnt,avg_row_len,sample_Size from user_tables where table_name ='TEST';

TABLE_NAME                       NUM_ROWS     BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE
------------------------------ ---------- ---------- ---------- ---------- ----------- -----------
TEST                               241809       3350        904          0          97       22770

Elapsed: 00:00:00.06

scott@DBA> analyze table test estimate statistics sample 20 percent;

Table analyzed.

Elapsed: 00:00:01.04

scott@DBA> select table_name,num_rows,blocks,avg_space,chain_cnt,avg_row_len,sample_Size from user_tables where table_name ='TEST';

TABLE_NAME                       NUM_ROWS     BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE
------------------------------ ---------- ---------- ---------- ---------- ----------- -----------
TEST                               241809       3350        904          0          97       43310

Elapsed: 00:00:00.03

scott@DBA.REGRESS.RDBMS.DEV.US.ORACLE.COM> analyze table test estimate statistics sample 80 percent;

Table analyzed.

Elapsed: 00:00:04.34

scott@DBA> select table_name,num_rows,blocks,avg_space,chain_cnt,avg_row_len,sample_Size from user_tables where table_name ='TEST';

TABLE_NAME                       NUM_ROWS     BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE
------------------------------ ---------- ---------- ---------- ---------- ----------- -----------
TEST                               240365       3350        904          0          97      240365

Elapsed: 00:00:00.01

scott@DBA.REGRESS.RDBMS.DEV.US.ORACLE.COM> analyze table test compute statistics;

Table analyzed.

Elapsed: 00:00:04.36

scott@DBA.REGRESS.RDBMS.DEV.US.ORACLE.COM> select table_name,num_rows,blocks,avg_space,chain_cnt,avg_row_len,sample_Size 
from user_tables where table_name ='TEST';

TABLE_NAME                       NUM_ROWS     BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE
------------------------------ ---------- ---------- ---------- ---------- ----------- -----------
TEST                               240365       3350        904          0          97      240365

Elapsed: 00:00:00.01

Como podemos observar entre más elevado es el porcentaje más cercanas son las estadísticas de la tabla a la opción COMPUTE y obviamente tarda mucho más tiempo en calcular las estadísticas.

  • Y los indices???

Bueno los indices son analizados al mismo tiempo que se analiza la tabla, veamos un ejemplo.
Primero creamos el indice en la tabla, verificamos que no hay estadísticas creadas para el indice y comenzamos a calcular para distintos porcentajes.

scott@DBA> create index testidx on test(object_name);
Index created.
scott@DBA> SELECT num_rows, sample_size, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key
2    FROM user_indexes
3   WHERE index_name = 'TESTIDX';

NUM_ROWS SAMPLE_SIZE LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
---------- ----------- ----------- ------------- ----------------------- -----------------------

Elapsed: 00:00:00.04

scott@DBA> analyze table test estimate statistics sample 10 percent;

Table analyzed.

Elapsed: 00:00:00.48

scott@DBA> SELECT num_rows, sample_size, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key
2    FROM user_indexes
3   WHERE index_name = 'TESTIDX';

NUM_ROWS SAMPLE_SIZE LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
---------- ----------- ----------- ------------- ----------------------- -----------------------
243057       25522        1219         29417                       1                       5

Elapsed: 00:00:00.01

scott@DBA> analyze table test estimate statistics sample 20 percent;

Table analyzed.

Elapsed: 00:00:00.87

scott@DBA> SELECT num_rows, sample_size, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key
2    FROM user_indexes
3   WHERE index_name = 'TESTIDX';

NUM_ROWS SAMPLE_SIZE LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
---------- ----------- ----------- ------------- ----------------------- -----------------------
242871       49411        1219         29310                       1                       5

Elapsed: 00:00:00.00

scott@DBA> analyze table test estimate statistics sample 80 percent;

Table analyzed.

Elapsed: 00:00:04.40

scott@DBA> SELECT num_rows, sample_size, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key
2    FROM user_indexes
3   WHERE index_name = 'TESTIDX';

NUM_ROWS SAMPLE_SIZE LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
---------- ----------- ----------- ------------- ----------------------- -----------------------
240365      240365        1189         28595                       1                       5

Elapsed: 00:00:00.01

scott@DBA> analyze table test compute statistics;

Table analyzed.

Elapsed: 00:00:04.39

Como podemos observar en el caso de los indices sucede lo mismo que con las tablas, entre el porcentaje es menor también lo es la exactitud de las estadísticas.

  • Desempeño(Performance)

Bueno y como afectan las estadísticas al performance. Vamos viendo un ejemplo donde veamos el explain plan sin estadísticas y con estadísticas.

scott@DBA> analyze table test delete statistics;Table analyzed.scott@DBA> select * from test where object_name='TEST';

Execution Plan
----------------------------------------------------------

Plan hash value: 464205115
---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     5 |   640 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST    |     5 |   640 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TESTIDX |     5 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"='TEST')

Note
----

- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
1743  recursive calls
0  db block gets
420  consistent gets
0  physical reads
0  redo size
1564  bytes sent via SQL*Net to client
381  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
50  sorts (memory)
0  sorts (disk)
5  rows processed

scott@DBA> analyze table test compute statistics;

Table analyzed.

scott@DBA> select * from test where object_name='TEST';

Execution Plan
----------------------------------------------------------
Plan hash value: 464205115
---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     8 |   696 |     9   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST    |     8 |   696 |     9   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TESTIDX |     8 |       |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"='TEST')
Statistics
----------------------------------------------------------
1685  recursive calls
0  db block gets
352  consistent gets
0  physical reads
0  redo size
1564  bytes sent via SQL*Net to client
381  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
50  sorts (memory)
0  sorts (disk)
5  rows processed

Como se puede observar en la sección de statistics vemos que los recursive calls y consisten gets son menores cuando se calculan las estadísticas.

  • Cada cuando se deben calcular estadísticas

Esta es una pregunta difícil de contestar ya que esto depende de la actividad(delete,insert,update) que tiene la base de datos en un determinado tiempo, por ejemplo si se borran/actualizan/insertan muchos datos probablemente es bueno calcular las estadísticas cada semana, de nuevo esto depende de la carga que se tenga en un tiempo determinado y la mejora manera es hacer pruebas y verificar los resultados.

  • Conclusiones

Esta fue una “embarrada rapida” sobre lo que son las estadísticas y como se calculan con analyze table. Como se pudo observar en los ejemplos si se calculan estadísticas ayuda al desempeño de los queries. En mi experiencia he visto que el no calcular estadísticas puede causar grandes problemas en el desempeño de la base de datos así que como regla general se deben de calcular. A partir de la versión 10g de Oracle se incluye un JOB que calcula las estadísticas de manera automática y a partir de la versión 9i se recomienda utilizar el paquete DBMS_STATS en lugar de ANALYZE TABLE (aunque aquí lo vimos como ejemplo ilustrativo).

En el siguiente capitulo voy a ver como se calculan con el paquete DBMS_STATS.

  1. #1 by Manuel Quintanilla on 27 September, 2007 - 16:06

    Cuando vas a escribir acerca de DBMS_STATS,

  2. #2 by Delfino Nunez on 27 September, 2007 - 18:07

    Hola Manuel,
    Pues la verdad no habia pensado escribir debido a que no recibia comentarios y pensaba que nadie leia mi blog pues deje de tomarle importancia; pero ahora que tengo un lector voy a retomarlo asi que en los proximos dias voy a escribir sobre como funciona el DBMS_STATS.

    Saludos y gracias por leer.

  3. #3 by luis on 29 October, 2007 - 15:07

    me sirvio, y de mucho lo que hablas de estadisticasa
    te aliento para que sigas escribiendo mas articulos relacionado a BD oracle
    bye

  4. #4 by Delfino Nunez on 29 October, 2007 - 20:25

    Hola Luis,
    Que bueno que te sirvió, trato de no decir mucha teoría para que no se haga tedioso y mejor mostrar ejemplos como el Tom Kyte.

    Ya escribí sobre DBMS_STATS un poco, así que puedes darle una revisada a ver si es útil para ti.

  5. #5 by Esmeralda on 10 November, 2007 - 00:42

    Hola Delfino!

    Sería excelente que continuaras escribiendo estos articulos, los haces facilmente digeribles. Gracias por la información me fue bastante útil.

    Estaré al pendiente de la siguiente nota

    Un saludo

    Esmeralda

  6. #6 by Ricardo on 9 October, 2008 - 19:56

    Buenisimas tus notas, segui escribiendo que son de gran utilidad

    Gracias, por ser como sos idolo

  7. #7 by Delfino N. on 9 October, 2008 - 23:19

    Gracias Ricardo por lo de idolo, ya me hiciste sentir importante. :)

    No he tenido tiempo de seguir escribiendo pero voy a buscar un espacio para publicar otra cosa mas.

  8. #8 by Mario on 28 July, 2009 - 23:25

    este es un procedimiento para ejecutar estadisticas en producción, solo se pone el nombre del owner y este aplica a todas las tablas.

    BEGIN
    SYS.DBMS_STATS.GATHER_SCHEMA_STATS (
    OwnName => ‘owner_name’
    ,Granularity => ‘ALL’
    ,Options => ‘GATHER AUTO’
    ,Gather_Temp => FALSE
    ,Method_Opt => ‘FOR ALL COLUMNS SIZE SKEWONLY ‘
    ,Cascade => TRUE
    ,No_Invalidate => FALSE);
    END;
    /

  9. #9 by delfinonunez on 28 July, 2009 - 23:42

    Es una pregunta o afirmacion?

    Solo debes cambiar owner_name por el nombre de tu usuario/schema de la aplicacion. Por defecto va a calcular todas las tablas e indices del usuario/schema con los parametros automaticos.

    Verifica que los parametros sean correctos para tu aplicacion, generalmente esas opciones estan bien. Si es la primera vez que lo vas a ejecutar te recomiendo hacerlo en el ambiente de test antes de produccion, ya que como he mencionado si se cambian las estadisticas es muy probable que se afecte el rendimiento de la base de datos/aplicacion para bien y para mal.

    Tambien podrias guardar tus estadisticas anteriores (hacer un backup) y calcularlas en produccion y si no funciona entonces regresar las anteriores.

    Hay que probar antes de aplicarlo en produccion…

  10. #10 by Volomario on 29 April, 2010 - 15:19

    Luis esta muy interesante tu articulo. Normalmente siempre calculo las estadisticas con un 10% (la verdad siempre lo hacia sin saber porque tenia que hacerse), pero siempre lo hago directamente en producción, la verdad nunca he tenido problemas, pero me llamo la atención tu comentario de que puedo respaldar las estadisticas y que problemas puede traerme una mala estadistica. Por otro lado tengo problemas de sesiones pero ese, es otro tema. Saludos

  11. #11 by Carlos on 19 June, 2011 - 21:03

    GRANDIOSO!! tu explicacion me valio oro. Me estoy iniciando en una empresa en treas de optimizacion y performance de un database con record mostruosos y tu articulo me es de gran ayuda. Saludos desde Milan – Italia.

  12. #12 by delfinonunez on 21 June, 2011 - 13:27

    Que bueno que estos post todavia sirven…

    Saludos Carlos.

  13. #13 by AlumnoABD on 1 August, 2011 - 09:09

    Tu post acaba de ser usado por todo un curso de ABD para la realizacion de un proyecto asi que (Y)

  14. #14 by Administrador on 21 September, 2011 - 08:49

    Gràcias por tu generosidad! Gran explicación con sencillez sublime!

    Un Saludo

  15. #15 by jesus on 20 September, 2012 - 16:46

    gracias me ayudo muxooo

  16. #16 by Alejandro on 2 January, 2013 - 12:44

    gracias delfino y sigue escribiendo por favor

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: