为 Oracle 收集表统计信息
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36813/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Table Stats gathering for Oracle
提问by Matthew Watson
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".
何时以及如何为 Oracle 版本 9 及更高版本执行表统计信息收集？您将如何为大型数据库收集统计信息，其中统计信息收集会与“营业时间”发生冲突。
采纳答案by stevechol
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.
如果可能，请在工作时间以外的自动流程中执行此操作，或者如果您必须在工作时间执行此操作，请选择对要为其收集统计信息的表的访问权限最少的时间。
回答by Nick Pierpoint
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 significantchange in its content. This does notnecessarily 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:
要保存您使用的当前统计数据视图：
dbms_stats.export_table_stats
and to restore them afterwards you use:
并在使用后恢复它们：
dbms_stats.import_table_stats
(There are corresponding procedures for schemaand database.)
（存在用于相应的程序schema和database。）
回答by stevechol
Make sure when using the estimate (sample_percent) that you gather at least 10 percent. Below that can yield very questionable results.
确保在使用估计值 (sample_percent) 时至少收集 10%。低于此值可能会产生非常可疑的结果。
回答by stevechol
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.
在收集时考虑备份当前的统计数据——这样你就可以比较它们（如果你感兴趣），如果你的新统计数据引起问题，可能会恢复它们。请记住，统计信息用于确定执行计划——如果您希望执行计划更改，您可能只想收集它们。
