{"id":2201,"date":"2011-05-16T12:00:00","date_gmt":"2011-05-16T10:00:00","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/how-to-retrieve-previous-table-statistics-using-oracle-dbmsstats\/"},"modified":"2011-05-16T12:00:00","modified_gmt":"2011-05-16T10:00:00","slug":"how-to-retrieve-previous-table-statistics-using-oracle-dbmsstats","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/how-to-retrieve-previous-table-statistics-using-oracle-dbmsstats\/","title":{"rendered":"How to retrieve previous table statistics using Oracle dbms_stats"},"content":{"rendered":"<p>Sometimes, you need to modify the statistics of a table. There are various reasons: either for the performance analysis or because the execution plan changed. Oracle offers two methods: statistics restore or statistics export. Both are in the dbms_stats package.<\/p>\n<h3>1. Statistics restore<\/h3>\n<p>This method uses the procedure dbms_stats.restore_%_stats, where % can be table, schema, database, and fixed_objects.<br \/>\nThis is an example for a restoration of a table with definite timestamp. The starting situation is:<\/p>\n<p>Statistics info:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; select table_name, num_rows,to_char(last_analyzed,'DD.MM.YYYY HH24:MI:SS')from dba_tables where owner ='SCHEMA1' and table_name='TAB1';TABLE_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NUM_ROWS\u00a0\u00a0\u00a0\u00a0 LAST_ANALYZED\n------------------------------ -------------\u00a0\u00a0\u00a0\u00a0 -------------------\nTAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 22.02.2011 10:20:07<\/pre>\n<p>&nbsp;<\/p>\n<p>Histograms info:<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">select table_name, column_name, count(*) \n\u00a0 from dba_histograms\u00a0 \n\u00a0where owner='SCHEMA1'\n\u00a0\u00a0 and table_name='TAB1'\n\u00a0group by table_name, column_name;TABLE_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 COLUMN_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 COUNT(*)\n-----------------\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0--------------------\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ----------\nTAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ADRESS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\nTAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a02\nTAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NPA\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\nTAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\nTAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LOCALITE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2<\/pre>\n<p>&nbsp;<\/p>\n<p>Now, let&#8217;s remove the statistics:<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">exec DBMS_STATS.DELETE_TABLE_STATS(OWNNAME=&gt;'SCHMEMA1', TABNAME=&gt;'TAB1');\nProcedure PL\/SQL terminate with success.<\/pre>\n<p>&nbsp;<\/p>\n<p>Checking information:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; select table_name,num_rows,to_char(last_analyzed,'DD.MM.YYYY HH24:MI:SS') from dba_tables where owner ='SCHEMA1' and table_name='TAB1';TABLE_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NUM_ROWS\u00a0\u00a0 LAST_ANALYZED\n------------------------------ -------------\u00a0\u00a0\u00a0-------------------\nTAB1SQL&gt; select table_name, column_name, count(*)\n\u00a0 2\u00a0\u00a0\u00a0 from dba_histograms\n\u00a0 3\u00a0\u00a0 where owner='SCHEMA1'\n\u00a0 4\u00a0\u00a0\u00a0\u00a0 and table_name='TAB1'\n\u00a0 5\u00a0\u00a0 group by table_name, column_name;TABLE_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 COLUMN_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 COUNT(*)\n----------------\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --------------------\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ----------\nTAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2<\/pre>\n<p>&nbsp;<\/p>\n<p>Now, let&#8217;s have a look which statistics are available:<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">select count(*), stats_update_time\n\u00a0 from dba_tab_stats_history\u00a0 \n\u00a0where owner='SCHEMA1'\n\u00a0\u00a0 and table_name='TAB1'\n\u00a0group by stats_update_time;\u00a0 COUNT(*)\u00a0\u00a0\u00a0\u00a0 STATS_UPDATE_TIME\n-------------\u00a0\u00a0 \u00a0---------------------------------------------------------------------------\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 11\/02\/11 21:00:47,054000 +01:00\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 22\/02\/11 10:20:07,587000 +01:00\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 22\/02\/11 10:25:12,524000 +01:00\n\u00a0\u00a09\u00a0rows selected.<\/pre>\n<p>&nbsp;<\/p>\n<p>Let&#8217;s try to restore the second line:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; exec dbms_stats.restore_table_stats(ownname=&gt;'SCHEMA1',tabname=&gt;'TAB1',AS_OF_TIMESTAMP=&gt;'22\/02\/11 10:20:07,587000 +01:00');Procedure PL\/SQL terminate with success.<\/pre>\n<p>&nbsp;<\/p>\n<p>Now, I could check if the previous statistics were retrieved correctly:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; select table_name, column_name, count(*)\n\u00a0 2\u00a0\u00a0\u00a0 from dba_histograms\n\u00a0 3\u00a0\u00a0 where owner='SCHEMA1'\n\u00a0 4\u00a0\u00a0\u00a0\u00a0 and table_name='TAB1'\n\u00a0 5\u00a0\u00a0 group by table_name, column_name;TABLE_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0COLUMN_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 COUNT(*)\n----------------\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0--------------------\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ----------\nTAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ADRESS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\nTAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\nTAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NPA\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\nTAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\nTAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LOCALITE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a02\nSQL&gt; select table_name,num_rows,to_char(last_analyzed,'DD.MM.YYYY HH24:MI:SS') from dba_tables where owner ='SCHEMA1' and table_name='TAB1';TABLE_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NUM_ROWS\u00a0\u00a0 LAST_ANALYZED\n------------------------------ -------------\u00a0\u00a0\u00a0-------------------\nTAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 22.02.2011 10:20:07<\/pre>\n<p>&nbsp;<\/p>\n<p>The disadvantage with this method is knowing which timestamp should be restored. In the following case, the solution is allowed to put a tag. It is therefore easier to restore the desired statistics.<\/p>\n<h3>2. Statistics export<\/h3>\n<p>An alternative is to store the statistics in a table and associate it with\u00a0a tag.<br \/>\nHere are the steps:<\/p>\n<h4>1) Create the statistics table<\/h4>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">exec dbms_stats.create_stat_table('SCHEMA1','STATS');<\/pre>\n<h4>2) Export the statistics<\/h4>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">exec dbms_stats.export_table_stats('SCHEMA1','TAB1',NULL,'STATS','TAG1_TAB1',TRUE);This procedure could be also used to extract statistics from the production to feed that of the test.<\/pre>\n<h4>3) Check statistics information<\/h4>\n<p>Statistics info:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; select table_name, num_rows,to_char(last_analyzed,'DD.MM.YYYY HH24:MI:SS')from dba_tables where owner ='SCHEMA1' and table_name='TAB1';TABLE_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NUM_ROWS\u00a0\u00a0\u00a0\u00a0 LAST_ANALYZED\n------------------------------ -------------\u00a0\u00a0\u00a0\u00a0 -------------------\nTAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 22.02.2011 10:20:07<\/pre>\n<p>&nbsp;<\/p>\n<p>Histograms info:<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">select table_name, column_name, count(*) \n\u00a0 from dba_histograms\u00a0 \n\u00a0where owner='SCHEMA1'\n\u00a0\u00a0 and table_name='TAB1'\n\u00a0group by table_name, column_name;TABLE_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 COLUMN_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 COUNT(*)\n-----------------\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0--------------------\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ----------\nTAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ADRESS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\nTAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a02\nTAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NPA\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\nTAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\nTAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LOCALITE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2<\/pre>\n<h4>4) Delete statistics<\/h4>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">exec DBMS_STATS.DELETE_TABLE_STATS(OWNNAME=&gt;'SCHEMA1', TABNAME=&gt;'TAB1');SQL&gt; select table_name,num_rows,to_char(last_analyzed,'DD.MM.YYYY HH24:MI:SS') from dba_tables where owner ='SCHEMA1' and table_name='TAB1';TABLE_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NUM_ROWS\u00a0\u00a0 LAST_ANALYZED\n------------------------------ -------------\u00a0\u00a0\u00a0-------------------\nTAB1<\/pre>\n<h4>5) Import statistics<\/h4>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">exec dbms_stats.import_table_stats('SCHEMA1','TAB1',NULL,'STATS','TAG1_TAB1',TRUE);<\/pre>\n<p>The statid parameter is allowed to store several evolutions of statistics and thus the capacity to go through the time.<br \/>\nHere the name is &#8216;TAG1_TAB1&#8217;, thus I could have &#8216;TAG2_TAB1&#8217;, &#8216;TAG3_TAB1&#8217;,.. too.<\/p>\n<h4>6) Check if statistics are corrects<\/h4>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">SQL&gt; select table_name,num_rows,to_char(last_analyzed,'DD.MM.YYYY HH24:MI:SS') from dba_tables where owner ='SCHEMA1' and table_name='TAB1';TABLE_NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NUM_ROWS LAST_ANALYZED\n------------------------------ ---------- -------------------\nTAB1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5 22.02.2011 10:20:07<\/pre>\n<p>I will come back on this topic in another posting.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Sometimes, you need to modify the statistics of a table. There are various reasons: either for the performance analysis or because the execution plan changed. Oracle offers two methods: statistics restore or statistics export. Both are in the dbms_stats package. 1. Statistics restore This method uses the procedure dbms_stats.restore_%_stats, where % can be table, schema, [&hellip;]<\/p>\n","protected":false},"author":9,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[197],"tags":[33,17,67,255],"type_dbi":[],"class_list":["post-2201","post","type-post","status-publish","format-standard","hentry","category-application-integration-middleware","tag-oracle-10g-to-8i","tag-oracle-11g","tag-performance","tag-statistics"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>How to retrieve previous table statistics using Oracle dbms_stats - dbi Blog<\/title>\n<meta name=\"description\" content=\"Sometimes, you need to modify the statistics of a table. There are various reasons: either for the performance analysis or because the execution plan changed. Oracle offers two methods: statistics restore or statistics export. Both are in the dbms_stats package.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/how-to-retrieve-previous-table-statistics-using-oracle-dbmsstats\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to retrieve previous table statistics using Oracle dbms_stats\" \/>\n<meta property=\"og:description\" content=\"Sometimes, you need to modify the statistics of a table. There are various reasons: either for the performance analysis or because the execution plan changed. Oracle offers two methods: statistics restore or statistics export. Both are in the dbms_stats package.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/how-to-retrieve-previous-table-statistics-using-oracle-dbmsstats\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2011-05-16T10:00:00+00:00\" \/>\n<meta name=\"author\" content=\"Yann Neuhaus\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Yann Neuhaus\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-to-retrieve-previous-table-statistics-using-oracle-dbmsstats\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-to-retrieve-previous-table-statistics-using-oracle-dbmsstats\/\"},\"author\":{\"name\":\"Yann Neuhaus\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/5bfc203607127a4915b7950c4a108681\"},\"headline\":\"How to retrieve previous table statistics using Oracle dbms_stats\",\"datePublished\":\"2011-05-16T10:00:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-to-retrieve-previous-table-statistics-using-oracle-dbmsstats\/\"},\"wordCount\":260,\"commentCount\":0,\"keywords\":[\"Oracle 10g to 8i\",\"Oracle 11g\",\"Performance\",\"Statistics\"],\"articleSection\":[\"Application integration &amp; Middleware\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/how-to-retrieve-previous-table-statistics-using-oracle-dbmsstats\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-to-retrieve-previous-table-statistics-using-oracle-dbmsstats\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/how-to-retrieve-previous-table-statistics-using-oracle-dbmsstats\/\",\"name\":\"How to retrieve previous table statistics using Oracle dbms_stats - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2011-05-16T10:00:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/5bfc203607127a4915b7950c4a108681\"},\"description\":\"Sometimes, you need to modify the statistics of a table. There are various reasons: either for the performance analysis or because the execution plan changed. Oracle offers two methods: statistics restore or statistics export. Both are in the dbms_stats package.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-to-retrieve-previous-table-statistics-using-oracle-dbmsstats\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/how-to-retrieve-previous-table-statistics-using-oracle-dbmsstats\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-to-retrieve-previous-table-statistics-using-oracle-dbmsstats\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to retrieve previous table statistics using Oracle dbms_stats\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/5bfc203607127a4915b7950c4a108681\",\"name\":\"Yann Neuhaus\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/d2729b4bbf77b0fe6f6d2b9fb1b2686404c89a16ce2701e860bfd1406212f796?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/d2729b4bbf77b0fe6f6d2b9fb1b2686404c89a16ce2701e860bfd1406212f796?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/d2729b4bbf77b0fe6f6d2b9fb1b2686404c89a16ce2701e860bfd1406212f796?s=96&d=mm&r=g\",\"caption\":\"Yann Neuhaus\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/yann-neuhaus\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"How to retrieve previous table statistics using Oracle dbms_stats - dbi Blog","description":"Sometimes, you need to modify the statistics of a table. There are various reasons: either for the performance analysis or because the execution plan changed. Oracle offers two methods: statistics restore or statistics export. Both are in the dbms_stats package.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/how-to-retrieve-previous-table-statistics-using-oracle-dbmsstats\/","og_locale":"en_US","og_type":"article","og_title":"How to retrieve previous table statistics using Oracle dbms_stats","og_description":"Sometimes, you need to modify the statistics of a table. There are various reasons: either for the performance analysis or because the execution plan changed. Oracle offers two methods: statistics restore or statistics export. Both are in the dbms_stats package.","og_url":"https:\/\/www.dbi-services.com\/blog\/how-to-retrieve-previous-table-statistics-using-oracle-dbmsstats\/","og_site_name":"dbi Blog","article_published_time":"2011-05-16T10:00:00+00:00","author":"Yann Neuhaus","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Yann Neuhaus","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/how-to-retrieve-previous-table-statistics-using-oracle-dbmsstats\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/how-to-retrieve-previous-table-statistics-using-oracle-dbmsstats\/"},"author":{"name":"Yann Neuhaus","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/5bfc203607127a4915b7950c4a108681"},"headline":"How to retrieve previous table statistics using Oracle dbms_stats","datePublished":"2011-05-16T10:00:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/how-to-retrieve-previous-table-statistics-using-oracle-dbmsstats\/"},"wordCount":260,"commentCount":0,"keywords":["Oracle 10g to 8i","Oracle 11g","Performance","Statistics"],"articleSection":["Application integration &amp; Middleware"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/how-to-retrieve-previous-table-statistics-using-oracle-dbmsstats\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/how-to-retrieve-previous-table-statistics-using-oracle-dbmsstats\/","url":"https:\/\/www.dbi-services.com\/blog\/how-to-retrieve-previous-table-statistics-using-oracle-dbmsstats\/","name":"How to retrieve previous table statistics using Oracle dbms_stats - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2011-05-16T10:00:00+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/5bfc203607127a4915b7950c4a108681"},"description":"Sometimes, you need to modify the statistics of a table. There are various reasons: either for the performance analysis or because the execution plan changed. Oracle offers two methods: statistics restore or statistics export. Both are in the dbms_stats package.","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/how-to-retrieve-previous-table-statistics-using-oracle-dbmsstats\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/how-to-retrieve-previous-table-statistics-using-oracle-dbmsstats\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/how-to-retrieve-previous-table-statistics-using-oracle-dbmsstats\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How to retrieve previous table statistics using Oracle dbms_stats"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/5bfc203607127a4915b7950c4a108681","name":"Yann Neuhaus","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/d2729b4bbf77b0fe6f6d2b9fb1b2686404c89a16ce2701e860bfd1406212f796?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/d2729b4bbf77b0fe6f6d2b9fb1b2686404c89a16ce2701e860bfd1406212f796?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/d2729b4bbf77b0fe6f6d2b9fb1b2686404c89a16ce2701e860bfd1406212f796?s=96&d=mm&r=g","caption":"Yann Neuhaus"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/yann-neuhaus\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/2201","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/9"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=2201"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/2201\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=2201"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=2201"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=2201"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=2201"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}