Re: New feature request: FlashBack Query

On 2/17/07, Joshua D. Drake <jd(at)commandprompt(dot)com> wrote:
>
> My understanding is that the main difference is that rollbacks are
> inexpensive for us, but expensive for Oracle. Talk to an Oracle DBA
> about their Rollback logs :0.

Yes, I have seen cases where undo segments are thrashed. Generally it works
well, and I agree it likely much cheaper on PostgreSQL as you would expect
fewer scattered reads because the old version is inline with the rest of the
data.

But if I recall undo segments are cached in Oracle, usually where I see
problems is where the DBA is completely incompetent and has undersized the
buffer cache. Oracle does direct reads (afaik) -- so undersizing the buffer
cache can be brutal. A very common mistake with Oracle was undersizing the
buffer cache and oversizing the shared pool (when the shared plans are
stored), and with 9i and later they tried to have the management tools
suggest the ideal values or have it automatically managed by the database.
Probably a step in the right direction, but I know they still have bumps to
iron it. :)

However, they don't have vacuum, we do.
>

Right, and I think that is more or less because Oracle doesn't need it.
Vacuum's main purpose (correct me if I am wrong) is to recover/mark rows
that are no longer used, and Oracle essentially reuses the space
immediately.

Obviously with Oracle if you bloat out a table and delete a ton of rows then
you have to rebuild the table, but that is more or less the same problem
that PostgreSQL has and where vacuum full comes into play.

The only benefit with the Oracle model is that you can achieve flashback,
which is a very rarely used feature in my book. The disadvantages is likely
overhead to perform the "rollback" and possibly more scattered reads. I can
say that I have used it, and it has come in handy, but hardly worth it. The
benefit with the PostgreSQL model is the likelihood of the old rows being
inline with the rest of the table data, potentially reducing scattered
reads. The disadvantage is vacuuming, it seems to be often overlooked --
possibly solved by defaulting autovacuum to on? (seems to be the way Oracle
is heading, defaulting statistics collection to on and other management
features).