DBMS_STATS Parte 2

GATHER_TABLE_STATS.

Bueno continuamos con la onda de DBMS_STATS, ahora con el procedimiento GATHER_TABLE_STATS. Este procedimiento permite calcular las estadísticas de una tabla, columnas e índices (como ya lo había mencionado anteriormente).

Sintaxis

DBMS_STATS.GATHER_TABLE_STATS (
   ownname          VARCHAR2,
   tabname          VARCHAR2,
   partname         VARCHAR2 DEFAULT NULL,
   estimate_percent NUMBER   DEFAULT to_estimate_percent_type
                                    (get_param('ESTIMATE_PERCENT')),
   block_sample     BOOLEAN  DEFAULT FALSE,
   method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),
   degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
   granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
   cascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')),
   stattab          VARCHAR2 DEFAULT NULL,
   statid           VARCHAR2 DEFAULT NULL,
   statown          VARCHAR2 DEFAULT NULL,
   no_invalidate    BOOLEAN  DEFAULT  to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
   force            BOOLEAN DEFAULT FALSE);
Parámetro Descripción
Owname Usuario/schema/dueño de la tabla(s).
Tabname Nombre de la tabla.
Partname Nombre de la partición.
Estímate_percent Porcentaje de registros para calcular las estadísticas. NULL significa compute y el rango puede ir de [0.000001,100], igual que en los índices, también se puede utilizar DBMS_STATS.AUTO_SAMPLE_SIZE.
Block_sample Si queremos utilizar el cálculo basado en bloques de datos (mínima estructura de almacenamiento de Oracle) en lugar de utilizar registros (rows). Lo hace de manera aleatoria (random).
Method_opt Si deseamos calcular histogramas.

Los valores pueden ser:

  • FOR ALL [INDEXED | HIDDEN] COLUMNS [size clause]
  • FOR COLUMNS [size clause] column|attribute [size_clause]..]

size_clause es definida como size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}

  • Integer: Numero de histogramas (buckets), el valor es de 1 a 254.
  • REPEAT: calcula histogramas solo en las columnas que ya tienen.
  • AUTO: Oracle determina a que columnas les va a calcular histograma basado en la carga y distribución de los datos de la columna (s).
  • SKEWONLY: Oracle determina a cuales columnas les va a calcular histograma basado en la distribución de los datos.

El default es FOR ALL COLUMNS SIZE AUTO.

Este esta interesante, a ver si puedo hacer un caso de prueba.

Degree Grado de paralelismo para calcular las estadísticas. Es muy similar al del índice así que si desean mas detalle por favor vean el manual en línea.
Granularity Nivel de detalle a calcular estadísticas, solo se utiliza cuando la tabla esta particionada.

Opciones:

ALL- Todas las particiones, subparticiones.

AUTO- Oracle determina a cuales. Es el default.

GLOBAL- Calcula estadísticas globales.

GLOBAL AND PARTITION- Calcula igual que global y a nivel partición.

PARTITION- A nivel partición.

SUBPARTITION- A nivel subparticion.

Cascade Calcula estadísticas en todos los índices de la tabla. Si se utiliza DBMS_STATS.AUTO_CASCADE Oracle determina a que índices calcular y a cuales no.

Esta opción es lo mismo que gather_index_stats a cada uno de los índices de manera manual.

Stattab Donde se van a guardar las estadísticas actuales (o viejitas).
Statid Como se van a identificar las estadísticas actuales cuando se guarden.
Statown Dueño de la tabla donde se guardan las estadísticas.
No_invalidate TRUE/FALSE si se desea invalidar los cursores (queries “parseados”). Por default Oracle determina si lo hace o no.
Force Calcular estadísticas sin importar si la tabla esta bloqueada.

A la practica…

Vamos a crear una tabla con la cual podamos jugar y ver si nos da los resultados que deseamos.

SQL> set timing on;
SQL> create table emps as
  2  select level empid,
  3         sysdate - (((18 * 365)/level) + dbms_random.value()*(47*365)) hired,
  4         trunc((50000 + dbms_random.value()*90000)) salary,
  5         dbms_random.string('A',10) name
  6  from dual
  7  connect by level <=1000000
  8  ;
Table created.
Elapsed: 00:00:53.67

Hay que crear el PK y un índice para la fecha de contratación basado en el año solamente.

SQL> alter table emps add constraint pk_emps primary key (empid);
Table altered.
SQL> create index emps_hired on emps (extract(year from hired));
Index created.
Elapsed: 00:00:02.86
SQL> set lines 130
SQL> alter session set nls_date_format='dd.mon.yyyy hh24:mi:ss';
Session altered.
Elapsed: 00:00:00.00
SQL> set null {null}

Ahora vamos a revisar que la tabla no tiene estadísticas y el índice si.

SQL> select table_name,
  2      num_rows,
  3      blocks,
  4      empty_blocks,
  5      avg_space,
  6      chain_cnt,
  7      avg_row_len,
  8      sample_size,
  9      last_analyzed
 10    from dba_tables
 11   where owner = 'SCOTT' and table_name = 'EMPS';
TABLE_NAME        NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE LAST_ANA
--------------- ---------- ---------- ------------ ---------- ---------- ----------- ----------- ---
EMPS            {null}     {null}     {null}       {null}     {null}     {null}      {null}      {null}
Elapsed: 00:00:00.00
SQL> select index_name, last_analyzed, num_rows, distinct_keys, leaf_blocks
  2    from dba_indexes
  3   where owner = 'SCOTT' and index_name = 'PK_EMPS';
INDEX_NAME                     LAST_ANALYZED        NUM_ROWS DISTINCT_KEYS LEAF_BLOCKS
------------------------------ ------------------ ---------- ------------- -----------
PK_EMPS                        05.oct.07 10:13:11    1000000       1000000        2087
Elapsed: 00:00:00.03

Borramos las estadísticas del índice para ver como se comporta.

SQL> exec dbms_stats.delete_INDEX_sTATS('SCOTT','PK_EMPS');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.18
SQL> exec dbms_stats.delete_index_stats('SCOTT','EMPS_HIRED');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL> select index_name, last_analyzed, num_rows, distinct_keys, leaf_blocks
  2    from dba_indexes
  3   where owner = 'SCOTT' and index_name like '%EMPS%';
INDEX_NAME                     LAST_ANALYZED        NUM_ROWS DISTINCT_KEYS LEAF_BLOCKS
------------------------------ ------------------ ---------- ------------- -----------
PK_EMPS                        {null}             {null}     {null}        {null}
EMPS_HIRED                     {null}             {null}     {null}        {null}
Elapsed: 00:00:00.01

Vamos a ver como se comporta Oracle con índices y tablas sin estadísticas.

SQL> select *
  2  from emps
  3  where extract(year from hired) = '1959';
Elapsed: 00:00:01.11
Execution Plan
----------------------------------------------------------
Plan hash value: 2150600608
------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |    53 |   105K|     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPS       |    53 |   105K|     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMPS_HIRED |  4524 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(EXTRACT(YEAR FROM INTERNAL_FUNCTION("HIRED"))=1959)
Note
-----
   - dynamic sampling used for this statement
Statistics
----------------------------------------------------------
        738  recursive calls
          0  db block gets
        197  consistent gets
        583  physical reads
          0  redo size
        597  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          9  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> select *
  2  from emps
  3  where extract(year from hired) = '2000';
21438 rows selected.
Elapsed: 00:00:02.37
Execution Plan
----------------------------------------------------------
Plan hash value: 2150600608
------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            | 23305 |    45M|     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPS       | 23305 |    45M|     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMPS_HIRED |  4524 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(EXTRACT(YEAR FROM INTERNAL_FUNCTION("HIRED"))=2000)
Note
-----
   - dynamic sampling used for this statement
Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
       7324  consistent gets
       4171  physical reads
          0  redo size
     828924  bytes sent via SQL*Net to client
      16100  bytes received via SQL*Net from client
       1431  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      21438  rows processed
SQL> select *
  2  from emps
  3  where to_char(hired,'YYYY') = '2000';
21438 rows selected.
Elapsed: 00:00:01.45
Execution Plan
----------------------------------------------------------
Plan hash value: 2715242515
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 23305 |    45M|  1197  (13)| 00:00:15 |
|*  1 |  TABLE ACCESS FULL| EMPS | 23305 |    45M|  1197  (13)| 00:00:15 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_CHAR(INTERNAL_FUNCTION("HIRED"),'YYYY')='2000')
Note
-----
   - dynamic sampling used for this statement
Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
       6224  consistent gets
         46  physical reads
          0  redo size
     828924  bytes sent via SQL*Net to client
      16100  bytes received via SQL*Net from client
       1431  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      21438  rows processed

Podemos ver que no esta mal, utiliza el índice que creamos y agregue un full scan para ver el comportamiento.

Ahora vamos a calcular las estadísticas con gather_table_stats para ver que es lo que cambia.

SQL> select table_name,
  2         num_rows,
  3      blocks,
  4      empty_blocks,
  5      avg_space,
  6      chain_cnt,
  7      avg_row_len,
  8      sample_size,
  9      last_analyzed
 10    from dba_tables
 11   where owner = 'SCOTT' and table_name = 'EMPS';
TABLE_NAME        NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE LAST_ANA
--------------- ---------- ---------- ------------ ---------- ---------- ----------- ----------- ---
EMPS                995047       4798            0          0          0          32       49610 05.oct.07 10:45:13
Elapsed: 00:00:00.09
SQL> select index_name, last_analyzed, num_rows, distinct_keys, leaf_blocks
  2    from dba_indexes
  3   where owner = 'SCOTT' and index_name like '%EMPS%';
INDEX_NAME                     LAST_ANALYZED        NUM_ROWS DISTINCT_KEYS LEAF_BLOCKS
------------------------------ ------------------ ---------- ------------- -----------
PK_EMPS                        05.oct.07 10:45:22    1000000       1000000        2087
EMPS_HIRED                     05.oct.07 10:45:27    1000000            49        2089
Elapsed: 00:00:00.42

Empezamos viendo el full scan para comparar. Podemos ver que después de calcular estadísticas el COST BASED OPTIMIZER mejoro el plan de ejecución, por ejemplo el número de bytes anterior era de 45M y después es de 281K una gran mejora.

SQL> select *
  2  from emps
  3  where to_char(hired,'YYYY') = '2000';
21438 rows selected.
Elapsed: 00:00:02.06
Execution Plan
----------------------------------------------------------
Plan hash value: 2715242515
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  9950 |   281K|  1180  (11)| 00:00:15 |
|*  1 |  TABLE ACCESS FULL| EMPS |  9950 |   281K|  1180  (11)| 00:00:15 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_CHAR(INTERNAL_FUNCTION("HIRED"),'YYYY')='2000')
Statistics
----------------------------------------------------------
        664  recursive calls
          0  db block gets
       6255  consistent gets
       4731  physical reads
          0  redo size
     828924  bytes sent via SQL*Net to client
      16100  bytes received via SQL*Net from client
       1431  SQL*Net roundtrips to/from client
          9  sorts (memory)
          0  sorts (disk)
      21438  rows processed

Ahhh, ahora podemos ver como después de calcular estadísticas Oracle toma otro camino diferente. Antes de calcular estadísticas Oracle decidió de usar el índice.

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            | 23305 |    45M|     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPS       | 23305 |    45M|     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMPS_HIRED |  4524 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(EXTRACT(YEAR FROM INTERNAL_FUNCTION("HIRED"))=2000)

Pero ahora vemos que Oracle esta decidiendo hacer un full scan…mmmm.

SQL> select *
  2  from emps
  3  where extract(year from hired) = '2000';
21438 rows selected.
Elapsed: 00:00:02.67
Execution Plan
----------------------------------------------------------
Plan hash value: 2715242515
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 24125 |   683K|  1181  (12)| 00:00:15 |
|*  1 |  TABLE ACCESS FULL| EMPS | 24125 |   683K|  1181  (12)| 00:00:15 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(EXTRACT(YEAR FROM INTERNAL_FUNCTION("HIRED"))=2000)
Statistics
----------------------------------------------------------
        689  recursive calls
          0  db block gets
       6265  consistent gets
       4740  physical reads
          0  redo size
     828924  bytes sent via SQL*Net to client
      16100  bytes received via SQL*Net from client
       1431  SQL*Net roundtrips to/from client
         12  sorts (memory)
          0  sorts (disk)
      21438  rows processed

Podemos ver como el calculo de estadísticas afecto el explain plan para un mismo query, básicamente Oracle nos esta diciendo que es mas “barato” hacer un full scan que usar un índice. Vamos a analizar los explain plans; el costo (CPU) es mas alto 5 vs 1181 pero los bytes son mejores en el full scan (683K vs 45M) así que Oracle ha decidido que es mejor hacer un full scan (no quiero entrar en detalles de la razón ya que me llevaría mucho tiempo explicarlo, por el momento vamos a dejarlo así pero existe una razón por que es mas costoso usar el índice vs un full scan EN ESTE CASO).

Lo que quería mostrar es que el plan de ejecución puede (va a cambiar) con el calculo de estadísticas y esto es debido a que Oracle tiene mas información para decidir cual es el “mejor” camino.

Pero podemos ver que para el siguiente caso continua utilizando el índice y de hecho mejora mucho el plan de ejecución.

Antes de calcular estadísticas el plan era el siguiente:

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |    53 |   105K|     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPS       |    53 |   105K|     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMPS_HIRED |  4524 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Después de calcular estadísticas:

SQL> select *
  2  from emps
  3  where extract(year from hired) = '1959';
Elapsed: 00:00:00.26
Execution Plan
----------------------------------------------------------
Plan hash value: 2150600608
------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |    29 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPS       |     1 |    29 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMPS_HIRED |     1 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(EXTRACT(YEAR FROM INTERNAL_FUNCTION("HIRED"))=1959)
Statistics
----------------------------------------------------------
        689  recursive calls
          0  db block gets
        125  consistent gets
         21  physical reads
          0  redo size
        597  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         12  sorts (memory)
          0  sorts (disk)
          1  rows processed

Podemos observar varias cosas aquí, el numero de bytes accesadsos son menos (29 vs 105K) lo mismo para el numero de rows (1 vs 53), inclusive si vemos las estadísticas, el numero de lecturas físicas es menor (21 vs 583) hay grandes diferencias.

Vamos a analizar poquito por que ahora con estadísticas Oracle los planes cambiaron.

SQL> select extract(year from hired), count(*),(count(*)/1000000)*100 PCT
  2  from scott.emps
  3  group by extract(year from hired)
  4  having extract(year from hired) in ('2000','1959');
EXTRACT(YEARFROMHIRED)   COUNT(*)        PCT
---------------------- ---------- ----------
                  2000      21438     2.1438
                  1959          1      .0001
Elapsed: 00:00:01.79

En los ejemplos que estamos utilizando buscamos por dos años,2000 y 1959, podemos ver que del total de millón de registros el año 2000 contiene 21,438(2%) y el año 1959 1(casi nada %), y es por eso que Oracle en el primero considera mejor hacer un full scan ya que va accesar una cantidad considerable de registros y en el otro caso es preferible usar un índice ya que es solo 1 registro el que se va accesar; obviamente Oracle no tenia esa información antes de calcular estadísticas, no sabia cuantos valores diferentes tenia ni que porcentaje del total de la tabla había respecto a un dato.

Conclusión

Podemos ver que al plan de ejecución mejoro muchísimo después de calcular estadísticas y eso que solo utilizamos los valores por default. También observamos que el explain plan va a cambiar una vez que calculemos estadísticas, el cambio puede ser benéfico o puede afectar así que es mejor probar antes de aplicarlo a una base de producción, tal vez en el caso del full scan nosotros deseamos que utilicé el índice, en este caso tendríamos que hacer algunos cambios pero por ahora así lo vamos a dejar para no ahondar en otras cosas y perdernos mas. Otra cosa que se vio fue que al momento de calcular estadísticas a la tabla también se le calcularon estadísticas a los índices de manera automática y realmente no necesitamos calcular individualmente cada índice.

En el próximo capitulo de esta telenovela voy a continuar con GATHER_TABLE_STATS y voy a utilizar otros parámetros para calcular las estadísticas y observar que hay de diferente.

Hasta la próxima…

  1. #1 by Griss on 8 February, 2008 - 17:57

    ¡Muy buena página! ¿ya está la segunda parte? la estaré esperando

  2. #2 by Delfino on 9 February, 2008 - 16:28

    Hola Griss,
    Pues ya esta la parte 3 y hablo de los histogramas.
    Si quieres leerla esta aqui:
    https://delfinonunez.wordpress.com/2007/10/15/dbms_stats-parte-3-continuacion%e2%80%a6/

  3. #3 by Cesar on 28 March, 2008 - 14:52

    Hola, muchas gracias por compartir tus conocimientos. Soy un principiante en Oracle en cuanto a temas de rendimiento y estos articulos la verdad que me han sido de gran ayuda.

    Cesar

  4. #4 by Percy on 29 June, 2008 - 07:11

    excelente el articulo

  5. #5 by Angelfire on 24 March, 2011 - 23:54

    Una pregunta, cuales son los parámetros mínimos que debe recibir DBMS_STATS.GATHER_TABLE_STATS?

    Gracias

  6. #6 by delfinonunez on 28 March, 2011 - 01:42

    Como puedes ver en la sintaxis arriba, los minimos son ownname y tabname son los unicos parametros que no tienen un valor “default”

  7. #7 by Angelfire on 8 April, 2011 - 18:45

    Muchas gracias por la respuesta, ahora tengo otra pregunta más.

    He estado intentando demostrar como las estadísticas ayudan a mejorar el desempeño de mis sentencias SQL.

    He hecho un ejemplo con un Join entre 3 tablas y he visto lo que hace el EXPLAIN PLAN con y sin estadísticas, es exactamente lo mismo, las estadísticas parecen no funcionar. Soy novato en el tema, es posible que esté calculando las estadísticas que no son, lo que he hecho es calcular las GATHER_TABLE_STATS para cada tabla.

    Cualquier respuesta es bienvenida, muchas gracias

  8. #8 by delfinonunez on 10 April, 2011 - 14:01

    Podrias poner en los comentarios el codigo para crear las tablas (no tienen que ser las originales,un ejemplo solamente), cuanta informacion estas cargando en ellas? si solo pones algunos cientos de registros tal vez siempre va a ser el mismo “execution plan”. Un ejemplo de como lo haces podria ayudar para no empezar de cero.

  9. #9 by Angelfire on 10 April, 2011 - 14:42

    Muchas gracias, creo que era eso, decidí correr las tablas con 2millones de datos aleatorios y variando un poco las consultas y los cambios fueron bastante satisfactorios con las estadísticas :D

  10. #10 by delfinonunez on 10 April, 2011 - 14:52

    cool!

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: