Re: Enterprise Manager warns of blocking on LGWR

On 2017/12/07 14:52, Jeffrey Beckstrom wrote:

> 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?

By "bypass", I hope you mean that you want to ignore blocking locks for LGWR? I do not believe there is a mechanism to do that.

I had your exact problem, in addition to EM complaining about "blocking" locks between PQ slaves. For LGWR, the problem happens when there is over-committing, like doing 1M inserts with a commit between each row. I could not force this problem using SQL/PLSQL, but I did create a short Perl program which inserts rows from a significantly large table into a new one. I used sys.wrm$_snapshot_details, that had ~1.5M rows in it. Running the program triggered our alert.

There are some Oracle bugs which may apply to you, but did not end up helping me:

18936207 (duplicate of 19805753) and 15996428

I never did get any resolution on this. After my 3 years of metrics for my primary database target got wiped due to a bug in EM12r5, I ended up trashing it and installed EM13r2. Even a new install has been very painful (I'm tracking about 25 different issues in various stages of resolution), but at least the blocking lock reporting appears to have been fixed in EM13r2. There are new "user lock" metrics where you can ignore alerts for TM and UL locking types, and only alert for TX blocking locks.

Probably not what you wanted to hear, though...

If you want the Perl script, let me know. It's only about 1200 bytes, but the hack will require some (minor?) mods to run in another environment. I ran it against 4 different 11.2.0.3 installations on AIX and Linux x86-64, but Support was never able to get it work...

Good luck!
Rich

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