Skip to Main Content
  • Questions

  • Havent commit but buffer cache data flush on disk

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, jing.

Asked: September 20, 2015 - 3:35 am UTC

Last updated: September 20, 2015 - 5:57 am UTC

Version: oracle 11g

Viewed 1000+ times

You Asked

Hi Tom,
I'm trying to understand the Undo and Redo, but there is one question confused me.
My test Oracle DB is oracle 11g
SQL> select * from v$version where rownum=1;

BANNER
------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

1.I created one test table named TJ,and I queried one row data
select dbms_rowid.rowid_relative_fno(rowid) REL_FNO,
dbms_rowid.rowid_block_number(rowid) BLOCKNO,
value from mars.tj where id=1;
REL_FNO BLOCKNO VALUE
---------- ---------- ------------------------------
5 1299 TJTJ
select object_id from dba_objects where object_name='TJ'
OBJECT_ID
----------
73524
2. I dump those data block
Start dump data blocks tsn: 6 file#:5 minblk 1299 maxblk 1299
Block dump from cache:
Dump of buffer cache at level 4 for tsn=6, rdba=20972819
BH (0x000007FF43FDE9C8) file#: 5 rdba: 0x01400513 (5/1299) class: 1 ba: 0x000007FF43CB4000
set: 9 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 30,28
dbwrid: 0 obj: 73524 objn: 73524 tsn: 6 afn: 5 hint: f
hash: [0x000007FF5E66BEE8,0x000007FF5E66BEE8] lru: [0x000007FF43FDEBE0,0x000007FF43FDE980]
ckptq: [NULL] fileq: [NULL] objq: [0x000007FF43FDE9A8,0x000007FF5AC4FD48]
st: XCURRENT md: NULL tch: 2
flags: only_sequential_access block_written_once redo_since_read
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
buffer tsn: 6 rdba: 0x01400513 (5/1299)
scn: 0x0000.0016de6f seq: 0x01 flg: 0x04 tail: 0xde6f0601
frmt: 0x02 chkval: 0xe0e6 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000012017000 to 0x0000000012019000
012017000 0000A206 01400513 0016DE6F 04010000 [......@.o.......]
012017010 0000E0E6 00000001 00011F34 0016DE6F [........4...o...]
012017020 00000000 00320002 01400510 00190002 [......2...@.....]
012017030 0000039D 00C0009C 000800F9 0000A000 [................]
012017040 0016D9E1 00190001 000002C4 00C0031F [................]
012017050 000C00E0 00008000 0014288E 00000000 [.........(......]
012017060 00000000 00020100 0016FFFF 1F6A1F50 [............P.j.]
012017070 00001F6A 1F500002 00001F7E 00000000 [j.....P.~.......]
012017080 00000000 00000000 00000000 00000000 [................]
Repeat 498 times
012018FB0 00000000 0202002C 540402C1 2C4A544A [....,......TJTJ,]
012018FC0 C1020201 4A540202 0202002C 740402C1 [......TJ,......t]
012018FD0 2C6A746A C1020200 61740802 696A676E [jtj,......tangji]
012018FE0 002C676E 03C10202 6A696C06 2C676E69 [ng,......lijing,]
012018FF0 C1020200 6A740602 74736574 DE6F0601 [......tjtest..o.]
Block header dump: 0x01400513
Object id on Block? Y
seg/obj: 0x11f34 csc: 0x00.16de6f itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1400510 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0002.019.0000039d 0x00c0009c.00f9.08 C-U- 0 scn 0x0000.0016d9e1
0x02 0x0001.019.000002c4 0x00c0031f.00e0.0c C--- 0 scn 0x0000.0014288e
bdba: 0x01400513
data_block_dump,data header at 0x12017064
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x12017064
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f50
avsp=0x1f6a
tosp=0x1f6a
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f50
0x14:pri[1] offs=0x1f7e
block_row_dump:
tab 0, row 0, @0x1f50
tl: 11 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 02
col 1: [ 4] 54 4a 54 4a
tab 0, row 1, @0x1f7e
tl: 13 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 03
col 1: [ 6] 6c 69 6a 69 6e 67
end_of_block_dump
End dump data blocks tsn: 6 file#: 5 minblk 1299 maxblk 1299
3. I updated this row but havent to commit
SQL>update tj set value=lower(value) where id=1；
And the transaction information as following:
select XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBASQN,UBAREC,STATUS from v$transaction;
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------------
7 22 714 3 4072 395 27 ACTIVE
select * from v$rollname where usn=7
USN NAME
---------- ------------------------------
7 __SYSSMU7_1101470402$
4. Then I dump the redo log and the data block again
===REDO LOG DUMP=====
REDO RECORD - Thread:1 RBA: 0x000023.00000862.0010 LEN: 0x01ec VLD: 0x0d
SCN: 0x0000.0016e1dc SUBSCN: 1 09/20/2015 07:09:31
CHANGE #1 TYP:0 CLS:1 AFN:5 DBA:0x01400513 OBJ:73524 SCN:0x0000.0016de6f SEQ:1 OP:11.19 ENC:0 RBL:0
KTB Redo
op: 0x01 ver: 0x01
compat bit: 4 (post-11) padding: 0
op: F xid: 0x0007.016.000002ca uba: 0x00c00fe8.018b.1b
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 0
ncol: 2 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01400513 hdba: 0x01400512
itli: 2 ispac: 0 maxfr: 4858
vect = 3
col 1: [ 4] 74 6a 74 6a
CHANGE #2 TYP:0 CLS:29 AFN:3 DBA:0x00c000e0 OBJ:4294967295 SCN:0x0000.0016e11e SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0016 sqn: 0x000002ca flg: 0x0012 siz: 188 fbi: 0
uba: 0x00c00fe8.018b.1b--UBA地址 pxid: 0x0000.000.00000000
CHANGE #3 TYP:0 CLS:30 AFN:3 DBA:0x00c00fe8 OBJ:4294967295 SCN:0x0000.0016e11d SEQ:7 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 188 spc: 4090 flg: 0x0012 seq: 0x018b rec: 0x1b
xid: 0x0007.016.000002ca
ktubl redo: slt: 22 rci: 0 opc: 11.1 [objn: 73524 objd: 73524 tsn: 6]
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00c00fe8.018b.14
prev ctl max cmt scn: 0x0000.0016da3b prev tx cmt scn: 0x0000.0016da9c
txn start scn: 0x0000.0016e0cf logon user: 85 prev brb: 12586467 prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 0
op: L itl: xid: 0x0001.019.000002c4 uba:0x00c0031f.00e0.0c
flg: C--- lkc: 0 scn: 0x0000.0014288e
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 2 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01400513 hdba: 0x01400512
itli: 2 ispac: 0 maxfr: 4858
vect = 3
col 1: [ 4] 54 4a 54 4a

===The data block dump====
*** 2015-09-20 07:14:43.356
Start dump data blocks tsn: 6 file#:5 minblk 1299 maxblk 1301
Block dump from cache:
Dump of buffer cache at level 4 for tsn=6, rdba=20972819
BH (0x000007FF41FE8098) file#: 5 rdba: 0x01400513 (5/1299) class: 1 ba: 0x000007FF41DB2000
set: 9 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 34,19
dbwrid: 0 obj: 73524 objn: 73524 tsn: 6 afn: 5 hint: f
hash: [0x000007FF43FDEA78,0x000007FF5E66BEE8] lru: [0x000007FF41FE82B0,0x000007FF41FE8050]
ckptq: [NULL] fileq: [NULL] objq: [0x000007FF43FDE9A8,0x000007FF5AC4FD48]
st: XCURRENT md: NULL tch: 3
flags: block_written_once redo_since_read
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x000007FF43FDE9C8) file#: 5 rdba: 0x01400513 (5/1299) class: 1 ba: 0x000007FF43CB4000
set: 9 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 34,19
dbwrid: 0 obj: 73524 objn: 73524 tsn: 6 afn: 5 hint: f
hash: [0x000007FF5E66BEE8,0x000007FF41FE8148] lru: [0x000007FF5E815090,0x000007FF4DFF2A20]
lru-flags: moved_to_tail
ckptq: [NULL] fileq: [NULL] objq: [NULL]
st: CR md: NULL tch: 2
cr: [scn: 0x0.16e1da],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.16e1da],[sfl: 0x0],[lc: 0x0.16de6f]
flags: only_sequential_access block_written_once redo_since_read
cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
buffer tsn: 6 rdba: 0x01400513 (5/1299)
scn: 0x0000.0016e1dc seq: 0x01 flg: 0x04 tail: 0xe1dc0601
frmt: 0x02 chkval: 0x47f1 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000012017000 to 0x0000000012019000
012017000 0000A206 01400513 0016E1DC 04010000 [......@.........]
012017010 000047F1 00000001 00011F34 0016DE6F [.G......4...o...]
012017020 00000000 00320002 01400510 00190002 [......2...@.....]
012017030 0000039D 00C0009C 000800F9 0000A000 [................]
012017040 0016D9E1 00160007 000002CA 00C00FE8 [................]
012017050 001B018B 00000001 00000000 00000000 [................]
012017060 00000000 00020100 0016FFFF 1F6A1F50 [............P.j.]
012017070 00001F6A 1F500002 00001F7E 00000000 [j.....P.~.......]
012017080 00000000 00000000 00000000 00000000 [................]
Repeat 498 times
012018FB0 00000000 0202022C 740402C1 2C6A746A [....,......tjtj,]
012018FC0 C1020201 4A540202 0202002C 740402C1 [......TJ,......t]
012018FD0 2C6A746A C1020200 61740802 696A676E [jtj,......tangji]
012018FE0 002C676E 03C10202 6A696C06 2C676E69 [ng,......lijing,]
012018FF0 C1020200 6A740602 74736574 E1DC0601 [......tjtest....]
Block header dump: 0x01400513
Object id on Block? Y
seg/obj: 0x11f34 csc: 0x00.16de6f itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1400510 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0002.019.0000039d 0x00c0009c.00f9.08 C-U- 0 scn 0x0000.0016d9e1
0x02 0x0007.016.000002ca 0x00c00fe8.018b.1b ---- 1 fsc 0x0000.00000000

My question is why I did not commit, but they data flush on the disk? If the other sessions want to query the same data how does oracle to do?
And even after I commit the transaction, and found the transaction layer still not change,meaning did not change the SCN number.
*** 2015-09-20 08:28:27.752
Start dump data blocks tsn: 6 file#:5 minblk 1299 maxblk 1299
Block dump from cache:
Dump of buffer cache at level 4 for tsn=6, rdba=20972819
BH (0x000007FF41FE8098) file#: 5 rdba: 0x01400513 (5/1299) class: 1 ba: 0x000007FF41DB2000
set: 9 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 33,19
dbwrid: 0 obj: 73524 objn: 73524 tsn: 6 afn: 5 hint: f
hash: [0x000007FF43FDEA78,0x000007FF5E66BEE8] lru: [0x000007FF41FE82B0,0x000007FF41FE8050]
obj-flags: object_ckpt_list
ckptq: [0x000007FF5E81CB78,0x000007FF5E81CB78] fileq: [0x000007FF5E8199F8,0x000007FF5E8199F8] objq: [0x000007FF5AC4FD58,0x000007FF5AC4FD58]
st: XCURRENT md: NULL tch: 5
flags: buffer_dirty block_written_once redo_since_read
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0x0.16e543] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x000007FF43FDE9C8) file#: 5 rdba: 0x01400513 (5/1299) class: 1 ba: 0x000007FF43CB4000
set: 9 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 33,19
dbwrid: 0 obj: 73524 objn: 73524 tsn: 6 afn: 5 hint: f
hash: [0x000007FF5E66BEE8,0x000007FF41FE8148] lru: [0x000007FF43FEF250,0x000007FF4DFF2A20]
lru-flags: moved_to_tail
ckptq: [NULL] fileq: [NULL] objq: [NULL]
st: CR md: NULL tch: 2
cr: [scn: 0x0.16e1da],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.16e1da],[sfl: 0x0],[lc: 0x0.16de6f]
flags: only_sequential_access block_written_once redo_since_read
cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
buffer tsn: 6 rdba: 0x01400513 (5/1299)
scn: 0x0000.0016e1dc seq: 0x01 flg: 0x04 tail: 0xe1dc0601
frmt: 0x02 chkval: 0x47f1 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000001CC57000 to 0x000000001CC59000
01CC57000 0000A206 01400513 0016E1DC 04010000 [......@.........]
01CC57010 000047F1 00000001 00011F34 0016DE6F [.G......4...o...]
01CC57020 00000000 00320002 01400510 00190002 [......2...@.....]
01CC57030 0000039D 00C0009C 000800F9 0000A000 [................]
01CC57040 0016D9E1 00160007 000002CA 00C00FE8 [................]
01CC57050 001B018B 00000001 00000000 00000000 [................]
01CC57060 00000000 00020100 0016FFFF 1F6A1F50 [............P.j.]
01CC57070 00001F6A 1F500002 00001F7E 00000000 [j.....P.~.......]
01CC57080 00000000 00000000 00000000 00000000 [................]
Repeat 498 times
01CC58FB0 00000000 0202022C 740402C1 2C6A746A [....,......tjtj,]
01CC58FC0 C1020201 4A540202 0202002C 740402C1 [......TJ,......t]
01CC58FD0 2C6A746A C1020200 61740802 696A676E [jtj,......tangji]
01CC58FE0 002C676E 03C10202 6A696C06 2C676E69 [ng,......lijing,]
01CC58FF0 C1020200 6A740602 74736574 E1DC0601 [......tjtest....]
Block header dump: 0x01400513
Object id on Block? Y
seg/obj: 0x11f34 csc: 0x00.16de6f itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1400510 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0002.019.0000039d 0x00c0009c.00f9.08 C-U- 0 scn 0x0000.0016d9e1
0x02 0x0007.016.000002ca 0x00c00fe8.018b.1b ---- 1 fsc 0x0000.00000000--
bdba: 0x01400513
data_block_dump,data header at 0x1cc57064
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x1cc57064
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f50
avsp=0x1f6a
tosp=0x1f6a
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f50
0x14:pri[1] offs=0x1f7e
block_row_dump:
tab 0, row 0, @0x1f50
tl: 11 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [ 4] 74 6a 74 6a
tab 0, row 1, @0x1f7e
tl: 13 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 03
col 1: [ 6] 6c 69 6a 69 6e 67
end_of_block_dump
End dump data blocks tsn: 6 file#: 5 minblk 1299 maxblk 1299

and Connor said...

It seems counter-intuitive, but whether the data is on disk or just in the buffer cache is almost of no relevance.

The only thing that really matters is can the data be *represented* in a consistent state right now, and also in the event of after a database crash. If both those are true, then the state of blocks on disk, the buffer cache, the undo area almost of no consequence.

For example, there's nothing to stop a dirty block being flushed from cache to disk (as long as there redo safely written away to preserve those changes, and the undo associated with those changes)

But there's a lot of other examples as well what would *seem* to be an inconsistency at various states during the life of a transaction with Oracle (and after the transaction ends as well). For example, delayed block cleanout, where a commit might not go back and mark blocks as being committed. That *sounds* like a potential for corruption, but its all just part of the kernel doing its best to minimise work where possible.

If we fast forward to the current versions of Oracle, where we now have things like "in-memory undo" and "private redo strands", there is a entire 'universe" of optimizations all of which almost "blur" the concept of what at a particular instant is on disk, what information is in the block, what is in memory *without* jeopardising the ability to paint a consistent picture of the data.

Some more examples / information here:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:811822300346967203

https://jonathanlewis.wordpress.com/2009/06/16/clean-it-up/

Hope this helps.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

jing tang, September 20, 2015 - 6:46 am UTC

Thank you for reponse. But one more question, if the data flush on the disk and the transaction havent been commit. At the point, I rollback it; Oracle have to use the undo record to reverse the data both on disk and cache, am I correct? And for consistent read, if the session B searched in the cache frist, found it was dirty and have to read the block from the disk, but the block have been flushed from my previous example, what's the oracle next to do? Using the undo directly?

To jing

Rajeshwaran Jeyabal, September 20, 2015 - 7:26 am UTC

But one more question, if the data flush on the disk and the transaction havent been commit.

Remember redo entires are flushed into Disk for
(i) every three seconds or
(ii) when atleast one MB of redo log buffer is full or
(iii) when the transaction commit explicitly

so in case of lengthy transaction, redo entires were flushed into disk before commit.

Oracle have to use the undo record to reverse the data both on disk and cache, am I correct?

Incase of conventional path operations Oracle will do Physical IO to bring back those blocks (dirty blocks) to cache any apply undo to reversal those changes.
incase of direct path operation, no undo just discard the newly allocated extends above the HWM

And for consistent read, if the session B searched in the cache frist, found it was dirty and have to read the block from the disk

Incase of consistent reads, check if the Transaction SCN is greater than block SCN (the requested block can be either in cache or disk)
a) if no active transaction on the block then make use of the block as such.
b) if an ongoing active transaction on the block, then make use of the undo to rollback that block to appropriate version.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

DBMS_ROWID

More on PL/SQL routine DBMS_ROWID here