Re: Newbie question re refreshing Materialized Views

Adam Cameron wrote:
> Hi
> Sorry if this is a dumb question. I HAVE spent a fair while googling about
> the place trying to work this out for myself, but have drawn a blank. I
> will continue with Google after submitting this.
>
> We have some sort of glitch on our Oracle box at present which is making it
> run somewhat slowly, which has revealed some unexepcted behaviour to me.
>
> We have a materlialized view which we need to do a full refresh of, every
> now and then. When the box is running fine, this process takes a few
> milliseconds: no problem there.
>
> Currently it is taking about a minute. This in itself is not a problem
> (well: it is, but it's someone ELSE'S problem, and they're on the case).
>
> The problem is that if a SELECT query is performed on that MV whilst it's
> being refreshed, a zero-row record set is returned. This, I guess, is
> because DURING the refresh process, the current data gets blitzed first,
> then the new data is recreated. And the SELECT query is hitting the view
> between those two steps, whilst there is no data. And I'm only seeing this
> now because the refresh is taking so long.
>
> This doesn't seem an appropriate behaviour to me.
>
> I would have thought there'd be some sort of view-level lock on the view
> whilst it's refreshing, so that anything trying to query it will WAIT until
> the refresh is finished, and then return sensible results. OR (and maybe
> preferably) the new data would be generated in the background, and once
> that's done, it would replace the "real" view... my theory being the data
> generation takes the time, but the swap out would be very quick. During
> the data creation, any SELECTs would query the "stale" data.
>
> I'm fairly certain what I'm seeing is just a vagary of my set up... all my
> Oracle knowledge comes out of a book that I bought (well: three books), and
> google searches. Also it's only a small part of my "day job" (which is a
> software developer), so I make no claims of any expertise or that I know
> what I'm doing. An interesting but dangerous position to be in!
>
> Does anyone know what I'm doing wrong, or what I need to tweak, or what I
> need to read to work out where I'm mucking something up? Indeed does
> anyone know what I'm even banging on about? ;-)
>
> I appreciate the efforts of anyone who takes time to give this some thought
> and nudge me in the right direction!

It would help to have the following:

1. Oracle version number to 4 decimal places
2. The DDL used to create the MV
3. An explain plan taken both before and after the change
   • you do run regular StatsPacks don't you?
4. Any indication of what has changed: Something has. -- Daniel A. Morgan University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org