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

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…

DBMS_STATS. Parte 1

DBMS_STATS

Otra manera de calcular estadisticas además del comando ANALYZE es el paquete DBMS_STATS. Este paquete se utiliza para modificar, ver, exportar, importar y borrar estadisticas de la base de datos.

Como ya habia mencionado Oracle recomienda la utilización de este paquete a partir de la versión 9i para el calculo de estadisticas en lugar de usar ANALYZE debido a que es mas exacto y mas eficiente. Aunque ANALYZE ya no sea la opción adecuada para calcular estadisticas eso no quiere decir que ya no sirva para nada, todavia se puede utilizar para validar la estructura de una tabla o buscar por CHAINED ROWS y algunas otras cosas.

Cuando se generan nuevas estadisticas para una tabla, columna o indice las estadisticas existentes son actualizadas por Oracle, cuando se actualizan las estadisticas Oracle invalida cualquier SQL que se encuentra en memoria (parsed) que accesa el objeto al cual se les estan calculando estadisticas. Esto quiere decir que si alguien ejecuto un query sobre la tabla empleados y se empiezan a calcular estadisticas sobre esa table Oracle invalida el query SQL que esta compartido en la memoria en lugar de re-utilizarlo (espero no haber confundido aqui esto tiene que ver con la manera en que Oracle reutiliza SQL previamente ejecutados, cualquier duda pregunten). Oracle utiliza las nuevas estadisticas cuando el query SQL es ejecutado de nuevo y por lo cual puede utilizar un plan de ejecucion diferente.

Procedimientos para el calculo de estadisticas dentro del paquete DBMS_STATS

Procedimiento	Que calcula?
GATHER_INDEX_STATS	Indices
GATHER_TABLE_STATS	Tablas, columnas e indices
GATHER_SCHEMA_STATS	Para todos los objetos del schema
GATHER_DICTIONARY_STATS	Para todos los objetos del diccionario de datos
GATHER_DATABASE_STATS	Para todos los objetos en la base de datos

Bueno vamos a empezar con el primero antes de que se haga mas aburrido y luego vamos explicando el por que de algunas cosas.

GATHER_INDEX_STATS

Bueno ya habiamos mencionado que este procedimiento calcula estadisticas a los indices.

Sintaxis

DBMS_STATS.GATHER_INDEX_STATS (
ownname          VARCHAR2,
indname          VARCHAR2,
partname         VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER   DEFAULT to_estimate_percent_type
(GET_PARAM('ESTIMATE_PERCENT')),
stattab          VARCHAR2 DEFAULT NULL,
statid           VARCHAR2 DEFAULT NULL,
statown          VARCHAR2 DEFAULT NULL,
degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type
(GET_PARAM('NO_INVALIDATE')),
force            BOOLEAN DEFAULT FALSE);

Parametro Descripcion

ownname Schema/usuario/dueño del indice al cual se le van a calcular

indname Nombre del indice

partname Nombre de la particion

estimate_percent Porcentaje de registros a estimar (NULL significa compute[todos]). Rango valido [0.000001,100]. Se recomienda usar la constante DBMS_STATS.AUTO_SAMPLE_SIZE para dejar que Oracle obtenga el valor adecuado para calcular las estadisticas.

stattab Tabla de estadisticas donde se guardan las actuales (mas adelante mencionamos eso)

statid Identificador para asociar las estadisticas actuales

statown Schema que contiene la tabla de estadisticas (si es diferente al usuario)

degree Grado de paralelismo, el default es NULL, lo cual significa que usa el que tenga la tabla cuando se creo.

granularity Detalle del calculo, solo se utiliza cuando la tabla esta particionada.

ALL,AUTO,DEFAULT,GLOBAL,GLOBAL AND PARTITION, PARTITION, SUBPARTITION.

no_invalidate No invalida los cursores (SQL query) que dependen del objeto si se pone TRUE. Por default los invalida inmediatamente.

force Calcula estadisticas inclusive si el objeto esta bloqueado.

Verficar el manual para obtener mas detalles de los parametros.(http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#sthref8108)\

Bueno, primero vamos a usar un procedimiento que ayuda a borrar las estadisticas actuales.

SQL> EXEC DBMS_STATS.DELETE_INDEX_STATS('SCOTT','PK_EMP');

PL/SQL procedure successfully completed.

Ahora vamos a ver que es lo que esta almacenado como estadisticas:

SQL> select OWNER,INDEX_NAME,NUM_ROWS,
2         SAMPLE_SIZE,LAST_ANALYZED,
3         BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS
4  from dba_indexes
5  where owner = 'SCOTT'
6  and index_name ='PK_EMP';
OWNER      INDEX_NAME   NUM_ROWS SAMPLE_SIZE LAST_ANAL     BLEVEL LEAF_BLOCKS DISTINCT_KEYS
---------- ---------- ---------- ----------- --------- ---------- ----------- -------------
SCOTT      PK_EMP

Aqui podemos ver que el indice no ha sido analizado y por lo tanto no tiene estadisticas aunque la tabla puede tenerlas como se muestra en el siguiente ejemplo:

SQL> select OWNER,table_NAME,LAST_ANALYZED
2  from dba_tables
3  where owner = 'SCOTT'
4  and table_name = 'EMP';
OWNER      TABLE_NAME                     LAST_ANAL
---------- ------------------------------ ---------
SCOTT      EMP                            22-AUG-07

Asi que podemos tener tablas con estadisticas e indices sin estadisticas y viceversa. Aunque no es remendable tener este tipo de estadisticas es posible.

Ahora vamos a calcular las estadisticas del indice:

SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS('SCOTT','PK_EMP');
PL/SQL procedure successfully completed.
SQL> select OWNER,INDEX_NAME,NUM_ROWS,
2         SAMPLE_SIZE,LAST_ANALYZED,
3         BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS
4  from dba_indexes
5  where owner = 'SCOTT'
6  and index_name ='PK_EMP';
OWNER      INDEX_NAME   NUM_ROWS SAMPLE_SIZE LAST_ANAL     BLEVEL LEAF_BLOCKS DISTINCT_KEYS
---------- ---------- ---------- ----------- --------- ---------- ----------- -------------
SCOTT      PK_EMP             14          14 02-OCT-07          0           1            14

Y asi de sencillo se pueden calcular estadisticas para un indice. En este caso utilizamos unicamente las opciones por default.

Vamos a analizar que se hizo, le dijimos que indice queremos calcular, pero podemos ver que el sample_size utilizado fue el mismo numero de registros de la tabla esto quiere decir que Oracle uso NULL que significa COMPUTE que significa 100% o todos los registros. Para este caso en que la tabla es muy pequeña es muy rapido pero hay casos donde las tablas son de millones de registros estoy puede tardar varios minutos asi que se recomienda dejar que oracle lo determine utilizando DBMS_STATS.AUTO_SAMPLE_SIZE.

Vamos a cambiar este parametro para compara resultados con una tabla con algunos registros demas:

SQL> INSERT INTO SCOTT.EMP2(EMPNO,ENAME)
2  SELECT LEVEL,'TEST'
3  FROM DUAL
4  CONNECT BY LEVEL <=1000000;
1000000 rows created.
SQL> EXEC DBMS_STATS.DELETE_INDEX_STATS('SCOTT','PK_EMP2');
PL/SQL procedure successfully completed.
SQL> select OWNER,INDEX_NAME,NUM_ROWS,
2         SAMPLE_SIZE,LAST_ANALYZED,
3         BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS
4  from dba_indexes
5  where owner = 'SCOTT'and index_name ='PK_EMP2';
OWNER      INDEX_NAME   NUM_ROWS SAMPLE_SIZE LAST_ANAL     BLEVEL LEAF_BLOCKS DISTINCT_KEYS
---------- ---------- ---------- ----------- --------- ---------- ----------- -------------
SCOTT      PK_EMP2
SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS('SCOTT','PK_EMP2');
PL/SQL procedure successfully completed.
SQL> select OWNER,INDEX_NAME,NUM_ROWS,
2         SAMPLE_SIZE,LAST_ANALYZED,
3         BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS
4  from dba_indexes
5  where owner = 'SCOTT'and index_name ='PK_EMP2';
OWNER      INDEX_NAME   NUM_ROWS SAMPLE_SIZE LAST_ANAL     BLEVEL LEAF_BLOCKS DISTINCT_KEYS
---------- ---------- ---------- ----------- --------- ---------- ----------- -------------
SCOTT      PK_EMP2       1000000     1000000 02-OCT-07          2        1875       1000000
SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS('SCOTT','PK_EMP2',null,10);
PL/SQL procedure successfully completed.
SQL> select OWNER,INDEX_NAME,NUM_ROWS,
2         SAMPLE_SIZE,LAST_ANALYZED,
3         BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS
4  from dba_indexes
5  where owner = 'SCOTT'and index_name ='PK_EMP2';
OWNER      INDEX_NAME   NUM_ROWS SAMPLE_SIZE LAST_ANAL     BLEVEL LEAF_BLOCKS DISTINCT_KEYS
---------- ---------- ---------- ----------- --------- ---------- ----------- -------------
SCOTT      PK_EMP2        971743      593140 02-OCT-07          2        1822        971743
SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS('SCOTT','PK_EMP2',null,50);
PL/SQL procedure successfully completed.
SQL> select OWNER,INDEX_NAME,NUM_ROWS,
2         SAMPLE_SIZE,LAST_ANALYZED,
3         BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS
4  from dba_indexes
5  where owner = 'SCOTT'and index_name ='PK_EMP2';
OWNER      INDEX_NAME   NUM_ROWS SAMPLE_SIZE LAST_ANALYZED            BLEVEL LEAF_BLOCKS DISTINCT_KEYS
---------- ---------- ---------- ----------- -------------------- ---------- ----------- -----------
SCOTT      PK_EMP2       1008372      615498 02/OCT/2007 11:53:18          2        1891       1008372
SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS('SCOTT','PK_EMP2',null,DBMS_STATS.AUTO_SAMPLE_SIZE);
PL/SQL procedure successfully completed.
SQL> select OWNER,INDEX_NAME,NUM_ROWS,
2         SAMPLE_SIZE,LAST_ANALYZED,
3         BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS
4  from dba_indexes
5  where owner = 'SCOTT'and index_name ='PK_EMP2';
OWNER      INDEX_NAME   NUM_ROWS SAMPLE_SIZE LAST_ANALYZED            BLEVEL LEAF_BLOCKS DISTINCT_KEYS
---------- ---------- ---------- ----------- -------------------- ---------- ----------- -----------
SCOTT      PK_EMP2       1000000     1000000 02/OCT/2007 11:55:34          2        1875       1000000

Aqui podemos ver que al igual que el comando ANALYZE si le damos un valor mas alto a estimate_percent entones obtendremos estadisticas mas exactas, pero de igual manera va a tardar mas tiempo en calcularlas. Puede observarse que en el ultimo ejemplo se calcularon usando AUTO_SAMPLE_SIZE y Oracle decidio que lo ideal era calcular para todos los registros.

Y asi se calculan estadisticas para indices, en lo personal yo casi nunca he utilizado este metodo debido a que siempre las calculo automaticamente con el procedimiento GATHER_TABLES_STATS o GATHER_SCHEMA_STATS, esto lo voy a mostrar en los proximos dias.

Los procedimientos tienen mas parametros sobre los cuales no mostre como funcionaban, la verdad no lo hice por que los parametros explican claramente que hacen cada uno y debido a que se utilizan para casos mas “avanzados” como particiones, paralelismo, etc. Y la verdad me dio flojera hacer una caso de prueba para ese tipo, creo que los mas importantes y que se aplican mas seguido son los mostrados, a menos que yo personalmente considere mencionar otro lo voy a hacer y si alguien desea saber como funciona entonces haganmelo saber.

Cuando calcular estadisticas?

De nuevo, no existe una regla sobre cada cuando se deben de calcular; pero algunos consejos son por ejemplo si se insertan/borran/actualizan un gran numero de registros a una tabla, tal vez millones, entonces inmediatamente despues hay que calcular debido a que si puede afectar los planes de ejecucion ya que se hizo un gran cambio. Si se desea hacer de manera automatica entonces depende de la carga de la base de datos, de la aplicacion, puede haber casos que semanalmente esta bien o hay veces que 1 vez al mes.

Bueno hasta la siguiente…con GATHER_TABLE_STATS.