Object Activity Tracking System (OATS) is a generic RDBMS based tracking service that provides information about various types of activities associated with different database objects. An activity represents a user or system-initiated action such as scanning or loading a table. Most of the activities are tracked in the form of frequencies (i.e. counts over fixed time intervals) such as the number of scans of a table in 15 minutes.

The database objects whose activities are tracked include tables and materialized views. Different types of activities include row insert, delete and update, table scan, load and truncate, partition maintenance operations (create, drop, move, split, merge, and exchange), materialized view rewrite and refresh.

Another important class of activities is related to the usage or non-usage of auxiliary structures such as MVs, indexes and zone maps.

Depending on the type of activity the tracking technique can be precise, approximate, or probabilistic. Most of the activities are tracked approximately mostly for efficiency reasons.

Depending on the type of activity and its usage by different clients a certain tracking technique would be more suitable than others. For example, counters are useful for tracking the index and materialized view usage. Counters are maintained for fixed time intervals in order to capture the object usage pattern over time. The same technique can be used to track the update activity of table columns by maintaining update counters for each. Setting bits in a bitvector is another form of tracking technique which is suitable for registering the occurrence of a certain activity within a time interval without saying how many times that activity occurred.

Precise Tracking: an activity is accounted for with 100% accuracy. Some form of atomic update or some latching is required to get precise tracking in a multi-processor environment. OATS generally avoidS precise tracking, unless a client needs that kind of precision.

Approximate Tracking: an activity is accounted for in almost all cases but with rare exceptions. An example of approximate tracking is the dirty update of activity counters. Dirty updating is very efficient because no locking or latching is performed and atomic operations are not used when counters are changed. However, dirty updating can result in lost updates leading to under counting. If lost updates are rare then dirty updating is a very efficient technique for approximate tracking.

Probabilistic Tracking: an activity is sampled with a certain probability, and it is accounted for when it becomes part of the sample. Probabilistic tracking produces activity data that is less accurate but it prevents frequent tracking actions.

Occurrence Tracking: records certain activity as having occurred within a time interval. Specifically, occurrence tracking provides information that says that certain object activity occurred at least once in a given time interval without saying how many times it occurred. The use of bitvector is a popular technique for occurrence tracking. Depending on how the bits are set in a bitvector the occurrence tracking can be either precise (no collisions) or approximate (possible collisions).

Counter Effects of Transactions and Rollback: Except for the precise tracking counters, none of the other types of counters is transactional or affected by rollback. This means that if a transaction fails, the approximate counters may still record any actions from that transaction. And when the database is rolled back to a save point, the approximate counters are not set back to their previous state.