How to Collect and Display System Statistics (CPU and IO) for CBO use
Last updated on DECEMBER 01, 2016
Applies to:Oracle Database - Enterprise Edition - Version 184.108.40.206 and later
Oracle Database - Personal Edition - Version 220.127.116.11 and later
Oracle Database - Standard Edition - Version 18.104.22.168 and later
Information in this document applies to any platform.
This bulletin explains how new system statistics can be collected and displayed for CBO to use and apprehend CPU and system I/O information. For each plan candidate, the optimizer computes estimates for I/O and CPU costs. You must have DBA privileges or GATHER_SYSTEM_STATISTICS role to update dictionary system statistics.
System statistics should be valid and represent the performance of the system. They are not something that should be frequently gathered because this can encourage plan instability with an unknown or minimal benefit. Often systems will work fine with no system statistics at all. If you do choose to gather system statistics (to capture a particular workload or to profile a particular activity) the main trigger would be that there has been relevant changes to the system (like incorporation of faster CPUs or IO subsystem) - it is a common misconception that gathering is necessary when adding more CPU cores (only faster/slower CPUs may suggest a need to gather fresh system stats).
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms