Skip to Main Content
  • Questions
  • Databases producing excessive loads of archive logs

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Vangelis.

Asked: February 10, 2021 - 12:20 pm UTC

Last updated: February 16, 2021 - 4:00 am UTC

Version: Oracle Database Enterprise Edition version 19.9

Viewed 10K+ times! This question is

You Asked

Hi all,

Our company is running on oracle database 19c version 19.9 on windows 2012r2 servers. Recently, I am facing an issue with two databases producing excessive loads of archive logs something around 100GB per day.

I tried to resize the redo logs though what I achieved was less number of archives but total log size remained the same.

Moreover, trying to find out what causes such high loads of logs, it usually appears to be the DBWx, M00x oracle processes producing such huge redo logging even at night.

But how could I figure out what's is causing those processes to produce such loads all day, e.g. is it application, a materialised view refreshing frequently etc.

As I am newbie, could please someone give me any suggestion how/where/what to look further on that issue, even some documentation to go through?

Thanks in advance.

Best regards,

VangelP

and Connor said...

it usually appears to be the DBWx, M00x oracle processes producing such huge redo logging even at night


Can you clarify how you are deducing that?

You can run something like this to see current sessions redo consumption

SQL>  select se.sid, se.username, se.program,
  2        s.name, st.value
  3   from v$statname s, v$sesstat st, v$session se
  4   where st.STATISTIC# = s.STATISTIC#
  5   and s.name = 'redo size'
  6   and st.sid = se.sid;

       SID USERNAME             PROGRAM                        NAME                    VALUE
---------- -------------------- ------------------------------ ------------------ ----------
         1                      ORACLE.EXE (SVCB)              redo size                   0
         2                      ORACLE.EXE (LREG)              redo size                   0
         4                      ORACLE.EXE (ARC1)              redo size                   0
         5                      ORACLE.EXE (W00G)              redo size             3577020
         7                      ORACLE.EXE (W005)              redo size             3296016
        10 MCDONAC              sqlplus.exe                    redo size           133032808
       123                      ORACLE.EXE (PMAN)              redo size                   0
       124                      ORACLE.EXE (W001)              redo size             4819232
       126                      ORACLE.EXE (ARC2)              redo size                   0
       127                      ORACLE.EXE (QM02)              redo size                   0
       130                      ORACLE.EXE (W00H)              redo size             4652836
       132                      ORACLE.EXE (M005)              redo size               84148
       245                      ORACLE.EXE (PMON)              redo size                   0
       246                      ORACLE.EXE (DIA0)              redo size                   0
       247                      ORACLE.EXE (PXMN)              redo size                   0
       248                      ORACLE.EXE (AQPC)              redo size                   0
       250                      ORACLE.EXE (W00I)              redo size             4059068
       256                      ORACLE.EXE (Q00A)              redo size               10740
       367                      ORACLE.EXE (CLMN)              redo size                   0
       368                      ORACLE.EXE (DBW0)              redo size            53280336
       369                      ORACLE.EXE (W006)              redo size             4400524
       371                      ORACLE.EXE (ARC3)              redo size                   0
       375                      ORACLE.EXE (W00D)              redo size             9442808
       377                      ORACLE.EXE (W00J)              redo size             2722244
       489                      ORACLE.EXE (PSP0)              redo size                   0
       490                      ORACLE.EXE (LGWR)              redo size                   0
       491                      ORACLE.EXE (MMON)              redo size             9822340
       493                      ORACLE.EXE (TT02)              redo size                   0
       494                      ORACLE.EXE (Q00B)              redo size                   0
       496                      ORACLE.EXE (M004)              redo size                   0
       611                      ORACLE.EXE (VKTM)              redo size                   0
       612                      ORACLE.EXE (CKPT)              redo size                   0
       613                      ORACLE.EXE (MMNL)              redo size                   0
       614                      ORACLE.EXE (W002)              redo size             6602960
       621                      ORACLE.EXE (M006)              redo size            33761328
       733                      ORACLE.EXE (GEN0)              redo size                   0
       734                      ORACLE.EXE (LG00)              redo size                   0
       736                      ORACLE.EXE (Q005)              redo size               83004
       737                      ORACLE.EXE (W008)              redo size             6906068
       739                      ORACLE.EXE (W00K)              redo size             4555732
       741                      ORACLE.EXE (QM00)              redo size                 584
       855                      ORACLE.EXE (MMAN)              redo size                   0
       856                      ORACLE.EXE (SMON)              redo size            10801232
       857                      ORACLE.EXE (CJQ0)              redo size               40256
       859                      ORACLE.EXE (M007)              redo size              288796
       861                      ORACLE.EXE (W009)              redo size             8930444
       863                      ORACLE.EXE (W00L)              redo size             1417656
       865                      ORACLE.EXE (Q006)              redo size               13272
       977                      ORACLE.EXE (DBRM)              redo size                   0
       978                      ORACLE.EXE (LG01)              redo size                   0
       979                      ORACLE.EXE (TMON)              redo size                   0
       981 SCOTT                sqlplus.exe                    redo size                4836
       982                      ORACLE.EXE (Q007)              redo size               21912
       983                      ORACLE.EXE (W007)              redo size             4266036
       985                      ORACLE.EXE (W00A)              redo size             6367388
       987                      ORACLE.EXE (W00M)              redo size             8278092
      1099                      ORACLE.EXE (GEN1)              redo size                   0
      1100                      ORACLE.EXE (SMCO)              redo size                   0
      1101                      ORACLE.EXE (M000)              redo size              612156
      1103                      ORACLE.EXE (TT00)              redo size                   0
      1105                      ORACLE.EXE (W003)              redo size             4695152
      1107                      ORACLE.EXE (M001)              redo size              243696
      1109                      ORACLE.EXE (W00B)              redo size             6692988
      1111                      ORACLE.EXE (W00N)              redo size             2160448
      1113                      ORACLE.EXE (Q003)              redo size                7328
      1221                      ORACLE.EXE (DIAG)              redo size                   0
      1222                      ORACLE.EXE (RECO)              redo size                   0
      1223                      ORACLE.EXE (ARC0)              redo size                   0
      1226                      ORACLE.EXE (W00C)              redo size             5112228
      1229                      ORACLE.EXE (W00E)              redo size             3631248
      1233                      ORACLE.EXE (Q008)              redo size               21688
      1343                      ORACLE.EXE (TT01)              redo size                   0
      1344                      ORACLE.EXE (VKRM)              redo size                   0
      1345                      ORACLE.EXE (W000)              redo size             5693972
      1347                      ORACLE.EXE (W004)              redo size             5368884
      1351                      ORACLE.EXE (W00F)              redo size             4168088
      1356                      ORACLE.EXE (Q009)              redo size                   0


but don't forget that this is total since startup, so background processes may *look* big. You can capture this over time and look for large *deltas* to see large redo consumers.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

units of value?

A reader, February 11, 2021 - 10:02 am UTC

what is the unit (byte,KB,MB,GB) of value column as per your query?
I assume that is bytes.
Connor McDonald
February 16, 2021 - 3:58 am UTC

bytes

Graham, February 11, 2021 - 10:25 am UTC

That's a really helpful query Connor provided and definitely worth using but I feel it is also important to step back a bit and look at the situation from a more strategic overview.

What is the nature of your company's application? Do you have one database per application or does your database support several applications? Is the 100GB daily redo a recent thing or has it always been the case? It's good to know what is "normal" for your system. Perhaps have a chat with your developers or business teams to see whether new or different functionality has been introduced recently, we've seen application updates here where redo and other system activity has almost doubled overnight to give us a "new normal" after major application changes.

Does the size of your database also grow accordingly? Are more and more records being stored? If they're not so much then the redo could be generated by changes to current data rather than additions. Again, this could be business logic.

It's a nice approach you've taken though to increase the sizes of the redo logs because very very frequent log switches can have a negative impact on performance, however it won't (as you've seen) change the total volume of redo. As 100GB is 100GB whether your redo logs are 256MB or 1GB or whatever.

Databases producing excessive loads of archive logs

Vangelis P, February 11, 2021 - 12:46 pm UTC

Hi again,

Thank you all for your prompt replies and comments.

I did run that query on both databases and output is as follows:

First database

SID USERNAME PROGRAM NAME VALUE
4 ORACLE.EXE (DBW0) redo size 1018042428
162 ORACLE.EXE (M001) redo size 273623740
36 ORACLE.EXE (M003) redo size 270237080
37 xxx xxxorcl64.exe redo size 49416896
278 xxx xxxorcl64.exe redo size 49416636
157 xxx xxxorcl64.exe redo size 49218824
2 ORACLE.EXE (SMON) redo size 44899764
261 ORACLE.EXE (M004) redo size 29213496
272 ORACLE.EXE (M002) redo size 26276084
153 ORACLE.EXE (M000) redo size 16716128

Second database:

SID USERNAME PROGRAM NAME VALUE
8 ORACLE.EXE (DBW0) redo size 984839548
34 ORACLE.EXE (W005) redo size 158307564
25 ORACLE.EXE (W003) redo size 148470676
340 ORACLE.EXE (W006) redo size 146107952
346 ORACLE.EXE (W007) redo size 145869552
325 ORACLE.EXE (W001) redo size 145306520
14 ORACLE.EXE (W002) redo size 131922424
323 ORACLE.EXE (W000) redo size 120570632
339 ORACLE.EXE (W004) redo size 111325716
322 ORACLE.EXE (SMON) redo size 26142268

Seems DBW doing a lot of work even at night. However, discussing it further with my team - newbie in Oracle and newly hired in that company- seems its application logic, as they are running frequent mview refreshes over dB links, so that explains excessive archive logging.

However, Investigating further on those mviews, most are set to force, doesn't that mean they do a complete refresh, thus, generating more archive since mviews are deleted? Wouldn't it be better to do a fast refresh?

But since refresh of mviews jobs run concurrently on same time intervals, I think avoiding excessive archiving is inevitable. Right?

Thanks in advance for all your assistance.

Best regards,

Vangelis
Connor McDonald
February 16, 2021 - 4:00 am UTC

You could look at non-atomic refreshes.

A standard refresh does: delete/insert.

A non-atomic one does: truncate/insert

(You run the risk of people querying the table when its empty however).

Fast refresh *might* a solution - very much depends on the views themselves.

https://docs.oracle.com/en/database/oracle/oracle-database/21/dwhsg/refreshing-materialized-views.html#GUID-64068234-BDB0-4C12-AE70-75571046A586

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database