Re: Can FOR UPDATE increase buffer gets?

From: Sidney Chen <huanshengchen_at_gmail.com>
Date: Mon, 10 Jun 2013 10:28:10 +0800
Message-ID: <CAM_ddu-YX+syC0DUm9tDAjhnwabCHe9mDpGNKBTxEvyPZVZRtQ_at_mail.gmail.com>

Hi Martin,
Any ETL job on the table on Sunday? It occurs to me that "delayed block cleanout" may come into play. The high buffer get may due to undo records applied for transaction table or data blocks consistent read. Please check the session statistics for the suffered session in crisis, see where comes the high buffer gets.

you can use Tanel Poder's snapper.sql to capture the session statistics or below sql, and look at the number of below statistcis carefully.

db block gets

consistent gets
consistent gets - examination
cleanout - number of ktugct calls
transaction tables consistent reads - undo records applied transaction tables consistent read rollbacks data blocks consistent reads - undo records applied

select

    ses.sid,
    sn.name,
    ses.value
from

    v$sesstat ses,
    v$statname sn
where

    sn.statistic# = ses.statistic#
and ses.sid in (&1)
and (
lower(sn.name) like lower('%gets%')
or lower(sn.name) like lower('%cleanout%') or lower(sn.name) like lower('%undo records applied%') )
/

On Mon, Jun 10, 2013 at 4:45 AM, Martin Klier <usn_at_usn-it.de> wrote:

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

-- 
Regards
Sidney Chen


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

Received on Mon Jun 10 2013 - 04:28:10 CEST