I was investigating a performance issue on new hardware and installed SLOB to do so. I discovered in the AWR report, that the redo generated per transaction was around four times that in development.
I checked everything I could think of, including copying the parameter file from development to the new hardware and using that, but the redo stayed stubbornly high. I asked the oracle-l mailing list for ideas. Jonathan Lewis responded suggesting the high redo could be caused by the following:
- private redo disabled - so no “large” redo entries generated in private
- some quirky little bug when auditing was enabled
- a couple of features that change “update” into “select for update/ update”
- trigger declarations - even NULL ones
- supplemental logging
The last option caught my eye because I know we have it enabled in production for the logical standby.
I decided to try something really simple, so I could understand it. I created a table:
Then ran the following from a script so on a quiet system nothing else ran between the two system change numbers (SCNs):
I could compare the results of
v$mystat, and also since I had the SCNs, I could dump the redo for this period.
Jonathan Lewis had pointed me
at one of his blog posts,
but this is also documented under Oracle Support note 1031381.6
This ends up in the trace file for the session. Since we are only dumping one update, it is much smaller than I expected. The database with supplemental logging switched on had more information in it. It would probably have been better to do an update on a table that had more columns because then the supplemental logging would be more verbose, as it would have to include the values of all columns, unless there was a unique index.
Asking the Database
Another way to understand the supplemental logging is to ask the database as follows. The first SQL tells me that supplemental logging is switched on at the database level to enable the database to uniquely identify columns.
Switching Supplemental Logging Off
From the output above, I understand what is switched on. The first SQL is what is important, the rest look like defaults. We have Primary Key, and Unique column logging switched on. I can switch this supplemental logging off using the following:
If the minimal supplemental logging can’t be dropped at this point, oracle support note 2114639.1 says to run:
Now supplemental data is switched off. Rerunning SLOB shows the redo to be the same as in dev.
Why the Difference?
I prefer all my databases to be identical because that means code will behave the same way in all environments. Clearly production needs more resource than development because more people are using it, and the costs in financial and environmental terms don’t allow allocatiuon of memory or CPU that won’t be used. There have to be some differences. However things like this should be the same. The question is, how come development has a different setting to my test on the new hardware?
The two databases were created differently. The dev one was created using a SAN snapshot and
create controlfile SQL command. The test database was created using an RMAN duplicate from a backup.
I infer from this that the supplemental logging configuration is stored in the control file.