8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Statistics Collection Enhancements in Oracle Database 12c Release 1 (12.1)
Oracle database 12c includes a number of new features related to statistics collection. Many of these features have been covered separately, so this article serves to bring all the features together.
- Automatic Column Group Detection
- Statistics Feedback
- Concurrent Statistics Collection
- Dynamic Statistics
- Enhancements to Incremental Statistics
- Enhancements to System Statistics
- Histogram Enhancements
- Online Statistics Gathering for Bulk Loads
- Session-Private Statistics for Global Temporary Tables
- SQL Plan Directives
- New DBMS_STATS Preferences
Related articles.
- Cost-Based Optimizer (CBO) And Database Statistics
- Statistics Collection Enhancements in Oracle Database 11g
- Optimizer Statistics Advisor in Oracle Database 12c Release 2 (12.2)
Enhancements to Incremental Statistics
Oracle 11g enhanced the collection of incremental statistics, such that partition level statistics could be used to accurately calculate global statistics, described here. In 11g, a partition exchange marks the statistics as stale on the new segment, even if they were up to date before the operation. This means the statistics on the partition must be gathered again before they are considered valid and used to update the global statistics.
Oracle 12c now supports the use of statistics for a segment used in a partition exchange. Provided the original segment has valid statistics, they will be used to amend the global statistics, removing the need to regather the statistics on the new partition once the exchange is complete.
Enhancements to System Statistics
The DBMS_STATS.GATHER_SYSTEM_STATS procedure includes a new GATHERING_MODE
of EXADATA
. This includes additional tests specific for Exadata systems. According to the documentation, "the multi-block read count and IO throughput statistics are set along with the CPU speed." This was present in 11gR2, but it was not documented, so it is now documented as a new feature in the database documentation.
For more information see:
- Automatic Column Group Detection in Oracle Database 12c Release 1 (12.1)
- Session-Private Statistics for Global Temporary Tables in Oracle Database 12c Release 1 (12.1)
- Concurrent Statistics Collection in Oracle Database 12c Release 1 (12.1)
- Dynamic Statistics in Oracle Database 12c Release 1 (12.1)
- Automatic Reoptimization in Oracle Database 12c Release 1 (12.1) : Statistics Feedback
- Histogram Enhancements in Oracle Database 12c Release 1 (12.1)
- Online Statistics Gathering for Bulk Loads in Oracle Database 12c Release 1 (12.1)
- SQL Plan Directives in Oracle Database 12c Release 1 (12.1)
- New DBMS_STATS Preferences in Oracle Database 12c Release 1 (12.1)
- Cost-Based Optimizer (CBO) And Database Statistics
- Statistics Collection Enhancements in Oracle Database 11g
- Optimizer Statistics Advisor in Oracle Database 12c Release 2 (12.2)
Hope this helps. Regards Tim...