The Oracle Application User Group Archive and Purge Special Interest Group held its semi-annual meeting on Sunday September 30th at Oracle OpenWorld. Once again, this session was very well attended – but more so this year because of the expert panel which included: Admed Alomari – Founder of Cybermoor, Isam Alyousfi – Seniorr Director, Lead Oracle Applications Tuning Group, Sameer Barakat, Oracle Applications Tuning Group, and Ziyad Dahbour – now at Informatica (Founder of TierData, Founder of Outerbay).
The Archive and Purge SIG typically leads discussions on how to leverage archive and purge functionality that is available in Oracle E-Business Suite. Many look to database archiving to address either performance or cost issues. By archiving aged or inactive data out of the production database, the application may see improved performance by having smaller data sets residing in key transaction tables. When this data is no longer in the production database, copies used for test, development, disaster recovery and backup are smaller so admins have less storage capacity requirements. Some archive to meet data retention regulations for compliance.
The SIG Chair, Brian Bent from Informatica, invited Oracle’s Applications tuning Group to lead the discussion on performance archiving. This group is tasked to improve Oracle E-Business Suite (OEBS) performance – they now are responsible for the archive and purge programs that are available under Oracle Administrator responsibilities. Isam explained that in R12 of OEBS, the archive and purge functionality has been expanded to cover 200+ routines. Through these routines, data is archived from production tables to temporary tables where the DBA has to export the data to a target location. The challenge with these routines is that once the data is archived and purged, the business users no longer have access to the data through OEBS. Also, these routines are known to not perform very well with larger data volumes.
Ziyad Dahbour has spent the last two decades developing software to specifically address these challenges – solutions that give users the ability to partition or archive OEBS data to improve performance while maintaining end user access to the aged data. When he was asked about best practices for archiving and purging OEBS data, he parted the following wisdom to the attendees:
- Look for opportunities to remove duplicate data. Purge extra data, merge redundant data, take advantage of opportunities to eliminate data that is not needed to support the business.
- Ask yourself why there is so much data in the database? Aside from duplicates, where is the data accumulating and understand the source.
- Understand how long the business is required to keep the data – are there compliance requirements or data retention regulations that needs to be adhered to?
- Is there a need to have ease of access to archived data? What users would need to access data and for how long?
- Is storage an issue? Do you have an opportunity to leverage cheaper storage for older data? What SLAs need to be met and can tiering data help facilitate meeting those SLAs?
- What are the data classes? It is important to understand what class of objects need to be retained (i.e. Payables, Receivables, ledgers, orders, etc.)
- Devise a partitioning strategy that is aligned with the business layer – working at the table level does not incorporate the viewpoint of the data classes.
Finally, someone asked, ‘When should we start implementing a data archive strategy?’ The panel unanimously responded with, ‘The archive strategy should be implemented on Day 1’. We, at Informatica, couldn’t agree more.