RE: Oracle Total Recall performance vs Materialized Views

1. Use recall to refresh any materialized view refreshes that fail or simply use recall to materialize the views. This costs some space and maintenance, but you don't pay for the recall over and over again as each query is made. If few queries are made, your least operational cost solution might be individual recall queries.
2. Move the whole shooting match to a frozen point in time clone of a physical dataguard database. Notice that you cancel recovery (for a while, in your case shortly before the redo through 00:00:00 is applied seems best), shut down the physical dataguard instance, clone the dataguard database, start recovery and roll THE CLONE (which has its own copy of the online redo logs) forward to exactly the time (or event) you want, shut down the clone, startup rename resetlogs THE CLONE, and then resume recovery on the untouched molested original physical dataguard database.

This allows you to use the bulk of the horsepower of your dataguard machinery to execute your point in time queries (which in fact will not need the point in time specifications at all.)

Since the CLONE is frozen in respect to the production database, the cautions against preparing aggregations in service of projecting results all evaporate. Since updates are not being done on the CLONE, the usual insert/update/delete overhead of excessive indexing goes away as well. Your mileage may vary. This approach has proved beneficial and workable since at least 1995. With modern cloning virtualization techniques such as Delphix, it probably works better than ever because the time to copy would be reduced. However it just simply works with or without fancy bells and whistles and it predates oracle dataguard, strictly needing only physical recovery to a point in time to work for it to work.

If someone thinks this requires re-instantiation of the dataguard database, the instructions have not been understood.

mwf

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Guillermo Alan Bort
Sent: Monday, July 29, 2013 12:00 PM
To: oracle-l-freelists
Subject: Oracle Total Recall performance vs Materialized Views

Hi,
  We are having a discussion about the following:

  we have a set of tables, from these tables we need to have a snapshot of the table at 00:00:00 exactly (all the tables at the same time). Right now we are using materialized views and we refresh them nightly. Of course this is a weak solution as if any refresh fails, we loose consistency.

  as I don't like square wheels and the architect can't seem to build a round one, I suggested using Oracle Total Recall, which based on the documentation does everything we need (including guaranteeing consistency and stuff like that).

  The architect claims that the performance on the queries with flashback (with FDA enabled) is unacceptable for the application. I am certain he didn't perform any testing, and I will do so as soon as I finish building the environment, but I'm sure there are some people out there who have already implemented total recall in production so my question to you is what is the performance overhead of FDA (I've read about the overhead during commit, and the restriction regarding DDLs; I'm wondering about the performance of queries).

Thank you very much,

Alan.-

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Mon Jul 29 2013 - 18:34:40 CEST