Incremental Statistics – A Real World Scenario

Incremental statistics reduce the time to gather global statistics for a partitioned table. Really? In some situations it may happen that incremental statistics slow down statistics calculation dramatically. An example of a real project in Oracle 12.1 and how it can be improved with Oracle 12.2.

Gathering global statistics of a partitioned table is time-consuming. Normally, all partitions of a (large) partitioned table must be scanned to calculate the statistics on table level. To reduce this time, incremental statistics were introduced with Oracle 11g. If this feature is enabled, additional statistics information is gathered for each partition and stored as “synopsis”. To (re-)calculate the global statistics, only the synopses must be read instead of the whole table. A good description of this concept can be found in the blog post Efficient Statistics Maintenance for Partitioned Tables Using Incremental Statistics – Part 1 of Nigel Bayliss. 

Incremental statistics are especially useful for environments where only a few partitions are changed at a time, but many partitions exist per table. This is usually the case in data warehouses where new data is loaded into a current partition. When the statistics on partition-level are gathered for the current partition, the global statistics are updated using the synopsis information of all the historical partitions and the current partition.

In a large data warehouse at one of my customers, we decided to introduce incremental statistics. Most of the tables are INTERVAL partitioned by date, containing daily partitions. We enabled interval partitioning for all of the 891 partitioned tables and started the DBMS_STATS.GATHER_SCHEMA_STATS procedure. The job was running for two weeks(!). While it was quite efficient at the beginning, it became slower and slower with every table. Finally, when all partition-level and global statistics were up-to-date, I thought the time-consuming part is over now. The initial effort was quite high, but now we are able to benefit from the synopses that were calculated during this long time.

This was a wrong assumption: Whenever we started a job to gather statistics for an individual partition (even if the partition contained only a small number of rows), it took around 40 minutes to calculate the synopsis information for this partition. What was going on here? For this, let’s have to look under the hood of DBMS_STATS.

A Look Under the Hood of DBMS_STATS

Synopsis tables

The synopsis information used for incremental statistics is stored in the SYSAUX tablespace in the two tables SYS.WRI$_OPTSTAT_SYNOPSIS_HEAD$ and SYS.WRI$_OPTSTAT_SYNOPSIS$. Since Oracle 12.1, the detail table WRI$_OPTSTAT_SYNOPSIS$ is partitioned using composite LIST-HASH partitioning.

On the customer database (Oracle Database 12.1.0.2 Enterprise Edition, around 24 TB of data), the head table WRI$_OPTSTAT_SYNOPSIS_HEAD$ contains about 13 million rows, the detail table WRI$_OPTSTAT_SYNOPSIS$ about 10 billion rows. The two tables together use about 320 GB of disk space in the SYSAUX tablespace. The reason for this high amount of synopsis data is the number of partitions in this database. All 891 partitioned tables together contained 325851 partitions. This was the state at end of November, about one month ago. Because new partitions are created every day for most of the tables, there will be a few thousand more now.

On the list of long-running SQL statements, the following query was always one of the most time-consuming statements. It seems that the query is executed very often (for each table or even each partition?) as part of the incremental statistics calculation. On a “normal” database, this is not critical, but on the 13 million rows of the synopsis table, each execution was running for several minutes. As far as I could see (without knowing the internal code of the DBMS_STATS package), this seems to be the “bad guy” that wasted most of the time.

 

SELECT DISTINCT BO#, GROUP#

  FROM SYS.WRI$_OPTSTAT_SYNOPSIS_HEAD$ H

 WHERE GROUP#   <> 0

   AND GROUP# NOT IN

       (SELECT T.OBJ# * 2 FROM SYS.TABPART$ T

       UNION ALL

       SELECT T.OBJ# * 2 FROM SYS.TABCOMPART$ T

       )

 

It would be possible to rewrite this SQL statement to run faster, but of course this was not an option. I’m not able nor allowed to change the internal queries of DBMS_STATS. But at least I found out that this statement is executed only on Oracle 12.1. I did some tests on my laptop with incremental statistics on Oracle version 11.2, 12.1 and 12.2. Only in the trace file of Oracle 12.1 I found this statement.

Searching for a Solution

While looking around for a solution or a workaround, I found several notes on My Oracle Support (MOS), for example the document Gather_Database_Stats_Job_Proc Taking More Time in 12.1.0.2 Than 11.2.0.4 (Doc ID 2041541.1). But the more documents I browsed, the more I got the impression that there are many bugs on 12.1.0.2 with incremental statistics.

In the blog post Incremental Statistics Collection in Oracle 12.1.0.2 – A True Story of Mike Dietrich, I read that there are really several issues with incremental statistics in Oracle 12.1.0.2, but there are multiple patches and bug fixes available. Unfortunately, the referenced MOS document Things to Consider When Using Incremental Statistics (Doc ID 2107602.1) is rather confusing, and some of the patches mentioned in the document exclude each other.

Finally, we decided to solve the performance problem with a simple, but effective solution: We turned off incremental statistics, as I also mentioned on Twitter.

Twitter

Instead of gathering incremental statistics after each load job, a statistics job will run now every Sunday. It gathers the partition-level statistics of all stale partitions and global statistics of tables, if required. Interestingly, this job runs much faster than the previous job with incremental statistics – although all partitions must be scanned now to gather the global statistics.

I’m not very happy with this (temporary) workaround. I still hope to find a better and more elegant way to gather global statistics on partitioned tables. Probably, Oracle 12.2 will solve our problem?

A Better Solution in Oracle 12.2?

A few days after this story, I had the chance to meet Nigel Bayliss, product manager of the Oracle optimizer, at the UKOUG Tech17 conference. He explained me that there is a new way to calculate the synopsis values in Oracle 12c Release 2. Instead of storing a lot of detail data in table WRI$_OPTSTAT_SYNOPSIS$ to retrieve the number of distinct values for each column, a HyperLogLog algorithm is used to calculate an approximate number of distinct values. If only this new algorithm is used, the (large) synopsis detail table will not be used anymore. It is still in place for backward compatibility. With the new DBMS_STATS preference APPROXIMATE_NDV_ALGORITHM, the method to calculate synopses can be defined. The default REPEAT OR HYPERLOGLOG uses the old method for existing tables, but the HyperLogLog algorithm for all new tables. If the preference is switched to HYPERLOGLOG, much less synopsis data is stored in the SYSAUX tablespace. An overview of this new way to calculate synopses for incremental statistics he described in his blog post Efficient Statistics Maintenance for Partitioned Tables Using Incremental Statistics – Part 3. Additionally, a set of demo scripts is available on GitHub.

I played around with these demo scripts and did some tests with the DBMS_STATS preference APPROXIMATE_NDV_ALGORITHM on my laptop. At least, the new method to calculate incremental statistics looks promising, and the “bad statement” described above seems to be gone. But currently I can’t say (yet) whether it will help to solve the performance issue of my customer’s database. Hopefully, my customer will decide to migrate to Oracle 12c Release 2 soon. Then I’m looking forward to enable incremental statistics again.

9 thoughts on “Incremental Statistics – A Real World Scenario

  1. Puzzled by the comment about ‘not storing detail’ in 12.2; my impression from a couple of quick tests was that hyperloglog simply meant the synopsis was much smaller (hundreds of rows instead of thousands) and that was the critical performance benefit.

    Liked by 1 person

    • Hi Jonathan

      No details are stored in table WRI$_OPTSTAT_SYNOPSIS$, but of course the HyperLogLog values must be stored somewhere. As described in Nigel’s blog post (part 3), this information is written to the SPARE2 column of table WRI$_OPTSTAT_SYNOPSIS_HEAD$: “New-style synopses do not store rows in this table. Instead, some additional (and compact) hash data is stored in the WRI$_OPTSTAT_SYNOPSIS_HEAD$ table (in the SPARE2 column).”

      You can see this for example when you run the script 01_mixed.sql from the mentioned GitHub demos and query WRI$_OPTSTAT_SYNOPSIS$ after that. For synopsis type “ADAPTIVE SAMPLING”, some rows are written to WRI$_OPTSTAT_SYNOPSIS$. For synopsis type “HYPERLOGLOG”, no rows are written, but instead, WRI$_OPTSTAT_SYNOPSIS_HEAD$.SPARE2 contains some (binary) data.

      Cheers, Dani.

      Liked by 1 person

      • Dani,

        Thanks for that – I should have read through Nigel’s blogs before commenting.

        I’ve found the notes I made a couple of years ago, which (as another scale point) describe how the synopsis for a single column of a single partition drops from 16,384 seprate rows in the detail table to a blob of 3,806 bytes in the header table.

        I think I may have unpacked the blob to identify 760 data points – hence my erroneous comment about hundreds vs. thousands of rows – but that number may have come from some tests I was doing on the (slightly) decreased accuracy of the approximate_ndv() mechanism.

        Regards
        Jonathan Lewis

        Liked by 2 people

  2. Hi Dani,

    did you gather with or without histograms ?
    In a testcase I made some weeks ago whenever I gather with histograms and update the partition key all partitions will re-gathered completely., independent how many rows I update.

    Regards
    Rainer Hartwig

    Liked by 1 person

  3. Hello Dani
    We have the behavior, that every night incremental stats will be set to true on partitioned tables.
    Do you have an idea, whether this is set from an oracle job or not?
    The Oracle Version is 12.1.
    Thanks, Reto

    Like

  4. Hi Reto

    The automated statistics gathering job dbms_stats.gather_database_stats_job_proc of Oracle does not change the behavior of incremental statistics. Incremental statistics are only gathered if the DBMS_STATS preference ‘INCREMENTAL’ is set to TRUE.
    In your case, it seems to be an application specific job that changes the preferences every night.

    Cheers, Dani.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s