When Oracle 11gR1 was released, an improvement was introduced that allows to drastically reduce the time it takes to calculate the statistics of partitioned tables: incremental statistics.
With each new version of Oracle Server this functionality has been improved, but due to compatibility issues it does not apply by default to the tables we already had configured prior to each upgrade, so it is likely that you currently have tables that make use of its full potential and also some that do not.
Is this your case? Read on and find out!
The requirements
First, in order to make use of incremental statistics, some parameters must be set for the partitioned table:
| Property | Value |
|---|---|
| incremental | true |
| publish | true |
| estimate_percent | dbms_stats.auto_sample_size |
| granularity | auto |
For this we must make calls to DBMS_STATS.set_table_prefs, to modify each of the properties.
The Magic
Instead of having to process all partitions for the statistics calculation, statistics are now calculated for each partition, and these are aggregated in order to have the global statistics of the table.
To achieve this, a new element is used: SYNOPSIS, which is a summary of the statistical data for each partition, such as the number of distinct values in the columns (NDV).
This data is stored in 2 tables: wri$_optstat_synopsis_head$ y wri$_optstat_synopsis$.
The problem
While everything works fine as it is, the initial algorithm—called ADAPTIVE SAMPLING—has the side effect that millions of rows will be recorded in the table wri$_optstat_synopsis$, and may become the first consumer of space in the tablespace. SYSAUX.
Depending on the number of partitions, the number of rows, the number of columns, etc., this table can occupy tens or even hundreds of gigabytes!
SQL> @?/rdbms/admin/awrinfo.sql
********************************************************
(1b) SYSAUX occupants space usage (v$sysaux_occupants)
********************************************************
|
| Occupant Name Schema Name Space Usage
| -------------------- -------------------- ----------------
| SM/OPTSTAT SYS 25,317.4 MB <<<-------
| SM/AWR SYS 420.1 MB
| SM/ADVISOR SYS 351.2 MBThe solution
It was with Oracle 12.2 that Oracle decided to introduce a new algorithm: HYPERLOGLOG, which offers a shorter time for the processing of the SYNOPSES, but what is even better: it is no longer necessary to use the table wri$_optstat_synopsis$, and with it all the millions of rows and tens of gigabytes to store them.
This new algorithm will only be used with new implementations of incremental statistics for partitioned tables. All tables that you already had configured before upgrading to Oracle 12.2 will continue to use the previous algorithm, with all its disadvantages.
Most likely you will want to start using the new algorithm right now, and it turns out that it is not complicated at all, but it will require a recalculation of the statistics, and that already implies time and resource usage, so a good planning is advisable.
Based on my experience, I recommend you follow these steps and you will achieve this with the least possible impact:
- If you have no problem rebuilding the statistics all at once for the entire table and its partitions:
Set properties
BEGIN
DBMS_STATS.set_table_prefs(
ownname => 'SH',
tabname => 'SALES',
pname => 'APPROXIMATE_NDV_ALGORITHM',
pvalue => 'HYPERLOGLOG'
);
DBMS_STATS.set_table_prefs(
ownname => 'SH',
tabname => 'SALES',
pname => 'INCREMENTAL_STALENESS',
pvalue => 'NULL'
);
END;
/
Be careful to use ‘NULL’ and not NULL.
1
2
Delete synopses
BEGIN
DBMS_STATS.delete_table_stats(
ownname => 'SH',
tabname => 'SALES',
stat_category => 'SYNOPSES'
);
END;
/Regather statistics
BEGIN
DBMS_STATS.gather_table_stats (
ownname => 'SH',
tabname => 'SALES'
);
END;
/
3
- If your thing is to take it easy and rebuild stats gradually:
Set properties
BEGIN
DBMS_STATS.set_table_prefs(
ownname => 'SH',
tabname => 'SALES',
pname => 'APPROXIMATE_NDV_ALGORITHM',
pvalue => 'HYPERLOGLOG'
);
DBMS_STATS.set_table_prefs(
ownname => 'SH',
tabname => 'SALES',
pname => 'INCREMENTAL_STALENESS',
pvalue => 'ALLOW_MIXED_FORMAT'
);
END;
/
ALLOW_MIDEX_FORMAT allows the simultaneous existence of partitions with SYNAPSES created with the algorithm ADAPTIVE SAMPLING and others created with the algorithm HYPERLOGLOG.
1
2
Regather stats partition by partition
BEGIN
FOR part IN ( SELECT partition_name
FROM dba_tab_partitions
WHERE table_owner = 'SH'
AND table_name = 'SALES'
ORDER BY
partition_name )
LOOP
DBMS_STATS.delete_table_stats(
ownname => 'SH',
tabname => 'SALES',
partname => part.partition_name,
stat_category => 'SYNOPSES'
);
DBMS_STATS.gather_table_stats(
ownname => 'SH',
tabname => 'SALES',
partname => part.partition_name,
granularity => 'PARTITION'
);
END LOOP;
END
/Here I run the recalculation partition by partition, but you could process a group of partitions today, another one tomorrow and so on, until there is no partition left without stats regathering.
Regather global stats
BEGIN
DBMS_STATS.gather_table_stats (
ownname => 'SH',
tabname => 'SALES'
);
END;
/
3
Conclusions
The introduction of incremental statistics for partitioned tables has allowed us to have up-to-date statistics with low resource consumption.
Over time, improvements have been made to the algorithms used for incremental statistics, which by default are applied to new objects.
Do not lose time and check the situation of your partitioned tables, and if so, plan the rebuild of their statistics with the new HYPERLOGLOG algorithm, and let us know how it went.
To complement the above, I recommend that you read the following notes:
| 1417133.1 | How To Collect Statistics On Partitioned Table |
| 814710.1 | SYSAUX Tablespace Fills Rapidly due to Increased WRI$_OPTSTAT_SYNOPSIS$ Size |
| 2916829.1 | How to rebuild Incremental Statistics with HYPERLOG |
