Snapshots vs PL/SQL?

Hi, I've written some custom pl/sql procedures who's only purpose is to replicate some tables across a dblink.

I'm considering the use snapshots instead.

The reason we used pl/sql in the first place is simply because it gives us more control over the amount of data that gets replicated at any given time (and we created a separate scheduler process to manage this)

We have a staging/operational database and a reporting db. The operational db has a high volume of transactional activity, that happens 24/7. The reporting db is what the customers connect to. We strive to make the reporting db as close to "real-time" as possible so that is why we are constantly moving data there. In order to minimize a significant performance degradation on the reporting db, we essentially replicate a few hundred rows at a time. Usually what happens is that the replicated table lags behind somewhat and this lag will increase. The transactional system will experience periods of low activity (whereby it is not adding new rows to the master table), once we hit this period, the replicated table begins to catch up.

We haven't been all that pleased with the performance of this method and I am wondering if snapshots would be any faster in moving the data? (For the particular tables in question, this would be a simple snapshot.) And since we do need to do a fast refresh quite frequently, I wonder what type of burden this would place on the resources of the reporting db since we'd have no control over the number of rows that would be replicated? For example if we refresh every hour, when it replicates it may just need to move 5 rows or it might need to move 10 thousand rows. (The amount of data generated in the transactional system is very unpredicatable).

I'm going to go ahead and experiment with the snapshots, but if anyone has any warnings that I might want to consider, I'd appreciate your feedback.

Thanks,
Gavin

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Dec 05 2000 - 18:40:20 CST