Q41. Which two are true about Optimizer Statistics?
- A. By default, they are automatically gathered by a maintenance job.
- B. They provide real-time data about schema objects.
- C. They can be gathered by the DBMS_STATS package.
- D. They are gathered by the SQL Turning Advisor.
- E. They are ignored by Optimizer if they are stale.
Answer: AC
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/optimizer-statistics-concepts.html#GUID-3ED2DA31-F61B-4E8F-9166-79FB01392B1D
Key points about Optimizer Statistics:
Essential for query optimization: The Oracle Optimizer relies on statistics to estimate the cost of different query execution plans and choose the most efficient one.
Automatic gathering: By default, a nightly maintenance job automatically gathers statistics for most schema objects.
Manual gathering: You can also manually gather statistics using the DBMS_STATS package, which offers more granular control over the process.
Regular maintenance: It’s crucial to regularly gather statistics to ensure they remain up-to-date and reflect changes in data distribution.
Stale statistics: Stale statistics can lead to suboptimal query plans, potentially impacting database performance.