I am interested in learning about the best practices for ensuring that the Oracle optimizer consistently chooses efficient execution plans without relying on hints, SQL patches, SQL profiles, or forced execution plans that may become outdated over time.
In your experience, what database features, parameters, and maintenance practices should be implemented to help the optimizer make the right decisions and minimize plan regressions?
For example:
- Which optimizer-related parameters are most important to review?
- What are the recommended statistics gathering strategies for Oracle 19c?
- How effective are Real-Time Statistics, Dynamic Statistics, SQL Plan Management (SPM), and Automatic Indexing in preventing regressions?
- What role do histograms, extended statistics, and accurate object statistics play?
- How do you manage volatile tables and changing data distributions?
- What monitoring and validation processes do you use to detect and prevent plan changes before they impact production?
I am particularly interested in feedback from teams running large Oracle 19c environments and the lessons they have learned in maintaining stable performance while allowing the optimizer to adapt naturally to data changes.
Thank you in advance for sharing your recommendations and real-world experiences.