Re: Mviews Refresh Timings

From: ddf <oratune_at_msn.com>
Date: Mon, 9 Mar 2009 06:35:36 -0700 (PDT)
Message-ID: <c99763d1-8ba1-46ef-9aea-efcdd567cc49_at_e3g2000vbe.googlegroups.com>

On Mar 9, 8:18 am, "lyx..._at_gmail.com" <lyx..._at_gmail.com> wrote:
> Hi David -
>
> This job refresh 30+ Mviews which are based on Tables. Database
> 10.2.0.3 and this is Apps 11.5.10.2 instance.
> Oracle seed mean this program/concurrent job comes with this release.
> I can not edit this program and store timings
> in one table for individual refresh.
>
> DBA_MVIEW or sys.SNAP_REFRESH$$ has column when refresh was completed
> (last time) but not when it was started.

DBA_JOBS will report when the refresh job started, but that won't tell you when each individual refresh task began.

I realize this is an Oracle Applications job, but if you really want to troubleshoot this you may need to disable the current job and write your own to refresh the 30 materialized views so you can time each refresh and store results into a job refresh history table. You could keep this instrumented job running until you learn why the refresh varies from 10 to 15 hours on occasion then disable it and enable the original, Oracle-scheduled job.

It's a thought.

David Fitzjarrell Received on Mon Mar 09 2009 - 08:35:36 CDT