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.
En nuestro caso owname es el mismo usuario de la tabla “problema”.

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>
  1. #1 by Tepox on 16 December, 2009 - 22:51

    Muy buen post. Espero con ansias el siguiente tema (lo que se puede hacer con las estadísticas guardadas). Pero me quedé esperando el anterior: Generar estadísticas con schema (solo vimos a nivel ídice y tabla)

    Saludos!!!

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: