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 9.2.0.1 and laterOracle Database - Personal Edition - Version 9.2.0.1 and later
Oracle Database - Standard Edition - Version 9.2.0.1 and later
Information in this document applies to any platform.
Goal
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).
Solution
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