I am looking for feedback from DBAs and performance experts who have experience managing highly volatile tables in Oracle Database 19.31.
In our environment, we have work tables, processing tables, and staging tables that can grow from a few thousand rows to several million rows during the same processing cycle. Managing optimizer statistics on these objects has become a challenge.
If statistics are unlocked, Oracle may automatically gather statistics while business processes are running. In some situations, this introduces additional overhead and can increase execution times. On the other hand, when statistics become stale or do not accurately reflect the current data volume, the optimizer may choose inefficient execution plans, resulting in significant performance regressions.
I would like to understand the most effective strategy for this type of workload:
- Do you regularly gather statistics on highly volatile tables, or do you prefer to lock them?
- How do you handle tables whose row counts change dramatically within a single batch process?
- What role do Dynamic Statistics (Dynamic Sampling) and Real-Time Statistics play in this scenario?
- Can these features provide sufficiently accurate cardinality estimates to help the optimizer consistently choose good execution plans without frequent statistics gathering?
- Have you observed plan instability or regressions when relying mainly on Dynamic Statistics or Real-Time Statistics?
- Are there any Oracle 19c (specifically 19.31) best practices that you would recommend for this type of environment?
My main objective is to maintain stable and efficient execution plans while minimizing the overhead of statistics collection on large volatile tables.
I would be very interested in hearing about real-world experiences, lessons learned, and practical recommendations from those who have faced similar challenges.
Thank you in advance for your insights.