Application performance can be affected by many factors, including: database size, server and infrastructure class, number of users accessing the application and the quality of the application code. Most application support teams, developers and database administrators will say that most of their time is spent reactively responding to performance issues as opposed to other support and maintenance issues.
Monitoring tools are widely available to help relieve the burden of performance management. The tools assist with a number of tasks, such as identifying performance bottlenecks and debugging poorly written code. But if the amount of data continues to grow and the infrastructure isn't upgraded to keep up with the volumes, performance will degrade continually.
Tuning the application will only get you so far. A costly, yet common way to improve performance is to upgrade the hardware infrastructure, such as upgrade to a faster disk subsystem, increase the number of CPUs or upgrade the class of server. However, these solutions are a temporary fix because the data volumes will continue to grow, as will the number of users accessing the system.
At the end of the day, no matter how much you "tune" the application, the performance will not improve in a predictable manner unless, as my father would say, "You take the bricks out of the trunk." In other words, by taking out the inactive and dormant data from the production system, you are taking the dead weight out of the production system. Moving it to an online active archive keeps the data available to end users. Reducing the size of the most heavily hit transaction tables, full table scans and reports will run significantly faster in the production system.
Maintain native application end user access to the archive data. There are a number of database archiving solutions available that provide the ability to relocate data while maintaining application access to the data. This means that even though the data is removed from the production database, it is still available to the application users. This is an important feature because it helps ensure compliance and end-user productivity. One of the main reasons end users choose not to implement data archive projects is because they fear that they will no longer have access to the data. With the products available in the market today, this is no longer an issue. Data is relocated to an online archive and completely accessible to the end users through the native application. This way, IT organizations realize the benefit from archiving while keeping the end users happy.As an example, one well-known CIO who wanted to implement database archiving was receiving a lot of resistance from his end user community. He decided to start archiving older data to an online active archive. First he archived 10 percent of the data, and then, on a monthly basis, he would archive another 10 percent. The CIO was able to archive more than 60 percent of the data without issue - largely due to the fact that they were still able to access the data. In fact, during their peak season it was noticed that the performance improvements had a direct impact on reducing the number of performance-related trouble tickets submitted to the IT support desk.
Another example where database archiving improved application performance involves a recent customer implementation where their financials application was in production for almost five years. It was found that by relocating all general ledger data older than two years to an online active archive, the closing process improved by more than 50 percent. By establishing a recurring archive process one month after every quarter, the growth rate of the financials database was reduced so much that they were able to defer capital expenditures for more storage and additional CPUs for that application by more than two years.
Intelligent Data Partitioning
Archiving inactive data to an online archive is, in effect, partitioning the data using the application business logic to determine its status. Examples of policies used to determine the inactive state are a closed purchase order, shipped inventory or a closed booking period. The policy is defined by the business application and the line of business's implemented processes.
The policy definition and execution varies by software vender. Most use native database technologies, such as partitioning or transaction-based SQL to separate the data.
For database partitioning, most relational database technologies have built-in features to partition data in a table based on a column in the table. For example, if there is a date field, tables can be partitioned by date. For complicated applications, such as Oracle E-Business Suite, partitioning is not an option because the business logic in determining the inactive state may span across multiple tables and schemas. In this case, transaction-based SQL is needed.
Transaction-based SQL partitioning is used to select the inactive records and move them to another database or another tablespace within the same database. With transaction-based SQL, the complete application transaction is relocated in its entirety, ensuring relational integrity is maintained in the archive database. Some solutions deploy the use of database triggers to execute the transaction-based SQL - this can consume CPU resources. It is recommended to evaluate nontrigger-based solutions when considering transaction-based SQL.
Lower Total Cost of Ownership
Because the relocated data is, in most cases, considered "read-only," there are typically fewer users accessing the data. As a result, it may be more cost-effective to set up the archive database on a lower cost infrastructure. For example, the server could have fewer CPUs than production and the disk-subsystem could be network-attached storage, serial advanced technology attachment disk drives, or even RAID 5 (as opposed to service area network or RAID 0/1). We have seen customers implement the archive on an environment that costs 40 percent less per gigabyte than the production system.
