RE: Streams

The supplemental logging information adds quite a bit of into to each record.
1) it writes the actual column offset, rather than the logical offset (which it needs to query to find)
2) it writes the original head rowid for the row in addition/or instead of the rowid for the chained piece
3) it writes all information about the primary/unique/key columns too.

So if I have a table that has a compound primary key, and 2 additional columns.

PK1	1
PK2	2
COL1	A
COL2	C

and I update a row to change COL1 from 'A' to 'B' then w/o logging turned on, oracle will write

ROWID modified
COL1 'A' to 'B'
plus index upkeep stuff and other junk

with supplemental logging PRIMARY on I get ROWID modified
COL1 'A' to 'B'
PK1 value 1
PK2 value 2

in a case like this it's about 20-30% more information in each redo record than before. Quite a big hit.

Also, since Oracle is using AQ to replicate data using STREAMS, each message replicated get's placed into a queue (another record inserted into the database AND redo logs) and removed from the queue (another record removed from the database AND redo logs) so there actually is a large amount of overhead with it.

-----Original Message-----
Sent: Thursday, October 24, 2002 4:01 PM To: Multiple recipients of list ORACLE-L

The statement about "no performance impact on the database being replicated from" may not be entirely accurate. In order to work, Oracle Streams (which uses Log Miner) needs "supplemental" redo data to be generated by the originating system, resulting from the ALTER DATABASE ADD SUPPLEMENTAL LOG DATA [ [ PRIMARY | UNIQUE ] KEY COLUMNS ] command. This causes additional data to be sent to the redo log stream when updates occur. I'm not certain exactly what level of supplemental log data is needed by Oracle Streams (if any), but perhaps someone else on the list can help with that?

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, October 24, 2002 4:33 PM

> David - I recently discussed this with an industry expert. Here is my note
> from that conversation:
> "Look at Oracle Streams, which uses Oracle's Advanced Queuing facility.
> This may be available in Standard Edition. The architecture is log-based,
> similar to Quest Shareplex. This means that it has no performance impact
on
> the database being replicated from. Streams is a group of components that
> allow you to build your replication scheme, more of a kit. It is not
proven
> in the real world yet. If your need is immediate, then avoid it, but if
your
> need is next year, then investigate Streams. This is only available in
> Oracle9i."
> That is all I know, hope that helps.
>
> Dennis Williams
> DBA, 40%OCP
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com
>
>
> -----Original Message-----
> Sent: Thursday, October 24, 2002 4:31 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Has anyone started using the Oracle streams feature? I'm wondering how
this
> compares to Advanced Rep. I am guessing this approach will provide better
> performance but thought I'd check with the list to see if someone can
> confirm
> this.
>
> Thanks, Dave
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: David Turner
> INET: turner_at_tellme.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: DENNIS WILLIAMS
> INET: DWILLIAMS_at_LIFETOUCH.COM
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tim Gorman
  INET: Tim_at_SageLogix.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Nick Wagner
  INET: Nick.Wagner_at_quest.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).