Materialized view query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Materialized view query

Hybrid View

  1. #1
    Join Date
    Nov 2009
    Posts
    2

    Materialized view query

    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,

  2. #2
    Join Date
    Apr 2007
    Location
    USA
    Posts
    110
    Quote Originally Posted by desi_bob02 View Post
    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,
    if you know the refresh job stats at 3:00 then you can do this and see the time it finished.
    SQL> alter session set nls_date_format ='DD-MM-YYYY hh24:mi:ss';

    Session altered.

    SQL> select last_refresh_date,mview_name from dba/user_mviews;

    I think you are better off creating the indexes on your base table rather than your MV if you had only one choice...because your MV refresh is a select against your table just as any user query...
    I have MVs without any index on them and they are great.
    Looking for the greatest evil in the world? Look in the mirror.

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by Tuma View Post
    I have MVs without any index on them and they are great.
    It all depends on the size of the MV - we do have small MVs doing great with no indexes but some large MVs are certainly benefiting of having a good indexing strategy in place.

    Answering posters question, in this particular scenario indexes have to be created as part of MV "refresh" process.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width