There is no sensible way to bypass LGWR.
LGWR writes redo buffer to the online logs (and to standby logs in some
modes). Even if you don't archive your logs, an instance restart requires
the online logs to clean up on deferred DBWR work recorded in the logs so
that DBWR work is not the pacing activity.
There are a few avenues of research to discover why LGWR might be
identified as blocking.
1) You could be wrapping logs with ARCH taking a long time to complete
and make a log available to write in. A quick way to see if this is in the
running is to look at your log history and see whether switches and last
write times on logs correspond with the reported blocks.
I'd check that one first. IF it is add to the number of log groups in
rotation (the default is tiny, I like 10 by pure preference unless I can
prove I need more) and possibly increase the size of each log.
In the old days some folks used to stash members of the online log groups in
any nook or cranny they could find, and hilarity would ensue on multipurpose
servers or disk farms when some batch process consumed near 100% of the i/o
capability of the device that hosted some part of the online redo log. On
slow devices, ARCH running could compete sufficiently with LGWR on a single
device to slow LGWR down.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Jeffrey Beckstrom
Sent: Thursday, December 07, 2017 3:52 PM
To: oracle-l-freelists; oracle-db-l
Subject: Enterprise Manager warns of blocking on LGWR
We are running EM 12.1.0.5. For the past several months, since upgrading our
agent to 12.1.0.5, across various databases we have been receiving a
blocking alert warning with the blocker being LGWR. By the time you log on
the database directly or through Enterprise Manager, no blocking locks can
be found. In fact, sometimes no active sessions are found or run quickly.
After about 15+ minutes the alert clears.
We do correctly receive the alert on user sessions that are causing a
locking condition.
Is there a way to bypass LGWR?
Jeffrey Beckstrom
Lead Database Administrator
Information Technology Department
Greater Cleveland Regional Transit Authority
1240 W. 6th Street
Cleveland, Ohio 44113
