Oracle 表统计信息收集
对于 Oracle 版本 9 及更高版本，应何时以及如何执行表统计信息收集？ 您将如何收集大型数据库的统计信息，其中统计信息的收集会与“工作时间”发生冲突。
When and how should table stats gathering be performed for Oracle, version 9 and up? How would you go about gathering stats for a large database, where stats gathering would collide with "business hours".
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我不同意在进行大量删除或插入后您应该始终重建统计信息。 一如既往，这取决于。 在数据仓库情况下，当重新构建物化视图时，您将进行大量删除和插入，但数据的基本结构不会改变。
仅当表的内容发生重大变化时，您才需要重新计算表的统计信息。 这并不一定意味着在大量删除或插入之后，而是当删除、插入或更新实质上改变了可能的执行计划的内容时。
如果您要截断表并重建（这将重置您的统计信息），则通常最好在重建表后截断和恢复统计信息之前存储统计信息，而不是进行昂贵的统计计算。
为了保存当前的统计视图，您可以使用：
并在之后恢复它们，您可以使用：（
对于
模式和数据库有相应的过程。）I don't agree that you should always rebuild your statistics after there have been lots of deletes or inserts. As ever, it depends. In a data warehouse situation, when re-building your materialized views you will be doing lots of deletes and inserts but the base structure of the data will not change.
You only need to re-calculate statistics on a table if there has been a significant change in its content. This does not necessarily mean after lots of deletes or inserts, but rather when deletes, inserts, or updates materially change the content with respect to possible execution plans.
If you are truncating tables and rebuilding (which will reset your statistics), instead of an expensive statistics calculation, you're often better off storing the statistics before truncating and restoring them once you've rebuilt the table.
For saving the current views of statistics you use:
and to restore them afterwards you use:
(There are corresponding procedures for
schemaanddatabase.)每当数据内容发生较大更改（例如大量删除或插入）时，就应该收集统计信息。 如果表结构发生了变化，您也应该收集统计信息。 建议使用“估计”选项。
如果可能的话，请在非工作时间以自动化流程执行此操作，或者如果您必须在工作时间内执行此操作，请选择对您希望收集统计数据的表的访问权限最少的时间。
Gathering stats should be done whenever there has been large changes to the data content, for example a large number of deletes or inserts. If the table structure has changed you should gather stats also. It is advisable to use the 'ESTIMATE' option.
Do this as an automated process out of business hours if possible, or if you have to do it during business hours then choose a time when there is minimum access to the tables you wish to gather stats for.
确保在使用估计值 (sample_percent) 时至少收集到 10%。 低于这个值可能会产生非常值得怀疑的结果。
Make sure when using the estimate (sample_percent) that you gather at least 10 percent. Below that can yield very questionable results.
收集时考虑备份当前统计数据 - 这样您就可以比较它们（如果您感兴趣），并且在新统计数据引起问题时可能恢复它们。 请记住，统计信息用于确定执行计划 - 如果您希望执行计划更改，您可能只想收集它们。
Consider backing up current stats when gathering -- that way you can compare them (if you're interested) and possibly restore them if your new stats cause problems. Keep in mind that stats are used to determine execution plans -- you may only want to gather them if you want execution plans to change.