Re: select for update wait issues

    SELECT ... FOR UPDATE WAIT will try to acquire locks on each row selected, and will wait (indefinitely) to get those locks. (This is distinct from SELECT without the FOR UPDATE clause, which locks nothing, and from the FOR UPDATE NOWAIT clause which returns an error if the locks cannot be obtained).

   There are lots of things that could be happening here, so Mladen's question is a good one -- what are the WAIT EVENTS you are seeing these statements waiting on? Some of them are probably waiting on locks held by other sessions (most likely other sessions running the same statement) -- it might be very interesting to see what the ones *not* waiting on locks are doing.

   In situations where you have lots of sessions running statements like this, things will often work fine so long as the SELECT statements -- and the rest of the transaction following them -- all run quickly. A session grabs a lock, does some work, commits that work, and the lock is released before there is a high probability of another session trying to lock the same row(s).

   If something has changed to make these transactions slower (that could the the SELECT statement itself, or the statements that follow, up and including to the COMMIT) some systems can move from running "just fine" to running "almost not at all" very abruptly.

  (Note: The foregoing statements are generalizations just loaded with assumptions. How well they might fit your situation is yet to be seen.)

   Please note that while you analyse this, it is important to pay attention to the AMOUNT OF TIME WAITED, and not just the number of waits. It will probably be helpful, too, to compare the amount of time waited on relevant events now versus the amount of time waited on the same events when the system was performing well.

   Apologies if I am telling you things you already know. If these are not things you already know, then hopefully this will at least help to get you started.

On Mon, Apr 13, 2015 at 12:25 PM, Sandra Becker <sbecker6925_at_gmail.com> wrote:

> Oracle 11.2.0.2
> Solaris 10 64-bit on Oracle T5
> EMC storage (fast cache enabled)
>
> Last Wednesday night we moved a production database to new hardware. We
> do not have the option to move it back. On Friday we started experiencing
> severe performance issues. ASH reports show the "SELECT...FOR UPDATE
> WAIT..." running as the top SQL for events and row sources. This is NOT
> new code. What can I look at, is there anything I can do, short of
> killilng sessions, to get this under control? I'm opening a ticket with
> Oracle, but this forum generally responds faster.
>
> I'm not really familiar with how the SELECT FOR UPDATE WAIT works and
> haven't found a good explanation yet. The select is on a partitioned
> table. Does it lock only a row or does it lock the partition? At times I
> can see a dozen or more of these statements running. Once they complete, I
> then see a lot of waits on log file sync.
>
> If anyone knows, how does having fast-cache enabled affect this?
>
> Thanks for any suggestions/guidance.
>
> --
> Sandy
> GHX
>

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