oracle 收集对象统计信息 收集选项

在表8-2中列出的收集选项参数指定了收集统计信息的过程如何进行，收集哪些类型的列统计信息，以及是否使从属SQL游标失效。

Ø  estimate_percent指定收集统计信息时是否使用采样。有效值为0.000001到100之间的十进制数字。当值为100时，其含义与NULL一样，意味着不进行采样。常量dbms_stats.auto_sample_size,也就是默认值(这个默认值可以修改),会让存储过程来决定采样大小。从11.1版本开始，推荐使用这个值。实际上，在大多数情况下，使用这个默认值不仅使收集的统计信息比使用类似10%的采样率进行收集要更加精确，而且也更加快速。

Ø  这是因为在11.1版本中引入了一种全新的算法，而且这种算法只能在指定参数值为dbms_stats.auto_sample_size时才可以使用。同时也要指出，因为这个新的算法需要对收集统计信息的表执行全表扫描，这在磁盘I/O子系统相对缓慢的系统上可能会花费很长时间。还要注意，某些特性(高频率直方图、混合直方图，还有增量统计信息)要求指定dbms_stats.auto_sample_size。

Ø  有一点很重要，将一个十进制数字作为参数传递进来时，由参数estimate_percent指定的值只不过是用于收集统计信息的最小百分比。事实上，正如下例所示，如果dbms_stats包认为由estimate_percent指定的值过小，那么程序包可能会自动增大这个值。假如不使用dbms_stats.auto_sample_size进行收集，可以使用较小的百分比来加速对象统计信息的收集；一般来说小于10个百分点就比较合适。对于特大的表，0.5个百分点、0.1个百分点，或者更小的值也不会有问题。

Ø  实际上的最佳值取决于数据分布情况。如果不确定该选什么，干脆就尝试不同的估算百分比然后比较收集的统计信息。这样，你可能会在性能和精确度之间找到一个最佳折衷点。注意，使用小的估算百分比可能不会产生稳定的统计信息。因为如果收集统计信息是在数据库或模式级别上执行，则过小的值会被自动增大，估算的百分比应该按最大的那张表来选择。顺便提一下，在外部表上采样是不受支持的：

BEGIN

 
dbms_stats.gather_schema_stats(ownname => user, estimate percent
=> 0.5);

END;

/

select table_name,

      
sample_size,

      
num_rows,

      
round(sample_size / num_rows * 100, 1) AS "%"

 
FROM user_tables

 WHERE num_rows > 0

 ORDER BY table_name;

Ø  block_sample指定是将行级采样还是块级采样用于统计信息的收集过程。尽管行级采样更加精确，但是块级采样更加迅速。因此，只有确定数据是随机分布时才应该使用块级采样。这个参数接受的值为TRUE和FALSE。默认值是FALSE。自11.1版本开始，这个参数只有在estimate_percent参数的值没有设置为dbms_stats.auto_sample_size时才会出现。

Ø  
method opt 指定是否收集列统计信息和直方图以及如何收集它们。下面是三种典型的用例。

A.为所有列收集列统计信息和直方图。所有直方图都按照相同的size_clause  参数值创建。如果指定的值为1,则不会创建任何直方图。语法如图8-7所示。举个例子，通过使用值for all columns size
254, 会为每个列创建一个拥有最高254个桶的直方图。

实际上，通过选项indexed和hidden,可以限制仅为索引列和隐藏列收集统计信息。一般来说，对象统计信息应该对所有列都可用。基于这个原因，应该避免使用这两个选项(因此在图8-7中它们被标记为灰色)。如果对于某些列来说不需要对象统计信息，则应该转而使用图8-8中描述的语法。使用hidden选项确实有一个合理的理由，那就是为某个作为扩展而刚刚加入到表中的虚拟列收集统计信息。

B. 在所有列上收集列统计信息并在一部分列上收集直方图。所有直方图都按照相同的 size_clause参数值创建。语法为图8-7和图8-8的一个组合：前者指定列统计信息的收集，后者指定直方图的收集。举个例子，指定"for all columns size 1 for columns size 254 coll"会使得系统在每个列上收集列统计信息并只在col1列上收集一个最多具有254个桶的直方图。

图8-8只为一部分列收集列统计信息和直方图，但为size_clause参数使用不同的值(见表8-6)。对于没有明确指明size_clause参数的列，使用默认的size_clause参数(即本图中左边的那个)。如果没有指定列，则不会收集任何列统计信息。column_clause参数可以是一个列名、一个扩展名或者一个扩展信息。如果指定了一个不存在的扩展信息，就会自动创建新的扩展信息。这个语法只能在调用gather_table_stats存储过程时才有效

表8-6
size_clause参数接受的值

C. 只为一部分列收集列统计信息和直方图，并且为size_clause参数使用不同的值。语法如图8-8所示。举例来说，指定for
columns size 1 id, col1 size 100, col2 size 5,col3 会使得系统在四个列上收集列统计信息，但是只会在列col1和col2上分别收集最多有100个桶和5个桶的直方图

这个参数的默认值是for
all columns size auto(默认值可以修改)。为简单起见， 使用size skewonly或者size auto。 如果执行得太慢或者选择的桶数不合理(或者所需要的直方图根本没有创建),那么就手工指定列的列表。如果指定了NULL,则会使用for all columns size 1。

列使用历史

dbms_stats包依赖于列使用历史来决定哪些列的直方图是有帮助的。为收集历史，当产生一个新的执行计划时，查询优化器会跟踪哪些列被WHERE子句引用了，并会存储它在SGA中找到的信息。然后，每隔一定时间，数据库引擎会将这些信息存储在数据字典表col_usage$中。通过执行类似下面这样的基于内部数据字典表的查询(该查询可以在col_usage.sql脚本中找到),就可以知道哪些列被WHERE子句引用了以及使用的是哪种类型的谓词。

timestamp列表明了最近使用的时间。其他列为硬解析次数(实际上，提供相同信息并接连不断执行的硬解析不包括在内)的计数。从未被WHERE子句引用过的列不会出现在col_usage$表中，所以，在输出中除了name之外，其他列值都为空。

COLUMN name FORMAT A4

COLUMN timestamp FORMAT A9

COLUMN equality FORMAT 9999

COLUMN equijoin FORMAT 9999

COLUMN noneequijoin FORMAT 9999

COLUMN range FORMAT 9999

COLUMN "LIKE" FORMAT 9999

COLUMN "NULL" FORMAT 9999

SELECT c.name, cu.timestamp,

      
cu.equality_preds AS equality, cu.equijoin_preds AS equijoin,

      
cu.nonequijoin_preds AS noneequijoin, cu.range_preds AS range,

      
cu.like_preds AS "LIKE", cu.null_preds AS "NULL"

FROM sys.col$ c, sys.col_usage$ cu,
sys.obj$ o, dba_users u

WHERE c.obj# = cu.obj# (+)

AND c.intcol# = cu.intcol# (+)

AND c.obj# = o.obj#

AND o.owner# = u.user_id

AND o.name = 'T'

AND u.username = user

ORDER BY c.col#;

自11.2.0.2版本开始，dbms_stats包的report_col_usage函数使得对col_usage$信息的选择变得更加容易了。注意，这是在8.2.4节中讨论过的相同函数。但是要知道，如果没有使用seed_col_usage函数，report_col_usage函数返回的报告不会包含关于潜在列组的信息。下面的查询展示了一个例子，并截取了一段输出：

SELECT dbms_stats.report_col_usage(ownname
=> user, tabname => 't') FROM dual;

此外，从11.2.0.2版本开始，dbms_stats包还提供了一种重置col_usage$表内容的方法。可以通过使用reset_col_usage存储过程来达到此目的。

Ø  degree指定为一个单独对象收集统计信息所使用的并行度。要使用在表/索引级别定义的并行度，请将这个值指定为NULL。要让存储过程自行决定并行度，指定这个值为常量dbms_stats.default_degree。其默认值为NULL(这个默认值可以修改)。注意处理多个对象时是串行执行的，除非使用了并行统计信息收集。这意味着并行化只对加速大型对象统计信息的收集起作用。要在同时处理多个对象时使用并行化，则有必要进行手工并行化(也就是说，同时开启多个任务)。并行收集对象统计信息只在企业版中可用。

并行统计信息收集

默认情况下，
dbms_stats包只会并行化在表或者分区级别(根据degree参数)的收集过程。也就是说，在任意给定的时间点，只会处理一个单独的表或分区。如果数据库服务器拥有大量空闲资源，而且需要处理的表或分区很多，这种情况下同时处理它们或许比较合理。基于这个目的，自 11.2.0.2版本开始，Oracle Database提供了一种称为并行统计信息收集 (concurrent statistics gathering)的新的收集模式。

并行统计信息收集是在gather_*_stats存储过程中实现的。要控制它，可以使用concurrent首选项。根据你所运行的版本，可以将它设置为下列值。

Ø  11.2:设置为FALSE会禁用这个特性(这是默认值),反之，设置为TRUE会启用这个特性。

Ø  12.1:设置为OFF会禁用这个特性(这是默认值),设置为MANUAL则只为手动统计信息收集启用这个特性，设置为AUTOMATIC只为自动统计信息收集启用这个特性，而设置为ALL会为所有类型的统计信息收集启用这个特性。

要想利用并行统计信息收集，必须满足以下要求。

Ø  初始化参数job_queue_processes的值至少应设置为4。这是因为同时处理多个表或者分区时，dbms_stats包会向Scheduler提交一定数量的任务。

Ø  Resource Manager应该是启用状态。因为dbms_stats包并不会控制同时运行多少个并发任务，所以，如果没有Resource Manager,系统的负载可能会超出控制范围。实际上，并行统计信息收集依赖于Scheduler和Resource Manager来生成最佳负载。

Ø  提交收集任务的用户必须拥有dba角色或者拥有以下权限：CREATE JOB、MANAGE SCHEDULER 以及MANAGE ANY QUEUE。

Ø  no_invalidate指定是否使依赖于所处理对象的游标失效，并进而指定是否禁止这些游标在未来继续使用。这个参数接受的值为TRUE、FALSE以及dbms_stats.auto_invalidate。将这个参数设置为TRUE时，依赖于更改的对象统计信息的游标不会失效，因此在未来的执行中仍然可以继续使用这些游标。

Ø  另一方面，如果将它设置为FALSE,所有相关的游标会立即失效。如果使用值dbms_stats.auto_invalidate(也就是一个等于NULL的常量),那么相关的游标会在一段时间后失效。最后一种选项有利于避免重新解析的集中出现。默认值是dbms_stats.auto_invalidate(这个默认值可以修改)。

Ø  使用了dbms_stats.auto_invalidate时，dbms_stats包会将所有依赖于变更的统计信息的游标标记为延迟失效状态。程序包会在游标级别设置一个时间戳，指明这个游标何时不应该再使用了。注意这个时间戳对于每个游标都是不同的，它基于一个从游标被标记的那一刻开始最长五个小时的随机值来设置。

Ø  真实的失效动作是由服务器进程来执行的，该进程尝试重用标记为延迟失效的游标。因此，如果一个标记为延迟失效的游标从来没有被重新解析过，那么它永远不会失效。唯一的例外是关联到并行SQL语句的游标。这样的游标会通过dbms_stats包立即失效。