database Oracle 数据库统计信息应该多久运行一次？

How often should Oracle database statistics be run?

提问by

In your experience, how often should Oracle database statistics be run? Our team of developers recently discovered that statistics hadn't been run our production box in over 2 1/2 months. That sounds like a long time to me, but I'm not a DBA.

根据您的经验，Oracle 数据库统计信息应该多久运行一次？我们的开发团队最近发现统计数据已经超过 2 1/2 个月没有运行我们的生产箱了。这对我来说听起来很长，但我不是 DBA。

采纳答案by user11318

At my last job we ran statistics once a week. If I remember correctly, we scheduled them on a Thursday night, and on Friday the DBAs were very careful to monitor the longest running queries for anything unexpected. (Friday was picked because it was often just after a code release, and tended to be a fairly low traffic day.) When they saw a bad query they would find a better query plan and save that one so it wouldn't change again unexpectedly. (Oracle has tools to do this for you automatically, you tell it the query to optimize and it does.)

在我的上一份工作中，我们每周进行一次统计。如果我没记错的话，我们将它们安排在星期四晚上，而在星期五，DBA 非常小心地监视运行时间最长的查询是否有任何意外。（选择星期五是因为它通常刚好在代码发布之后，而且往往是一个流量相当低的一天。）当他们看到一个糟糕的查询时，他们会找到一个更好的查询计划并保存它，这样它就不会再次意外更改. （Oracle 有工具可以自动为您执行此操作，您告诉它要优化的查询，它就会这样做。）

Many organizations avoid running statistics out of fear of bad query plans popping up unexpectedly. But this usually means that their query plans get worse and worse over time. And when they do run statistics then they encounter a number of problems. The resulting scramble to fix those issues confirms their fears about the dangers of running statistics. But if they ran statistics regularly, used the monitoring tools as they are supposed to, and fixed issues as they came up then they would have fewer headaches, and they wouldn't encounter them all at once.

许多组织避免运行统计数据，因为害怕意外弹出错误的查询计划。但这通常意味着他们的查询计划随着时间的推移变得越来越糟糕。当他们运行统计数据时，他们会遇到许多问题。由此产生的解决这些问题的争夺证实了他们对运行统计数据危险的担忧。但是，如果他们定期运行统计数据，按预期使用监控工具，并在出现问题时解决问题，那么他们的头痛就会减少，而且他们不会同时遇到这些问题。

回答by grokster

Since Oracle 11g statistics are gathered automatically by default.

由于默认情况下自动收集 Oracle 11g 统计信息。

Two Scheduler windows are predefined upon installation of Oracle Database:

安装 Oracle 数据库时预定义了两个调度程序窗口：

• WEEKNIGHT_WINDOW starts at 10 p.m. and ends at 6 a.m. every Monday through Friday.
• WEEKEND_WINDOW covers whole days Saturday and Sunday.

• WEEKNIGHT_WINDOW 每周一至周五从晚上 10 点开始，到早上 6 点结束。
• WEEKEND_WINDOW 涵盖周六和周日全天。

When statistics were last gathered?

上次收集统计数据的时间？

SELECT owner, table_name, last_analyzed FROM all_tables ORDER BY last_analyzed DESC NULLS LAST; --Tables.
SELECT owner, index_name, last_analyzed FROM all_indexes ORDER BY last_analyzed DESC NULLS LAST; -- Indexes.

Status of automated statistics gathering?

自动统计收集的状态？

SELECT * FROM dba_autotask_client WHERE client_name = 'auto optimizer stats collection';

Windows Groups?

Windows 组？

SELECT window_group_name, window_name FROM dba_scheduler_wingroup_members;

Window Schedules?

窗口时间表？

SELECT window_name, start_time, duration FROM dba_autotask_schedule;

Manually gather Database Statistics in this Schema:

在此模式中手动收集数据库统计信息：

EXEC dbms_stats.gather_schema_stats(ownname=>NULL, cascade=>TRUE); -- cascade=>TRUE means include Table Indexes too.

Manually gather Database Statistics in all Schemas!

手动收集所有模式中的数据库统计信息！

-- Probably need to CONNECT / AS SYSDBA
EXEC dbms_stats.gather_database_stats;

回答by Jonathan Rupp

Whenever the data changes "significantly".

每当数据“显着”变化时。

If a table goes from 1 row to 200 rows, that's a significant change. When a table goes from 100,000 rows to 150,000 rows, that's not a terribly significant change. When a table goes from 1000 rows all with identical values in commonly-queried column X to 1000 rows with nearly unique values in column X, that's a significant change.

如果一个表从 1 行变为 200 行，这是一个重大变化。当一个表从 100,000 行变为 150,000 行时，这并不是一个非常重要的变化。当一个表从 1000 行在 X 列中具有相同值的行变为在 X 列中具有几乎唯一值的 1000 行时，这是一个重大变化。

Statistics store information about item counts and relative frequencies -- things that will let it "guess" at how many rows will match a given criteria. When it guesses wrong, the optimizer can pick a verysuboptimal query plan.

统计信息存储有关项目计数和相对频率的信息——这些信息将让它“猜测”有多少行将匹配给定的条件。当它猜测错误时，优化器可以选择一个非常次优的查询计划。

回答by David Medinets

What Oracle version are you using? Check this page which refers to Oracle 10:

您使用的是哪个 Oracle 版本？检查参考 Oracle 10 的这个页面：

http://www.acs.ilstu.edu/docs/Oracle/server.101/b10752/stats.htm

http://www.acs.ilstu.edu/docs/Oracle/server.101/b10752/stats.htm

It says:

它说：

The recommended approach to gathering statistics is to allow Oracle to automatically gather the statistics. Oracle gathers statistics on all database objects automatically and maintains those statistics in a regularly-scheduled maintenance job.

收集统计信息的推荐方法是允许 Oracle 自动收集统计信息。Oracle 自动收集所有数据库对象的统计信息，并在定期安排的维护作业中维护这些统计信息。

回答by Joe Skora

When I was managing a large multi-user planning system backed by Oracle, our DBA had a weekly job that gathered statistics. Also, when we rolled out a significant change that could affect or be affected by statistics, we would force the job to run out of cycle to get things caught up.

当我管理一个由 Oracle 支持的大型多用户计划系统时，我们的 DBA 每周有一项工作是收集统计数据。此外，当我们推出可能影响统计数据或受统计数据影响的重大更改时，我们会强制工作超出周期以赶上事情。

回答by MichaelN

With 10g and higher version of oracle, up to date statistics on tables and indexes are needed by the optimizer to make "good" execution plan decision. How often you collect statistics is a tricky call. It depends on your application, schema, data rate and business practice. Some third party apps which are written to be backward compatible with older version of oracle do not perform well with the new optimizer. Those application require that tables have no stats so that the db resorts back to rule base execution plan. But on the average oracle recommends that stats be collected on tables with stale statistics. You can set tables to be monitor and check their state and have them analyze if/when stale. Often that is enough, sometime it is not. It really depend on your database. For my database we have a set of OLTP tables that need nightly stats collection to maintain performance. Other tables are analyze once a week. On our large dw database, we analyze as needed as the tables are too large for regular analysis without affecting overall db load and performance. So the correct answer is, it depends on the application, data change and business needs.

使用 10g 和更高版本的 oracle，优化器需要表和索引的最新统计信息才能做出“好的”执行计划决策。你多久收集一次统计数据是一个棘手的问题。这取决于您的应用程序、架构、数据速率和业务实践。某些编写为向后兼容旧版 oracle 的第三方应用程序在新的优化器中表现不佳。这些应用程序要求表没有统计信息，以便数据库重新使用规则库执行计划。但平均而言，oracle 建议在具有陈旧统计信息的表上收集统计信息。您可以将表设置为监控并检查它们的状态，并让它们分析是否/何时陈旧。通常这就足够了，有时还不够。这真的取决于你的数据库。对于我的数据库，我们有一组 OLTP 表，需要每晚收集统计数据以保持性能。其他表每周分析一次。在我们的大型 dw 数据库上，我们根据需要进行分析，因为表太大而无法进行常规分析，而不会影响整体数据库负载和性能。所以正确的答案是，这取决于应用程序、数据变化和业务需求。

回答by Justin Cave

Make sure to balance the risk that fresh statistics cause undesirable changes to query plans against the risk that stale statistics can themselves cause query plans to change.

确保平衡新统计信息导致查询计划发生不良更改的风险与陈旧统计信息本身可能导致查询计划更改的风险。

Imagine you have a bug database with a table ISSUE and a column CREATE_DATE where the values in the column increase more or less monotonically. Now, assume that there is a histogram on this column that tells Oracle that the values for this column are uniformly distributed between January 1, 2008 and September 17, 2008. This makes it possible for the optimizer to reasonably estimate the number of rows that would be returned if you were looking for all issues created last week (i.e. September 7 - 13). If the application continues to be used and the statistics are never updated, though, this histogram will be less and less accurate. So the optimizer will expect queries for "issues created last week" to be less and less accurate over time and may eventually cause Oracle to change the query plan negatively.

假设您有一个带有表 ISSUE 和列 CREATE_DATE 的错误数据库，其中列中的值或多或少地单调增加。现在，假设该列上有一个直方图，它告诉 Oracle 该列的值在 2008 年 1 月 1 日和 2008 年 9 月 17 日之间均匀分布。这使得优化器可以合理地估计将如果您正在查找上周（即 9 月 7 日至 13 日）创建的所有问题，则返回。但是，如果继续使用应用程序并且统计信息从未更新，则此直方图将越来越不准确。因此优化器会期望“上周创建的问题”的查询随着时间的推移越来越不准确，并可能最终导致 Oracle 对查询计划进行负面更改。

回答by David Aldridge

In the case of a data warehouse-type system you can consider collecting no statistics at all, and relying on dynamic sampling (setting optimizer_dynamic_sampling to level 2 or above).

对于数据仓库类型的系统，您可以考虑完全不收集任何统计信息，并依靠动态采样（将 optimizer_dynamic_sampling 设置为 2 级或更高级别）。

回答by Chion

Generally it's not recommended to gather statistics so frequent on the whole database unless you have a strong justification for that, such as a bulk insert or big data change happen frequently on the database. gathering statistics on the database in this frequency MAY change the queries execution plan to a new poor execution plans, the thing may cost you much time trying to tune every query affected by the new poor plans, this is why you should test the impact of gathering new statistics on a test database, or in case you don't have the time or the man power for that, at least you should keep a fallback plan by backing up the original statics before you gather new ones, so in case you gather a new statistics and then the queries didn't perform as expected, you can easily restore back the original statistics.

通常不建议在整个数据库上如此频繁地收集统计信息，除非您有充分的理由，例如数据库上经常发生批量插入或大数据更改。以这种频率收集数据库的统计信息可能会将查询执行计划更改为新的糟糕的执行计划，这可能会花费您很多时间来尝试调整受新的糟糕计划影响的每个查询，这就是为什么您应该测试收集的影响测试数据库上的新统计数据，或者如果您没有时间或人力，至少您应该在收集新数据之前通过备份原始静态数据来制定后备计划，以防万一新的统计信息然后查询没有按预期执行，您可以轻松恢复原始统计信息。

There is a very useful script can help you backup original statistics and gather new ones and provide you with SQL command you can use to restore back the original statics in case the thing didn't go as expected after gathering new statistics. You can find the script in this link: http://dba-tips.blogspot.com/2014/09/script-to-ease-gathering-statistics-on.html

有一个非常有用的脚本可以帮助您备份原始统计信息并收集新统计信息，并为您提供 SQL 命令，您可以使用它来恢复原始统计信息，以防在收集新统计信息后事情没有按预期进行。您可以在此链接中找到脚本：http: //dba-tips.blogspot.com/2014/09/script-to-ease-gathering-statistics-on.html