Fixed Objects Statistics (GATHER_FIXED_OBJECTS_STATS) Considerations
(Doc ID 798257.1)
Last updated on MAY 22, 2023
Applies to:
Oracle Database - Personal Edition - Version 10.1.0.2 and laterOracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Oracle Database - Standard Edition - Version 10.1.0.2 and later
Oracle Database Cloud Schema Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
- 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.
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Cause |
| Solution |
| References |