My Oracle Support Banner

System Statistics: Scaling the System to Improve CBO optimizer (Doc ID 153761.1)

Last updated on OCTOBER 23, 2022

Applies to:

Oracle Database - Personal Edition - Version 9.0.1.0 and later
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.

Purpose

Purpose of this note is to explain the impact of gathering system statistics on the CBO optimizer and to give DBA guidance to use this functionality.
Most recent perspective is that system stats does not necessarily need to be gathered per following blog:

https://blogs.oracle.com/optimizer/should-you-gather-system-statistics

It is always advisable to thoroughly test any recommendations, as every environment is different.

Scope

This Document is intended for Oracle DBAs about system statistics.

Details

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
Purpose
Scope
Details
 Gathering system statistics
 Features of the system statistics procedures of the DBMS_STATS package
 Example of Gathering System Statistics
 Create the statistic holding table
 Activate job processes if none is started
 Gather statistics during relevant activity periods via DBMS_STATS.GATHER_SYSTEM_STATS
 Verify collected statistics
 Activate the statistics
 Checking the values
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.