Re: Fast Refresh of Snaphots Locking?

From: A. Bardeen <abardeen1_at_yahoo.com>
Date: Sun, 16 Sep 2001 19:30:50 -0700
Message-ID: <F001.0038FF5B.20010916194518@fatcity.com>

Tracy,

Exclusive locks are not held on the master tables during the refresh so I don't think that's the culprit. I suspect it may be a problem with deleting a large number of rows from the snapshot log that is causing contention. The first thing that comes to mind is transactions waiting for an available ITL slot especially if multiple sessions are getting the same block off the free list. As you mention, though, I would expect this locking to last for the duration of the refresh.

There are some known self-deadlocking issues in 8i during snapshot refreshes until 9i, but those typically result in the refresh dieing with an ORA-4020 or "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!" entries in the alert.log.

HTH,

• Anita
  • Tracy Rahmlow <Tracy.Rahmlow_at_aexp.com> wrote:
    > When we do a fast refresh of a snapshot and the
    > number of entries in the log
    > are high (750,000+) our users complain about poor
    > performance for approximately
    > a 5 - 10 minute period. (This occurs in the
    > database where the master resides)
    > As of yet I have not been notified in a timely
    > manner to investigate as it is
    > occuring. I thought there was no locking on the
    > master site, so my initial
    > reaction is that this is not the issue. It is also
    > strange that it is a short
    > period of time. I believe much shorter than the
    > time it takes to refresh the
    > snapshot. Also, only appears to be an issue with a
    > large (in our shop) number
    > of transactions in the snapshot log. No complaints
    > any other time. Has
    > anybody seen any threshold limit for refreshing
    > incrementally (fast)? Is there
    > locking occuring on dictionary tables which is
    > extended in length due to the
    > number of transactions? Any thoughts? Thanks
    

    ---

Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: A. Bardeen
  INET: abardeen1_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Sun Sep 16 2001 - 21:30:50 CDT