Re: Oracle Total Recall performance vs Materialized Views

From: Kevin Jernigan <kevin.jernigan_at_oracle.com>
Date: Mon, 29 Jul 2013 19:07:04 -0400
Message-ID: <51F6F598.5000405_at_oracle.com>

It won't necessarily be slower to run the flashback query: Total Recall (now simply called Flashback Data Archive) keeps history rows in a separate table that is automatically partitioned by time. Before 11.2.0.4, the history tables will also automatically be compressed using Advanced Row Compression (fka OLTP Table Compression). This means that flashback queries will automatically benefit from partition pruning and compression if available. In addition, it is possible / advisable to create indexes on the history tables to further improve performance of flashback queries.

So, depending on the complexity of the flashback queries, it's quite possible that they could be equal to or faster than queries on materialized views.

-KJ

-- 
Kevin Jernigan
Senior Director Product Management
Advanced Compression, Hybrid Columnar
Compression (HCC), Database File System
(DBFS), SecureFiles, Database Smart Flash
Cache, Total Recall, Database Resource
Manager (DBRM), Direct NFS Client (dNFS),
Continuous Query Notification (CQN),
Index Organized Tables (IOT), Information
Lifecycle Management (ILM)
(650) 607-0392 (o)
(415) 710-8828 (m)

On 7/29/13 6:47 PM, Guillermo Alan Bort wrote:


> Hi guys,

>

>     Thanks for all your replies :-)

>

>      We discussed using DataGuard (this is how it's done in other locations)

> but there were conflicting requirements here (other schemas that need to

> use the same database and write on their own tables) and the  need to add

> indexes as needed for new reports (query tuning is pretty much non-existent

> here, and way above my paygrade to discuss). We also analyzed the

> posibility of replicating the schema with GoldenGate from the online schema

> and have the replicats down until midnight, tart them up  let them apply

> all the trails until midnight and then shut them down again. This was

> rejected due to limited space in the shared filesystem (this is an Exadata,

> so we are very restricted in some areas, sadly space is one of them). And

> certain misgivings most people have about Golden Gate.

>

>      Delphix actually did turn up in my  search, we are too far in the

> project and way to close to major deadlines to even consider bringin in a

> new tool (probably spend a good deal of time getting the "money" stuff

> done) and we have no test environment. (Yes, I am very well aware of the

> risks we are taking anyway, but management thinks they are acceptable).

> Delphix, however does seem like a tool worth remembering for the next

> project...

>

>      We ended up trying  to patch the system and recreated some

> materializaed views and grouped the  refresh jobs to make them "mostly

> consistent"...

>

>      I also looked into Oracle Total Recall, but what was brought up to

> reject it was that  some queries have very strict performance  requirements

> which can be guaranteed by a materialized view but  that reading the

> flashback data archive was too much overhead. I was wondering if somebody

> benchmarked this. Run a query against a materialized view and then run a

> query against a table using flashback to sysdate-1. I know it will be

> slower to run the flashback query, but my question is how much slower.

>

>

>     Thanks,

> Cheers

>

> Alan.-

>

>

> On Mon, Jul 29, 2013 at 6:16 PM, Mark W. Farnham <mwf_at_rsiz.com> wrote:

>

>> yep. That also works.****

>>

>> ** **

>>

>> The trade-off is slightly different in that you do need to let the log

>> application lag and additional aggregations are not possible.****

>>

>> ** **

>>

>> Kyle also mentioned a way to do this directly with Delphix, which should

>> work fine whether or not you’re on dataguard. Doing it on the dataguard

>> server has the very interesting ability to utilize what is usually quite a

>> bit of excess CPU and i/o horsepower over what is required to keep logs up

>> to date.****

>>

>> ** **

>>

>> mwf****

>>

>> ** **

>>

>> *From:* Adam Musch [mailto:ahmusch_at_gmail.com]

>> *Sent:* Monday, July 29, 2013 2:29 PM

>> *To:* mwf_at_rsiz.com

>> *Cc:* cicciuxdba_at_gmail.com; oracle-l-freelists

>> *Subject:* Re: Oracle Total Recall performance vs Materialized Views****

>>

>> ** **

>>

>> Mark:

>>

>> One could do the following, instead:****

>>

>> At 11:50 on the standby database, stop automatic log application.****

>>

>> At 12:10 or thereabouts, recover the database to exactly midnight.****

>>

>> Open the database read only.  Log transport should still run, but not

>> managed recovery.****

>>

>> When done using the standby database as a reporting environment, resume

>> the recovery process.  If all the logs are present and one has turned off

>> the checksumming on the standby, we've seen redo log application on a

>> standby run at about an 8:1 time ratio.****

>>

>> That would eliminate the need to clone any other databases or to perform

>> resetlogs; it's just another standby frozen in time at midnight, and if

>> you're not using active data guard, it's not like the standby could be

>> queried in any event.****

>>

>> ** **

>>

>> On Mon, Jul 29, 2013 at 11:34 AM, Mark W. Farnham <mwf_at_rsiz.com> wrote:***

>> *

>>

>> Two things seem likely to be useful:

>>

>> 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.****

>>

>> mwf****

>>

>> ** **

>>

>>

>> --

>> Adam Musch

>> ahmusch_at_gmail.com ****

>>

> --

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

>

>


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

Received on Tue Jul 30 2013 - 01:07:04 CEST