Re: Fast Refresh of Snaphots Locking?

There was a bug (fixed 8.1.7.?) in the way oracle handles snapshot refreshes. Basically it invalidates all SQL that is using the refresh and takes a lock on the object that is being used. We found that this caused severe shared_pool fragmentation on a few of our instances.

Also, the way Oracle does a snapshot refresh is a full tablescan of the snapshot log. So once the log gets fairly full, you need to do an alter table <snapshot log name> move;
to lower the HWM to get half decent performance again.

• Babette
  • Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, September 11, 2001 3:30 AM

> 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
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Tracy Rahmlow
> INET: Tracy.Rahmlow_at_aexp.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).
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Babette Turner-Underwood
  INET: babattt_at_home.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).