Performance Sizing
Recommendations
Data Volume Overview
| Audiences | SRC_CH | SRC_CH |
|---|---|---|
| 100,000 | 11 Million | 6 Million |
Airflow (for DAG Orchestration)
| CPU | RAM | DISK |
|---|---|---|
| 8 | 32 GB | 500 GB |
Canonical DB
| CPU | RAM | DISK | Oracle Node |
|---|---|---|---|
| 16 | 64 GB | 500 GB | 1 |
Note: This is minimum sizing. Actual production environments
require higher specifications depending on concurrency, ETL frequency, query
complexity, number of DAGs, task concurrency, and data volume.
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:
- 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.
- Increase chunk size appropriately to speed up the csv generation process.
- Add multiple threads for faster execution.