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
