为什么他们使用DBMS_STATS.GATHER_TABLE_STATS？ [英] why do they use DBMS_STATS.GATHER_TABLE_STATS ?

问题描述

我发现文档解释说，oracle使用这些来进行性能调整等，但不太明白它实际上做了什么。

I found documents explaining that oracle uses these for performance tuning etc but didn't quite understand that what does it actually do. Can some one explain it to me in simple word with very basic example or something?

推荐答案

大多数企业数据库，包括Oracle，使用一个基于成本的优化器来确定给定SQL语句的适当查询计划。这意味着优化器使用有关数据的信息来确定如何执行查询，而不是依赖规则（这是较旧的基于规则的优化器所做的）。

Most enterprise databases, Oracle included, use a cost-based optimizer to determine the appropriate query plan for a given SQL statement. This means that the optimizer uses information about the data to determine how to execute a query rather than relying on rules (this is what the older rule-based optimizer did).

例如，假设一个简单的错误跟踪应用程序的表。

For example, imagine a table for a simple bug-tracking application

CREATE TABLE issues (
  issue_id number primary key,
  issue_text clob,
  issue_status varchar2(10)
);

CREATE INDEX idx_issue_status
    ON issues( issue_status );

如果我是一个大公司，我可能在这个表中有100万行。其中，100个具有ACTIVE的 issue_status ，10,000个具有QUEUED的 issue_status ，并且989,900个状态为COMPLETE 。如果我要对表运行查询以查找活动的问题

If I'm a large company, I might have 1 million rows in this table. Of those, 100 have an issue_status of ACTIVE, 10,000 have an issue_status of QUEUED, and 989,900 have a status of COMPLETE. If I want to run a query against the table to find my active issues

SELECT *
  FROM issues
 WHERE issue_status = 'ACTIVE'

优化程序有一个选择。它可以使用 issue_status 上的索引，然后对索引中匹配的每一行执行表中的单行查找，或者可以对 issues 表。哪个计划更有效取决于表中的数据。如果Oracle希望查询返回表中的一小部分数据，则使用索引会更有效。如果Oracle希望查询返回表中的大部分数据，则表扫描将更有效。

the optimizer has a choice. It can either use the index on issue_status and then do a single-row lookup in the table for each row in the index that matches or it can do a table scan on the issues table. Which plan is more efficient will depend on the data that is in the table. If Oracle expects the query to return a small fraction of the data in the table, using the index would be more efficient. If Oracle expects the query to return a substantial fraction of the data in the table, a table scan would be more efficient.

DBMS_STATS.GATHER_TABLE_STATS 是什么收集统计数据，让Oracle做出这个决定。它告诉Oracle表中大约有100万行，对于 issue_status 列有3个不同的值，并且数据不均匀分布。所以Oracle知道使用索引来查找所有活动问题。但它也知道，当你转身，并尝试寻找所有已解决的问题

DBMS_STATS.GATHER_TABLE_STATS is what gathers the statistics that allow Oracle to make this determination. It tells Oracle that there are roughly 1 million rows in the table, that there are 3 distinct values for the issue_status column, and that the data is unevenly distributed. So Oracle knows to use an index for the query to find all the active issues. But it also knows that when you turn around and try to look for all the closed issues

SELECT *
  FROM issues
 WHERE issue_status = 'CLOSED'

。

收集统计信息允许查询计划随着数据量和数据分布的更改而随时间变化。当您首次安装问题跟踪器时，您将只有很少COMPLETED个问题，以及更多ACTIVE和QUEUED问题。随着时间的推移，COMPLETED问题的数量上升得更快。当您在表中获得更多行并且处于各种状态的行的相对分数发生变化时，查询计划将发生变化，以便在理想情况下始终获得最高效的计划。

Gathering statistics allows the query plans to change over time as the data volumes and data distributions change. When you first install the issue tracker, you'll have very few COMPLETED issues and more ACTIVE and QUEUED issues. Over time, the number of COMPLETED issues rises much more quickly. As you get more rows in the table and the relative fraction of those rows that are in the various statuses change, the query plans will change so that, in the ideal world, you always get the most efficient plan possible.

这篇关于为什么他们使用DBMS_STATS.GATHER_TABLE_STATS？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！