-- Demo case: Researching private redo and LRBA / Space pressure in database cache -- Date: 28-12-2014 -- References: http://www.apress.com/9781430239543 -- http://jonathanlewis.wordpress.com/oracle-core/oc-6-writing-and-recovery/#comment-77903 -- Demo run on Oracle version: 11.2.0.3.6 create table BUFFCACHETEST (num number); begin for i in 1..10 loop insert into BUFFCACHETEST values(i); end loop; commit; end; / exec DBMS_STATS.GATHER_TABLE_STATS('TEST', 'BUFFCACHETEST'); select NUM, DBMS_ROWID.ROWID_OBJECT(ROWID) as OBJECT_ID, DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) as RELATIVE_FNO, DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) as BLOCK_NUM, DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) AS ROW_NUM from BUFFCACHETEST; NUM OBJECT_ID RELATIVE_FNO BLOCK_NUM ROW_NUM ---------- ---------- ------------ ---------- ---------- 1 75992 4 2454 0 2 75992 4 2454 1 3 75992 4 2454 2 4 75992 4 2454 3 5 75992 4 2454 4 6 75992 4 2454 5 7 75992 4 2454 6 8 75992 4 2454 7 9 75992 4 2454 8 10 75992 4 2454 9 select GROUP#, SEQUENCE#, STATUS from V$LOG; GROUP# SEQUENCE# STATUS ---------- ---------- ---------------- 1 106 CURRENT 2 104 INACTIVE 3 105 INACTIVE select HLADDR, decode(STATE,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9, 'memory',10,'mwrite',11,'donated', 12,'protected', 13,'securefile', 14,'siop',15,'recckpt', 16, 'flashfree', 17, 'flashcur', 18, 'flashna') as STATE, PRV_HASH, NXT_HASH, BA, DBARFIL, DBABLK from X$BH where OBJ = 75992 and DBABLK = 2454; HLADDR STATE PRV_HASH NXT_HASH BA DBARFIL DBABLK ---------------- ---------- ---------------- ---------------- ---------------- ---------- ---------- 0000000083C7AA30 xcur 0000000083C7B300 0000000083C7B300 0000000072C7C000 4 2454 oradebug setmypid oradebug dump buffers 4; CHAIN: 42050 LOC: 0x83c7b300 HEAD: [0x72fdc938,0x72fdc938] BH (0x72fdc888) file#: 4 rdba: 0x01000996 (4/2454) class: 1 ba: 0x72c7c000 set: 7 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 35,28 dbwrid: 0 obj: 75992 objn: 75992 tsn: 4 afn: 4 hint: f hash: [0x83c7b300,0x83c7b300] lru: [0x72fdcaa0,0x72fdc840] lru-flags: debug_dump obj-flags: object_ckpt_list ckptq: [0x72beca38,0x75bdd598] fileq: [0x83e255c0,0x75bdd5a8] objq: [0x72fdcac8,0x800d9e00] objaq: [0x72fdcad8,0x800d9de0] st: XCURRENT md: NULL fpin: 'ktspbwh2: ktspfmdb' tch: 3 flags: buffer_dirty redo_since_read LRBA: [0x6a.f7c.0] LSCN: [0x0.1503e0] HSCN: [0x0.1503e1] HSUB: [1] select to_number('6a','xx') as log_file_seq, to_number('f7c','xxx') as log_file_block from dual; LOG_FILE_SEQ LOG_FILE_BLOCK ------------ -------------- 106 3964 startup force; alter system switch logfile; alter system switch logfile; alter system switch logfile; alter system switch logfile; select GROUP#, SEQUENCE#, STATUS from V$LOG; GROUP# SEQUENCE# STATUS ---------- ---------- ---------------- 1 109 INACTIVE 2 110 INACTIVE 3 111 CURRENT select * from BUFFCACHETEST; select HLADDR, decode(STATE,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9, 'memory',10,'mwrite',11,'donated', 12,'protected', 13,'securefile', 14,'siop',15,'recckpt', 16, 'flashfree', 17, 'flashcur', 18, 'flashna') as STATE, PRV_HASH, NXT_HASH, BA, DBARFIL, DBABLK from X$BH where OBJ = 75992 and DBABLK = 2454; HLADDR STATE PRV_HASH NXT_HASH BA DBARFIL DBABLK ---------------- ---------- ---------------- ---------------- ---------------- ---------- ---------- 0000000083C7AA30 xcur 0000000083C7B300 0000000083C7B300 0000000073AF8000 4 2454 oradebug setmypid oradebug dump buffers 4; CHAIN: 42050 LOC: 0x83c7b300 HEAD: [0x73bf42d8,0x73bf42d8] BH (0x73bf4228) file#: 4 rdba: 0x01000996 (4/2454) class: 1 ba: 0x73af8000 set: 9 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 5,28 dbwrid: 0 obj: 75992 objn: 75992 tsn: 4 afn: 4 hint: f hash: [0x83c7b300,0x83c7b300] lru: [0x73bf4440,0x73bf7ae0] lru-flags: debug_dump ckptq: [NULL] fileq: [NULL] objq: [0x73bf4468,0x8008b500] objaq: [0x73bf4478,0x8008b4f0] st: XCURRENT md: NULL fpin: 'kdswh01: kdstgr' tch: 1 flags: only_sequential_access LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535] select indx, to_number(ktifprpc,'XXXXXXXXXXXXXXXX') - to_number(ktifprpb,'XXXXXXXXXXXXXXXX') redo_usage from x$ktifp; INDX REDO_USAGE ---------- ---------- 0 0 update BUFFCACHETEST set NUM=11 where NUM=1; select indx, to_number(ktifprpc,'XXXXXXXXXXXXXXXX') - to_number(ktifprpb,'XXXXXXXXXXXXXXXX') redo_usage from x$ktifp; INDX REDO_USAGE ---------- ---------- 0 1316 update BUFFCACHETEST set NUM=12 where NUM=2; select indx, to_number(ktifprpc,'XXXXXXXXXXXXXXXX') - to_number(ktifprpb,'XXXXXXXXXXXXXXXX') redo_usage from x$ktifp; INDX REDO_USAGE ---------- ---------- 0 2612 update BUFFCACHETEST set NUM=12 where NUM=3; select indx, to_number(ktifprpc,'XXXXXXXXXXXXXXXX') - to_number(ktifprpb,'XXXXXXXXXXXXXXXX') redo_usage from x$ktifp; INDX REDO_USAGE ---------- ---------- 0 3908 select HLADDR, decode(STATE,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9, 'memory',10,'mwrite',11,'donated', 12,'protected', 13,'securefile', 14,'siop',15,'recckpt', 16, 'flashfree', 17, 'flashcur', 18, 'flashna') as STATE, PRV_HASH, NXT_HASH, BA, DBARFIL, DBABLK from X$BH where OBJ = 75992 and DBABLK = 2454; HLADDR STATE PRV_HASH NXT_HASH BA DBARFIL DBABLK ---------------- ---------- ---------------- ---------------- ---------------- ---------- ---------- 0000000083C7AA30 xcur 0000000083C7B300 00000000737E3D98 000000007315C000 4 2454 0000000083C7AA30 cr 00000000733E4E38 00000000737E3EC8 0000000073540000 4 2454 0000000083C7AA30 cr 00000000737E3D98 0000000073BF42D8 0000000073542000 4 2454 0000000083C7AA30 cr 00000000737E3EC8 0000000083C7B300 0000000073AF8000 4 2454 oradebug setmypid oradebug dump buffers 4; CHAIN: 42050 LOC: 0x83c7b300 HEAD: [0x733e4e38,0x73bf42d8] BH (0x733e4d88) file#: 4 rdba: 0x01000996 (4/2454) class: 1 ba: 0x7315c000 set: 9 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 32,28 dbwrid: 0 obj: 75992 objn: 75992 tsn: 4 afn: 4 hint: f hash: [0x737e3d98,0x83c7b300] lru: [0x733eeb30,0x733e4d40] lru-flags: debug_dump obj-flags: object_ckpt_list ckptq: [0x83e5b230,0x733ed748] fileq: [0x83e5b2b0,0x83e5b2b0] objq: [0x8008b510,0x8008b510] objaq: [0x73bf4478,0x8008b4f0] st: XCURRENT md: NULL fpin: 'kdswh01: kdstgr' tch: 2 flags: buffer_dirty redo_since_read LRBA: [0x6f.10c.0] LSCN: [0x0.15561e] HSCN: [0x0.15561e] HSUB: [1] BH (0x737e3ce8) file#: 4 rdba: 0x01000996 (4/2454) class: 1 ba: 0x73540000 set: 9 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 32,28 dbwrid: 0 obj: 75992 objn: 75992 tsn: 4 afn: 4 hint: f hash: [0x737e3ec8,0x733e4e38] lru: [0x83e25328,0x733eec60] lru-flags: debug_dump moved_to_tail ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL] st: CR md: NULL fpin: 'kdswh01: kdstgr' tch: 2 cr: [scn: 0x0.155613],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.155613],[sfl: 0x0],[lc: 0x0.1503e1] flags: BH (0x737e3e18) file#: 4 rdba: 0x01000996 (4/2454) class: 1 ba: 0x73542000 set: 9 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 32,28 dbwrid: 0 obj: 75992 objn: 75992 tsn: 4 afn: 4 hint: f hash: [0x73bf42d8,0x737e3d98] lru: [0x733ebce0,0x73bf4310] lru-flags: debug_dump moved_to_tail ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL] st: CR md: NULL fpin: 'kdswh01: kdstgr' tch: 2 cr: [scn: 0x0.1555c3],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.1555c3],[sfl: 0x0],[lc: 0x0.1503e1] flags: BH (0x73bf4228) file#: 4 rdba: 0x01000996 (4/2454) class: 1 ba: 0x73af8000 set: 9 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 32,28 dbwrid: 0 obj: 75992 objn: 75992 tsn: 4 afn: 4 hint: f hash: [0x83c7b300,0x737e3ec8] lru: [0x737e3f00,0x74be0600] lru-flags: debug_dump moved_to_tail ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL] st: CR md: NULL fpin: 'kdswh01: kdstgr' tch: 2 cr: [scn: 0x0.1555b4],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.1555b4],[sfl: 0x0],[lc: 0x0.1503e1] select to_number('6f','xx') as log_file_seq, to_number('10c','xxx') as log_file_block from dual; LOG_FILE_SEQ LOG_FILE_BLOCK ------------ -------------- 111 268 commit; select indx, to_number(ktifprpc,'XXXXXXXXXXXXXXXX') - to_number(ktifprpb,'XXXXXXXXXXXXXXXX') redo_usage from x$ktifp; INDX REDO_USAGE ---------- ---------- 0 0 oradebug setmypid oradebug dump buffers 4; CHAIN: 42050 LOC: 0x83c7b300 HEAD: [0x733e4e38,0x73bf42d8] BH (0x733e4d88) file#: 4 rdba: 0x01000996 (4/2454) class: 1 ba: 0x7315c000 set: 9 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 159,19 dbwrid: 0 obj: 75992 objn: 75992 tsn: 4 afn: 4 hint: f hash: [0x737e3d98,0x83c7b300] lru: [0x733eeb30,0x733e4d40] lru-flags: debug_dump ckptq: [NULL] fileq: [NULL] objq: [0x73bf4468,0x8008b500] objaq: [0x73bf4478,0x8008b4f0] st: XCURRENT md: NULL fpin: 'kdswh01: kdstgr' tch: 2 flags: block_written_once redo_since_read LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1] BH (0x737e3ce8) file#: 4 rdba: 0x01000996 (4/2454) class: 1 ba: 0x73540000 set: 9 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 159,19 dbwrid: 0 obj: 75992 objn: 75992 tsn: 4 afn: 4 hint: f hash: [0x737e3ec8,0x733e4e38] lru: [0x767f3ac0,0x733eec60] lru-flags: debug_dump moved_to_tail ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL] st: CR md: NULL fpin: 'kdswh01: kdstgr' tch: 2 cr: [scn: 0x0.155613],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.155613],[sfl: 0x0],[lc: 0x0.1503e1] flags: BH (0x737e3e18) file#: 4 rdba: 0x01000996 (4/2454) class: 1 ba: 0x73542000 set: 9 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 159,19 dbwrid: 0 obj: 75992 objn: 75992 tsn: 4 afn: 4 hint: f hash: [0x73bf42d8,0x737e3d98] lru: [0x733ebce0,0x73bf4310] lru-flags: debug_dump moved_to_tail ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL] st: CR md: NULL fpin: 'kdswh01: kdstgr' tch: 2 cr: [scn: 0x0.1555c3],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.1555c3],[sfl: 0x0],[lc: 0x0.1503e1] flags: BH (0x73bf4228) file#: 4 rdba: 0x01000996 (4/2454) class: 1 ba: 0x73af8000 set: 9 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 159,19 dbwrid: 0 obj: 75992 objn: 75992 tsn: 4 afn: 4 hint: f hash: [0x83c7b300,0x737e3ec8] lru: [0x737e3f00,0x74be0600] lru-flags: debug_dump moved_to_tail ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL] st: CR md: NULL fpin: 'kdswh01: kdstgr' tch: 2 cr: [scn: 0x0.1555b4],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.1555b4],[sfl: 0x0],[lc: 0x0.1503e1] flags: only_sequential_access