Controlling Snapshot Refreshes 1

[uparna]

Hi ,
I have a set of snapshots that are refreshed automatically every 12 hours , replicating data from a remote database. The problem is that the link is sometimes unstable and hence occasionally goes down.
If the snapshot replication is going on at this time/or if it begins a refresh when the link is down ,
it results in chaios as the entire data in the snapshot is lost !
This happens as i am creating a snapshot with a complex selection formula necessitating a complete refresh ...... see the problem?
Is there anyway that i can make the snapshots refresh only of the link is up.. or is there anyway wherein i can make the original data remain in the snapshots (i dont think the second option is possible..)

Help! :-0
Regards,
S. Jayaram Uparna .

If the need arises,you are welcome to mail me at oracguru@yahoo.com .I would be glad to help you out.

 

[carp]

It seems to me that the root question ought to be "why does my link fall apart"?

However, assuming there's nothing to be done for the reliability of your network, then I think you're going to have to find an alternative to snapshots.

I would suggest writing a procedure that pulls the data you need over to your local database (if that will lower the amount of time you need the network to be functional) and do your calculations locally. Part of this procedure should be an exception handler that reschedules the procedure to run again if the link fails (You could set the interval for retrying at whatever value suits you - 1 minute, 5 minutes, 1 hour, etc.). Use dbms_job to schedule this procedure to run when you need it to, with an interval of 12 hours. In the exception handler, have it reschedule itself at the proper interval. So the logic would look something like this:

PROCEDURE my_snapshot IS
v_failure_retry_interval NUMBER := 5/1440; -- 5 minutes
BEGIN
-- GET ALL OF THE DATA FROM THE REMOTE DATABASE
-- ONCE YOU HAVE EVERYTHING TRANSLOADED, DO ANY CLEANUP
-- REQUIRED ON REMOTE DATABASE
-- NOW THAT EVERYTHING IS LOCAL, DO YOUR CALCULATIONS, ETC.
EXCEPTION
-- WHEN LINK FAILS, RESCHEDULE my_snapshot TO RUN AT
-- SYSDATE + v_failure_retry_interval;
END;

Of course, if you want to do your snapshots at noon and midnight and it takes an hour to successfully complete one of your refreshes (due to network failures), you might end up with refreshes occurring at midnight and 1:00pm. If this is a problem, then you will have to figure out some way to accomodate this, too!

 

[uparna]

Hi Carp ,
Thanks a lot for the reply.

What really pains me is that no one knows anyway by which i can control snapshot refreshes.... of course , i can drop the whole snapshot idea and use procedures as you suggest , but somehow , i feel that snapshots are a better way of handling data.
What do you think ?
I have also thought of the following , please tell me what you think of this :
I can create the snapshot such that it runs only once . Then , would it be possible for me to refresh the snapshot using the dbms_refresh utility , inside the procedure only if the link is up ? is there any way for me to do this ?
I know that we have to use the exception block and all that..... but then again , it would be just like a glorified procedure at its heart and not a materialized view !

whatddaya say ? Regards,
S. Jayaram Uparna .

If the need arises,you are welcome to mail me at oracguru@yahoo.com .

 

[carp]

Well, unfortunately I've never actually used snapshots, so we're into my pale grey zone now (I understand the theory but never got my hands dirty with them). Consequently, I don't know that I can be of much help to you on this.
My personal philosophy has always been that if I can duplicate the behavior without too much trouble, I prefer to build my own procedures - at least I understand how they're working and can control them the way I want to. This looks like one of those situations to me (unless, of course, you were hoping to use materialized views with the query rewrite feature!).

 

[uparna]

HI Carp,
I guess you are right again , as usual . I will sit today and mull over this topic before deciding . Anyway , i am intrigued by the last sentence in your post ".... to use materialized views with the query rewrite feature..." .. what is that ?
Please enlighten me ! Regards,
S. Jayaram Uparna .

If the need arises,you are welcome to mail me at oracguru@yahoo.com .

 

[yaffle]

The idea behind the query rewrite feature is that you define your MV to match a commonly executed query on a (set of) large tables; then instead of having to select from your snapshot explicitly by re-writing your query code, the RDBMS will detect the conditions of your query and determine that it'd be more efficient to use the MV instead of the base table. I'm not sure if it'll work properly across database links.
Switching to quote the manual mode:

[tt]Several steps must be followed to enable query rewrite:

1.Individual materialized views must have the ENABLE QUERY REWRITE clause.

2.The initialization parameter QUERY_REWRITE_ENABLED must be set to TRUE.

3.Cost-based optimization must be used either by setting the initialization parameter OPTIMIZER_MODE to "ALL_ROWS" or
"FIRST_ROWS", or by analyzing the tables and setting OPTIMIZER_MODE to "CHOOSE".

If step 1 has not been completed, a materialized view will never be eligible for query rewrite. ENABLE QUERY REWRITE can be
specified either when the materialized view is created, as illustrated below, or via the ALTER MATERIALIZED VIEW statement.

CREATE MATERIALIZED VIEW store_sales_mv
ENABLE QUERY REWRITE
AS
SELECT s.store_name,
SUM(dollar_sales) AS sum_dollar_sales
FROM store s, fact f
WHERE f.store_key = s.store_key
GROUP BY s.store_name;

A query gets rewritten only when a certain number of conditions are met:

1.Query rewrite must be enabled for the session.

2.A materialized view must be enabled for query rewrite.

3.The rewrite integrity level should allow the use of the materialized view. For example, if a materialized view is not fresh and query
rewrite integrity is set to ENFORCED, then the materialized view will not be used.

4.Either all or part of the results requested by the query must be obtainable from the precomputed result stored in the materialized view.

To determine this, the optimizer may depend on some of the data relationships declared by the user via constraints and dimensions. Such
data relationships include hierarchies, referential integrity, and uniqueness of key data, and so on.

The optimizer uses a number of different methods to rewrite a query. The first, most important step is to determine if all or part of the results
requested by the query can be obtained from the precomputed results stored in a materialized view.

The simplest case occurs when the result stored in a materialized view exactly matches what is requested by a query. The Oracle optimizer
makes this type of determination by comparing the SQL text of the query with the SQL text of the materialized view definition. This method
is most straightforward and also very limiting.

When the SQL text comparison test fails, the Oracle optimizer performs a series of generalized checks based on the joins, grouping,
aggregates, and column data fetched. This is accomplished by individually comparing various clauses (SELECT, FROM, WHERE, GROUP
BY) of a query with those of a materialized view.
[/tt]

 

[uparna]

Yaffle,
WOW ! you must really be a bombshell in Unix! Thanks a lot !

Regards,
S. Jayaram Uparna .

If the need arises,you are welcome to mail me at oracguru@yahoo.com .