Re: supplemental logging

OraGeek wrote:

> Got a few questions about supplemental logging...
>
> What is the over head to run it?

Potentially enormous. A commit's not a commit until LGWR has flushed the transactional redo. Anything that increases the amount of redo that has to be flushed will slow the database down, noticeably.

>I understand that there are different
> levels.

True. You can, if you are totally bonkers, switch it on at the database level. Alter database add supplemental log data (primary key) columns. Or alter database add supplemental log data (unique index) columns. Or, if you really want a trip to the happy farm, both together.

The reason that's so nutty is that every table now starts putting its primary key into the redo. That's a big hit on LGWR. If a table doesn't have a primary key, then every column of the table is put into the redo, even though you only updated one of them. That's a massive hit on LGWR.

But you can also do it for specific tables (which makes more sense, probably). Alter table emp add supplemental log group emplog(empid,ename) ALWAYS. That means an update to someone's salary now causes their empid and ename to be added to the redo. But you can also do alter table emp add supplemental log group emplog(empid,ename); ...and then the extra columns are only added if one or other of those two columns are affected by a piece of DML. Update the guy's salary, and those extra columns won't be added to the redo stream.

So the thing is controllable, and can be set with a reasonable degree of precision so that LGWR is not completely swamped. But to do it right takes a lot of time and thought, and a lot of people will be tempted to go for the more sweeping versions, and then weep at the performance consequences.

>
> Do most folks have it enabled or disabled?

Depends what you mean. The statement 'alter database add supplemental log data; (no primary keys or unique indexes mentioned) switches on what's known as 'minimal supplemental logging' in 9i Release 2. Minimal logging is required if you want Log Miner (and hence Oracle Streams) to be able to work with, for example, chained and migrated rows and clusters. It poses very little additional overhead to the database, and Oracle recommends that you switch this on, even though it's off by default (it was on by default in 9i Release 1).

So if most people follow Oracle recommendations (which in some cases we hope they don't) then most people would have minimal logging on in 9iR2.

But the more intensive flavours of supplemental logging: most people would have those off. Because all of them impose a burden on the database that is measurable... and you'd only be willing to wear that cost if you needed the features it enables. Those features being, generally speaking, Oracle Streams and Logical standby databases (and Log Miner, of course, for trying to recover data after some bad DML that's been followed by a move of the table, which renders the rowids in the redo useless).

Most people don't use Streams or Logical Standby databases, so most people have no need for the higher-strength supplemental logging, and therefore (hopefully) most people would have the thing switched off at those higher strengths.

> Thanks much.

No worries.
Regards
HJR

-- 
--------------------------------------------
See my brand new website, soon to be full of 
new articles: www.dizwell.com.
Nothing much there yet, but give it time!!
--------------------------------------------