select for update wait issues
Date: Mon, 13 Apr 2015 10:25:03 -0600
Message-ID: <CAJzM94BK_zg5-fKO6j_g1jGE4T5bB9NZyOhHTO2qTdUSvZgkng_at_mail.gmail.com>
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
Received on Mon Apr 13 2015 - 18:25:03 CEST