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:
size_clause es definida como size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}
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…
¡Muy buena página! ¿ya está la segunda parte? la estaré esperando
Hola Griss,
Pues ya esta la parte 3 y hablo de los histogramas.
Si quieres leerla esta aqui:
http://delfinonunez.wordpress.com/2007/10/15/dbms_stats-parte-3-continuacion%e2%80%a6/
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
excelente el articulo