Restoring Table Statistics (Doc ID 452011.1)

本文主要是介绍Restoring Table Statistics (Doc ID 452011.1)，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

In this Document

Purpose

Scope

Details

How long does Oracle retain the statistics for ?

How do I know how many days the statistics are available for ?

How do I find the statistics history for a given table ?

How do I restore the statistics ?

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Oracle Database - Standard Edition - Version 10.1.0.2 and later
Oracle Database - Personal Edition - Version 10.1.0.2 and later
Information in this document applies to any platform.

PURPOSE

This document outlines the facility by which statistics may be restored following their removal. This facility is available from Orale 10g onwards.

SCOPE

Assist with restoring statistics in the event that they are overwritten.

DETAILS

Beginning with Oracle10G, when statistics are gathered for a table, the old statistics are retained so should
there be any problem with performance of queries dependent on those statistics, the old ones can be restored.

How long does Oracle retain the statistics for ?

The default period for which statistics are retained is 31 days but this can be altered with:-

execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (xx) 

- where xx is the number of days to retain them

NOTE: There is an overhead of storage in the SYSAUX tablespace with statistics so care should be taken not to cause the tablespace to fill with the statistics

How do I know how many days the statistics are available for ?

select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual; 

- will return the number of days stats are currently retained for.

select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual; 

- will return the date statistics have been purged up to (so only dates newer then this can possibly be restored to). Any request to restore stats from this date or older will fail with: "ORA-20006: Unable to restore statistics , statistics history not available"

How do I find the statistics history for a given table ?

select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history 

Will show the times statistics were regathered for a given table.

How do I restore the statistics ?

Having decided what date you know the statistics were good for, you can use:-

 execute DBMS_STATS.RESTORE_TABLE_STATS ('owner','table',date)
 execute DBMS_STATS.RESTORE_DATABASE_STATS(date)
 execute DBMS_STATS.RESTORE_DICTIONARY_STATS(date)
 execute DBMS_STATS.RESTORE_FIXED_OBJECTS_STATS(date)
 execute DBMS_STATS.RESTORE_SCHEMA_STATS('owner',date)
 execute DBMS_STATS.RESTORE_SYSTEM_STATS(date) 

ie

 execute dbms_stats.restore_table_stats ('SCOTT','EMP','25-JUL-07 12.01.20.766591 PM +02:00'); 

这篇关于Restoring Table Statistics (Doc ID 452011.1)的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

原文地址:
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.chinasem.cn/article/632534。如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈，一经查实，立即删除！我们的邮箱：23002807@qq.com