ohhh ok now I understand - It does its does by magical muniplation of the datas wit a select wand that reads the wild cards that says only see what I tell you to see...
READ CONSISTENCY
If you run a simple query like "what time is it?" or "what was the weather like last week?", you proably won't have any issues: you're looking for a discreet value that that is either static, or not likely to be updated by anyone at the eaxact time your're looking for the answer. They are also very simple questions.
If you had a more complicated question and were even fighting other users to get your question answered, you could have a question like "how much of a discount is a customer due based on bulk sales made over the last ninety days in which no other discount was applied?" If you don't have an ODS or something else prebuilt for that question, you'll have to go and ask parts of that question to several different sections of your database until you get enough pieces to answer your question.
That's when you need a read-consistent view of the database. That basically means that your query has a START and a FINISH. So, you may need five tables to answer your query and you may even need to make multiple trips to those tables.
We've already walked thru a basic SELECT statement question sample. So we know that a SELECT does not magically produce data; Oracle has to build that data by reading data blocks.
Say the SQL engine identifies 2 data blocks that have to be read to get a part of your query answered (wow, at least it'd be fast, right?).
So, your process reads the first block and while it is, some bozo comes in right after you start reading that first block and changes data in the second block before you get to it.
What happens? Do you see the new data or not?
-------------------------------------------
To see, let's walk thru what happens when you UPDATE data (I'm going to skip over the DELETE/INSERT aspect of an UPDATE because I think that's fairly obvious and doesn't help us here).
I have a table(segment) with 10 blocks in it. I UPDATE a row in the second block. Because I'm initiating a DML statement, Oracle needs a place to keep track of what I'm doing and how things looked before I started. It doesn't want to use the data block I'm actually modifying to track all that I'm doing because then there wouldn't be any room left for data.
So, basically, Oracle uses a work area to hold data the way it looked before I made changes. And, whether I've explained it well or not, it's a good thing.
Whenever I run a statement, my statement asks what the current SCN is. That SCN sticks with my statement, not because I will push that number anywhere, but because it represents a "point-in-time" that my statement started.
Now (like for a select before I update) Oracle says that all of the data I see will be the way it looked when my statement started. Oracle calls this "Statement-level Read Consistency".
That should be enough to answer my question above. If I start reading datablocks, then Oracle will only return one of two things, the data as it was when my SELECT started, or an error saying it can't find all the data as it was when my statement started (that's the "snapshot too old" error).
What if I immediately rerun my select statement? Will I now see the new data in the second block? Could it cause problems if I did see the new data (sounds weird, huh?)
-----------------------------------------------
Whether you'd see the new data or not depends on your ISOLATION LEVEL. Oracle comes with 3 isolation levels:
READ COMMITTED - the default. It means, if it was commited before your statement/transaction started, you will see it
READ ONLY - not an ANSI92 standard
and
SERIALIZABLE - the fun one. This is when you really need the entire transactions to depend on a "point-in-time". You can SELECT all day long in this mode, but updates could be blocked if the data changed underneath you since your transaction started.
More about isolation levels in the future. For now, we're just using the default READ COMMITED.
OK, so we have our select statement that we've run once, we're using the default isolation level, we know the data changed DURING our last select, so will we see the new data when we rerun the SELECT? Yes, as long as it was commited before we reran our select.
See where this could cause problems in a large transaction where we run the same query over and over (for some reason)? More about these in, yes, a future post.
Back to what happens when I run an UPDATE statement.
Because I am (potentially) changing data in the data block, Oracle needs a backup of that data first in case I run into an error or simply decide to ROLLBACK because I changed my mind about making the change.
So, as soon as I run an update statement, people are going to start using my rollback segment to get the last known (committed) version of that block. Once I commit my transaction, all NEW statements will see my change, currently running queries will see the old version which is in the rollback segment.
----------------------------------------
Delayed block cleanout (DBC)
Someone always suffers for the sake of consistency and concurrency, don't they(assuming DBC occurs)? In this case (after a commited update) it's the first select run after the commit. In most OLTP environments, this is not a problem (unless you get ORA-1555 errors).
Ok, I have a data block. I initiate an UPDATE. Oracle updates the datablock, copies the original (last) version to a rollback segment and puts a pointer in the datablock to point to that rollback entry. Until I commit my UPDATE, other users looking for that datablock will find a little note saying:
"Hey, this block has been changed, go look in the rollback segments for the version you need (to reflect what was here when your query started)."
When I commit my UPDATE, then all the regular COMMIT actions kick off. As far as the data block and the rollback segment goes, when the rollback segment is notified that the commit is official (log writer gives it the thumbs up), it marks that transaction as committed. Then it goes about notifying all of the related datablocks that the COMMIT is official.
In our example, it was short and sweet, so there are probably no issues with this commit cleanout.
However, if I were updating thousands or millions of rows, Oracle may not make the notification to each data block in time. By "in time" I mean maybe someone ran a SELECT in the same nanosecond that the rollback segment was notified of the commit.
It is possible (happens a lot on some systems) that the query goes to the datablock only to find my note from above. So the query then jumps to the rollback segment hoping to find the version of data it needs only to be interupted.
"Can you do me a favor?" the rollback segment asks the query. "That transaction just committed and I haven't had time to tell the datablock yet. Could you let Oracle know so it nobody else has to make this detour?"
That is why you can see a query run slow right after a lot of updates have been run (doing some kinds of bulk loading) and that is also why you can see SELECT statements appear to generate REDO! That's one reason for DBC.
It is also possible that the datablock was flushed to disk by DBWR to make room for some other datablocks from the time I started my update to the time I commited it). Then it really just follows the above example, but I mention it because it demonstrates that you could have uncommitted data written in a datafile and it's nothing to worry about. Oracle protects you from uncommited data as we will see in our next post: recovering the database.
There are other reasons that could cause DBC, but Oracle keeps improving the commit mechanism. In older versions of Oracle, DBC was about the only way to do it.
In newer versions of Oracle, the term UNDO has replaced ROLLBACK in terms of tablespaces, etc. There have been a ton of improvements, but under the hoods, they are still auto managed rollback segments (largely). That's also why you can still see ORA-1555 even on newer versions of Oracle.
Lot's of stuff here, hope I made it a little easier.