Pro Oracle Database 11g Administration (67 page)

I almost never specify START WITH and NEXT as a refresh mechanism. I strongly prefer to use another scheduling utility such as cron. When using cron, it’s easy to create a log file that details how the job ran and whether there were any issues. Also, when using cron, it’s easy to have the log file e-mailed to a distribution list so the support DBAs are aware of any issues.

Regardless, it’s important to understand how START WITH and NEXT work, because sooner or later you’ll find yourself in an environment where DBAs or developers prefer to use the DBMS_JOB package for refreshes. When you’re troubleshooting refresh issues, you must understand how this refresh mechanism works.

The START WITH parameter specifies the date when you want the first refresh of an MV to occur. The NEXT parameter specifies a date expression that Oracle uses to calculate the interval between refreshes.

For example, this MV initially refreshes one minute in the future (sysdate+1/1440) and subsequently refreshes on a daily basis (sysdate+1):

create materialized view inv_mv

refresh

start with sysdate+1/1440

next sysdate+1

as

select

inv_id

,inv_desc

from inv;

You can view details of the scheduled job by querying USER_JOBS:

select

job

,schema_user

,to_char(last_date,'dd-mon-yyyy hh24:mi:ss') last_date

,to_char(next_date,'dd-mon-yyyy hh24:mi:ss') next_date

,interval

,broken

from user_jobs;

Here’s some sample output:

JOB SCHEMA_USE LAST_DATE NEXT_DATE INTERVAL B

------ ---------- -------------------- -------------------- --------------- -

50 INV 21-oct-2010 15:30:28 sysdate+1 N

You can also view job information in the USER_REFRESH view:

398

CHAPTER 15 ■ MATERIALIZED VIEWS

select

rowner

,rname

,job

,to_char(next_date,'dd-mon-yyyy hh24:mi:ss')

,interval

,broken

from user_refresh;

Here’s some sample output:

ROWNER RNAME JOB TO_CHAR(NEXT_DATE,'DD-MON- INTERVAL B

----------- ----------- ------ -------------------------- --------------- -

INV INV_MV 50 22-oct-2010 15:30:29 sysdate+1 N

When you drop an MV, the associated job is also removed. If you want to manually remove a job, use the REMOVE procedure of DBMS_JOB. This example removes job number 32, which was identified from the previous queries:

SQL> exec dbms_job.remove(32);

■
Note
You can't use START WITH or NEXT in conjunction with an MV that refreshes ON COMMIT.

Efficiently Performing a Complete Refresh

When an MV does a complete refresh, the default behavior is to use a DELETE statement to remove all records from the MV table. After the delete is finished, records are selected from the master table and inserted into the MV table. The delete and insert are done as one transaction; this means anybody selecting from the MV during the complete-refresh process sees the data as it existed before the DELETE

statement. Anybody accessing the MV immediately after the INSERT commits sees a fresh view of the data.

In some scenarios, you may want to modify this behavior. If a large amount of data is being refreshed, the DELETE statement can take a long time. You have the option of instructing Oracle to perform the removal of data as efficiently as possible via the ATOMIC_REFRESH parameter. When this parameter is set to FALSE, it allows Oracle to use a TRUNCATE statement instead of a DELETE when performing a complete refresh:

SQL> exec dbms_mview refresh('INV_MV',method=>'C',atomic_refresh=>false); TRUNCATE works faster than DELETE for large data sets because TRUNCATE doesn’t have the overhead of generating redo. The disadvantage of using the TRUNCATE statement is that a user selecting from the MV

may see zero rows while the refresh is taking place.

Handling the ORA-12034 Error

When you attempt to perform a fast refresh of an MV, you may sometimes get the ORA-12034 error. For example:

SQL> exec dbms_mview.refresh('PRODUCTLINEITEM','F');

399

CHAPTER 15 ■ MATERIALIZED VIEWS

The statement subsequently throws this error message:

BEGIN dbms_mview.refresh('PRODUCTLINEITEM','F'); END;

*

ORA-12034: materialized view log on "CDS_PROD_ES2_LIVE"."PRODUCTLINEITEM"

younger than last refresh

To resolve this error, try to completely refresh the MV:

SQL> exec dbms_mview.refresh('PRODUCTLINEITEM','C');

After the complete refresh has finished, you should be able to perform a fast refresh without receiving an error:

SQL> exec dbms_mview.refresh('PRODUCTLINEITEM','F');

The ORA-12034 error is thrown when Oracle determines that the MV log was created after the last refresh took place in the associated MV. In other words, the MV log is younger than the last refresh of MV. There are several possible causes:

• The MV log was dropped and re-created.

• The MV log was purged.

• The master table was reorganized.

• The master table was truncated.

• The previous refresh failed.

In this situation, Oracle knows that transactions may have been created between the last refresh time of the MV and when the MV log was created. In this scenario, you have to first perform a complete refresh before you can start using the fast-refresh mechanism.

Monitoring Materialized View Refreshes

This section contains some very handy examples of how to monitor MV refresh jobs. Examples include how to view the last refresh time, determine whether a job is currently executing, determine the progress of a refresh job, and checks to see whether MVs haven’t refreshed within the last day. Scripts like these are invaluable for troubleshooting and diagnosing refresh problems.

Viewing Materialized Views’ Last Refresh Times

When you’re troubleshooting issues with MVs, usually the first item to check is the LAST_REFRESH_DATE in DBA/ALL/USER_MVIEWS. Viewing this information allows you to determine whether the MVs are refreshing on schedule. Run this query as the owner of the MV to display the last refresh date: select

mview_name

,to_char(last_refresh_date,'dd-mon-yy hh24:mi:ss')

,refresh_mode

,refresh_method

from user_mviews

order by 2;

400

CHAPTER 15 ■ MATERIALIZED VIEWS

Here’s some sample output:

MVIEW_NAME TO_CHAR(LAST_REFRESH_DAT REFRES REFRESH_

------------------------------ ------------------------ ------ --------

GEM_COMPANY_MV 29-jul-10 06:18:58 DEMAND COMPLETE

TOP_REG_DAILY 29-jul-10 06:57:33 DEMAND FORCE

The LAST_REFRESH_DATE column of DBA/ALL/USER_MVIEWS shows the last date and time that an MV

successfully finished refreshing. The LAST_REFRESH_DATE is NULL if the MV has never successfully refreshed.

Determining Whether a Refresh Is in Progress

If you need to know what MVs are running, then use this query:

select

sid

,serial#

,currmvowner

,currmvname

from v$mvrefresh;

Here’s some sample output:

SID SERIAL# CURRMVOWNER CURRMVNAME

---------- ---------- -------------------- -------------------------------

1034 47872 REP_MV USERS_MV

Monitoring Real-Time Refresh Progress

If you deal with large MVs, the next query shows you the real-time progress of the refresh operation.

When you’re troubleshooting issues, this query can be very useful. Run the following as the user that has privileges on the internal SYS tables:

column "MVIEW BEING REFRESHED" format a25

column inserts format 9999999

column updates format 9999999

column deletes format 9999999

--

select

currmvowner_knstmvr || '.' || currmvname_knstmvr "MVIEW BEING REFRESHED", decode(reftype_knstmvr, 1, 'FAST', 2, 'COMPLETE', 'UNKNOWN') reftype, decode(groupstate_knstmvr, 1, 'SETUP', 2, 'INSTANTIATE',

3, 'WRAPUP', 'UNKNOWN' ) STATE,

total_inserts_knstmvr inserts,

total_updates_knstmvr updates,

total_deletes_knstmvr deletes

from x$knstmvr x

where type_knst = 6

and exists (select 1

from v$session s

where s.sid=x.sid_knst

and s.serial#=x.serial_knst);

401

CHAPTER 15 ■ MATERIALIZED VIEWS

When an MV first starts refreshing, you see this output:

MVIEW BEING REFRESHED REFTYPE STATE INSERTS UPDATES DELETES

------------------------- -------- ----------- -------- -------- --------

REP_MV.USERS_MV UNKNOWN SETUP 0 0 0

After a few seconds, the MV reaches the INSTANTIATE state:

REP_MV.USERS_MV FAST INSTANTIATE 0 0 0

As the MV refreshes, the INSERTS, UPDATES, and DELETES columns are updated appropriately: REP_MV.USERS_MV FAST INSTANTIATE 860 274 0

When the MV is almost finished refreshing, it reaches the WRAPUP state: REP_MV.USERS_MV FAST WRAPUP 5284 1518 0

After the MV has completed refreshing, the query returns no rows:

no rows selected

As you can imagine, this query can be quite useful for troubleshooting and diagnosing MV refresh issues.

Checking Whether MVs Are Refreshing Within a Time Period

When you’re dealing with MVs, it’s nice to have some sort of automated way of determining whether refreshes are occurring. Use the following shell script to detect which MVs haven’t refreshed within the last day and then send an e-mail if any are detected:

#!/bin/bash

# See Chapter 2 for details on using a utility

# like oraset to source OS variables

# Source oracle OS variables

. /var/opt/oracle/oraset $1

#