How to Collect and Display System Statistics (CPU and IO) for CBO use

(Doc ID 149560.1)

Last updated on DECEMBER 01, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
Oracle 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).

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.

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