Hi Ask Tom team,
We are building a CDC from our Oracle OLTP systems, using Golden Gate or related technology. The developers, including myself, wanted to enable supplemental logging on all columns for us to build streaming analytics solutions, which would need the complete row during an update event, and not just the PK and changed columns. Our OLTPs already have supplemental logging enabled on the primary key columns and our DBAs are completely against enabling the same for all columns.
To test the impact of all column supplemental logging, I did a simple test of loading and updating (updating just the NAMESPACE column below) 1 M rows into a table (schema same as that of ALL_OBJECTS). Both during my insert and update, I committed once at the end:
Table:
Name Null Type
-------------- -------- ------------
OWNER VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(30)
This is what I ran to check the redo size generated (before and after the transaction):
select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('redo size')||'%';
The following to check the undo blocks used by my transaction:
SELECT USED_UBLK FROM V$TRANSACTION;
This is what I have seen as the difference in redo log size and undo block usage between all column supplemental logging vs primary key supplemental logging:
i. There wasn't a noticeable difference between the execution timings of the insert and update transactions with 'all column supplemental logging' vs 'primary key supplemental logging'
ii. During the 'update transaction', the 'all column supplemental logging' generated 55-60% additional redo, compared to 'primary key supplemental logging'. The redo generated for the 'insert' transaction was almost the same between the two logging modes
iii. The 'update' transaction used twice as much as the undo blocks in case of 'all column supplemental logging' vs the pk one
I am trying to reason out the results of the tests and also, if there will any other factors to be looked at.
Can you please help me understand:
i. During the 'update' transaction, was the additional undo block usage because of the fact that undo will record the before image of all the columns and not just the changed ones?
ii. How does the supplemental logging internally works? What overheads does it create on the database?
iii. If we need to enable 'all column supplemental logging' in our OLTPs, should be just look at tuning the redo log buffers, redo log groups, file sizes, optimising disk I/Os (we do have SSDs)? Are their other factors to be looked at, like DR replication etc?
I did try to post a simple test on livesql, but was getting 'internal server errors' multiple times, so couldn't share a link with the question.
Please help.
i. During the 'update' transaction, was the additional undo block usage because of the fact that undo will record the before image of all the columns and not just the changed ones? Not exactly. We *do* need to store more information, and we do by recording additional *undo*. See below.
ii. How does the supplemental logging internally works? What overheads does it create on the database?We store additional information in the undo stream, because undo goes into the redo, and hence we capture both. Just adding more redo is not enough, because if I do (say): update T set col = 'A' where col = 'B', then the redo *still* only contains "A", not the original "B"
Julian Dyke wrote an excellent post on supplemental logging here
http://www.juliandyke.com/Research/GoldenGate/GoldenGateSupplementalLogging.php iii. If we need to enable 'all column supplemental logging' in our OLTPs, should be just look at tuning the redo log buffers, redo log groups, file sizes, optimising disk I/Os (we do have SSDs)? Are their other factors to be looked at, like DR replication etc?Like anything - to get benefits you pay a cost somewhere. So you'll burn a little more CPU, and you'll consume more redo log space more aggressively. But that's still going to be much more efficient that doing this yourself (eg with triggers or additional tables etc).
That also answers your related question. Balance off the level of supplemental logging you need with the benefits you need to get. Then you can make an informed decision about what to go for. If your DBAs say "No" they should be able to *justify* why they said that. Just as you should be able to justify the benefits.