RE: Snapshot (Materialized View) Maladies
- From: "Hostetter, Jay M" <JHostetter@xxxxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 4 Jun 2004 15:50:09 -0400
We have had similar experiences. Our mviews are pretty basic too.
Failures are usually caused by taking a node or database down, or by a
network issue.
- We have monitoring scripts, just as you mentioned.
- We recreate our mviews off-hours whenever there are changes to the
source table. We haven't really had the chance to get into the
replication of DDL. There have been times when I've dropped an mview
and the underlying table sticks around, even though the mview is gone.
The workaround is to create the mview with a prexisting table option.
- On some occassions, I have to set job_queue_processes=0 and actually
go out and kill the job process at the OS level. Then set
job_queue_processes back to a number > 0.
Jay
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Michael Fontana
Sent: Friday, June 04, 2004 3:37 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Snapshot (Materialized View) Maladies
I am getting prepared to write up an enhancement request to Oracle for
improved administration, configuration and problem detection tools for
Materialized View (aka snapshot) processing. Having implemented and
managed these little devils for close to eight years now, I am
knowledgeable enough to know that they are not nearly as problem free as
the manuals imply. The reports of issues, bugs and subsequent fixes
surrounding these objects are legion.
Our main issue with them is that there is nothing reported in the alert
log and no easy detection method for problem determination when they are
NOT WORKING! You must check DBA_JOBS and numerous other tables via
scripts for exceptions (such as - job running too long, job hasn't run
at all, job not running at frequency or time specified, etc). Worst of
all, sometimes even these queries fail to detect that the snapshot has
not refreshed, and ain't ever gonna. In extreme cases, we've had to
rebuild several million row mviews at the recommendation of Oracle,
simply because we had a network outage or some other glitch.
Any comments or similar experiences?
I know actually writing an enhancement request that gets implemented is
probably a topic for an entire separate listserv, but any words of
assistance along those lines would be appreciated as well.
Michael Fontana
Sr. DBA
NTT/Verio
**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use
of the individual or entity to which they are addressed and may contain
information that is privileged, proprietary and confidential. If you are not
the intended recipient, you may not use, copy or disclose to anyone the message
or any information contained in the message. If you have received this
communication in error, please notify the sender and delete this e-mail
message. The contents do not represent the opinion of D&E except to the extent
that it relates to their official business.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
