How do I optimize my Oracle B2C Service database?

Environment:

Oracle B2C Service, Data Management 

Resolution:

It is a best practice to define and configure a data management strategy in order to keep a "healthy" database size. A database measuring under 500G is considered healthy. A healthy database simplifies keeping your application on a supported version. Version updates are more time consuming, and cutover is prone to last minute delays when the production database exceeds 500G.

Oracle B2C Service provides two tiers for storage for incident data: the database tier and the incident archive tier. The database tier is intended for short term storage of open and active incidents while the archive tier is built for long term storage of closed incidents.

Managing the Lifecycle of Incident Data:
Incidents typically follow a lifecycle consisting of the following stages: creation, activity, closure, archival, and destruction. Throughout this lifecycle, customers should consider what their business requirements are and design a data management strategy that aligns with their strategy. Recognizing this lifecycle and developing a strategy for handling incidents at each stage of the lifecycle will contribute to the best possible experience for your customers, agents, and other Oracle B2C Service users.

Ultimately, Oracle B2C Service should be used for transactional activity with your customers and collaboration between your customers and agents. Oracle B2C Service should not be used for warehousing data. For this reason, archiving or purging aggressively is strongly recommended for high volume customers.

The Oracle B2C Service - Data Volume Management Whitepaper provides insight into various data volume management capabilities offered by Oracle B2C Service. This allows customers to efficiently manage data storage on their site and meet their organizational compliance policies.
 

Essential Step:

Implement Data Lifecycle Management (DLM) policies. The transactions table is one of the largest tables in nearly every database. Select transaction types can be deleted through the Data Lifecycle Management editor. Refer to what is Data Lifecycle Management? for more information. High level, you will need to utilize the Browser UI, add the DLM Editor to the BUI navigation set, update your Profile to grant yourself "Bulk Delete" permission, and then familiarize yourself with the feature.

Implement an incident data retention policy which meets business requirements. There are two common strategies. The majority of customers elect to archive incidents as soon as possible and then eventually purge those archived incidents at a later date. Refer to Options for removing older incidents without deleting them from our site for more information.

Other customers skip archiving entirely and instead, purge incidents directly from the database after a specific duration. Refer to purging the database of old incidents or answers for more information.
 

Secondary Steps:

Optimized configuration settings are an important step towards a streamlined database. Configuration settings should be reduced sequentially, in small increments, because they all depend on the agedatabase utility. You should make a small reduction, day after day, and then monitor the agedatabase utility to ensure a reasonable execution time was achieved. (Ideally 4-6 hours) Refer to how to check utility status for my site for more information.

For example, PURGE_DELETE_INC_PERFORMANCE should not be updated from 0 to 90 in one or two edits. Instead, the configuration setting should be set to 1460. Allow a business day to pass so agedatabase executes, verify the utility execution time, then reduced the configuration setting daily by 50-100 until arriving at the targeted value.