Re: ADG lag after upgrading to 12.1
From: Rich J <rjoralist3_at_society.servebeer.com>
Date: Thu, 28 Mar 2019 08:52:38 -0500
Message-ID: <0ce169a2c194c1d4cdf64d20d07ca9a2_at_society.servebeer.com>
>
> Good call, as DBUA did recreate my spfile, kindly "fixing" many of my parameter values. I rechecked in V$ARCHIVE_DEST, even though I'm running ASYNC where the default is "NOAFFIRM" and yes, the AFFIRM column is indeed "NO".
Date: Thu, 28 Mar 2019 08:52:38 -0500
Message-ID: <0ce169a2c194c1d4cdf64d20d07ca9a2_at_society.servebeer.com>
On 2019/03/19 15:26, Rich J wrote:
>> It might be worth checking the settings in LOG_ARCHIVE_DEST_n, and see if you are using AFFIRM or NOAFFIRM. >> Switching to NOAFFIRM would confirm if the lag was cause by waiting for the write to SRL to ack (and to confirm there's no parameters in there like DELAY=nnn ) although that may well be classed as transport lag.
>
> Good call, as DBUA did recreate my spfile, kindly "fixing" many of my parameter values. I rechecked in V$ARCHIVE_DEST, even though I'm running ASYNC where the default is "NOAFFIRM" and yes, the AFFIRM column is indeed "NO".
As an update to this, I see that the lags coincide with the LGWR on the standby getting caught in -- or causing -- a library cache lock for minutes at a time.
I see that the library cache locks seem to coincide with particularly unruly SQLs (e.g. ~300 binds in a convoluted WHERE clause of a generated SQL statement) on the standby, which is used for ad-hoc reporting. Attempts to investigate some of these SQLs show that they've aged out of the cache in less than 30 minutes.
Sounds like the Shared Pool is too small, so I'm going to attempt to increase that as soon as I can get some downtime.
Posting here for posterity... :)
Rich
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Mar 28 2019 - 14:52:38 CET