How to Collect and Display System Statistics (CPU and IO) for CBO use
(Doc ID 149560.1)
Last updated on JUNE 29, 2021
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
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
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 does not necessarily need to be gathered. This is dependent upon the environment. Per following blog, it is not mandatory to gather 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).
Note that if System Statistics are gathered (regardless of method), an immediate review of the correctness of the values generated is a must. Values that normally need to be validated are MREADTIM, SREADTIM and MBRC. Validate these against reported Operating System figures. Note that Oracle CPU speed IS NOT the same as hardware CPU speed - the value computed by Oracle is fine. If in doubt of the correctness of any of these values, it is more conservative to DELETE System Stats than the possibility of non-representative values that may adversely affect plan generation and the performance of your queries.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!