Thanks for the question, Stefan.
Asked: February 18, 2016 - 3:22 pm UTC
Last updated: February 19, 2016 - 12:49 pm UTC
Version: every release
Viewed 1000+ times
You Asked
Dear Ask Tom team,
Transaction isolation by multiversion concurrency control and read consistency is one of Oracle's most fundamental features, and I know how much this is to be appreciated. What I find difficult to understand, however, is why Oracle doesn't enable me to deliberately do without this feature in special cases.
In our OLTP system, a DBMS_SCHEDULER job is responsible for archiving business incidents which have reached a final, immutable state. The relevant data reside in read-only partitions of a number of hierarchically organized tables. The archiver's central task is to execute a single big SELECT statement which collects all these data, performs calls to PL/SQL decode functions, and delivers valid XML.
Obviously the archiver doesn't need read consistency at all. It could happily live without protection from phantom reads, nonrepeatable reads, and even dirty reads---just because our application is designed in a way that we know that neither of these could ever occur.
Regarding the archiver alone, read consistency is nothing else than an unnecessary burden, requiring the DBMS to keep more undo log than needed. *Much* more in our example: For the most part archiving is just a snap, but for some huge and very rare business incidents, it takes several days and finally runs into ORA-01555, snapshot too old. We didn’t ask the DBMS to keep this amount of undo log, let alone to interrupt our archiver.
I imagine that permitting to reduce the isolation level for a specific session would not interfere with the overall architecture of the DBMS, and that implementing such a feature should be feasible with reasonable effort. If necessary, Oracle could even limit this to read-only sessions, e.g. by throwing an exception as soon as a session tried to modify any data.
What do you think? Thank you for your insights.
Kind regards,
Stefan
and Chris said...
I fail to see the benefit of this.
"Obviously the archiver doesn't need read consistency at all."
It's not obvious to me that you don't need this.
Take dirty reads: this enables you to read data that was later rolled back! Are you really saying you want to report information that was never fully persisted to the database?
You're right this is a feature that *could* be implemented. But I suspect most cases where people think they need to disable read-consistency they've failed to consider some aspect which means that, actually they really, really do need it. People would misuse this.
A query that takes several days to run sounds sub-optimal to me. Rather than trying to fundamentally change how Oracle works it's better to spend effort attempting to reduce the runtime of this. Your users will be happier too! If you want help with this please post a new question with the relevant details (tables, execution plans, etc.).
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment