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:
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…
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.
Linux en windows
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
Generador de numeros
Esta opción de generar numeros esta interesante.
http://oracledba.co.uk/
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.
Estadísticas, estadísticas y más estadísticas Parte 1.
Últimamente hemos tenido algunos problemas de performance algunos de ellos han estado relacionados con la generación de estadísticas en las tablas, así que decidí escribir algo para recordarme que son, para que sirven y como funcionan.
- Que son y para que sirven?
Primero lo primero, que son las famosas estadísticas. Bueno básicamente datos/información que describe en detalle los objetos dentro de la base de datos, regularmente las tablas y los indices. Estas estadísticas son utilizadas por el query optimizer que se encarga de seleccionar el plan de ejecución más optimo para un query.
Ejemplos de estadísticas son:
- Tablas (numero de registros, numero de bloques de datos, tamaño promedio del registro).
- Columnas (Numero de valores distintos, numero de valores NULOS, como esta distribuida la información en las columnas.
- Indices (Numero de hojas, niveles, clustering factor.
- Sistema (Utilización y desempeño de I/O y del CPU )
Toda esta información se guarda dentro del diccionario de datos de Oracle. Debido a la naturaleza cambiante(delete,update,insert) de los objetos (tablas, indices) se deben calcular las estadísticas de manera regular dependiendo de la carga de cambios que se tenga en la base de datos.
- Como calculo las estadísticas?
Existen dos formas(instrucciones) para calcular estadísticas: ANALYZE TABLE y DBMS_STATS.
Hoy nada mas voy a ver ANALYZE TABLE, después entraré en detalle con DBMS_STATS ya que requiere más tiempo.
- ANALYZE TABLE
Esta instrucción permite calcular estadísticas, validar la estructura de un indice o tabla, identificar en una tabla registros migrados o encadenados (migrated, chained).
Nota: Aunque esta instrucción todavía existe en la versión 10g no se recomienda usarla para calcular estadísticas desde la versión 9i. Así que solo es ilustrativa para saber que existe.
Requisitos
Se debe tener el privilegio ANALYZE ANY para poder analizar la tabla de otro usuario, pero si se desea analizar las tablas de su propio usuario entonces solo debe existir el objeto dentro de mi schema.
Sintaxis rápida
ANALYZETABLE xxx COMPUTE STATISTICSINDEX ESTIMATE STATISTICS {SAMPLE nn [ROWS|PERCENT]}
CLUSTER DELETE STATISTICS
Donde xxx es el nombre de la tabla o el indice.
Opciones
COMPUTE STATISTICS. Calcula las estadísticas de manera exacta, básicamente lee todos y cada unos de los registros con sus columnas para generar estadísticas, esto quiere decir que si tengo una tabla con 100 millones de registros esta opción leerá todos los registros, esta opción pude ser la más tardada debido a eso pero es la más exacta.
ESTIMATE STATISTICS. Como su nombre lo indica esta opción estima las estadísticas, regularmente esta opción es la más utilizada debido a que es acertada(no en todos los casos) y además toma mucho menos tiempo que la otra. Se puede especificar SAMPLE nn donde nn puede ser el numero de registros(ROWS) o el porcentaje de la tabla (PERCENT).
Ejemplos,ejemplos y más ejemplos…
Primero vamos a crear una tabla e insertarle datos.
scott@DBA> create table test as select * from all_objects; scott@DBA> insert into test select * from all_objects; 48073 rows created. scott@DBA> insert into test select * from all_objects; 48073 rows created. scott@DBA> insert into test select * from all_objects; 48073 rows created. scott@DBA> insert into test select * from all_objects; 48073 rows created. scott@DBA> commit; Commit complete. scott@DBA> select count(*) from test; COUNT(*) ---------- 240365
Después revisamos si la tabla tiene estadísticas.
scott@DBA> select table_name, num_rows, blocks, avg_space, chain_cnt, avg_row_len, sample_Size from user_tables where table_name ='TEST'; TABLE_NAME NUM_ROWS BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE ------------------------------ ---------- ---------- ---------- ---------- ----------- ----------- TEST
Aqui podemos confirmar que la tabla no tiene información estadística. Ahora vamos a calcular estadísticas y luego verificamos que es lo que sucede.
scott@DBA> analyze table test compute statistics; Table analyzed.Elapsed: 00:00:08.34
Aqui podemos ver que tarda 8 segundos y medio en terminar de calcular todas las estadísticas, ahora vamos a revisar que información guardo.
scott@DBA> select table_name, num_rows, blocks, avg_space, chain_cnt, avg_row_len, sample_Size from user_tables where table_name ='TEST';TABLE_NAME NUM_ROWS BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE ------------------------------ ---------- ---------- ---------- ---------- ----------- ----------- TEST 240365 3350 904 0 97 240365 Elapsed: 00:00:00.01
Aquí se muestra que ahora Oracle tiene información respecto a la tabla.
Ahora vamos a calcular las estadísticas con sample.
Primero hay que borrar las estadísticas de la tabla, esto se realiza con la opción DELETE STATISTICS.
scott@DBA> analyze table test delete statistics; Table analyzed.Elapsed: 00:00:00.18 scott@DBA> select table_name,num_rows,blocks,avg_space,chain_cnt,avg_row_len,sample_Size from user_tables where table_name ='TEST'; TABLE_NAME NUM_ROWS BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE ------------------------------ ---------- ---------- ---------- ---------- ----------- ----------- TEST Elapsed: 00:00:00.01 scott@DBA> analyze table test estimate statistics; Table analyzed. Elapsed: 00:00:00.06 scott@DBA> select table_name,num_rows,blocks,avg_space,chain_cnt,avg_row_len,sample_Size from user_tables where table_name ='TEST'; TABLE_NAME NUM_ROWS BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE ------------------------------ ---------- ---------- ---------- ---------- ----------- ----------- TEST 242821 3350 873 0 97 993 Elapsed: 00:00:00.01
Como podemos observar el calculo tardo muchísimo menos tiempo que el calculo completo (6 centésimas de seg VS 8.5 segs), ademas que la información estimada es diferente al calculo completo.
Vamos a probar con 10%, 20% y 80% de SAMPLE.
scott@DBA> analyze table test delete statistics; Table analyzed.Elapsed: 00:00:00.20 scott@DBA> analyze table test estimate statistics sample 10 percent; Table analyzed. Elapsed: 00:00:00.71 scott@DBA> select table_name,num_rows,blocks,avg_space,chain_cnt,avg_row_len,sample_Size from user_tables where table_name ='TEST'; TABLE_NAME NUM_ROWS BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE ------------------------------ ---------- ---------- ---------- ---------- ----------- ----------- TEST 241809 3350 904 0 97 22770 Elapsed: 00:00:00.06 scott@DBA> analyze table test estimate statistics sample 20 percent; Table analyzed. Elapsed: 00:00:01.04 scott@DBA> select table_name,num_rows,blocks,avg_space,chain_cnt,avg_row_len,sample_Size from user_tables where table_name ='TEST'; TABLE_NAME NUM_ROWS BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE ------------------------------ ---------- ---------- ---------- ---------- ----------- ----------- TEST 241809 3350 904 0 97 43310 Elapsed: 00:00:00.03 scott@DBA.REGRESS.RDBMS.DEV.US.ORACLE.COM> analyze table test estimate statistics sample 80 percent; Table analyzed. Elapsed: 00:00:04.34 scott@DBA> select table_name,num_rows,blocks,avg_space,chain_cnt,avg_row_len,sample_Size from user_tables where table_name ='TEST'; TABLE_NAME NUM_ROWS BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE ------------------------------ ---------- ---------- ---------- ---------- ----------- ----------- TEST 240365 3350 904 0 97 240365 Elapsed: 00:00:00.01 scott@DBA.REGRESS.RDBMS.DEV.US.ORACLE.COM> analyze table test compute statistics; Table analyzed. Elapsed: 00:00:04.36 scott@DBA.REGRESS.RDBMS.DEV.US.ORACLE.COM> select table_name,num_rows,blocks,avg_space,chain_cnt,avg_row_len,sample_Size from user_tables where table_name ='TEST'; TABLE_NAME NUM_ROWS BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE ------------------------------ ---------- ---------- ---------- ---------- ----------- ----------- TEST 240365 3350 904 0 97 240365 Elapsed: 00:00:00.01
Como podemos observar entre más elevado es el porcentaje más cercanas son las estadísticas de la tabla a la opción COMPUTE y obviamente tarda mucho más tiempo en calcular las estadísticas.
- Y los indices???
Bueno los indices son analizados al mismo tiempo que se analiza la tabla, veamos un ejemplo.
Primero creamos el indice en la tabla, verificamos que no hay estadísticas creadas para el indice y comenzamos a calcular para distintos porcentajes.
scott@DBA> create index testidx on test(object_name); Index created. scott@DBA> SELECT num_rows, sample_size, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key 2 FROM user_indexes 3 WHERE index_name = 'TESTIDX'; NUM_ROWS SAMPLE_SIZE LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY ---------- ----------- ----------- ------------- ----------------------- ----------------------- Elapsed: 00:00:00.04 scott@DBA> analyze table test estimate statistics sample 10 percent; Table analyzed. Elapsed: 00:00:00.48 scott@DBA> SELECT num_rows, sample_size, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key 2 FROM user_indexes 3 WHERE index_name = 'TESTIDX'; NUM_ROWS SAMPLE_SIZE LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY ---------- ----------- ----------- ------------- ----------------------- ----------------------- 243057 25522 1219 29417 1 5 Elapsed: 00:00:00.01 scott@DBA> analyze table test estimate statistics sample 20 percent; Table analyzed. Elapsed: 00:00:00.87 scott@DBA> SELECT num_rows, sample_size, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key 2 FROM user_indexes 3 WHERE index_name = 'TESTIDX'; NUM_ROWS SAMPLE_SIZE LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY ---------- ----------- ----------- ------------- ----------------------- ----------------------- 242871 49411 1219 29310 1 5 Elapsed: 00:00:00.00 scott@DBA> analyze table test estimate statistics sample 80 percent; Table analyzed. Elapsed: 00:00:04.40 scott@DBA> SELECT num_rows, sample_size, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key 2 FROM user_indexes 3 WHERE index_name = 'TESTIDX'; NUM_ROWS SAMPLE_SIZE LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY ---------- ----------- ----------- ------------- ----------------------- ----------------------- 240365 240365 1189 28595 1 5 Elapsed: 00:00:00.01 scott@DBA> analyze table test compute statistics; Table analyzed. Elapsed: 00:00:04.39
Como podemos observar en el caso de los indices sucede lo mismo que con las tablas, entre el porcentaje es menor también lo es la exactitud de las estadísticas.
- Desempeño(Performance)
Bueno y como afectan las estadísticas al performance. Vamos viendo un ejemplo donde veamos el explain plan sin estadísticas y con estadísticas.
scott@DBA> analyze table test delete statistics;Table analyzed.scott@DBA> select * from test where object_name='TEST';
Execution Plan
----------------------------------------------------------
Plan hash value: 464205115
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 640 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 5 | 640 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TESTIDX | 5 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"='TEST')
Note
----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
1743 recursive calls
0 db block gets
420 consistent gets
0 physical reads
0 redo size
1564 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
50 sorts (memory)
0 sorts (disk)
5 rows processed
scott@DBA> analyze table test compute statistics;
Table analyzed.
scott@DBA> select * from test where object_name='TEST';
Execution Plan
----------------------------------------------------------
Plan hash value: 464205115
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 696 | 9 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 8 | 696 | 9 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TESTIDX | 8 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"='TEST')
Statistics
----------------------------------------------------------
1685 recursive calls
0 db block gets
352 consistent gets
0 physical reads
0 redo size
1564 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
50 sorts (memory)
0 sorts (disk)
5 rows processed
Como se puede observar en la sección de statistics vemos que los recursive calls y consisten gets son menores cuando se calculan las estadísticas.
- Cada cuando se deben calcular estadísticas
Esta es una pregunta difícil de contestar ya que esto depende de la actividad(delete,insert,update) que tiene la base de datos en un determinado tiempo, por ejemplo si se borran/actualizan/insertan muchos datos probablemente es bueno calcular las estadísticas cada semana, de nuevo esto depende de la carga que se tenga en un tiempo determinado y la mejora manera es hacer pruebas y verificar los resultados.
- Conclusiones
Esta fue una “embarrada rapida” sobre lo que son las estadísticas y como se calculan con analyze table. Como se pudo observar en los ejemplos si se calculan estadísticas ayuda al desempeño de los queries. En mi experiencia he visto que el no calcular estadísticas puede causar grandes problemas en el desempeño de la base de datos así que como regla general se deben de calcular. A partir de la versión 10g de Oracle se incluye un JOB que calcula las estadísticas de manera automática y a partir de la versión 9i se recomienda utilizar el paquete DBMS_STATS en lugar de ANALYZE TABLE (aunque aquí lo vimos como ejemplo ilustrativo).
En el siguiente capitulo voy a ver como se calculan con el paquete DBMS_STATS.
Generación Automatica de Estadisticas
Existen dos maneras maneras de generar estadísticas en Oracle: ANALYZE TABLE y DBMS_STATS.
El siguiente script permite calcular estadísticas en las tablas nuevas y en las que no las tengan actualizadas, además de calcular histogramas en las tablas que nosotros le proporcionemos.
Primero hay que saber a que usuario le vamos a calcular estadísticas.
vowner VARCHAR2 (100) := ‘APPLOWNER‘;
Para poder calcularle a las tablas que tienen las estadísticas des actualizadas primero las tablas se deben monitorear para que Oracle pueda identificar cuales tablas han tenido cambios significativos y por lo tanto sea necesario re calcular estadísticas. Para monitorear las tablas se utiliza el comando ALTER TABLE tblname MONITORING.
Se utiliza un cursor el cual genera los comandos para alterar las tablas que no son monitoreadas, regularmente las tablas nuevas.
Note que aqui se utiliza la variable anteriormente declarada.
CURSOR nomonitoringtables IS SELECT 'ALTER TABLE ' || vowner || '.' || table_name || ' MONITORING ' AS command FROM dba_tables WHERE MONITORING = 'NO' AND TEMPORARY = 'N' AND owner = vowner;
Como también deseamos calcular histogramas a ciertas tablas entonces declaramos una colección la cual contiene la lista de tablas a las cuales se les calcularan histogramas. también se tiene que saber de que tamaño va a ser el histograma, para esto utilizamos una variable.
En caso que no se desee calcular histogramas a ninguna de las tablas entonces dejar la inicialización de la colección vacía. Algo asi:
histtbl_t ();
TYPE histtbl_t IS TABLE OF VARCHAR2 (500);
--calculate histograms to the next tables
vhisttables histtbl_t := histtbl_t ('tbl1','tbl4','tbl3');
vhistogramsize NUMBER := 200;
Como primer paso en la ejecución calculamos las estadísticas a las tablas que no tienen y a las que no están actualizadas.
--CALCULATE STALE STATISTICS DBMS_STATS.gather_schema_stats (ownname => vowner, options => 'GATHER AUTO', estimate_percent => DBMS_STATS.auto_sample_size, method_opt => 'for all columns size auto', DEGREE => 2, gather_temp => FALSE, block_sample => TRUE, CASCADE => TRUE, no_invalidate => FALSE );
Después si hay tablas a las cuales se desea calcular histogramas entonces se calcula a ese grupo de tablas.
--calculate histograms IF vhisttables.COUNT () <> 0 THEN FOR indx IN vhisttables.FIRST .. vhisttables.LAST LOOP DBMS_STATS.gather_table_stats (ownname => vowner, tabname => vhisttables (indx), estimate_percent => DBMS_STATS.auto_sample_size, block_sample => TRUE, method_opt => 'FOR ALL INDEXED COLUMNS SIZE '||vhistogramsize , DEGREE => 2, CASCADE => TRUE, no_invalidate => FALSE ); END LOOP; END IF;
Por ultimo, utilizando el cursor para alterar las tablas, se alteran las tablas nuevas que no han sido monitoreadas para que a la siguiente ejecución se calculen las estadísticas.
--ALTER NEW TABLES TO MONITORING
FOR vtables IN nomonitoringtables
LOOP
EXECUTE IMMEDIATE vtables.command;
END LOOP;
Script completo:
/*
Calculate Weekly Statistics for APPLOWNER
*/
DECLARE
vowner VARCHAR2 (100) := 'APPLOWNER';
CURSOR nomonitoringtables
IS
SELECT 'ALTER TABLE ' || vowner || '.' || table_name || ' MONITORING ' AS command
FROM dba_tables
WHERE MONITORING = 'NO' AND TEMPORARY = 'N' AND owner = vowner;
TYPE histtbl_t IS TABLE OF VARCHAR2 (500);
--calculate histograms to the next tables
vhisttables histtbl_t := histtbl_t ('SALDOS_IMP');
vhistogramsize NUMBER := 200;
BEGIN
--CALCULATE STALE STATISTICS
DBMS_STATS.gather_schema_stats (ownname => vowner,
options => 'GATHER AUTO',
estimate_percent => DBMS_STATS.auto_sample_size,
method_opt => 'for all columns size auto',
DEGREE => 2,
gather_temp => FALSE,
block_sample => TRUE,
CASCADE => TRUE,
no_invalidate => FALSE
);
--calculate histograms
IF vhisttables.COUNT () <> 0
THEN
FOR indx IN vhisttables.FIRST .. vhisttables.LAST
LOOP
DBMS_STATS.gather_table_stats (ownname => vowner,
tabname => vhisttables (indx),
estimate_percent => DBMS_STATS.auto_sample_size,
block_sample => TRUE,
method_opt => 'FOR ALL INDEXED COLUMNS SIZE '||vhistogramsize,
DEGREE => 2,
CASCADE => TRUE,
no_invalidate => FALSE
);
END LOOP;
END IF;
--ALTER NEW TABLES TO MONITORING
FOR vtables IN nomonitoringtables
LOOP
EXECUTE IMMEDIATE vtables.command;
END LOOP;
END;
Si se desea que este script se ejecute de manera automática entonces hay que calendarizarlo en un job como se muestra a continuación.
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => '/*
Calculate Weekly Statistics for APPLOWNER
*/
DECLARE
vowner VARCHAR2 (100) := ''APPLOWNER'';
CURSOR nomonitoringtables
IS
SELECT ''ALTER TABLE '' || vowner || ''.'' || table_name || '' MONITORING '' AS command
FROM dba_tables
WHERE MONITORING = ''NO'' AND TEMPORARY = ''N'' AND owner = vowner;
TYPE histtbl_t IS TABLE OF VARCHAR2 (500);
--calculate histograms to the next tables
vhisttables histtbl_t := histtbl_t (''tbl1'',''tbl2'',''tbl3'');
vhistogramsize NUMBER := 200;
BEGIN
--CALCULATE STALE STATISTICS
DBMS_STATS.gather_schema_stats (ownname => vowner,
options => ''GATHER AUTO'',
estimate_percent => DBMS_STATS.auto_sample_size,
method_opt => ''for all columns size auto'',
DEGREE => 2,
gather_temp => FALSE,
block_sample => TRUE,
CASCADE => TRUE,
no_invalidate => FALSE
);
--calculate histograms
IF vhisttables.COUNT () <> 0
THEN
FOR indx IN vhisttables.FIRST .. vhisttables.LAST
LOOP
DBMS_STATS.gather_table_stats (ownname => vowner,
tabname => vhisttables (indx),
estimate_percent => DBMS_STATS.auto_sample_size,
block_sample => TRUE,
method_opt => ''FOR ALL INDEXED COLUMNS SIZE ''||vhistogramsize,
DEGREE => 2,
CASCADE => TRUE,
no_invalidate => FALSE
);
END LOOP;
END IF;
--ALTER NEW TABLES TO MONITORING
FOR vtables IN nomonitoringtables
LOOP
EXECUTE IMMEDIATE vtables.command;
END LOOP;
END;'
,next_date => to_date('17/12/2006 00:00:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'TRUNC(SYSDATE+7)'
,no_parse => TRUE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
END;
/
commit;
powered by performancing firefox
http://www.thesimpledollar.com/2006/12/01/30-essential-pieces-of-free-and-open-software-for-windows/
http://www.thesimpledollar.com/2006/12/01/30-essential-pieces-of-free-and-open-software-for-windows/
powered by performancing firefox
Como seleccionar datos “dummy” sin tablas.
La función TABLE sirve para convertir/forzar los resultados de una colección para que puedan ser seleccionados como si fueran una tabla común y corriente.
Por ejemplo, si tenemos datos en una colección de la siguiente manera y queremos hacer un SELECT y que aparezcan los datos 1 a 1 por renglón, entonces hacemos lo siguiente:
SELECT * FROM TABLE( sys.dbms_debug_vc2coll (1,2,3,4,5) ) ;
Aquí podemos ver la función TABLE la cual toma los valores de una colección en este caso la colección es del tipo dbms_debug_vc2coll, el resultado es el siguiente:
COLUMN_VALUE
———————–
1
2
3
4
5
5 rows selected.
Y lo mismo funciona para strings:
SELECT * FROM TABLE( sys.dbms_debug_vc2coll (‘uno’,'dos’,'tres’,'cuatro’) ) ;
COLUMN_VALUE
———————–
uno
dos
tres
cuatro
4 rows selected.
powered by performancing firefox
