介绍
本贴涵盖了Oracle Database 12c Release 2中是如何为分区表改进增量统计信息的内容。如果你还不是很熟悉增量统计信息语境下的synopses，那请在阅读前，查看（本系列的）第1和第2部分。
从Oracle Database 12c Release 2开始，采用了新的synopsis格式，其比早期版本中使用的格式有更显著的压缩。为了方便描述，我将称Oracle Database 12c Release 2的格式为新格式，而之前的格式为旧格式。
如果你之前了解过增量统计信息维护，但因为SYSAUX中额外的空间使用而决定不使用的话，则这个增强是特别适合的。Oracle Database 12c Release 2解决了这个问题，并且，在多种场景下，降低了管理synopses所需的系统资源量。
一种synopsis的新类型
synopsis是存储在一对数据字典表(SYS. WRI $ _OPTSTAT_SYNOPSIS$ 和SYS.WRI $ _OPTSTAT_SYNOPSIS_HEAD $ )中的元数据。该元数据由Oracle自动管理，所以，通常并不需要知道其底层实现。尽管如此，我还是会介绍一些细节，因为这将帮助我们理解其为何变化得如此显著。
Oracle Database 12c Release 2中的Synopses目前使用（默认情况下）称为HyperLogLog (HLL)的算法创建。这是计算表列值中大致唯一值数量的最先进算法。尽管它是近似值，但它仍然是非常精确的，典型的误差率小于2%。在Oracle Database 12c Release 2之前，synopses由存储在 WRI $ _OPTSTAT_SYNOPSIS $ 表中的行构成。如果有大量的分区和表列，并且列上包含有大量的唯一值，则表中的行数可能是非常大的。新格式的synopses不在该表中存储行，取而代之的是部分额外的哈希数据被存储到WRI $ _OPTSTAT_SYNOPSIS_HEAD $ 表中（使用SPARE2列）
新的synopses能小多少？好吧，正如你可能已经猜到的，顾问们的说法：“视情况而定”。如前所述，synopses使用的空间是分区数、列数和列上唯一值数量的函数。我可以给你举一个例子：在我们的测试系统有一个8TB的测试表，有84个分区。旧格式的synopses的大小是大约160MB，而新格式的synopses只有6MB。我在Github上创建了一个示例（见下文），用于产生大量的synopsis数据。在这个示例中，新格式的synopses几乎不占用任何空间，而老格式的synopses则占用了大约160MB(在SYSAUX中）。
如何使用
为了使用新格式的synopses,你需要做什么吗？什么都不需要！如果你选择使用增量统计信息，会默认使用。而且，相较于早期版本，需要你在Oracle Database 12c Release 2中做的，并没有什么不同。
Synopses控制
你可以使用DBMS_STATS中的，名为APPROXIMATE_NDV_ALGORITHM偏好参数来控制创建的类型。
默认值是REPEAT 或 HYPERLOGLOG：如果一个表正在使用旧格式的synopses,那么它将继续这样使用旧格式，而如果一个表正在使用新格式的synopses，那么也将继续使用新格式。
没有什么理由使用默认值之外的值，除非你正在升级数据库到Oracle Database 12c Release 2。如果是这种情况，你可能要考虑其选项。下面会谈及。
升级
如果你正在升级一个使用增量统计的数据库，那么你会希望迁移到使用新格式的Synopses。你要怎么做呢？最初需要注意的是那些同一张表中，即有旧格式，又有新格式synopses的表分区。好消息是你可以控制何时，以及如何从一种类型的synopses过渡到另一种。
有一个名为INCREMENTAL_STALENESS的DBMS_STATS的偏好参数，它控制是否允许在从旧格式过渡到新格式时，表中可以拥有不同类型synopses的分区。让我们来看一下，在升级到Oracle Database 12c Release 2后，不同的场景下是如何处理的。从“非常保守”（即，保持旧行为）到“积极”（即，立即利用新特性）是有一系列选择的。下表描述了从最保守的场景案例到最激进的场景案例。
| 使用场景 | 动作 |
|---|---|
| 最初，你希望对所有表继续使用旧格式的synopses。我们建议使用新格式的，如果你愿意的话，也可以晚些再使用它们。HLL之前使用的算法称为自适应采样 | EXEC DBMS_STATS.SET_TABLE_PREFS(‘table_owner’,‘table-name’, ‘APPROXIMATE_NDV_ALGORITHM’, ‘ADAPTIVE SAMPLING’) |
| 你希望使用旧格式的表继续使用旧格式，新创建的增量管理的表使用新格式的synopses。没有synopses的增量管理的表，收集统计信息时，会使用新格式的synopses,而使用老格式的增量管理的表，则会继续使用它。 | 无须操作。这是缺省的行为。 APPROXIMATE_NDV_ALGORYTHM 的缺省值，是REPEAT 或 HYPERLOGLOG. |
| 你有一些非常大的分区表。它们正使用旧格式的synopses,而你希望逐步地用新的替换掉旧的 | 旧格式的synopses不会立即被新格式的替代，而新分区将会采用新格式。虽然混合模式会产生不太准确的统计信息，但其优点是不需要在前台重新收集所有表的统计信息。自动统计信息收集任务，将逐步的收集老格式分区上的统计信息，并生成新格式的synopses.最终，所有的分区均会使用新格式，统计信息也会更准确。 EXEC DBMS_STATS.SET_TABLE_PREFS (table_owner, table_name,‘APPROXIMATE_NDV_ALGORITHM’,‘HYPERLOGLOG’) 注意：INCREMENTAL_STALENESS 偏好参数必须是 ALLOW_MIXED_FORMAT, 但是，并不需要显式设置它，因为这就是默认值（除非你修改了它）。 |
| 你有时间重新收集所有统计信息。增量管理的表使用的是旧格式，而你希望立即用新格式替换掉旧格式。 | 如果你有一个窗口期用来完成分区表统计信息的重新收集，那么这是建议的方法。 EXEC DBMS_STATS.SET_TABLE_PREFS(table_owner,table_name, ‘APPROXIMATE_NDV_ALGORITHM’, ‘HYPERLOGLOG’) 你还需要指定不希望新旧格式混合存在于同一个表上: EXEC DBMS_STATS.SET_TABLE_PREFS (table_owner, table_name, ‘INCREMENTAL_STALENESS’, ‘NULL’) 你要仔细一些，偏好参数的值要设为’NULL’ (有引号) ，而不是NULL (无引号)。 NULL (无引号)会设置偏好参数为其默认值,在本例中是ALLOW_MIXED_FORMAT。一旦这些偏好参数设置后，你就需要重新收集表的统计信息了。 |
记住，你也可以在库级，全局级和SCHEMA级设置DBMS_STATS的偏好参数（比如APPROXIMATE_NDV_ALGORITHM) ，就像在表级上设置那样（如上例所示）。
总结
Oracle Database 12c Release 2中的synopsis格式比之前的格式更紧实。如果你的数据库非常大，希望在维护统计信息良好精确度的同时，还能节省大量SYSAUX中的空间。希望维护synopses的系统开销也会下降（比如，交换分区时）。有关此脚本和一些示例脚本的更多信息，请查看GitHub。
如果您对本文或GitHub中的脚本有任何评论，请在下面继续。
原文链接: https://blogs.oracle.com/optimizer/post/efficient-statistics-maintenance-for-partitioned-tables-using-incremental-statistics-part-3
Efficient Statistics Maintenance for Partitioned Tables Using Incremental Statistics – Part 3
March 23, 2017 | 6 minute read
Nigel Bayliss
Product Manager
Introduction
This post covers how Oracle has improved incremental statistics for partitioned tables in Oracle Database 12c Release 2. If you’re not already familiar with synopses in the context of incremental statistics then take a look at Part 1 and Part 2 before you read on.
Beginning with Oracle Database 12c Release 2 there’s a new synopsis format that’s significantly more compact than the format used in earlier releases. For brevity, I’ll refer to the Oracle Database 12c Release 2 format as new and the previous format as old.
This enhancement is particularity relevant if you looked at incremental statistics maintenance in the past but decided not to use it because of the additional space usage in SYSAUX. Oracle Database 12c Release 2 resolves this issue and, in many cases, reduces the amount of system resource required to manage synopses.
A New Type of Synopsis
A synopsis is metadata stored in a couple of tables in the data dictionary (SYS.WRI and SYS.WRI). The metadata is managed automatically by the Oracle Database, so there’s generally no reason to be aware of the underlying implementation. Nevertheless, I’ll cover some of the details here because it will help you to see why the change is so significant.
Synopses in Oracle Database 12c Release 2 are now created (by default) using an algorithm called HyperLogLog (HLL). This is a state-of-the-art algorithm that calculates the approximate number of distinct values for table column values. Even though it is an approximation, it is nevertheless very accurate with a typical error rate of less than 2%. Prior to Oracle Database 12c Release 2, synopses consisted of rows stored in the WRI table. The number of rows in this table can be very large if there are a large number of partitions and table columns, and if the columns contain a large number of distinct values. New-style synopses do not store rows in this table. Instead, some additional (and compact) hash data is stored in the WRI table (in the SPARE2 column).
How much smaller are the new synopses? Well, as you’ve probably guessed, it’s the consultants’ answer: “It Depends”. As outlined above, the space used by synopses is a function of the number of partitions, columns and distinct values in columns. I can give you an example from one of our test systems containing an 8TB test table with 84-partitions. The total size of the old-style synopses was around 160MB and the new-style synopses totaled only 6MB. The example I created in GitHub (see below) was contrived to generate a particularly large amount of synopsis data. In the example I’ve given, new-style synopses take up virtually no space at all and the old-style synopses take up about 160MB (in SYSAUX).
How to Use Them
What do you need to do to use new-style synopses? Nothing! They are used by default if you choose to use incremental statistics and you don’t need to do anything different in Oracle Database 12c Release 2 compared to earlier releases.
Controlling Synopses
You can control the type of that will be created using a DBMS_STATS preference called APPROXIMATE_NDV_ALGORITHM.
The default is REPEAT OR HYPERLOGLOG: if a table is using old-style synopses then it will continue to do so, and tables using new-style synopses will continue to use those!
There’s no reason to use anything other than the default unless you are upgrading a database to Oracle Database 12c Release 2. If this is the case then you might want to consider the options. That’s covered next.
Upgrading
If you are upgrading a database that’s using incremental statistics, then you will want to migrate to using the new-style synopses. How do you go about doing that? It’s worth noting from the outset that it’s possible to have partitions with old-style and new-style synopses in the same table. Also, the good news is that you can control when and how to transition from one type of synopses to the other.
There is a DBMS_STATS preference called INCREMENTAL_STALENESS. It the controls whether or not you want to allow partitions within an individual table to have different types of synopses during the transition period from old-style to new-style. Let’s look at the different scenarios and how to proceed after you have upgraded to Oracle Database 12c Release 2. There is a spectrum of choice from “very conservative” (i.e., maintaining old behaviors) to “aggressive” (i.e., taking advantage of new features immediately). The chart below describes the different scenarios from the most conservative cases to most aggressive cases.
| Use-case | Action |
|---|---|
| Initially,you want to continue to use old-format synopses for all tables.We recommend that you use the new-style synopses, but can choose to use them later on if you prefer. The algorithm used prior to HLL is called adaptive sampling. | EXEC DBMS_STATS.SET_TABLE_PREFS(‘table_owner’,‘table-name’, ‘APPROXIMATE_NDV_ALGORITHM’, ‘ADAPTIVE SAMPLING’) |
| You want tables using old-style synopses to continue to use them.Newly created incrementally-managed tables will use new-style synopses. Incrementally-managed tables without synopses will use new-style when statistics are gathered.Incrementally-managed tables with old-style synopses will continue to use them. | No action. This is the default behavior.The APPROXIMATE_NDV_ALGORYTHM is, by default, REPEAT OR HYPERLOGLOG. |
| You have some very large partitioned tables. They are using old-style synopses and you want to gradually replace the old with the new. | Old-format synopses are not immediately replaced and new partitions will have synopses in the new format. Mixed formats will yield less accurate statistics but the advantage is that there is no need to re-gather all table statistics in the foreground. The statistics auto job will gradually re-gather statistics on partitions with old format synopses and generate new format synopses. Eventually, new format synopses will be used for all partitions and statistics will be accurate. EXEC DBMS_STATS.SET_TABLE_PREFS (table_owner, table_name,‘APPROXIMATE_NDV_ALGORITHM’,‘HYPERLOGLOG’)Note that INCREMENTAL_STALENESS preference must have the value ALLOW_MIXED_FORMAT, but it does not need to be set explicity (unless you’ve changed it) because it is the default setting。 |
| You have time to re-gather all statistics. Incrementally managed tables are using old-style synopses and you want to replace the old-style with the new immediately. | If you have a window of time to completely re-gather statistics for partitioned tables, then this is the recommended approach. EXEC DBMS_STATS.SET_TABLE_PREFS(table_owner,table_name, ‘APPROXIMATE_NDV_ALGORITHM’, ‘HYPERLOGLOG’) You also need to specify that you don’t want a mix of old synopses and new synopses in the same table: EXEC DBMS_STATS.SET_TABLE_PREFS (table_owner, table_name, ‘INCREMENTAL_STALENESS’, ‘NULL’)You need to take some care here. The preference value should be set to ‘NULL’ (in quotes) and not NULL (without quotes). NULL (without quotes) sets a preference to its default value, which in this case is ALLOW_MIXED_FORMAT.Once these preferences are set you will need to re-gather the table’s statistics. |
Remember that you can also set DBMS_STATS preferences (such as APPROXIMATE_NDV_ALGORITHM) at the database, global and schema-level as well as at the table level (as per the examples above).
Summary
The synopsis format in Oracle Database 12c Release 2 is much more compact than the previous format. If your database is very large, expect to save a lot of space in SYSAUX while maintaining very good accuracy for your statistics. You can expect the system overhead required to manage synopses to drop too (for example, when you exchange partitions). For more on this and some example scripts, take a look at GitHub.
If you have comments on this post or the scripts in GitHub, please go ahead below.
