Como recuperar estadisticas anteriores con Oracle 10G

A partir de la version 10g de Oracle se incluye una funcionalidad muy util para recuperar estadísticas. Cada vez que se utiliza el paquete DBMS_STATS para modificar las estadísticas la versión anterior de ellas es guardada automáticamente en caso que se desee recuperar. Esta opción es muy util si notamos que despues de calcular estadísticas el performance se degradó de manera considerable y necesitamos regresar las anteriores.

Pero no hay nada mejor que un ejemplo para mostrar como funciona…

Primero creamos una tabla para nuestras pruebas

SQL> create table t as select * from dba_objects;

Table created.

Contamos los registros y verificamos que no hay estadisticas

SQL> select count(*) from t;

COUNT(*)
----------
49779

SQL> select table_name, num_rows, blocks, avg_space, avg_row_len, last_analyzed
2  from user_tables;

TABLE   NUM_ROWS     BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANAL
----- ---------- ---------- ---------- ----------- ---------
T

Recopilamos las estadisticas de la tabla

SQL>exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

Verificamos que las estadisticas fueran recopiladas

SQL>select table_name,num_rows,blocks,avg_space,avg_row_len,last_analyzed
2  from user_tables;

TABLE   NUM_ROWS     BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANAL
----- ---------- ---------- ---------- ----------- ---------
T          49779        704          0          93 26-AUG-09

Aqui podemos constatar que el NUM_ROWS es igual al COUNT(*) que hicimos anteriormente, aunque no siempre es asi.
Ahora vamos a verificar cuanta historia de estadisticas tenemos para esta tabla

SQL>select table_name,stats_update_time
2  from dba_tab_stats_history
3  where owner=user;

TABLE STATS_UPDATE_TIME
----- ---------------------------------------------------------------------------
T     26-AUG-09 06.51.01.496224 AM -04:00

Podemos observar que se han calculado una sola vez las estadisticas para la tabla.
Ahora vamos a insertar mas registros y recalcular estadisticas

SQL>insert into t select * from dba_objects;
49778 rows created.
SQL> /
49778 rows created.
SQL> /
49778 rows created.
SQL> commit;
Commit complete.

Verificamos el numero de registros y las estadisticas actuales. Podemos verificar que las estadisticas no estan actualizadas ya que el numero de registros es por mucho mayor al registrado en las estadisticas.

SQL>select count(*) from t;

COUNT(*)
----------
199113
SQL>select table_name,num_rows,blocks,avg_space,avg_row_len,last_analyzed
2  from user_tables;

TABLE   NUM_ROWS     BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANAL
----- ---------- ---------- ---------- ----------- ---------
T          49779        704          0          93 26-AUG-09

Ahora volvemos a recopilar estadisticas y verificamos que sean similares al COUNT

SQL>exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.
SQL> select table_name,num_rows,blocks,avg_space,avg_row_len,last_analyzed
2  from user_tables;

TABLE   NUM_ROWS     BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANAL
----- ---------- ---------- ---------- ----------- ---------
T         199113       2784          0          93 26-AUG-09

Ahora verificamos cuanto historial tenemos

SQL> select table_name,stats_update_time
2  from dba_tab_stats_history
3  where owner=user;

TABLE STATS_UPDATE_TIME
----- ---------------------------------------------------------------------------
T     26-AUG-09 06.51.01.496224 AM -04:00
T     26-AUG-09 07.01.01.161096 AM -04:00

Podemos observar que ahora tenemos 2 registros de cuando se calcularon estadisticas.
Ahora podemos regresar en el tiempo y dejar estadisticas a la fecha que queramos.
Vamos a crear otro registro historico para juagar un poco las estadisticas.

SQL>insert into t select * from dba_objects;
49778 rows created.
SQL> /
49778 rows created.
SQL> select count(*) from t;

COUNT(*)
----------
298669

SQL>select table_name,num_rows,blocks,avg_space,avg_row_len,last_analyzed
2  from user_tables;

TABLE   NUM_ROWS     BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANAL
----- ---------- ---------- ---------- ----------- ---------
T         199113       2784          0          93 26-AUG-09

SQL> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

SQL> l
1  select table_name,num_rows,blocks,avg_space,avg_row_len,last_analyzed
2* from user_tables
SQL> /

TABLE   NUM_ROWS     BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANAL
----- ---------- ---------- ---------- ----------- ---------
T         298002       4170          0          93 26-AUG-09

SQL> select table_name,stats_update_time
2  from dba_tab_stats_history
3  where owner=user;

TABLE  STATS_UPDATE_TIME
-----  ---------------------------------------------------------------------------
T      26-AUG-09 06.51.01.496224 AM -04:00
T      26-AUG-09 07.01.01.161096 AM -04:00
T      26-AUG-09 07.04.17.929191 AM -04:00

Tenemos 3 registros historicos de estadisticas y ahora podemos regresar las estadisticas tal y como estaban al inicio si lo deseamos.
Actualmente tenemos las estadisticas actualizadas, o sea NUM_ROWS=298,002
Vamos a regresar las estadisticas cuando NUM_ROWS era 119,113

SQL> exec dbms_stats.restore_table_stats(user,'T','26-AUG-09 07.01.01.161096 AM -04:00');

PL/SQL procedure successfully completed.
SQL> select table_name,num_rows,blocks,avg_space,avg_row_len,last_analyzed
2  from user_tables;

TABLE   NUM_ROWS     BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANAL
----- ---------- ---------- ---------- ----------- ---------
T         199113       2784          0          93 26-AUG-09

Podemos ver que regresamos las estadisticas al valor anterior.
Vamos a verificar cuanta historia tenemos ahora.

SQL> select table_name,stats_update_time
2  from dba_tab_stats_history
3  where owner = user;

TABLE  STATS_UPDATE_TIME
-----  ---------------------------------------------------------------------------
T      26-AUG-09 06.51.01.496224 AM -04:00
T      26-AUG-09 07.01.01.161096 AM -04:00
T      26-AUG-09 07.04.17.929191 AM -04:00

TABLE  STATS_UPDATE_TIME
-----  ---------------------------------------------------------------------------
T      26-AUG-09 07.06.50.924803 AM -04:00

Podemos observas que se creo otro registro historico, esto es normal ya que se crea 1 registro cada vez que se usa el paquete DBMS_STATS.
Vamos a regresar las estadisticas como estaban al inicio

SQL> exec dbms_stats.restore_table_stats(user,'T','26-AUG-09 06.51.01.496224 AM -04:00');

PL/SQL procedure successfully completed.
SQL> select table_name,num_rows,blocks,avg_space,avg_row_len,last_analyzed
2  from user_tables;

TABLE   NUM_ROWS     BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANAL
----- ---------- ---------- ---------- ----------- ---------
T          49779        704          0          93 26-AUG-09

SQL> select count(*) from t;

COUNT(*)
----------
298669

SQL> select table_name,stats_update_time
2  from dba_tab_stats_history
3  where owner = user;

TABLE  STATS_UPDATE_TIME
-----  ---------------------------------------------------------------------------
T      26-AUG-09 06.51.01.496224 AM -04:00
T      26-AUG-09 07.01.01.161096 AM -04:00
T      26-AUG-09 07.04.17.929191 AM -04:00
T      26-AUG-09 07.06.50.924803 AM -04:00
T      26-AUG-09 07.10.30.136210 AM -04:00

Con esta opción podemos estar seguros que podremos regresar las estadisticas de las tablas en caso necesario. Por default Oracle guarda los ultimos 31 dias de estadisticas, y como muchas opciones esto puede ser configurado a nuestras necesidades. Hay que notar que solo las estadisticas calculadas por medio de DBMS_STATS son las que se pueden regresar y registrar en la vista DBA_STATS_HISTORY, si por alguna razon se calculan estadisticas con ANALYZE TABLE estas no se pueden recuperar.

Al borrar el historico de las estadisticas obviamente limpia la vista que contiene los registros historicos y no se pueden recuperar.

SQL> exec dbms_stats.purge_stats(systimestamp);

PL/SQL procedure successfully completed.

SQL> select table_name,stats_update_time
2  from dba_tab_stats_history
3  where owner=user;

no rows selected<

SQL> drop table t;

Table dropped.

Ahora vamos a ver un ejemplo con la opcion de regresar las estadisticas para todo el schema. No voy a describir los pasos ya se explican por si mismos.

SQL> create table t as select * from dba_objects;

Table created.
SQL> create table d as select * from dba_objects;

Table created.
SQL> exec dbms_stats.gather_Schema_stats(user);

PL/SQL procedure successfully completed.

SQL> select table_name,num_rows,blocks,avg_space,avg_row_len,last_analyzed
2  from user_tables;

TABLE   NUM_ROWS     BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANAL
----- ---------- ---------- ---------- ----------- ---------
T          49779        704          0          93 26-AUG-09
D          49779        704          0          93 26-AUG-09

SQL> insert into t select * from dba_objects;
49779 rows created.
SQL> /
49779 rows created.
SQL> insert into d select * from dba_objects;
49779 rows created.
SQL> /
49779 rows created.
SQL> /
49779 rows created.
SQL> exec dbms_stats.gather_Schema_stats(user);
PL/SQL procedure successfully completed.

SQL> select table_name,num_rows,blocks,avg_space,avg_row_len,last_analyzed
2  from user_tables;

TABLE   NUM_ROWS     BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANAL
----- ---------- ---------- ---------- ----------- ---------
T         149337       2090          0          93 26-AUG-09
D         199116       2784          0          93 26-AUG-09

SQL> select table_name,stats_update_time
2  from dba_tab_stats_history
3  where owner=user;

TABLE  STATS_UPDATE_TIME
-----  ---------------------------------------------------------------------------
D      26-AUG-09 07.18.00.582750 AM -04:00
T      26-AUG-09 07.18.01.161388 AM -04:00
D      26-AUG-09 07.17.06.242711 AM -04:00
T      26-AUG-09 07.17.06.781828 AM -04:00

SQL> exec dbms_stats.restore_schema_stats(user,'26-AUG-09 07.18.00.582750 AM -04:00');

PL/SQL procedure successfully completed.

SQL> select table_name,num_rows,blocks,avg_space,avg_row_len,last_analyzed
2  from user_tables;

TABLE   NUM_ROWS     BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANAL
----- ---------- ---------- ---------- ----------- ---------
T          49779        704          0          93 26-AUG-09
D         199116       2784          0          93 26-AUG-09

SQL> exec dbms_stats.restore_schema_stats(user,'26-AUG-09 07.18.01.161388 AM -04:00');

PL/SQL procedure successfully completed.

SQL> select table_name,num_rows,blocks,avg_space,avg_row_len,last_analyzed
2  from user_tables;

TABLE   NUM_ROWS     BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANAL
----- ---------- ---------- ---------- ----------- ---------
T         149337       2090          0          93 26-AUG-09
D         199116       2784          0          93 26-AUG-09

SQL> exec dbms_stats.restore_schema_stats(user,'26-AUG-09 07.18.00.000000 AM -04:00');

PL/SQL procedure successfully completed.

SQL> select table_name,num_rows,blocks,avg_space,avg_row_len,last_analyzed
2  from user_tables;

TABLE   NUM_ROWS     BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANAL
----- ---------- ---------- ---------- ----------- ---------
T          49779        704          0          93 26-AUG-09
D          49779        704          0          93 26-AUG-09
  1. Como recuperar estadisticas anteriores con Oracle 10G - DbRunas

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: