DBMS_STATS Parte 4, Exportar estadísticas
Esta semana recibimos una llamada de un usuario diciendo que un reporte estaba muy lento (« típico caso») que antes se ejecutaba rápidamente pero ahora estaba tomando mucho tiempo. Y en efecto, el reporte estaba tomando como 1 hora para terminar.
Ok, manos a la obra ; lo primero que hicimos fue pedirles a los developers el query del reporte, no lo envían y hasta me asuste con el monstruo de query… en total 1969 líneas de código que incluían alrededor de 5 tablas con subqueries, group by, case when, sum over partition,etc,etc… digno de regresárselo al developer inmediatamente y decirle que no mam… que lo reescriba. Pero obviamente no hicimos eso, lo primero fue sacar un explain plan del query y al parecer estaba usando índices correctamente con excepción de 2 tablas donde estaba haciendo full scan. Primer síntoma que encontramos fue esos full scans pero “típico” los Developers nos dijeron que no habían cambiado nada en el reporte que ese era el Query que siempre había funcionado bien (y al final supimos que era cierto?) . Bueno entonces algo tuvo que cambiar para que el reporte estuviera lento, entonces lo segundo que revisamos fueron las estadísticas de las tablas y ahí fue donde encontramos el error, no se habían calculado desde la creación del sistema en Marzo.
Bueno lo que haríamos seria calcular estadísticas y ver que pasaba con el reporte, pero me acorde que en varios lados recomiendan antes de cambiar las estadísticas guardar un respaldo en caso que deseemos regresar a las anteriores, mmmm, como hacer eso. Investigue un poco y de eso voy a hablar en este post, como exportar estadísticas y que se puede hacer con ellas.
Para que exportar estadísticas?
El “problema” al calcular estadísticas es que los planes de ejecución pueden cambiar en algún punto en la vida de la base de datos; un grupo de tablas al inicio puede tener cientos de registros pero después de 2 años tienen millones, esto hace que al calcular las estadísticas en esas tablas los planes de ejecución cambien. Pero no se supone que ese es el punto de calcular estadísticas, darle a Oracle la información mas actualizada sobre las tablas para que pueda tomar mejores decisiones en cuanto al plan de ejecución? totalmente cierto, eso es lo que queremos, pero hay ciertas situaciones en las que necesitamos saber que es lo que ha cambiado y nadie nos puede dar la respuesta (ni los developers, ni los managers, menos los usuarios). Por lo tanto lo ideal seria poder ver el plan de ejecución anterior (el bueno) con el plan de ejecución actual (el malo, lento); pero esto es complicado ya que tendríamos que estar guardando planes de ejecución por cada Query,etc. Pero que tal si tenemos las estadísticas de la semana pasada o el mes pasado, podríamos importarlas en el ambiente de pruebas y ver que es lo que ha cambiado, probar, probar y probar.
Bueno, pues Oracle ya pensó al respecto y provee un conjunto de procedimientos para facilitar la tarea. A continuación voy a mostrar con un ejemplo como guardar y comparar estadísticas para una tabla.
Vamos a crear una tabla y calcularle estadísticas.
SQL> create table emp2 as select * from emp;
Table created.
SQL> set null <<null>>
SQL> select to_char(last_analyzed,'dd/mm/yyyy hh24:mi:ss') last_analyzed,
2 num_rows,
3 blocks,
4 avg_space
5 from user_tables where table_name='EMP2'
6 ;
LAST_ANALYZED NUM_ROWS BLOCKS AVG_SPACE
------------------- ---------- ---------- ----------
<<null>> <<null>> <<null>> <<null>>
SQL> exec dbms_stats.gather_Table_stats(user,'EMP2');
PL/SQL procedure successfully completed.
SQL> select to_char(last_analyzed,'dd/mm/yyyy hh24:mi:ss') last_analyzed,
2 num_rows,
3 blocks,
4 avg_space
5 from user_tables where table_name='EMP2';
LAST_ANALYZED NUM_ROWS BLOCKS AVG_SPACE
------------------- ---------- ---------- ----------
16/07/2008 15:57:41 14 4 0
Bueno ahora hay que hacer que las estadísticas cambien y para hacerlo hay que cambiar la tabla EMP2. Vamos insertar 1 millón de registros.
SQL> insert into emp2 select * from emp connect by rownum <= 1000000;
1000013 rows created.
Ahora si calculamos estadísticas obviamente todo va a cambiar, pero lo que queremos hacer es guardar las anteriores y hacer una comparación.
Entonces para hacer un respaldo de las estadísticas anteriores primero debemos crear una tabla donde guardemos las estadísticas pasadas. Esto se realiza fácilmente con el procedimiento CREATE_STAT_TABLE.
DBMS_STATS.CREATE_STAT_TABLE (
ownname VARCHAR2,
stattab VARCHAR2,
tblspace VARCHAR2 DEFAULT NULL);
| Parámetro | Descripción |
| Owname | Nombre del schema/usuario que va a tener la tabla de estadisticas. |
| Stattab | Nombre de la nueva tabla a crear. |
| Tblspace | Nombre del tablespace donde queremos guardarla, si no se especifica se crea en el tablespace default de owname. |
SQL> exec dbms_stats.create_stat_table(USER,’HISTORIAL’);
PL/SQL procedure successfully completed.
Según la documentación de Oracle la información de estadísticas se debe acceder solamente con los procedimientos de DBMS_STATS, pero la tabla no es cosa del otro mundo y si alguien esta interesado puede jugar con ella.
SQL> desc historial;
Name Null? Type
----------------------------------------- -------- ----------------------------
STATID VARCHAR2(30)
TYPE CHAR(1)
VERSION NUMBER
FLAGS NUMBER
C1 VARCHAR2(30)
C2 VARCHAR2(30)
C3 VARCHAR2(30)
C4 VARCHAR2(30)
C5 VARCHAR2(30)
N1 NUMBER
N2 NUMBER
N3 NUMBER
N4 NUMBER
N5 NUMBER
N6 NUMBER
N7 NUMBER
N8 NUMBER
N9 NUMBER
N10 NUMBER
N11 NUMBER
N12 NUMBER
D1 DATE
R1 RAW(32)
R2 RAW(32)
CH1 VARCHAR2(1000)
Ok, ahora que ya tenemos nuestra tabla como copiamos nuestras estadísticas a ella. Para eso utilizamos el procedimiento dbms_stats.EXPORT_TABLE_STATS, aunque para este ejemplo solo estamos utilizando la parte de una sola tabla hay otros procedimientos que permiten hacerlo a nivel schema, base de datos, etc:
EXPORT_COLUMN_STATS Procedure
EXPORT_DATABASE_STATS Procedure
EXPORT_DICTIONARY_STATS Procedure
EXPORT_FIXED_OBJECTS_STATS Procedure
EXPORT_INDEX_STATS Procedure
EXPORT_SCHEMA_STATS Procedure
EXPORT_SYSTEM_STATS Procedure
EXPORT_TABLE_STATS Procedure
En lo personal yo recomendaría hacerlo hasta nivel schema para así no perder las estadísticas de una tabla.
EXPORT_TABLE_STATS
Permite sacar las estadisticas de una tabla y guardarlas en otra.
DBMS_STATS.EXPORT_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
stattab VARCHAR2,
statid VARCHAR2 DEFAULT NULL,
cascade BOOLEAN DEFAULT TRUE,
statown VARCHAR2 DEFAULT NULL);
| Parametro | Descripción |
| Owname | Nombre del schema/usuario dueño de la tabla de la cual queremos respaldar. |
| Tabname | Nombre de la tabla que queremos respaldar. |
| Partname | Nombre de la partición. |
| Stattab | Nombre de la tabla donde vamos a guardar las estadísticas. |
| Statid | Identificador para las estadísticas. Es opcional pero es recomendable identificarlas. |
| Cascade | TRUE default, si deseamos exportar estadísticas de índices y columnas. |
| Statown | Quien es el dueño de la tabla de estadísticas, diferente a owname. |
SQL> exec dbms_stats.export_table_stats(USER,’EMP2′,null, ‘HISTORIAL’,'Primera’,TRUE,’SCOTT’);
PL/SQL procedure successfully completed.
SQL> SELECT COUNT(*) FROM HISTORIAL;
COUNT(*)
———-
9
Ahora ya tenemos un respaldo de estadísticas (si alguien esta interesado puede hacer un select a la tabla y ver que contiene), en este momento podemos calcular las nuevas y comenzar a comparar.
SQL> exec dbms_stats.gather_Table_stats(user,'EMP2');
PL/SQL procedure successfully completed.
SQL> select to_char(last_analyzed,'dd/mm/yyyy hh24:mi:ss') last_analyzed,
2 num_rows,
3 blocks,
4 avg_row_len
5 from user_tables where table_name='EMP2';
LAST_ANALYZED NUM_ROWS BLOCKS AVG_ROW_LEN
------------------- ---------- ---------- -----------
16/07/2008 16:29:16 1007517 5539 34
Perfecto, ahora tenemos estadísticas nuevas. Obviamente en nuestro ejemplo ya sabemos que fue lo que cambio, pero en un ambiente real regularmente no lo sabemos.
Bueno ahora como le hacemos para comparar las estadísticas actuales con las estadísticas anteriores. Para esto nos vamos a servir de un poco de PL/SQL, la verdad no se si Oracle provee alguna herramienta mas amigable para comparar por que hacer un programa para comparar si es un poco fastidioso, sobre todo cuando se desea comparar varias tablas a la vez.
El procedimiento que nos da información (muy poca a mi parecer y un poco limitado) de las estadísticas es dbms_stats.GET_TABLE_STATS.
DBMS_STATS.GET_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, numrows OUT NUMBER, numblks OUT NUMBER, avgrlen OUT NUMBER, statown VARCHAR2 DEFAULT NULL, cachedblk OUT NUMBER, cachehit OUT NUMBER); SQL> set serveroutput on SQL> SQL> DECLARE 2 numr NUMBER; 3 numb NUMBER; 4 avgr NUMBER; 5 vtabname varchar2(100); 6 vstattab varchar2(100); 7 vstatid varchar2(100); 8 BEGIN 9 10 vtabname := 'EMP2'; 11 vstatid := 'PRIMERA'; 12 vstattab := 'HISTORIAL'; 13 14 dbms_stats.get_table_stats( 15 ownname=> USER, 16 tabname=> vtabname, 17 partname=>null, 18 stattab=>vstattab, 19 statid=> vstatid, 20 numrows=>numr, 21 numblks=>numb, 22 avgrlen=>avgr, 23 statown=>USER); 24 25 dbms_output.put_line ('Table: '||vtabname); 26 dbms_output.put_line('# of rows: ' || TO_CHAR(numr)); 27 dbms_output.put_line('# of blocks: ' || TO_CHAR(numb)); 28 dbms_output.put_line('Avg row len: ' || TO_CHAR(avgr) || ' bytes'); 29 30 end; 31 / Table: EMP2 # of rows: 14 # of blocks: 4 Avg row len: 37 bytes
PL/SQL procedure successfully completed.
Con esto podemos comparar el antes y despues
| Antes | Despues | |
| Rows | 14 | 1007517 |
| Blocks | 4 | 5539 |
| Avg Row Len | 37 | 34 |
Así que esta es una manera “rápida” y sucia de respaldar las estadísticas de una tabla (aunque es posible hacerlo para el schema, BD,etc) y después comparar para saber que fue lo que paso.
En el siguiente post voy a mostrar que más se puede hacer con las estadísticas que tenemos guardadas.
</pre>
