Tuning Recommendations

For best results, Oracle DB tuning should be performed by an expert Database Administrator. The following recommendations can help guide the optimization effort:

Canonical DB

  • Add database nodes and enable Oracle RAC to improve load balancing, reduce contention, increase throughput, and provide high availability.
  • Use Table Partitioning to lower I/O load, speed up queries on large tables, and allow processing in manageable segments.
  • Increase Resource Sizing by scaling the RAM to 128GB - 256 GB. Expand storage beyond 1 TB using SSD/NVMe, and add CPU cores for parallel processing.
  • Refresh optimizer statistics at intervals to ensure accurate query planning and consistent performance.
  • Improve ETL/Data Load Efficiency by using bulk loading tools, implement incremental loads, and schedule heavy jobs during off-peak hours.
  • Strengthen Backup and Recovery by performing daily RMAN backups, manage archive logs properly, and regularly test recovery procedures.
  • Monitor System Health using AWR/ASH to detect bottlenecks, track key performance metrics, and implement alerts for critical thresholds.
  • Plan for Future Scalability by adopting a multi-node design, separate compute and storage when needed, and ensure schemas support growing CH/RH data volumes.

Airflow

  • Increase chunk size appropriately to speed up the csv generation process.
  • Add multiple threads for faster execution based on redo log size.
  • For massive loads, it may help to disable/rebuild indexes after loading.