Can FOR UPDATE increase buffer gets?

From: Martin Klier <usn_at_usn-it.de>
Date: Sun, 09 Jun 2013 22:45:54 +0200
Message-ID: <51B4E982.8000002_at_usn-it.de>



Hello list,

it may be a very silly question, and until a few weeks ago, I would have said "no": Can selecting FOR UPDATE increase buffer gets?

What my system does:
- Call a PL/SQL package

  • package does SELECT ID FROM TABLE WHERE STATUS=0 AND ROWID=1 FOR UPDATE
  • do this (and follow up with some dependent processing) in <50 connections at the same time

What I can see:
- TABLE has 6 million records

  • STATUS = 0 hits 100.000 rows
  • There's an index on STATUS+ID, and it's used by the execution plan in question, thuis no table access of any kind (as desired)
  • In "peacetime" the execution plan has <10 buffer gets
  • Sunday night the very same execution plan has 17M or up to 258M buffer gets
  • Sunday night all relevant sessions have SQL_ID matching the above SQL and wait events as I'd expect for heavy buffer getting, but no row lock contention waits
  • 10046 Level 12 trace on the SQL_ID only shows wait for CPU quantum
  • Executing anything else on Sunday night never comes back due to massive system overload.

And, I never had the chance to reproduce the issue, whatever I do my artificial situation never boils up.

So my question, refined: Can the fact that this is a FOR UPDATE somehow cause massive buffer getting?

To clarify: I never was able to reproduce the issue with or without FOR UPDATE, and was never able to run and get back the select without FOR UPDATE in crisis.
So I'm asking for theoretical background, not for help in this very special situation.

Thanks a lot in advance!
Martin Klier

-- 
Usn's IT Blog for Linux, Oracle, Asterisk
http://www.usn-it.de

--
http://www.freelists.org/webpage/oracle-l
Received on Sun Jun 09 2013 - 22:45:54 CEST

Original text of this message