Siempre he querido usar Linux pero por alguna razon no he podido, esta es una buena opcion para hacerlo. Yo creo que lo voy a intentar despues.
powered by performancing firefox
sys@DBA> select rownum from dual connect by rownum <=10; ROWNUM ---------- 1 2 3 4 5 6 7 8 9 10 10 rows selected. sys@DBA> select dbms_random.value from dual connect by rownum <=10; VALUE ---------- .876879822 .04653477 .592060427 .273758898 .488285804 .523971849 .717315039 .092101034 .598717486 .99935022 10 rows selected. 10 rows selected.
Ú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.
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:
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.
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.
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.
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.
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.
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.
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.
