Troubleshooting Materialized View (MV)

Troubleshooting Materialized View (MV) Problems in PeopleSoft Reporting.

Step1 - Notification: Email will notify PeopleSoft DBA team on Materialized View issue:

Sample Email to PeopleSoft DBA’s:

Materialized Views Refreshes Out of Date as of Thu Sep 10 03:22:03 EDT 2009 on psrptprd

SQL> select Name, owner, refresh_mode,

       2  to_char(last_refresh, 'dd-MON-yyyy hh:mi PM') last_refresh,

       3  to_char(Start_with,'dd-MON-yyyy hh:mi PM') next_refresh

       4  from all_snapshots

       5  where start_with < sysdate - 2/24;

NAME                           OWNER           REFRESH_MODE    LAST_REFRESH               NEXT_REFRESH                                                                                                  

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

MV_PS_PROJ_RESOURCE_PART       MVIEW_FIN       PERIODIC        09-SEP-2009 11:58 PM       10-SEP-2009 12:58 AM

           

Step2 – Determining status of MV: Check status of job to see if it is broken or failed. If failed column below is > 0 then go to step 3 otherwise if failed column is = 0 and job is not broken go to step 4.

                      

       Example: Determining status of MV_PS_PROJ_RESOURCE_PART

SELECT

SUBSTR(job,1,6) "Job",

SUBSTR(log_user,1,5) "User",

SUBSTR(schema_user,1,5) "Schema",

SUBSTR(To_CHAR(last_date, 'DD.MM.YYY HH24:MI'),1,16) "Last Date",

SUBSTR(To_CHAR(next_date, 'DD.MM.YYY HH24:MI'),1,16) "Next Date",

SUBSTR(broken,1,2) "B",

SUBSTR(failures,1,8) "Failed",

SUBSTR(what,1,100) "Refresh"

FROM DBA_JOBS

Where what like '%PS_PROJ_RESOURCE%';

If job is not broken (B=N) or has not failed (Failed=0).  No further action required by DBA except to check database performance and health - job is still running, go to step 4.

Job    User            Schema          Last Date       Next Date       B

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

Failed

--------

Refresh

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

3250   MVIEW           MVIEW           10.09.009 05:57 10.09.009 06:57 N

0

dbms_refresh.refresh('"MVIEW_FIN"."MV_PS_PROJ_RESOURCE_PART"');

 If job is broken (B=Y) or has failed (Failed>0).  Check database alert log to investigate underlying cause. An attempt to refresh the MV may provide an Oracle error. Go to step 3.

Job    User            Schema          Last Date       Next Date       B

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

Failed

--------

Refresh

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

3250   MVIEW           MVIEW           10.09.009 05:57 10.09.009 06:57 Y

1

dbms_refresh.refresh('"MVIEW_FIN"."MV_PS_PROJ_RESOURCE_PART"');

Step3 – MV failed: If job fails it will try multiple times before setting the job status to broken (N).  Depending on the state of the job error when you first encounter it you may be require to break the job manually if it hasn't been broken already and kill related session.  Ensure that you allow sessions to rollback completely otherwise a full refresh may hang.

Example 1: Check on job status for MV_PS_PROJ_RESOURCE_PART.

SQL> SELECT a.job, a.sid, what,

TO_CHAR(SYSDATE, 'mm/dd/yyyy hh24:mi:ss') now,

TO_CHAR(a.this_date, 'mm/dd/yyyy hh24:mi:ss') this

FROM   dba_jobs_running a, dba_jobs b

WHERE  a.job = b.job and a.job=3250; 

no rows selected  (no sessions to kill).

Example 2: Active session - kill session and monitor rollback to completion.

SELECT a.sid, a.serial#, a.username, b.xidusn, b.used_urec, b.used_ublk

FROM v$session a, v$transaction b

WHERE a.saddr = b.ses_addr and a.sid=71;

SQL> SELECT a.sid, a.serial#, a.username, b.xidusn, b.used_urec, b.used_ublk

  2  FROM v$session a, v$transaction b

  3  WHERE a.saddr = b.ses_addr and a.sid=71;

       SID    SERIAL# USERNAME                           XIDUSN  USED_UREC

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

 USED_UBLK

----------

        71       6720 MVIEW_FIN                             119  159383334

   4699036

The result of the about query should return “no rows selected” for a success rollback of the transaction for the session killed. 

Next test whether a fast refresh is possible.  Test fast refresh of MV_PS_PROJ_RESOURCE_PART to see if you encounter invalid ROWID error.  If you encounter "ORA-12008: error in materialized view refresh path" then a full refresh is required.

Example: Test for fast fresh of MV_PS_PROJ_RESOURCE_PART.

SQL> conn mview_fin/xxxxxxx@psrptprd

Connected.

SQL> EXECUTE DBMS_MVIEW.REFRESH('MV_PS_PROJ_RESOURCE_PART','F');

BEGIN DBMS_MVIEW.REFRESH('MV_PS_PROJ_RESOURCE_PART','F'); END;

*

ERROR at line 1:

ORA-12008: error in materialized view refresh path

ORA-01410: invalid ROWID

ORA-02063: preceding line from FINANCE

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2545

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2751

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2720

ORA-06512: at line 1

      

Finally check alert log for ora-600 errors that indicate a shared memory errors and flush the shared pool three times to clear this.  Run full refresh and notify Greg Noble; Tripurari Sinha; Gareth Hale; Manish Verma.  Full Refresh usually takes 5 and 1/2 hours for MV_PS_PROJ_RESOURCE_PART.

Example:

SQL> conn mview_fin/xxxxxxx@psrptprd

Connected.

SQL> EXECUTE DBMS_MVIEW.REFRESH('MV_PS_PROJ_RESOURCE_PART','COMPLETE',atomic_refresh=>FALSE);

Ensure that statistics is gathered for the complete mview refresh.

Example:

exec dbms_stats.gather_table_stats('MVIEW_FIN', 'MV_PS_PROJ_RESOURCE_PART', CASCADE=>TRUE);

Step 4 – No major action required, MV is running and has not failed or broken: Check status of MV job again to ensure job has not broken and is stilling running.  Also, check the general health of master  and reporting databases (by checking the alert log, MV log and undo tablepaces and database performance (focusing on the shared_pool) to ensure that there are no general issues with the databases such lack of database memory or lack of space on key tablespaces or issues with the MV log. 

Example: MV_PS_PROJ_RESOURCE_PART job has not failed or broken.

SELECT

SUBSTR(job,1,6) "Job",

SUBSTR(log_user,1,5) "User",

SUBSTR(schema_user,1,5) "Schema",

SUBSTR(To_CHAR(last_date, 'DD.MM.YYY HH24:MI'),1,16) "Last Date",

SUBSTR(To_CHAR(next_date, 'DD.MM.YYY HH24:MI'),1,16) "Next Date",

SUBSTR(broken,1,2) "B",

SUBSTR(failures,1,8) "Failed",

SUBSTR(what,1,100) "Refresh"

FROM DBA_JOBS

Where what like '%PS_PROJ_RESOURCE%';

Job    User            Schema          Last Date       Next Date       B

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

Failed

--------

Refresh

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

3250   MVIEW           MVIEW           25.09.009 02:24 25.09.009 03:24 N

0

dbms_refresh.refresh('"MVIEW_FIN"."MV_PS_PROJ_RESOURCE_PART"');

Example: MV_PS_PROJ_RESOURCE_PART job is still running.

oracle >SQL> SELECT a.job, a.sid, what,

TO_CHAR(SYSDATE, 'mm/dd/yyyy hh24:mi:ss') now,

TO_CHAR(a.this_date, 'mm/dd/yyyy hh24:mi:ss') this

FROM   dba_jobs_running a, dba_jobs b

WHERE  a.job = b.job and a.job=3250;

       JOB        SID

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

WHAT

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

NOW                 THIS

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

      3250          123

dbms_refresh.refresh('"MVIEW_FIN"."MV_PS_CA_ACCTG_LN_PC_PART"');

09/25/2009 06:35:52 09/25/2009 03:24:17

 

Appendix

For a complete refresh (using dbms_mview.refresh) sys.snap$ and sys.mlog$ are updated to reflect the time of the refresh. The materialized base view is deleted and all rows selected from the master table are inserted into the snapshot base table and then sys.slog$ is updated to reflect the time of the refresh.

For a fast refresh, the steps are sys.snap$ and sys.mlog$ are updated to reflect the time of the refresh.  Rows in the materialized base view are deleted and all rows selected from the master table are inserted into the snapshot base table and then sys.slog$ is updated to reflect the time of the refresh.  Rows that are not needed anymore for a refresh by any materialized view are deleted from the materialized view log (e.g. SYSFS.MLOG$_PS_PROJ_RESOURCE).

A long running refresh will have a PC_REPORTS_USER session in the F90xxx or H90xxx databases and a long running SQL operation for that session normally gives an indication of how long the refresh will take to complete.  For a full refresh there may be an automatic fast refresh after the full refresh that gets the materialized view up to the current time.

See below for examples of fast and complete refresh for MV_PS_PROJ_RESOURCE_PART.  Consider using atomic_refresh=FALSE option for complete refresh – the default for Oracle 11g is atomic_refresh=TRUE (DELETE instead of a TRUNCATE).

Example:

EXECUTE DBMS_MVIEW.REFRESH('MV_PS_PROJ_RESOURCE_PART','F'); -- fast refresh

EXECUTE DBMS_MVIEW.REFRESH('MV_PS_PROJ_RESOURCE_PART','COMPLETE',atomic_refresh=>FALSE); -- complete refresh by truncating MV.

Use DBMS_JOB and ALTER MATERIALIZED VIEW statements to manage and schedule MV jobs.

Example: MV_PS_PROJ_RESOURCE_PART is scheduled to run every hour.

exec dbms_job.broken(3250, FALSE);

ALTER MATERIALIZED VIEW MVIEW_FIN.MV_PS_PROJ_RESOURCE_PART

REFRESH 

NEXT sysdate + 1/24;

Ensure that statistics is gathered for complete refresh.

Example:

exec dbms_stats.gather_table_stats('MVIEW_FIN', 'MV_PS_PROJ_RESOURCE_PART', CASCADE=>TRUE);

Thanks Thomas. 

How to Create a New Materialized View.

In F8XPRD

 

create materialized view log on sysfs.PS_XXXXX tablespace

with rowid, sequence excluding new values;

grant select on sysfs.PS_XXXXX to  pc_reports_user;

grant select on sysfs.MLOG$_PS_XXXXX to pc_reports_user;

in PSRPTPRD as Mview_F8X

DROP MATERIALIZED VIEW MVIEW_F8X.MV_PS_XXXXX;

CREATE MATERIALIZED VIEW MVIEW_F8X.MV_PS_XXXXX

TABLESPACE MVIEW_F8X

NOCACHE

NOLOGGING

COMPRESS

NOPARALLEL

BUILD IMMEDIATE

USING INDEX

REFRESH FAST

START WITH sysdate

NEXT SYSDATE + 1/24

WITH ROWID

AS

SELECT * FROM "SYSFS"."PS_XXXX"@F8X "PS_XXXXX";

CREATE INDEX MView_F8X.MV_PS_XXXXX_IX1 ON MView_F8X.MV_PS_XXXXX

(COL1, COL2, COL3)

LOGGING

TABLESPACE PSINDEX

NOPARALLEL;

ALTER TABLE MVIEW_F8X.MV_PS_XXXXX ADD (

  CONSTRAINT PS_XXXXX_PK

 UNIQUE (COL1, COL2, COL3)

    DEFERRABLE INITIALLY IMMEDIATE);

 

GRANT SELECT ON MVIEW_F8X.MV_PS_XXXXX TO SYSFS;

DROP PUBLIC SYNONYM PS_XXXXX;

CREATE PUBLIC SYNONYM PS_XXXXX FOR MVIEW_F8X.MV_PS_XXXXX;

Materialized View Monitoring

The check is simply to ensure that the Materialized view has been refreshed within 2 hours of its scheduled time by checking all_snapshots.

select Name, owner, refresh_mode,

to_char(last_refresh, 'dd-MON-yyyy hh:mi PM') last_refresh,

to_char(Start_with,'dd-MON-yyyy hh:mi PM') next_refresh

from all_snapshots

where start_with < sysdate - 2/24;