OraFAQ Forum: Replication » Materialized view query

Home » Server Options » Replication » Materialized view query (oracle 10.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Materialized view query [message #454346]

Wed, 05 May 2010 02:41

desi_bob02
Messages: 8
Registered: November 2009
Location: india

Junior Member

Hi all,

how to check the time taken for materialized view to execute at one time.
My materialized view is a complete refresh which automatically executes at 3:00.

a) I have queried dba_jobs and dba_mview_refresh_times, and matched the last_date and last_refresh to find the total time. How can I be know that JOB 80 corresponds to my view MVIEW_UNION in a much easier way?

SQL> select job,last_date,total_time from dba_jobs order by last_date;
JOB LAST_DATE TOTAL_TIME
---------- ----------------- ----------
80 05/05/10 03:00:02 5255

SQL>select name,last_refresh from dba_mview_refresh_times where owner
NAME LAST_REFRESH
------------------------------ -----------------
MVIEW_UNION 05/05/10 03:00:02

b) Also is total_time the time it took the query to execute today at 3:00, i.e it started at 3:00 and ended at around 4:45(3+5255 msec) or is it the overall time it has taken since the day it was scheduled in dba_jobs( i.e 1 week back)

c) Also my source table dosent contain any indexes and I have created indexes on materialized view, i want to know if the complete refresh will recreate the indxes.

thanks in advance,
regards,

Report message to a moderator

Re: Materialized view query [message #454421 is a reply to message #454346]

Wed, 05 May 2010 08:22

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

Quote:

How can I be know that JOB 80 corresponds to my view MVIEW_UNION in a much easier way?

Check

select job,last_date,total_time,what from dba_jobs order by last_date;

Quote:

Also is total_time the time it took the query to execute today at 3:00, i.e it started at 3:00 and ended at around 4:45(3+5255 msec) or is it the overall time it has taken since the day it was scheduled in dba_jobs( i.e 1 week back)

http://download-uk.oracle.com/docs/cd/B14117_01/server.101/b10755/statviews_1078.htm#i1580430

Quote:

c) Also my source table dosent contain any indexes and I have created indexes on materialized view, i want to know if the complete refresh will recreate the indxes.

During complete refresh index will go to invalid
Babu

Report message to a moderator

Re: Materialized view query [message #454423 is a reply to message #454421]

Wed, 05 May 2010 08:37

desi_bob02
Messages: 8
Registered: November 2009
Location: india

Junior Member

thanks a ton for replying babu,

can you kindly clarify these points Smile

TOTAL_TIME -
Total wall clock time spent by the system on this job, in seconds

can you kindly explain if this is the time spend on the last job or total time spend on this job since it was scheduled
.

Also about "During complete refresh index will go to invalid "
can you kindly tell as to how to find out if the index is invalid. I had created an index "testindex1" on the MV it is still showing from toad, also if you can kindly point to some documentaiton of indexes becoming invalid for complete refresh.

thanks again Smile

Report message to a moderator

Re: Materialized view query [message #454425 is a reply to message #454423]

Wed, 05 May 2010 08:40

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

can you kindly explain if this is the time spend on the last job or total time spend on this job since it was scheduled

Since it was created.

Regards
Michel

[Updated on: Wed, 05 May 2010 08:41]

Report message to a moderator

Re: Materialized view query [message #454427 is a reply to message #454425]

Wed, 05 May 2010 08:52

desi_bob02
Messages: 8
Registered: November 2009
Location: india

Junior Member

thank you michel, so how to find the time taken by the MV,
one method would be to execute the
dbms_refresh.refresh('MV name') and finding out the time taken during 1 refresh and then dividing it by total number of refreshes till date, but this might not be accurate, is there any other way.

also about the indexes becoming invalid, i have checked and after complete refresh when i quererd dba_indexes status , the index is valid, actually have to make theses configuraiton on the producation so want to be sure.
thanks again Smile

Report message to a moderator

Re: Materialized view query [message #454430 is a reply to message #454427]

Wed, 05 May 2010 09:12

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

>>so how to find the time taken by the MV,

Okay Try

spool time_refresh_mv.log

select to_char(sysdate,'DD-MM-YYYY HH24:MI:SS') from dual; 
exec dbms_refresh.refresh('MV name')
select to_char(sysdate,'DD-MM-YYYY HH24:MI:SS') from dual; 

spool off

Babu

[Updated on: Wed, 05 May 2010 09:12]

Report message to a moderator

Re: Materialized view query [message #454884 is a reply to message #454346]

Sat, 08 May 2010 01:37

desi_bob02
Messages: 8
Registered: November 2009
Location: india

Junior Member

thank you Babu and Michel for your replies,
but its surprising that time taken for last refresh by MV is not automatically stored anywhere in oracle Mad

Report message to a moderator

Re: Materialized view query [message #454886 is a reply to message #454884]

Sat, 08 May 2010 01:51

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

There are so many things Oracle can store, each one different for each customer, Oracle can't store all of them, if you want some statistics to be store then the only thing you can and you have to do is to open an "Enhancement Request".
If one does not ask for what he want, he can't blame Oracle (now asking does not mean you will have it but you can then be angry against Oracle).

Regards
Michel

[Updated on: Sat, 08 May 2010 02:22]

Report message to a moderator

Re: Materialized view query [message #454888 is a reply to message #454886]

Sat, 08 May 2010 02:04

desi_bob02
Messages: 8
Registered: November 2009
Location: india

Junior Member

Hi Michel,

Oracle Database's robustness in providing all the data that i have required in my short carrer as a DBA, made me think as to why this information coudn't be stored automatically Smile

. This is the first time i have found something that is not already automated in Oracle. Following the below steps for hunderds of MV's that i have would not be feasible.

As you rightly said will raise an enhancement request with Oracle.

Thanks again for replying,
regards,
bob

Report message to a moderator

Re: Materialized view query [message #454891 is a reply to message #454888]

Sat, 08 May 2010 02:24

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

made me think as to why this information coudn't be stored automatically

It could (the proof is that total time is) but it has not be chosen.

Quote:

This is the first time i have found something that is not already automated in Oracle.]

You're lucky, I found many others but far less than in any other rdbms (hopefully for me that works on Oracle Smile

).

Regards
Michel

[Edit: typos]

[Updated on: Tue, 23 April 2019 03:43]

Report message to a moderator

Previous Topic:	ORA-00997
Next Topic:	Replication in 10G without DB links.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 06 14:01:29 CST 2025