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.
February 16, 2021 - 3:58 am UTC
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
Thank you all for your prompt replies and comments.
I did run that query on both databases and output is as follows:
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
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.