Monitoring Refreshes=======================The following queries can be used to monitor mview refreshes.This is part of metelink note 258252.1 MATERIALIZED VIEW REFRESH: Locking, Performance, MonitoringWhen an mview was last successfully refreshed———————————————–The following query will indicate when an mview was last successfullyrefreshed and what type of refresh was done.column last_refresh_type format a18column owner format a7column mview_name format a12select owner, mview_name, last_refresh_type, last_refresh_datefrom dba_mviews;OWNER MVIEW_NAME LAST_REFRESH_TYPE LAST_REFRESH_DATE——- ———— —————— ——————SCOTT CREF_DEPT COMPLETE 24-JAN-03 22:17:25SCOTT MYDEPT FAST 23-JAN-03 15:44:24SCOTT MY_EMP FAST 27-JAN-03 21:40:13SCOTT UPSNP_DEPT COMPLETE 24-JAN-03 14:35:37Status of a Refresh Group Using the Job Queue———————————————The following query shows all the jobs scheduled in the job queue to performrefreshes. It shows when the last refresh was run, the total amount of timespent by the system running the job since the instance started, if the job isbroken or has failures, and when the next refresh will run.column broken format a6alter session set nls_date_format=’DD-MON-YY hh24:MI:SS’;select job, last_date last_refresh,next_date next_refresh, total_time,broken, failures, whatfrom dba_jobswhere what like ‘%dbms_refresh%’;JOB LAST_REFRESH NEXT_REFRESH TOTAL_TIME BROKEN FAILURES—– —————— —————— ———- —— ———-WHAT————————————————————————1 05-FEB-03 16:37:57 05-FEB-03 16:47:57 2 N 0dbms_refresh.refresh(’”SYS”.”REFRESHG1″‘);Note: TOTAL_TIME – you can determine how long (in seconds) a refresh job takesto run by querying total_time for the job before and after the job runs,and calculating the difference. This is helpful in determining whatinterval to use to stager refreshes.Refreshes Currently Running——————————–The following query shows all refresh jobs that are currently running, whenthey started, and if any have failed.select r.job, r.this_date, r.failuresfrom all_jobs_running r, all_jobs jwhere j.job = r.joband j.what like ‘%dbms_refresh%’;JOB THIS_DATE FAILURES———- —————— ———-1 05-FEB-03 16:37:57 0If an mview belongs to a refresh group, you can also use the ALL_REFRESHand ALL_REFRESH_CHILDREN to obtain information about the mview refreshselect r.rowner, r.rname, r.job, c.name,r.next_date next_refresh, r.brokenfrom all_refresh r, all_refresh_children cwhere r.job = c.job;ROWNER RNAME JOB NAME NEXT_REFRESH BROKEN———- ————— —– ———- —————— ——SYS REFRESHG1 1 MYDEPT 05-FEB-03 16:47:57 N



Komentáře