Operational Data Store (ODS) Best Practices on Exadata

Base on my consulting experience, I like to share some best practices running Operational Data Store on Exadata: –

  1. Use direct path load as much as possible.
  2. Maximize parallel data load using different techniques ( Partitioning, Parallel DML, etc.)
  3. User parallel degree based on you application needs, look into AUTO DOP feature.
  4. Make sure to use all Exadata Features (smart scan, offloading, and storage indexes) to get extreme performance.
  5. Use Histograms carefully, they can have an adverse impact on your application.
  6. Test optimizer mode for your application before you start developing you ODS application.
  7. Data compression is very important. Not only it can help you with query performance, it can also save some storage. Watch your CPU utilization.
  8. If you are planning to consolidate many data sources to a single ODS, make sure to implement Exadata consolidation best practices.
  9. Use proper data models 3NF or Star Schema based on your Needs. Most ODS are 3NF.
  10. Make sure to collect stats after every ETL loads, use incremental for huge tables.
  11. User partition smartly to achieve partition-wise join or Star Transformation.
  12. Carefully analyze your partition needs make sure it will help yours with ETL load. Just don’t create partitions randomly, number of partition matters
  13. If you carefully, Range, hash and interval partitioning will help you achieve extreme performance with Exadata.
  14. If you are planning to introduce partitioning to an existing application, please test thoroughly.
  15. Avoid Global Indexes, use local indexes instead.
  16. Test thoroughly before making any index invisible. It can have an adverse impact on other queries.
  17. Data load should be fast as possible with minimum impact on users, end user comes first.
  18. Make sure to achieve partition-wise join or Star Schema transformation.
  19. User summary tables for as needed.
  20. If you are planning have your ODS/Staging/DW within same Exadata machine, implement IORM (Exadata IO Resource Manager)