Re: Archive Log Size

• I was just rereading this thread and ran into a slightly different case I am just starting to dig into. My environment is 11.2.0.4 RAC (3 nodes).

NAME                      VALUE
-------------------- ----------
Fixed Size              2269072
Variable Size        3.9460E+10
Database Buffers     8.8584E+10
Redo Buffers          227807232

  2      indx,
  3      total_bufs_kcrfa,
  4      strand_size_kcrfa,
  5      index_kcrf_pvt_strand,
  6      space_kcrf_pvt_strand
  7  from
  8*     x$kcrfstrand

---------- ---------------- ----------------- ---------------------
---------------------
         0           215624         110399488                     0
            0
         1           215624         110399488                     0
            0

So if each public thread takes 105M, I have ~~46M left over. Filling one thread and switching, as discussed in Jonathan's article, should give me archive logs of ~105 M. Instead, I am getting an archive log of ~ the rump size in the redo (256-105-105=46M).

I checked the obvious (not a manual log switch, not archive_lag_target). I'll chime in if I find something interesting. Also wondering if any obvious things I'm missing off the top.

Henry

   -
- *From*: Martin Klier - Performing Databases GmbH

   <martin.klier_at_xxxxxxxxxxxxxxxxx>

• *To*: jlewisoracle_at_xxxxxxxxx
• *Date*: Thu, 14 Jan 2021 13:34:06 +0100 (CET)

Jonathan,

that's awesome. I already wild-guessed in that direction (something like the relation of private strands to the size of the log buffer), especially since we have a massively asynchronous load / semi loaded system. But this explanation is very plausible and good.

Thank you, also for the quick response.
Martin

-- 
Martin Klier // Performing Databases GmbH
Managing Partner // Senior DB Consultant
Oracle ACE Director

martin.klier_at_xxxxxxxxxxxxxxxxx // https://www.performing-databases.com
<https://www.performing-databases.com%20/>;

Von: "Jonathan Lewis" <jlewisoracle_at_xxxxxxxxx>
An: "Oracle-L Freelists" <oracle-l_at_xxxxxxxxxxxxx>
Gesendet: Mittwoch, 13. Januar 2021 17:30:46
Betreff: Re: Archive Log Size


This might be highly relevant:


[
https://www.red-gate.com/simple-talk/sql/oracle/are-your-oracle-archived-log-files-much-too-small/
|
https://www.red-gate.com/simple-talk/sql/oracle/are-your-oracle-archived-log-files-much-too-small/
]


Regards
Jonathan Lewis


On Wed, 13 Jan 2021 at 16:11, Martin Klier - Performing Databases GmbH < [
mailto:martin.klier_at_xxxxxxxxxxxxxxxxx
<martin.klier_at_xxxxxxxxxxxxxxxxx%C2%A0>;| martin.klier_at_xxxxxxxxxxxxxxxxx ] >
wrote:


Hi folks,


I observe a behaviour I can't explain:


Platform: Windows
Oracle Version: 12.1.0.2
Online Redo Log Size: initially 300MB, later 1GB


Sometimes the archived redo logs get as big as the redo log size is, but
that's
rarely the case. Usually, I end up with less than 100MB. Why?


Whenever I hit ARCHIVE_LAG_TARGET, it's easy to explain, why they are
smaller.
Same during backups or when I switch manually. But why during normal
operations, even high redo log pressure (log switch every 30sec), IMO the
redo
logs (and thus, the archives) should be pretty full, isn't it?


The ratio is about 100MB for 300GB Redo Logs, and about 800MB for 1GB Redo
Log
size.


Any ideas?


Thanks a lot
Martin


--
Martin Klier // Performing Databases GmbH
Managing Partner // Senior DB Consultant
Oracle ACE Director


[ mailto:martin.klier_at_xxxxxxxxxxxxxxxxx
<martin.klier_at_xxxxxxxxxxxxxxxxx%C2%A0>;| martin.klier_at_xxxxxxxxxxxxxxxxx ]
// [
https://www.performing-databases.com/ ;|
https://www.performing-databases.com
<https://www.performing-databases.com%20/>;
]

--
http://www.freelists.org/webpage/oracle-l