Blogroll

Nologging redo size

It is probably easy to calculate hourly redo rate or daily redo rate using AWR data. For example, my script awr_redo_size.sql can be used to calculate daily redo rate, and awr_redo_size_history.sql can be used to calculate hourly redo rate. Hourly redo rate is especially useful since you can export to an excel spreadsheet, graph it to see redo rate trend.

Direct mode operations write directly in to the database file skipping buffer cache. Minimal redo(aka invalidation redo) is generated, if the database is not in force logging mode. Keeping the database in no force logging mode is peachy as long as you don’t use Data guard, Streams, or Golden Gate.

Suddenly, business decide to use one of these log mining based replication products. This means that you must turn on Force logging at the database level so that replication tools can capture (just replay in the case of Data guard) the redo information correctly and consistently.

But, what if your application performs high amount of direct mode operation, such as insert /*+ append */ operations? Now, you need to estimate the redo size to identify the effect of FORCE LOGGING mode That estimation gets little tricky.

Direct writes

During direct mode operation, blocks are pre-formatted and written directly to the disk bypassing buffer cache. If the database is altered to Force logging mode, then still direct mode operations will write to the block. In addition to direct mode writes, these operations will generate redo for the blocks written directly, almost similar to writing the whole block in to the redo log files. This will increase redo size.

There are few statistics capturing the direct mode writes. Using these statistics, we can estimate the redo size for direct mode operations.

Statistics

Statistics ‘physical writes direct’ includes mostly three component statistics as given below.

Script awr_redo_nologging_size.sql uses this formula to estimate the amount of redo size if the database is altered to FORCE Logging mode. One caution with this script is that, this script assumes an uniform block size( of what you specify , 8192 is default). If you use multiple block sizes in your database, then specify the biggest block size in use (or average!). Script will overestimate it, but it is better than underestimation.

In the code output above, notice the line for 1/11/2012. Estimated total redo size is ~1,417GB if we alter the database to FORCE LOGGING mode at database level. Out of that 1417 GB redo size, ~600 GB of redo will be generated due to direct mode operations from the calculation: 11,022GB will be generated due to direct mode operations minus adjustment for direct writes to temporary tablespace of size 10,424GB (over 10TB writes to temporary tablespace).

Example #2
In this example, notice 28-DEC-11. 62GB of redo estimated if alter the database to force logging mode. Out of that just 600MB of redo will be generated due to direct mode operation.

Yes, You could potentially use v$archived_log (not gv$archived_log since the archive log files from all nodes are visible in v$archived_log itself). Following SQL can give you some idea, but I think, I should write it better since it might be useful for the Standard Edition customers without AWR data.

Vyacheslav Rasskazovsaid

Quote: “Minimal redo(aka invalidation redo) is generated, if the database is not in force logging mode”
A think, more correctly, is “Minimal redo(aka invalidation redo) is generated, if the database is not in force logging mode AND segment have nologging attribute OR database in noarchivelog mode”.