Oracle 收集表统计时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18689337/
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
Oracle Gather Table Statistics Time
提问by Guddu
Gather statistics on some Oracle tables take a long time. These tables have a record count ranging from 2 Million Records to 9 Million Records. The tables have around 5-6 indexes on each one of them.
收集某些 Oracle 表的统计信息需要很长时间。这些表的记录数从 200 万条记录到 900 万条记录不等。每个表都有大约 5-6 个索引。
The Oracle version is
Oracle 版本是
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
"CORE 10.2.0.1.0 Production"
TNS for IBM/AIX RISC System/6000: Version 10.2.0.1.0 - Productio
NLSRTL Version 10.2.0.1.0 - Production
The gather stats syntax is
收集统计信息的语法是
dbms_stats.gather_table_stats('OWNER', 'TABLE_NAME', estimate_percent => 100, method_opt => 'for all columns size auto', cascade => true);
We cannot change the parameters of the above gather stats command as the application vendor insists as these parameters be used.
我们不能更改上述 gather stats 命令的参数，因为应用程序供应商坚持使用这些参数。
Please let me know if there is anything we could do to reduce the time taken by the Gather Statistics JOB. I noticed that the JOB when runs, causes the app performance to reduce a little and this is not acceptable.
请让我知道我们是否可以采取任何措施来减少收集统计工作所花费的时间。我注意到运行时 JOB 会导致应用程序性能降低一点，这是不可接受的。
I also noticed that some of the tables occupy a lot of space on the Disk but the real data (by doing a estimate of record count multiplied by average row length) is a lot lesser. Looks like the tables need to be Compacted/Shrunk/High Water Mark Reset etc.
我还注意到一些表在磁盘上占用了大量空间，但实际数据（通过估计记录数乘以平均行长度）要少得多。看起来表格需要压缩/收缩/高水位线重置等。
Some tables for example occupy 9 GB on Disk but Real Data is shown to be 1.2 GB . . . Almost 70% Space Wasted in Fragmentation.
例如，某些表在磁盘上占用 9 GB，但实际数据显示为 1.2 GB。. . 碎片化浪费了近 70% 的空间。
Will a ALTER TABLE Shrink reduce the overall time taken to collect statistics on the table? Is it recommended?
ALTER TABLE Shrink 是否会减少收集表统计信息所需的总时间？推荐吗？
回答by Jon Heller
Yes, shrinking space will help. If you can afford to take the application down for a while, and the tables aren't going to just bounce right back to their previous size, then shrinking space is always a good idea.
是的，缩小空间会有所帮助。如果您可以暂时关闭应用程序，并且表不会立即恢复到以前的大小，那么缩小空间始终是一个好主意。
Other than that, if parameters can't change there's not much you can do to improve things. Setting the DEGREEparameter can signficantly improve performance in some cases. I know you said you can't change any parameters, but I don't see how they could complain about that one. Although it may make the job run faster it would probably impact the system performance even more (but for a shorter period of time).
除此之外，如果参数不能改变，你就没有什么可以改进的了。DEGREE在某些情况下，设置该参数可以显着提高性能。我知道你说过你不能改变任何参数，但我不明白他们怎么会抱怨那个。尽管它可能会使作业运行得更快，但它可能会更大程度地影响系统性能（但会影响更短的时间）。
The best solution might be to upgrade to 11g, where any sane application would use estimate_percent => dbms_stats.auto_sample_size. Statistics collection in 11g is muchbetter than 10g. With features like improved auto sample algorithms, incremental statistics, setting statistics preferences, and concurrent statistics, gathering statistics is often much faster and more accurate.
最好的解决方案可能是升级到 11g，任何正常的应用程序都会使用estimate_percent => dbms_stats.auto_sample_size. 统计数据收集在11g是多在10G更好。借助改进的自动采样算法、增量统计、设置统计首选项和并发统计等功能，收集统计数据通常更快、更准确。
