Oracle database internals by Riyaj

Discussions about Oracle performance tuning, RAC, Oracle internal & E-business suite.

Nologging redo size

Posted by Riyaj Shamsudeen on January 25, 2012

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.

Update: I added another script to calculate redo rate if you don’t have AWR license. redo_size_archived_log.sql.

Introduction to Direct Mode Writes

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.

Physical writes direct = < writes to data file due to direct mode operations> +
                              physical writes direct to temporary tablespace +
                              physical writes direct (LOB)

To identify the size of direct writes to data file, excluding temp files, then the formula becomes trivial as :

Physical writes to datafile = block_size * ( physical writes direct -
                                                physical writes direct to temporary tablespace )

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.

awr_redo_nologging_size.sql v1.00 by Riyaj Shamsudeen @orainternals.com

To generate Report about Redo rate from AWR tables

Enter the block size(Null=8192):
Enter past number of days to search for (Null=30):21

DB_NAME   REDO_DATE                redo_size (MB) phy_writes_dir (MB) phy_writes_dir_temp(MB)
--------- ------------------- ------------------- ------------------- -----------------------
...
TEST1      01/09/2012 00:00:00          554,967.92        4,337,470.54            4,048,463.09
TEST1      01/10/2012 00:00:00          725,161.69        7,631,308.52            7,311,254.35
TEST1      01/11/2012 00:00:00        1,417,910.43       11,022,558.04           10,424,339.66
TEST1      01/12/2012 00:00:00          162,109.27        2,756,108.79            2,658,140.35
TEST1      01/13/2012 00:00:00          736,137.74        5,449,356.39            5,107,896.82
TEST1      01/14/2012 00:00:00          880,102.10        3,494,355.88            3,119,470.18
...

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.

DB_NAME   REDO_DATE            redo_size (MB) phy_writes_dir (MB) phy_writes_dir_temp(MB)
--------- --------------- ------------------- ------------------- -----------------------
...
TEST2     24-DEC-11                 19,149.11            2,796.57                2,361.68
TEST2     25-DEC-11                 18,362.74            1,630.83                1,379.95
TEST2     26-DEC-11                 60,097.50            3,867.92                3,303.37
TEST2     27-DEC-11                 55,696.98            3,266.89                2,756.84
TEST2     28-DEC-11                 62,971.37            4,650.37                4,096.75
TEST2     29-DEC-11                 62,167.32            3,839.07                3,255.70
TEST2     30-DEC-11                 64,072.57            4,462.38                3,788.39
...

Summary
In summary, we can estimate the amount of redo size if we alter the database to FORCELOGGING mode. This is a very useful estimation while implementing these replication tools.

Thanks to Kirti Deshpande and Kalyan Maddali for testing out my script. Of course, any mistake in the script is mine, only mine.

7 Responses to “Nologging redo size”

  1. Yasir said

    Dear Sir,
    Can we get the same from gv$archived_log?
    It also has blocks and block_size column. So blocks*block_size would give redo rate based on first_time.

    • 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.

      select to_char(first_time, ‘mm/dd’) ArchiveDate,
      sum(BLOCKS*BLOCK_SIZE/1024/1024) LOGMB
      from V$ARCHIVED_LOG
      where first_time > sysdate – 7
      and dest_id=1
      group by to_char(first_time, ‘mm/dd’)
      order by to_char(first_time, ‘mm/dd’)
      /

      Cheers
      Riyaj

  2. Vyacheslav Rasskazov said

    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”.

  3. Anand said

    Good one..

    Anand

  4. Mike Eubanks said

    Thanks for the script. However, I think you left off a join condition in the last WHERE:

    and inst.startup_time = redo_data.startup_time

    I found that it was multiplying my actual redo size by the number of rows in dba_hist_database_instance.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 200 other followers

%d bloggers like this: