Re: Mviews Refresh Timings

On Mar 8, 5:06 pm, "lyx..._at_gmail.com" <lyx..._at_gmail.com> wrote:
> Hi All,
>
> We have production box on 10g R2 on Linux system. One Mview refresh
> job which refreshes bunch
> of mviews takes normally 10 hours. It is 3 TB database. But sometime
> this jobs take 15 hours or more.
>

Define 'bunch'. Obviously this is more than one but it would help to know how many materialized views were refreshed by this one job. It would also help to know what these materialized views are based upon (tables/views/other materialized views) and what maintenance, if any, the base objects are subject to.

> I agree that this should look from performance tuning prospective but
> at this time I am looking for idea to track the speed of this refresh.
>

Tracking the 'speed of this refresh' is fine, but it doesn't give you the granularity you'll need to see which materialized views in this refresh group are causing the 'problem'. Of course only knowing '10g R2' doesn't help much, as that covers 10.2.0.1 through 10.2.0.4; an exact release number would be beneficial.

> The jobs which refresh is one Oracle Seeded program which I can not
> edited.

What do you mean by that statement?

> Is there a way to know how much time it spends in refreshing
> one particular Mview.
>

Is there any way you can separate these materialized views into individual jobs? That would be one way. Another would be to record entries into V$SESSION_LONGOPS using
DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS then query the V $SESSION_LONGOPS view during the period when the refresh job runs.

> So, that I can trace down that this Mview is taking much time than un-
> usual. I would also note down some timing baselines.
>

See above.

> I could not find this straight forward view for this.

There isn't one, afaik.

> View got timings
> when it was last refreshed.
>

According to the refresh job which executes to refresh the data. I expect all of the materialized views in this particular job get the same refresh date/time.

> Your reply would be appreciated.  I am looking for which mview is
> being refreshed curently and when it started and completed.
>
> Regards,

David Fitzjarrell Received on Mon Mar 09 2009 - 07:57:22 CDT