Re: Setting Dirty Reads or No Lock for SELECT

"broom" <broom_at_voicenet.com> wrote in message news:c948eb61.0105310721.3fd6f640_at_posting.google.com...
> "Howard J. Rogers" <howardjr_at_www.com> wrote in message
 news:<3b161b79_at_news.iprimus.com.au>...
> > Come on! To suggest that snapshot too old is an outcome of Oracle's
 refusal
> > to do 'dirty reads' is a bit like saying that we'd prefer it if people
> > karked it from communicable diseases because refrigerating the vaccines
 is a
> > tad inconvenient on the electricity bill.
>
> While I agree "disk space is cheap", it is meaningless.
>
> A question was posed.
>
> Rather than answer the question with:
>
> "You can't do that and this is why."
>
> It was answered with:
>
> "You don't need to do that, and you must be
> a moron (or incompetent) if you THINK you really
> need to do that."

I most certainly didn't call you a moron, but it *is* inappropriate to fire long-running queries off in the middle of a busy OLTP day, and not just for 1555 reasons. Your query will be doing amounts of I/O that will impact everybody else. So, the first place to start is to question whether there is a real need to mix the two types of things. That's not questioning your competence, but establishing the business requirement.

If the business requirement is real, then I told you the answer -and no, it wasn't a meaningless one. Disk space is the answer, and huge rollback segments. I even pointed you to some scripts that will fix the problem permanently -but they work on the principle of raising blocking transactions, and hence inflating your rollback segments (and disk requirements) on demand.

So in fact, you posed a question to which the answer was given "You oughtn't to do that, but if you need to, here's what you have to do".

>
> There is no such thing as infinite space or
> time. No matter how cheap disk is.
>
> I'd REALLY like to be in the middle of a load/build/update
> and query the uncommitted data currently being written. From a
> different session.

Well, I'd say that most SQL Loads would be performed, for obvious reasons, as Direct Path loads -and no matter what Oracle did with its rollback mechanism, you'd never be able to read stuff placed above the high water mark until the high water mark was moved (at the end of the load, speaking in general terms).

You can also always query the results of your updates before they are committed (not from a different session, I agree).

>I'd also like to have a long running query
> to grind against the whole base for a few hours NOT abort just
> because it was being updated.

Well, you don't have to have it so. If you need to do long-running selects on a database undergoing change, there are ways of handling that which will protect against 1555s.

>I do "marketing" data. This means
> 5% of it is CRAP anyway. If I get 95% valid, and did not have
> to copy the base or shut it down to others, it's a major win!
>
> Sure, I also like Oracle's usage of rbs for read consistancy,
> but I want to be able to choose. Just like I have the option
> whether to write logs. Make it safe and consistent by default,
> give me dirty reads as a "expert" option. Make it a "hidden"
> variable in init.ora, force me to bounce the instance to enable
> it, make it unsupported, I don't CARE, just let me do it knowing
> the data is in a state of flux!
>

I see your request, and it makes sense (and I was, you remember, arguing against 'snapshot too old' being the issue. There are always things you can do to miminise the possibility of snapshot too old, and there are one or two things you can do to guarantee that it never happens. All options, however, do require you to invest space in your rollback segments. So the real issue imho is not snapshot too old, but badly handled rollback segments). But Oracle long ago made a decision (call it a marketing one, if you like) that writers should never block readers, thus making their product behave quite differently to the others on the market, and the whole read consistency thing was therefore born -and engineered into the very heart of the product.

> And to compare this desire to allowing people to die to
> save a few bucks on electricity has GOT to be the height
> of hyperbole.

I think you might be taking things a tad too seriously there. The jest (for such it was intended to be) was to point out that that which is highly beneficial and convenient should not be blamed or criticised when, to obtain the benefit, you have to invest in some maintenance activity and (perhaps) some costs.

Until they re-engineer Oracle, the answer to your need is to invest in more rollback space.

Regards
HJR Received on Sun Jun 03 2001 - 04:40:58 CDT