{"id":7547,"date":"2020-02-13T09:54:18","date_gmt":"2020-02-13T09:54:18","guid":{"rendered":"https:\/\/rax-stg-wp-aws.com\/blogs\/restoring-statistics-in-oracle-database\/"},"modified":"2026-11-03T19:28:44","modified_gmt":"2026-11-03T19:28:44","slug":"restoring-statistics-in-oracle-database","status":"publish","type":"blogs","link":"https:\/\/www.rackspace.com\/en-gb\/blogs\/restoring-statistics-in-oracle-database\/","title":{"rendered":"Restoring statistics in Oracle Database"},"content":{"rendered":"<p><strong>Introduction<\/strong><br \/>\nThis blog post explores when and how you might restore statistics in Oracle\u00ae&nbsp;Database.<\/p>\n<p>As a database administrator (DBA), you probably often find that gathering fresh&nbsp;statistics leads to the optimizer executing suboptimal execution plans. In those&nbsp;cases, you might want to restore the statistics from a time when the performance&nbsp;was better.<\/p>\n<p>However, different versions of Oracle Database have minor changes in the way they handle statistics:<\/p>\n<ul>\n<li>Oracle 10g began automatically preserving the statistics for easy restoration.<\/li>\n<li>In 11.1 and later, Oracle introduced a better way, allowing you to defer&nbsp;statistics publishing.<\/li>\n<\/ul>\n<p><strong>Possible reasons for poor statistics performance<\/strong><\/p>\n<p>You might find it necessary to gather statistics to ensure that the optimizer&nbsp;chooses the optimal execution plans, but gathering statistics invalidates the&nbsp;parsed representation of the SQL statement. Also, reparsing the statement after&nbsp;gathering stats can make the optimizer choose an execution plan that is&nbsp;different and less optimized than the original plan.<\/p>\n<p>In Oracle 10g and later, you can restore statistics by using the <strong>dbms_stats&nbsp;<\/strong>package, which gives you the option to restore statistics and export statistics.<\/p>\n<p>By default, Oracle retains historical statistics for 31 days, but you can change that period.<\/p>\n<p>The following image shows the SQL command to show the current retention period:<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26431-body-Picture1_1.png\" alt=\"oracle statistics Part 1\" \/><\/p>\n<p>\nTo change the retention period, run the following command, where <em>*xx*<\/em> is the number of days that you want to use:&nbsp;<\/p>\n<p><em>&nbsp; &nbsp; &nbsp;SQL&gt; execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (xx)<\/em><\/p>\n<p>The following query helps you to understand which historical data stats you can restore:&nbsp;<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26431-body-Picture2_1.png\" alt=\"statistics oracle Pic 2\" \/><\/p>\n<p><strong>Note:<\/strong> This example shows the statistics from the previously mentioned date&nbsp;and later.&nbsp;<\/p>\n<p><strong>Restoring table statistics<\/strong><\/p>\n<p>This example demonstrates how to restore table statistics from a prior date.<\/p>\n<p>First, identify which statistics are available by running the following command:<\/p>\n<p>&nbsp; &nbsp; SQL&gt; select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history where table_name like &#8216;MY_TABLE&#8217; and owner=&#8217;MYSELF&#8217; order by 2;<\/p>\n<p>&nbsp; &nbsp; TABLE_NAME &nbsp; &nbsp; &nbsp;STATS_UPDATE_TIME<br \/>\n&nbsp; &nbsp; &#8212;&#8212;&#8212;&#8212;&#8212; &#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8211;<br \/>\n&nbsp; &nbsp; MY_TABLE &nbsp; &nbsp; &nbsp; &nbsp;20-DEC-19 05.32.26.887184 AM -05:00<br \/>\n&nbsp; &nbsp; MY_TABLE &nbsp; &nbsp; &nbsp; &nbsp;20-DEC-19 10.10.19.361091 PM -05:00<br \/>\n&nbsp; &nbsp; MY_TABLE &nbsp; &nbsp; &nbsp; &nbsp;21-DEC-19 05.32.14.475934 AM -05:00<br \/>\n&nbsp; &nbsp; MY_TABLE &nbsp; &nbsp; &nbsp; &nbsp;21-DEC-19 10.10.18.725917 PM -05:00<br \/>\n&nbsp; &nbsp; MY_TABLE &nbsp; &nbsp; &nbsp; &nbsp;22-DEC-19 10.10.17.841143 PM -05:00<br \/>\n&nbsp; &nbsp; MY_TABLE &nbsp; &nbsp; &nbsp; &nbsp;23-DEC-19 05.32.56.168779 AM -05:00<br \/>\n&nbsp; &nbsp; MY_TABLE &nbsp; &nbsp; &nbsp; &nbsp;23-DEC-19 10.10.23.633939 PM -05:00<br \/>\n&nbsp; &nbsp; MY_TABLE &nbsp; &nbsp; &nbsp; &nbsp;24-DEC-19 05.32.14.082730 AM -05:00<br \/>\n&nbsp; &nbsp; MY_TABLE &nbsp; &nbsp; &nbsp; &nbsp;24-DEC-19 10.10.21.712948 PM -05:00<br \/>\n&nbsp; &nbsp; MY_TABLE &nbsp; &nbsp; &nbsp; &nbsp;25-DEC-19 05.32.13.710159 AM -05:00<br \/>\n&nbsp; &nbsp; MY_TABLE &nbsp; &nbsp; &nbsp; &nbsp;25-DEC-19 10.10.17.836929 PM -05:00<br \/>\n&nbsp; &nbsp; MY_TABLE &nbsp; &nbsp; &nbsp; &nbsp;26-DEC-19 05.32.14.545533 AM -05:00<br \/>\n&nbsp; &nbsp; MY_TABLE &nbsp; &nbsp; &nbsp; &nbsp;26-DEC-19 10.10.12.808687 PM -05:00<br \/>\n&nbsp; &nbsp; MY_TABLE &nbsp; &nbsp; &nbsp; &nbsp;27-DEC-19 05.32.13.779967 AM -05:00<\/p>\n<p>You can see that <strong>MY_TABLE&nbsp;<\/strong>was analyzed several times in the past few days. To restore the table statistics of the table gathered on<br \/>\n<strong>21-DEC-19 10.10.18.725917 PM -05:00<\/strong>, run the following command:<\/p>\n<p><em>&nbsp; &nbsp; SQL&gt; execute dbms_stats.restore_table_stats(&#8216;MYSELF&#8217;,&#8217;MY_TABLE&#8217;,&#8217;21-DEC-19 10.10.18.725917 PM -05:00&#8242;);<\/em><\/p>\n<p><em>&nbsp; &nbsp; PL\/SQL procedure completed.<\/em><\/p>\n<p><strong>&nbsp;Restoring schema statistics<\/strong><\/p>\n<p>This example demonstrates how to restore schema statistics from a prior date.<\/p>\n<p><em>&nbsp; &nbsp; SQL&gt; exec dbms_stats.restore_schema_stats(ownname=&gt;&#8217;MYSELF&#8217;, AS_OF_TIMESTAMP=&gt;&#8217;21-DEC-19 10.10.18.725917 PM -05:00&#8242;);<\/em><\/p>\n<p>To find the available schema statistic options for the <strong>AS_OF_TIMESTAMP<\/strong>, run the following command and choose the appropriate date for restoration:<\/p>\n<p><em>&nbsp;&nbsp; &nbsp; select count(*), stats_update_time from dba_tab_stats_history where owner=&#8217;MYSELF&#8217;group by stats_update_time;<\/em><\/p>\n<p><strong>Restore other statistics<\/strong><\/p>\n<p>You&#8217;ve seen how to restore table and schema stats. &nbsp;The following list shows all the entities for which you can restore previous statistics:<\/p>\n<ul>\n<li>TABLE\\_STATS<\/li>\n<li>SCHEMA\\_STATS<\/li>\n<li>DATABASE\\_STATS<\/li>\n<li>DICTIONARY\\_STATS<\/li>\n<li>FIXED\\_OBJECTS\\_STATS<\/li>\n<li>SYSTEM\\_STATS<\/li>\n<\/ul>\n<p><strong>Export statistics that you want to save<\/strong><\/p>\n<p>You can also export statistics that you want to save or export&nbsp;current statistics before you make changes by performing the following&nbsp;steps:<\/p>\n<p>1) Create the statistics table by running a command similar to the following example:<\/p>\n<p>&nbsp;&nbsp; &nbsp; Exec dbms_stats.create_stat_table(ownname =&gt; &#8216;MYSELF&#8217;, stattab =&gt; &#8216;MYSELF_STATS_&lt;DATE&gt;&#8217;,tblspace =&gt; &#8216;&lt;Tablespace Name&gt;&#8217;);<\/p>\n<p>&nbsp; &nbsp; <strong>ownname<\/strong>&nbsp;owner name<br \/>\n&nbsp;&nbsp; &nbsp;<strong>stattab:<\/strong>tablename under user MYSELF to create<br \/>\n&nbsp; &nbsp;<strong> tblspace<\/strong>: tablespace under which to create this table<\/p>\n<p>2) Export the statistics in the preceding table by running a command similar to&nbsp;the following example:<\/p>\n<p><em>&nbsp; exec dbms_stats.export_table_stats(&#8216;SCHEMA1&#8242;,&#8217;TAB1&#8242;,NULL,&#8217;STATS&#8217;,&#8217;TAG1_TAB1&#8242;,TRUE);<\/em><\/p>\n<p>\n&nbsp; &nbsp;For example:<\/p>\n<p><em>&nbsp;&nbsp; &nbsp; Exec dbms_stats.export_database_stats(statown =&gt; &#8216;MYSELF&#8217;, stattab =&gt; &#8216;MYSELF_STATS&#8217;);<\/em><\/p>\n<p><strong>Conclusion<\/strong><\/p>\n<p>The information and queries in this post help you to restore all types of&nbsp;database statistics (Table, Database, Schema, Fixed\\_Object, System, and&nbsp;Dictionary) to a reasonable past date, ensuring smooth database performance.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Restoring statistics in Oracle Database<\/p>\n","protected":false},"author":2,"featured_media":7549,"menu_order":0,"comment_status":"open","ping_status":"open","template":"","format":"standard","meta":{"content-type":"","nid":"26431","content_type":"blog","created_by":"10130","authored_on":"2024-01-02T09:54:18+00:00","field_article_industries":"","tag_category":"Technical","field_blog_author_text":"by Sundeep Dhingra, Oracle Database Administrator, Rackspace Technology","seo_description":"Restore statistics in Oracle Database to optimize performance. Address issues arising from fresh statistics.\r\n","field_featured_image":"","insights_image":"\/wp-content\/uploads\/2026\/blog_technical_images\/26431-insights_image-oracle-technical-blog-1-insights.jpg","field_partners":"","rxt_platforms":"Oracle","product":"","publisheddate":"2020-02-13T09:54:18","rxt_solutions":"Databases","opengraph_tag":"","x_tag":"","teaser_flag":"Technical Blog","seo_titile":"Restoring Statistics in Oracle Database | Rackspace Technology","field_topics":"","field_slug":"restoring-statistics-in-oracle-database","footnotes":""},"categories":[178],"tags":[204],"class_list":["post-7547","blogs","type-blogs","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","tag-technical"],"_links":{"self":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7547","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs"}],"about":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/types\/blogs"}],"author":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/comments?post=7547"}],"version-history":[{"count":3,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7547\/revisions"}],"predecessor-version":[{"id":9529,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7547\/revisions\/9529"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/media\/7549"}],"wp:attachment":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/media?parent=7547"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/categories?post=7547"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/tags?post=7547"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}