Predicting when a partition in Oracle database will be archived

Question

We have an Oracle database in which the main table "A1" is partitioned by the hour of insertion of the row. A row once inserted, may be updated depending upon data in 6 other tables (B,C,D,E,F,G) in the same database.

A partition in table A1 typically gets updated for a few hours after it starts receiving data. After the updates to the partition are finished, the partition gets archived, i.e, it is moved from production to an archive database. The time of creation of a partition and the time of its archival are logged.

Now, my question is given the past history of creation and archival times of partitions, how can I predict when a partition created now in A1 will be archived? What kind of model (regression, etc.) is the best to answer this question? How can I set up a model that tracks the dependence of the archival time of a partition on the attributes in other tables (B,C,D,E,F,G)?

Answer 1

I'd suggest first visualize your data.

The time to archive a partition could be defined as a difference between the partition start (first possible insert) and archiving time (last possible update).

Plot the total distribution of this data for some time interval - check if it follows some distribution. Is the average, median or sd meaningful?

Make a boxplot for each hour to see inter quartile interval and the outliers.

Plot it as a time series - does it have a trend or daily/weekly pattern?

In your simple set up (with one predictor and one response) this will give you impression about the accuracy that you can expect from a model. The choose of the model depends on the result of the visualization.

I personally would start with the simplest models:

1. all partition archived after $N$ hours;
2. independent model for each hour predicting say $.9$ quantile of some history interval;
3. more advanced 2.: consider the weekday and/or trend and/or ignoring outliers.

Always check the accuracy and stop if expectations are met.