[20140324]延迟块提交测试.txt
做一个延迟块提交测试。主要出现一般是修改许多记录，修改后当前块已经不在buffer，在commit时，如果这时要读取在磁盘的数据块代
价太大，oracle使用一种延迟机制，在下次select时读取它，提交修改信息，这个时候产生redo信息。
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
create table t (id number ,name varchar2(20));
insert into t values (1,'a');
commit ;
SCOTT@test> select rowid,t.* from t ;
ROWID ID NAME
------------------ ---------- --------------------
AABFkXAAEAAAACnAAA 1 a
SCOTT@test> @lookup_rowid AABFkXAAEAAAACnAAA
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
284951 4 167 0 4,167 alter system dump datafile 4 block 167 ;
--关闭数据库，再测试。
SCOTT@test> @bh1 4 167
no rows selected
--没有读取不在buffer中。
SCOTT@test> select rowid,t.* from t where rowid='AABFkXAAEAAAACnAAA';
ROWID ID NAME
------------------ ---------- --------------------
AABFkXAAEAAAACnAAA 1 a
SCOTT@test> @bh1 4 167
DBARFIL DBABLK CLASS STATE STATE1 CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ CR_UBA_REC CR_XID_USN CR_XID_SLT CR_XID_SQN CR_CLS_BAS CR_CLS_WRP LRBA_SEQ LRBA_BNO HSCN_BAS HSCN_WRP HSUB_SCN OBJECT_NAME
------- ----------- ----- ----- ------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------
4 167 1 1 xcur 0 0 0 0 0 0 0 0 0 0 0 0 0 4294967295 65535 65535 T
--已经加载块4，167在buffer。
--打开session1，修改记录不提交：
update t set name='b' where rowid='AABFkXAAEAAAACnAAA';
SCOTT@test> @bh1 4 167
DBARFIL DBABLK CLASS STATE STATE1 CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ CR_UBA_REC CR_XID_USN CR_XID_SLT CR_XID_SQN CR_CLS_BAS CR_CLS_WRP LRBA_SEQ LRBA_BNO HSCN_BAS HSCN_WRP HSUB_SCN OBJECT_NAME
------- ----------- ----- ----- ------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------
4 167 1 1 xcur 0 0 0 0 0 0 0 0 0 0 0 928 41708 3268411537 0 1 T
--可以发现LRBA_SEQ变成928，对应是redo seq。
SCOTT@test> select * from v$log ;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------
1 1 928 52428800 512 1 NO CURRENT 3268407908 2014-03-24 09:52:47 2.8147E+14
2 1 926 52428800 512 1 YES INACTIVE 3268353896 2014-03-20 22:13:22 3268388097 2014-03-21 11:36:05
3 1 927 52428800 512 1 YES INACTIVE 3268388097 2014-03-21 11:36:05 3268407908 2014-03-24 09:52:47
SCOTT@test> alter system flush buffer_cache;
System altered.
SCOTT@test> @bh1 4 167
DBARFIL DBABLK CLASS STATE STATE1 CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ CR_UBA_REC CR_XID_USN CR_XID_SLT CR_XID_SQN CR_CLS_BAS CR_CLS_WRP LRBA_SEQ LRBA_BNO HSCN_BAS HSCN_WRP HSUB_SCN OBJECT_NAME
------- ----------- ----- ----- ------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------
4 167 1 0 free 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 T
--state1=free ,LRBA_SEQ=0,表示已经写信息到磁盘中。
--回到session1，执行提交命令:
SCOTT@test> commit ;
Commit complete.
SCOTT@test> @bh1 4 167
DBARFIL DBABLK CLASS STATE STATE1 CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ CR_UBA_REC CR_XID_USN CR_XID_SLT CR_XID_SQN CR_CLS_BAS CR_CLS_WRP LRBA_SEQ LRBA_BNO HSCN_BAS HSCN_WRP HSUB_SCN OBJECT_NAME
------- ----------- ----- ----- ------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------
4 167 1 0 free 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 T
--可以发现state1 ＝ free，并没有读取相应的数据块。
SCOTT@test> set autot traceonly
SCOTT@test> select rowid,t.* from t where rowid='AABFkXAAEAAAACnAAA';
Execution Plan
----------------------------------------------------------
Plan hash value: 3207308387
-------------------------------------------------------------------------------- ---
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------- ---
| 0 | SELECT STATEMENT | | 1 | 37 | 1 (0)| 00:00:0 1 |
| 1 | TABLE ACCESS BY USER ROWID| T | 1 | 37 | 1 (0)| 00:00:0 1 |
-------------------------------------------------------------------------------- ---
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
1 physical reads
116 redo size
672 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--可以发现产生了redo。第2次执行就没有了。
SCOTT@test> select rowid,t.* from t where rowid='AABFkXAAEAAAACnAAA';
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
672 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SCOTT@test> @bh1 4 167
DBARFIL DBABLK CLASS STATE STATE1 CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ CR_UBA_REC CR_XID_USN CR_XID_SLT CR_XID_SQN CR_CLS_BAS CR_CLS_WRP LRBA_SEQ LRBA_BNO HSCN_BAS HSCN_WRP HSUB_SCN OBJECT_NAME
------- ----------- ----- ----- ------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------
4 167 1 1 xcur 0 0 0 0 0 0 0 0 0 0 0 928 45450 3268411934 0 1 T
4 167 1 0 free 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 T
--可以发现STATE1=xcur的记录，LRBA_SEQ=928,也说明产生了日志。
--补充一下，测试脚本：
$ cat bh1.sql
set echo off
--------------------------------------------------------------------------------
-- @name: bh
-- @author: dion cho
-- @note: show block header
-- @usage: @bh f# b#
--------------------------------------------------------------------------------
col object_name format a12
col state1 format a6
col dbarfil format 999999
col dbablk format 9999999999
col state format 99
col class format 99
select
b.dbarfil,
b.dbablk,
b.class,
state,
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') as state1,
cr_scn_bas,
cr_scn_wrp,
cr_uba_fil,
cr_uba_blk,
cr_uba_seq,
cr_uba_rec,
cr_xid_usn,
cr_xid_slt,
cr_xid_sqn,
cr_cls_bas,
cr_cls_wrp,
lrba_seq,
lrba_bno,
hscn_bas,
hscn_wrp,
hsub_scn,
(select object_name from dba_objects where object_id = b.obj) as object_name
from x$bh b
where
dbarfil = &1 and
dbablk = &2
;
$ cat lookup_rowid.sql
set verify off
column dba format a20
column text format a40
SELECT DBMS_ROWID.ROWID_OBJECT ('&1') "OBJECT",
DBMS_ROWID.ROWID_RELATIVE_FNO ('&1') "FILE",
DBMS_ROWID.ROWID_BLOCK_NUMBER ('&1') "BLOCK",
DBMS_ROWID.ROWID_ROW_NUMBER ('&1') "ROW",
DBMS_ROWID.ROWID_RELATIVE_FNO ('&1')
|| ','
|| DBMS_ROWID.ROWID_BLOCK_NUMBER ('&1')
"DBA",
'alter system dump datafile '
|| DBMS_ROWID.ROWID_RELATIVE_FNO ('&1')
|| ' block '
|| DBMS_ROWID.ROWID_BLOCK_NUMBER ('&1')
|| ' ;'
text
FROM DUAL;