Re: Oracle materialized view strange problem.

From: Laurenz Albe <invite_at_spam.to.invalid>
Date: 25 Apr 2006 10:10:36 GMT
Message-ID: <1145959835.172778@proxy.dienste.wien.at>

Jack <amitkr.3_at_gmail.com> wrote:
> We have 2 databses (A & B).Initially both were 8i (we never faced this
> problem then). Now B is 10g.

How exactly did B get to be 10g now?

Please be as specific as you can.

> One query at 'A' uses a fast refreshing materialized view based on one
> table of 'B'.
>
> At one time this query runs without any problem (1-2 minutes ) and
> moments later the exact query takes a long time (I left it running and
> it completed in 20hrs).

I cannot offer a solution, but I am interested in your problem as we also use Materialized Views between 8 and 10 and are anxious to know potential problems.

There's two things I would do to determine the problem: - Look for locks in the target database when the refresh takes a long time. - If possible, turn on SQL tracing on B and see what statements are issued and where the time gets lost.

Yours,
Laurenz Albe Received on Tue Apr 25 2006 - 05:10:36 CDT