Last updated on JULY 07, 2017
Applies to:Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Oracle Database - Standard Edition - Version 10.1.0.2 and later
Oracle Database - Personal Edition - Version 10.1.0.2 and later
Information in this document applies to any platform.
- Missing or bad statistics on the X$ / fixed tables can lead to performance degradation or hangs. Various X$ views are protected by latches and as a result can be very expensive to query in large / busy systems.
- Most commonly this issue is seen on the underlying X$ tables for DBA_EXTENTS, V$ACCESS, V$RMAN_BACKUP_JOB_DETAILS, and V$RMAN_STATUS, but any fixed table protected through latching can experience this.
- Another commonly seen symptom is extreme TEMP space usage driven by poor plans against the fixed tables.
- RMAN, Data Guard, Streams, and Grid Control make heavy usage of the fixed tables through the DBA/V$ views and so can often bear the brunt of performance issues.
- These are not an exhaustive list of symptoms. Any item using X$ views and getting bad plans may be experiencing poor plans because of missing or bad statistics.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms