Oracle Streams - worth it?

#1

Hi,

We are evaluating different ways for a one-way replication from one
database to another. Additional requirement is, pat of replicated data
(detail rows only) should be updateable. Currently, we use materialized
views with refresh jobs running every minute. An insert trigger on the
master table's snapshot creates a queue entry for each replicated row.
Later, an asyncronous job goes through the queue and pulls detail rows via
database link. This has worked fine so far, but we started thinking of
creating multiple replicas (each of them would only recieve a portion of the
overall data), and we're afraid that materilized view maintenance overhead
in the master database will become too taxing.

One of the possibilities we're looking at is Oracle Streams, but I have
heard some allegations that it is inherently slow because it uses AQ as
transport mechanism.

So my question is, is it worth considering Oracle Streams for really
high load, almost mission-critical application? The lag time should not
exceed few minutes to fulfill some of our Service Level Agreements.

Also, what other alternatives should ve consider - be it Oracle or third
party products? I know we bought a Shareplex license a while ago (not sure
if it's still valid, but we might renew it if it's worth it)

Thanks in advance,

Isaac Blank

#2

Isaac Blank wrote:

Quote:

Hi,

We are evaluating different ways for a one-way replication from one
database to another. Additional requirement is, pat of replicated data
(detail rows only) should be updateable. Currently, we use materialized
views with refresh jobs running every minute. An insert trigger on the
master table's snapshot creates a queue entry for each replicated row.
Later, an asyncronous job goes through the queue and pulls detail rows
via database link. This has worked fine so far, but we started thinking
of creating multiple replicas (each of them would only recieve a portion
of the overall data), and we're afraid that materilized view maintenance
overhead in the master database will become too taxing.

One of the possibilities we're looking at is Oracle Streams, but I
have heard some allegations that it is inherently slow because it uses
AQ as transport mechanism.

So my question is, is it worth considering Oracle Streams for really
high load, almost mission-critical application? The lag time should not
exceed few minutes to fulfill some of our Service Level Agreements.

Also, what other alternatives should ve consider - be it Oracle or
third party products? I know we bought a Shareplex license a while ago
(not sure if it's still valid, but we might renew it if it's worth it)

Thanks in advance,

Isaac Blank

AQ is vastly superior, in every sense, to what you are currently using.

But I am fascinated by anyone claiming AQ is slow. Who and where?
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#3

Isaac Blank wrote:

Quote:

Hi,

We are evaluating different ways for a one-way replication from one
database to another. Additional requirement is, pat of replicated data
(detail rows only) should be updateable. Currently, we use materialized
views with refresh jobs running every minute. An insert trigger on the
master table's snapshot creates a queue entry for each replicated row.
Later, an asyncronous job goes through the queue and pulls detail rows
via database link. This has worked fine so far, but we started thinking
of creating multiple replicas (each of them would only recieve a portion
of the overall data), and we're afraid that materilized view maintenance
overhead in the master database will become too taxing.

One of the possibilities we're looking at is Oracle Streams, but I
have heard some allegations that it is inherently slow because it uses
AQ as transport mechanism.

So my question is, is it worth considering Oracle Streams for really
high load, almost mission-critical application? The lag time should not
exceed few minutes to fulfill some of our Service Level Agreements.

Also, what other alternatives should ve consider - be it Oracle or
third party products? I know we bought a Shareplex license a while ago
(not sure if it's still valid, but we might renew it if it's worth it)

Thanks in advance,

Isaac Blank

AQ is vastly superior, in every sense, to what you are currently using.

But I am fascinated by anyone claiming AQ is slow. Who and where?
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#4

Isaac Blank wrote:

Quote:

Hi,

We are evaluating different ways for a one-way replication from one
database to another. Additional requirement is, pat of replicated data
(detail rows only) should be updateable. Currently, we use materialized
views with refresh jobs running every minute. An insert trigger on the
master table's snapshot creates a queue entry for each replicated row.
Later, an asyncronous job goes through the queue and pulls detail rows
via database link. This has worked fine so far, but we started thinking
of creating multiple replicas (each of them would only recieve a portion
of the overall data), and we're afraid that materilized view maintenance
overhead in the master database will become too taxing.

One of the possibilities we're looking at is Oracle Streams, but I
have heard some allegations that it is inherently slow because it uses
AQ as transport mechanism.

So my question is, is it worth considering Oracle Streams for really
high load, almost mission-critical application? The lag time should not
exceed few minutes to fulfill some of our Service Level Agreements.

Also, what other alternatives should ve consider - be it Oracle or
third party products? I know we bought a Shareplex license a while ago
(not sure if it's still valid, but we might renew it if it's worth it)

Thanks in advance,

Isaac Blank

AQ is vastly superior, in every sense, to what you are currently using.

But I am fascinated by anyone claiming AQ is slow. Who and where?
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#5

Isaac Blank wrote:

Quote:

Hi,

We are evaluating different ways for a one-way replication from one
database to another. Additional requirement is, pat of replicated data
(detail rows only) should be updateable. Currently, we use materialized
views with refresh jobs running every minute. An insert trigger on the
master table's snapshot creates a queue entry for each replicated row.
Later, an asyncronous job goes through the queue and pulls detail rows
via database link. This has worked fine so far, but we started thinking
of creating multiple replicas (each of them would only recieve a portion
of the overall data), and we're afraid that materilized view maintenance
overhead in the master database will become too taxing.

One of the possibilities we're looking at is Oracle Streams, but I
have heard some allegations that it is inherently slow because it uses
AQ as transport mechanism.

So my question is, is it worth considering Oracle Streams for really
high load, almost mission-critical application? The lag time should not
exceed few minutes to fulfill some of our Service Level Agreements.

Also, what other alternatives should ve consider - be it Oracle or
third party products? I know we bought a Shareplex license a while ago
(not sure if it's still valid, but we might renew it if it's worth it)

Thanks in advance,

Isaac Blank

AQ is vastly superior, in every sense, to what you are currently using.

But I am fascinated by anyone claiming AQ is slow. Who and where?
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#6

Isaac Blank wrote:

Quote:

Hi,

We are evaluating different ways for a one-way replication from one
database to another. Additional requirement is, pat of replicated data
(detail rows only) should be updateable. Currently, we use materialized
views with refresh jobs running every minute. An insert trigger on the
master table's snapshot creates a queue entry for each replicated row.
Later, an asyncronous job goes through the queue and pulls detail rows
via database link. This has worked fine so far, but we started thinking
of creating multiple replicas (each of them would only recieve a portion
of the overall data), and we're afraid that materilized view maintenance
overhead in the master database will become too taxing.

Eh? MV's are on the slave side, the master has MV log tables and
triggers. Unless you have written your own replication mechanism, you
describe advanced replication.
Oracle can do horizontal as well as vertical partitioned replication.

Quote:

One of the possibilities we're looking at is Oracle Streams, but I
have heard some allegations that it is inherently slow because it uses
AQ as transport mechanism.

Consider it, especially if you're on 10G. In 9iRel2 it was (still)
buggy, and I had better results with AdvRep. Have not done serious
testing with 10G

Quote:

So my question is, is it worth considering Oracle Streams for really
high load, almost mission-critical application? The lag time should not
exceed few minutes to fulfill some of our Service Level Agreements.

That has nothing to do with high load. If you're talking
several thousands to millions of transactions per second, I'd
be worried about your setup (replicate data near real time),
too. Independent of technology.

--

Regards,
Frank van Bortel

Top-posting in UseNet newsgroups is one way to shut me up

#7

Isaac Blank wrote:

Quote:

Hi,

We are evaluating different ways for a one-way replication from one
database to another. Additional requirement is, pat of replicated data
(detail rows only) should be updateable. Currently, we use materialized
views with refresh jobs running every minute. An insert trigger on the
master table's snapshot creates a queue entry for each replicated row.
Later, an asyncronous job goes through the queue and pulls detail rows
via database link. This has worked fine so far, but we started thinking
of creating multiple replicas (each of them would only recieve a portion
of the overall data), and we're afraid that materilized view maintenance
overhead in the master database will become too taxing.

Eh? MV's are on the slave side, the master has MV log tables and
triggers. Unless you have written your own replication mechanism, you
describe advanced replication.
Oracle can do horizontal as well as vertical partitioned replication.

Quote:

One of the possibilities we're looking at is Oracle Streams, but I
have heard some allegations that it is inherently slow because it uses
AQ as transport mechanism.

Consider it, especially if you're on 10G. In 9iRel2 it was (still)
buggy, and I had better results with AdvRep. Have not done serious
testing with 10G

Quote:

So my question is, is it worth considering Oracle Streams for really
high load, almost mission-critical application? The lag time should not
exceed few minutes to fulfill some of our Service Level Agreements.

That has nothing to do with high load. If you're talking
several thousands to millions of transactions per second, I'd
be worried about your setup (replicate data near real time),
too. Independent of technology.

--

Regards,
Frank van Bortel

Top-posting in UseNet newsgroups is one way to shut me up

#8

Isaac Blank wrote:

Quote:

Hi,

We are evaluating different ways for a one-way replication from one
database to another. Additional requirement is, pat of replicated data
(detail rows only) should be updateable. Currently, we use materialized
views with refresh jobs running every minute. An insert trigger on the
master table's snapshot creates a queue entry for each replicated row.
Later, an asyncronous job goes through the queue and pulls detail rows
via database link. This has worked fine so far, but we started thinking
of creating multiple replicas (each of them would only recieve a portion
of the overall data), and we're afraid that materilized view maintenance
overhead in the master database will become too taxing.

Eh? MV's are on the slave side, the master has MV log tables and
triggers. Unless you have written your own replication mechanism, you
describe advanced replication.
Oracle can do horizontal as well as vertical partitioned replication.

Quote:

One of the possibilities we're looking at is Oracle Streams, but I
have heard some allegations that it is inherently slow because it uses
AQ as transport mechanism.

Consider it, especially if you're on 10G. In 9iRel2 it was (still)
buggy, and I had better results with AdvRep. Have not done serious
testing with 10G

Quote:

So my question is, is it worth considering Oracle Streams for really
high load, almost mission-critical application? The lag time should not
exceed few minutes to fulfill some of our Service Level Agreements.

That has nothing to do with high load. If you're talking
several thousands to millions of transactions per second, I'd
be worried about your setup (replicate data near real time),
too. Independent of technology.

--

Regards,
Frank van Bortel

Top-posting in UseNet newsgroups is one way to shut me up

#9

Isaac Blank wrote:

Quote:

Hi,

We are evaluating different ways for a one-way replication from one
database to another. Additional requirement is, pat of replicated data
(detail rows only) should be updateable. Currently, we use materialized
views with refresh jobs running every minute. An insert trigger on the
master table's snapshot creates a queue entry for each replicated row.
Later, an asyncronous job goes through the queue and pulls detail rows
via database link. This has worked fine so far, but we started thinking
of creating multiple replicas (each of them would only recieve a portion
of the overall data), and we're afraid that materilized view maintenance
overhead in the master database will become too taxing.

Eh? MV's are on the slave side, the master has MV log tables and
triggers. Unless you have written your own replication mechanism, you
describe advanced replication.
Oracle can do horizontal as well as vertical partitioned replication.

Quote:

One of the possibilities we're looking at is Oracle Streams, but I
have heard some allegations that it is inherently slow because it uses
AQ as transport mechanism.

Consider it, especially if you're on 10G. In 9iRel2 it was (still)
buggy, and I had better results with AdvRep. Have not done serious
testing with 10G

Quote:

So my question is, is it worth considering Oracle Streams for really
high load, almost mission-critical application? The lag time should not
exceed few minutes to fulfill some of our Service Level Agreements.

That has nothing to do with high load. If you're talking
several thousands to millions of transactions per second, I'd
be worried about your setup (replicate data near real time),
too. Independent of technology.

--

Regards,
Frank van Bortel

Top-posting in UseNet newsgroups is one way to shut me up

#10

"Frank van Bortel" <frank.van.bortel (AT) gmail (DOT) com> wrote

Quote:

Isaac Blank wrote:
Hi,

We are evaluating different ways for a one-way replication from one
database to another. Additional requirement is, pat of replicated data
(detail rows only) should be updateable. Currently, we use materialized
views with refresh jobs running every minute. An insert trigger on the
master table's snapshot creates a queue entry for each replicated row.
Later, an asyncronous job goes through the queue and pulls detail rows
via database link. This has worked fine so far, but we started thinking
of creating multiple replicas (each of them would only recieve a portion
of the overall data), and we're afraid that materilized view maintenance
overhead in the master database will become too taxing.

Eh? MV's are on the slave side, the master has MV log tables and
triggers. ......................

I was not clear enough. Indeed I had in mind MV log tables.

Quote:

..................Unless you have written your own replication mechanism,
you
describe advanced replication.

Well, initially we had MVs defined on prebuilt tables. We also needed the
detail data to be updateable, so we created a row level trigger on the
detail replica that would merge all the new/changed rows (we never delete)
into another table that we would later update per application logic whenever
needed. It worked fine for a while, then the load got high, and the
performance degraded catastrophically. The higher the load, the longer the
refresh takes and it is a single transaction, no matter how many rows. Next
refresh takes even longer because it has to catch up more, and even longer,
etc. So we decided not to replicate the detail table at all. Instead, a
row level trigger on master table replica would just create an entry in a
queu and few second later a dbms_job would dequeue and pull detail rows for
each master row one transaction per master row.

Quote:

Oracle can do horizontal as well as vertical partitioned replication.

True, but we're thinking of some really sofisticated rules, or maybe even
changing rules on the fly. That's why Oracle Streams looks so promising,
though I am not sure if using ANYDATA is not a pain in the neck

Quote:

One of the possibilities we're looking at is Oracle Streams, but I
have heard some allegations that it is inherently slow because it uses
AQ as transport mechanism.

Consider it, especially if you're on 10G. In 9iRel2 it was (still)
buggy, and I had better results with AdvRep. Have not done serious
testing with 10G

Yes, we are on 10G. I should have mentioned that in the original post.

Quote:

So my question is, is it worth considering Oracle Streams for really
high load, almost mission-critical application? The lag time should not
exceed few minutes to fulfill some of our Service Level Agreements.

That has nothing to do with high load. If you're talking
several thousands to millions of transactions per second, I'd
be worried about your setup (replicate data near real time),
too. Independent of technology.

At peak times, we might be close to a thousand rows insereted per second, I
think.

Anyway, based on what you provided, I think we will start evaluating both
Advanced Replication and Oracle Streams. Will be fun setting up test
environment to see which one performs better.

Thank you for your input, it is very helpful

Isaac Blank

Quote:

Regards,
Frank van Bortel

Top-posting in UseNet newsgroups is one way to shut me up