Restore tables statistics
Hello All,
I have a quick question about some doubts about how to recover the stats from a table.
According to the following query against dba_tab_stats_history the only stats available for those two tables are the one generated after April-15.
SQL> SELECT table_name, TO_CHAR (stats_update_time, 'DD-MON-YYYY HH24:MI:SS') as stats_upd_time
FROM dba_tab_stats_history
WHERE owner = 'PROGRA'
AND table_name IN
('PRO_PROGRAMACION_DIA_DETALLES', 'PUB_ORDEN_LINEA_AVISOS')
TABLE_NAME STATS_UPD_TIME
------------------------------ ------------------------------
PRO_PROGRAMACION_DIA_DETALLES 15-ABR-2012 10:55:17
PUB_ORDEN_LINEA_AVISOS 15-ABR-2012 11:24:13
PUB_ORDEN_LINEA_AVISOS 17-ABR-2012 11:10:27
3 rows selected.
SQL> select dbms_stats.get_stats_history_availability from dual
GET_STATS_HISTORY_AVAILABILITY
---------------------------------
17/03/12 02:23:16,842757000 -03:0
0
1 row selected.
SQL> select dbms_stats.get_stats_history_retention from dual
GET_STATS_HISTORY_RETENTION
---------------------------
31
1 row selected.
April 15th was the last time we ran the DBMS_STATS.gather_database_stats procedure, and somebody is asking me to recover the stats from those two tables since some queries are not performing as expected. Data pump export files are available from 11-Abr (one from every day). My question is:
Is the query to dba_tab_stats_history enough to say we don't old stats? What kind of stats may I expect to find at the dump files taken prior to April 15th?
If searching for the stats at the dump file makes sense even though the query to stats_history is getting no results, Should I recover the tables under a different schema and afterwards export and re-import the statistics using the DBMS_STATS.EXPORT_TABLE_STATS and .IMPORT_TABLE_STATS procedures?
Thanks in advance.
Regards.
Martin