Oracle Core # Transactions and Consistency

I am reading Jonathan Lewis’s latest book Oracle Core recently.

Something I learned from chapter Transactions and Consistency:

1. Why index scan is more likely to run into ORA-01555 than full table scan?
From the below statements, we can know one of the reason is: Oracle has to get a consistent read on the index leaf block first, then it has to get a consistent read on the row itself. For full table scan, it only need to get a consistent read on the row. That possibly reduces half CR.

Note When Oracle accesses a row through an indexed access path, it may be able to use the RowCR (row consistent read) mechanism when it gets to the table block. This means that Oracle has gone through the complex processing I’ve just described to get the index leaf block into a read-consistent state and has then been able to check that the row itself has not changed since the reconstructed commit SCN for the index entry.

There is a another reason widely known is that full table scan can use multiblock read(db_file_multiblock_read_count) and parallelism.

2. How “Delayed Block Cleanout” get the commit SCN if the transction slot has been overwritten?
From the below statements, the answer is : Oracle gets the previous commint SCN from the transaction control(transaction table control section in Undo Segment Header). It’s called upper bound commit.

it goes to read the correct the undo segment header block (consistent get - examination), to check the state of the
transaction. But in this case the wrap# of the transaction is higher than expected (the slot has been
reused about 50 times since the original transaction ran).

Because the transaction slot has been overwritten, Oracle can infer that the transaction has
committed, which (in this case) is all it cares about. But it needs to put something in as the commit SCN
on the ITL, so what value should it use?

It could simply use the SCN it finds in the overwritten transaction slot, but—in much the same way
that Oracle picks the oldest (least recently committed) entry when it has to reuse an ITL entry from a
data block—Oracle also reuses the oldest (least recently committed) transaction table slot in the undo
segment when it needs to start a new transaction, so a better approximation would simply be to use the
oldest commit SCN it can find in the transaction table. In fact, Oracle can do just a little bit better than
that, because each time Oracle reuses a transaction table slot, it copies the previous commit SCN from
that slot into the transaction control mentioned at the start of this chapter. So, for the upper bound
commit SCN, Oracle identifies the relevant undo segment, picks the SCN from its transaction control,
and copies that into the ITL.

...

It need only check that its original transaction committed before the final select statement began. It doesn’t need to know exactly when the transaction committed, so again it can use the SCN from the transaction control as a good-enough approximation to
the commit SCN.

3. Why consistent read(CR) is expensive? How expensive it is?
When the upper bound commit SCN is not good enough for a CR (for example, the SCN is larger than the start SCN of a long query). Oracle don’t know whether we should use that block or not. Oracle has to do Transaction Table Rollback(Transaction Table Consistent Read) many many times until it get the good-enough/exact commit SCN, or it run into ORA-01555(the required undo block has been overwritten).

Transaction Table Consistent Read

The steps involved in creating a read-consistent copy of the transaction table are as follows:
1. I clone the undo segment header block in memory—that’s the action that
increments the statistic transaction tables consistent read rollbacks.

2. I use the uba from the transaction control to identify the first undo record of
the transaction that last updated the transaction control. This was, as we have
seen, the oldest transaction slot available in the transaction table at that
moment.
3. The undo record tells me which transaction table slot its undo should be
applied to (remember the slt: 0xNN entry on the record), and the commit SCN
for that slot, so I can apply the undo to my clone—that’s the action that
increments the statistic transaction tables consistent reads - undo
records applied. At the same time I read back the uba and scn values that need
to be written to the transaction control and apply them.

4. At this point I have taken the transaction table and transaction control one
step back into the past. It’s possible that the commit SCN I’ve recovered is
“good enough” (i.e., the first time I’ve seen an SCN lower than the exact value
I’m interested in) for an upper bound commit. It’s possible that this step
actually took slot 13 (the one I was interested in) back to exactly the right wrap#
and gave me the exact commit SCN. (In my case I said that I was starting with a
wrap# of 0x6a61 and needed to get back to wrap# 0x6a5f, so I’m not going to be
that lucky that soon; I’ll have to get through wrap# 0x6a60 first.)

5. If I haven’t found a low enough SCN yet, I have at least managed to construct
an older version of the transaction control, so I go back to step 2 and repeat
until I reach a suitable value or run out of undo records and crash out with
Oracle error “ORA-01555 snapshot too old” because I haven’t kept enough
history in the undo segment to look that far back into the past.

One example provided by Jonathan Lewis, Oracle applied 1395 undo records to create a single readconsistent
copy of the undo segment header. Bear in mind, all these operations are “single block read”.

...the same count on a statistic called transaction tables consistent reads - undo records applied. We have done a lot
of work to find a suitable commit SCN; we have applied 1,395 undo records to create a single readconsistent
copy of the undo segment header so that we can see what the transaction table looked like at
the moment of the original commit.

Now I understand why there are lots of single block read on undo segments when FULL TABLE SCAN a busy table with large db_file_multiblock_read_count.

4.The last piece of this chapter is Read consistency for LOBs.

Read consistency for LOBs is completely different. Essentially, Oracle doesn’t update LOBs; it simply
keeps old copies of LOBs for a while before overwriting them, but uses the standard read-consistency
mechanism on the LOBINDEX to allow it to point to the correct old copy. There is a special “snapshot
too old” error for LOBs (ORA-22924) that appears when the index has become read consistent but the
LOB value has been overwritten.