Re: ADG lag after upgrading to 12.1
- From: Rich J <rjoralist3@xxxxxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 28 Mar 2019 08:52:38 -0500
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
Other related posts:
