MINSCN?Cache Fusion Read Consistent
- by Liu Maclean(???)
????? ???Ask Maclean Home ??? RAC ? Past Image PI????, ?????????,???11.2.0.3 2 Node RAC??????????:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
www.oracledatabase12g.com
SQL> drop table test purge;
Table dropped.
SQL> alter system flush buffer_cache;
System altered.
SQL> create table test(id number);
insert into test values(1);
insert into test values(2);
commit;
/* ???? rowid??TEST????2????????? */
select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from test;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------ ------------------------------------
89233 1
89233 1
SQL> alter system flush buffer_cache;
System altered.
Instance 1 Session A ??UPDATE??:
SQL> update test set id=id+1 where id=1;
1 row updated.
Instance 1 Session B ??x$BH buffer header?? ?? ??Buffer???
SQL> select state,cr_scn_bas from x$bh where file#=1 and dbablk=89233 and state!=0;
STATE CR_SCN_BAS
---------- ----------
1 0
3 1227595
X$BH ??? STATE????Buffer???, ???????:
STATE NUMBER
KCBBHFREE 0 buffer free
KCBBHEXLCUR 1 buffer current (and if DFS locked X)
KCBBHSHRCUR 2 buffer current (and if DFS locked S)
KCBBHCR 3 buffer consistant read
KCBBHREADING 4 Being read
KCBBHMRECOVERY 5 media recovery (current & special)
KCBBHIRECOVERY 6 Instance recovery (somewhat special)
????????????? : state =1 Xcurrent ? state=2 Scurrent ? state=3 CR
??? Instance 2 ?? ????????????? ,???? gc current block 2 way ??Current Block ??? Instance 2, ?? Instance 1 ??”Current Block” Convert ? Past Image:
Instance 2 Session C
SQL> update test set id=id+1 where id=2;
1 row updated.
Instance 2 Session D
SQL> select state,cr_scn_bas from x$bh where file#=1 and dbablk=89233 and state!=0;
STATE CR_SCN_BAS
---------- ----------
1 0
3 1227641
3 1227638
STATE =1 ?Xcurrent block???? Instance 2 , ??? Instance 1 ??? GC??:
Instance 1 Session B
SQL> select state,cr_scn_bas from x$bh where file#=1 and dbablk=89233 and state!=0;
STATE CR_SCN_BAS
---------- ----------
3 1227641
3 1227638
8 0
3 1227595
???????, ??????Instance 1??session A????TEST??SELECT??? ,????? 3? State=3?CR ? ??????1?:
Instance 1 session A ?????UPDATE? session
SQL> alter session set events '10046 trace name context forever,level 8:10708 trace name context forever,level 103: trace[rac.*] disk high';
Session altered.
SQL> select * from test;
ID
----------
2
2
select state,cr_scn_bas from x$bh where file#=1 and dbablk=89233 and state!=0;
STATE CR_SCN_BAS
---------- ----------
3 1227716
3 1227713
8 0
?????????v$BH ????CR????,?????SELECT??? CR????????,???????? ?????????? ??X$BH?????? , ?????CR??SCN Version???: 1227641?1227638?1227595, ?SELECT?????? 2???SCN version?CR? 1227716? 1227713 ???, Oracle?????????
?????????SELECT??????event 10708? rac.*???TRACE,??????TRACE??:
PARSING IN CURSOR #140444679938584 len=337 dep=1 uid=0 oct=3 lid=0 tim=1335698913632292 hv=3345277572 ad='bc0e68c8' sqlid='baj7tjm3q9sn4'
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ NO_PARALLEL("TEST") FULL("TEST") NO_PARALLEL_INDEX("TEST") */ 1 AS C1, 1 AS C2 FROM "SYS"."TEST" "TEST") SAMPLESUB
END OF STMT
PARSE #140444679938584:c=1000,e=27630,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=1950795681,tim=1335698913632252
EXEC #140444679938584:c=0,e=44,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1950795681,tim=1335698913632390
*** 2012-04-29 07:28:33.632
kclscrs: req=0 block=1/89233
*** 2012-04-29 07:28:33.632
kclscrs: bid=1:3:1:0:7:80:1:0:4:0:0:0:1:2:4:1:26:0:0:0:70:1a:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:4:3:2:1:2:0:3f:0:1c:86:2d:4:0:0:0:0:a2:3c:7c:b:70:1a:0:0:0:0:1:0:7a:f8:76:1d:1:2:dc:5:a9:fe:17:75:0:0:0:0:0:0:0:0:0:0:0:0:63:e5:0:0:0:0:0:0:10:0:0:0
2012-04-29 07:28:33.632578 : kjbcrc[0x15c91.1 76896.0][9]
2012-04-29 07:28:33.632616 : GSIPC:GMBQ: buff 0xba1e8f90, queue 0xbb79f278, pool 0x60013fa0, freeq 1, nxt 0xbb79f278, prv 0xbb79f278
2012-04-29 07:28:33.632634 : kjbmscrc(0x15c91.1)seq 0x2 reqid=0x1c(shadow 0xb4bb4458,reqid x1c)mas@2(infosz 200)(direct 1)
2012-04-29 07:28:33.632654 : kjbsentscn[0x0.12bbc1][to 2]
2012-04-29 07:28:33.632669 : GSIPC:SENDM: send msg 0xba1e9000 dest x20001 seq 24026 type 32 tkts xff0000 mlen x17001a0
2012-04-29 07:28:33.633385 : GSIPC:KSXPCB: msg 0xba1e9000 status 30, type 32, dest 2, rcvr 1
*** 2012-04-29 07:28:33.633
kclwcrs: wait=0 tm=689
*** 2012-04-29 07:28:33.633
kclwcrs: got 1 blocks from ksxprcv
WAIT #140444679938584: nam='gc cr block 2-way' ela= 689 p1=1 p2=89233 p3=1 obj#=76896 tim=1335698913633418
2012-04-29 07:28:33.633490 : kjbcrcomplete[0x15c91.1 76896.0][0]
2012-04-29 07:28:33.633510 : kjbrcvdscn[0x0.12bbc1][from 2][idx 2012-04-29 07:28:33.633527 : kjbrcvdscn[no bscn <= rscn 0x0.12bbc1][from 2]
*** 2012-04-29 07:28:33.633
kclwcrs: req=0 typ=cr(2) wtyp=2hop tm=689
??TRACE???? ?????????TEST??????, ???????Dynamic Sampling?????,???????TEST?? CR???,???????’gc cr block 2-way’ ??:
2012-04-29 07:28:33.632654 : kjbsentscn[0x0.12bbc1][to 2]
12bbc1= 1227713 ???X$BH????CR???,kjbsentscn[0x0.12bbc1][to 2] ????? ? Instance 2 ???SCN=12bbc1=1227713 DBA=0x15c91.1 76896.0 ? CR Request(obj#=76896)
??kjbrcvdscn????? [no bscn <= rscn 0x0.12bbc1][from 2] ,??? ??receive? SCN Version =12bbc1 ???Best Version
CR Server Arch
??????????????????SELECT??:
PARSING IN CURSOR #140444682869592 len=18 dep=0 uid=0 oct=3 lid=0 tim=1335698913635874 hv=1689401402 ad='b1a188f0' sqlid='c99yw1xkb4f1u'
select * from test
END OF STMT
PARSE #140444682869592:c=4999,e=34017,p=0,cr=7,cu=0,mis=1,r=0,dep=0,og=1,plh=1357081020,tim=1335698913635870
EXEC #140444682869592:c=0,e=23,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1357081020,tim=1335698913635939
WAIT #140444682869592: nam='SQL*Net message to client' ela= 7 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1335698913636071
*** 2012-04-29 07:28:33.636
kclscrs: req=0 block=1/89233
*** 2012-04-29 07:28:33.636
kclscrs: bid=1:3:1:0:7:83:1:0:4:0:0:0:1:2:4:1:26:0:0:0:70:1a:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:4:3:2:1:2:0:2:0:1c:86:2d:4:0:0:0:0:a2:3c:7c:b:70:1a:0:0:0:0:1:0:7d:f8:76:1d:1:2:dc:5:a9:fe:17:75:0:0:0:0:0:0:0:0:0:0:0:0:63:e5:0:0:0:0:0:0:10:0:0:0
2012-04-29 07:28:33.636209 : kjbcrc[0x15c91.1 76896.0][9]
2012-04-29 07:28:33.636228 : GSIPC:GMBQ: buff 0xba0e5d50, queue 0xbb79f278, pool 0x60013fa0, freeq 1, nxt 0xbb79f278, prv 0xbb79f278
2012-04-29 07:28:33.636244 : kjbmscrc(0x15c91.1)seq 0x3 reqid=0x1d(shadow 0xb4bb4458,reqid x1d)mas@2(infosz 200)(direct 1)
2012-04-29 07:28:33.636252 : kjbsentscn[0x0.12bbc4][to 2]
2012-04-29 07:28:33.636358 : GSIPC:SENDM: send msg 0xba0e5dc0 dest x20001 seq 24029 type 32 tkts xff0000 mlen x17001a0
2012-04-29 07:28:33.636861 : GSIPC:KSXPCB: msg 0xba0e5dc0 status 30, type 32, dest 2, rcvr 1
*** 2012-04-29 07:28:33.637
kclwcrs: wait=0 tm=865
*** 2012-04-29 07:28:33.637
kclwcrs: got 1 blocks from ksxprcv
WAIT #140444682869592: nam='gc cr block 2-way' ela= 865 p1=1 p2=89233 p3=1 obj#=76896 tim=1335698913637294
2012-04-29 07:28:33.637356 : kjbcrcomplete[0x15c91.1 76896.0][0]
2012-04-29 07:28:33.637374 : kjbrcvdscn[0x0.12bbc4][from 2][idx 2012-04-29 07:28:33.637389 : kjbrcvdscn[no bscn <= rscn 0x0.12bbc4][from 2]
*** 2012-04-29 07:28:33.637
kclwcrs: req=0 typ=cr(2) wtyp=2hop tm=865
???, “SELECT * FROM TEST”??????’gc cr block 2-way’??:2012-04-29 07:28:33.637374 : kjbrcvdscn[0x0.12bbc4][from 2][idx 2012-04-29 07:28:33.637389 : kjbrcvdscn[no bscn
??Foreground Process? Remote LMS??got?? SCN=1227716 Version?CR, ??? ?????X$BH ?????scn???
??????????Instance 1????2?SCN???CR?, ???????????Instance 1 Buffer Cache?? ??SCN Version ???CR ???????
?????????:
SQL> alter system set "_enable_minscn_cr"=false scope=spfile;
System altered.
SQL> alter system set "_db_block_max_cr_dba"=20 scope=spfile;
System altered.
SQL> startup force;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1570009088 bytes
Fixed Size 2228704 bytes
Variable Size 989859360 bytes
Database Buffers 570425344 bytes
Redo Buffers 7495680 bytes
Database mounted.
Database opened.
???? “_enable_minscn_cr”=false ? “_db_block_max_cr_dba”=20 ???RAC????, ??????:
?Instance 2 Session C ?update??????? ?????Instance 1 ????? ,????Instance 1?Request CR
SQL> update test set id=id+1 where id=2; -- Instance 2
1 row updated.
SQL> select * From test; -- Instance 1
ID
----------
1
2
??? Instance 1? X$BH??
select state,cr_scn_bas from x$bh where file#=1 and dbablk=89233 and state!=0;
STATE CR_SCN_BAS
---------- ----------
3 1273080
3 1273071
3 1273041
3 1273039
8 0
SQL> update test set id=id+1 where id=3;
1 row updated.
SQL> select * From test;
ID
----------
1
2
SQL> select state,cr_scn_bas from x$bh where file#=1 and dbablk=89233 and state!=0;
STATE CR_SCN_BAS
---------- ----------
3 1273091
3 1273080
3 1273071
3 1273041
3 1273039
8 0
...................
SQL> select state,cr_scn_bas from x$bh where file#=1 and dbablk=89233 and state!=0;
STATE CR_SCN_BAS
---------- ----------
3 1273793
3 1273782
3 1273780
3 1273769
3 1273734
3 1273715
3 1273691
3 1273679
3 1273670
3 1273643
3 1273635
3 1273623
3 1273106
3 1273091
3 1273080
3 1273071
3 1273041
3 1273039
3 1273033
19 rows selected.
SQL> select state,cr_scn_bas from x$bh where file#=1 and dbablk=89233 and state!=0;
STATE CR_SCN_BAS
---------- ----------
3 1274993
????? ???? “_enable_minscn_cr”(enable/disable minscn optimization for CR)=false ? “_db_block_max_cr_dba”=20 (Maximum Allowed Number of CR buffers per dba) 2? ??? ????? Instance 1 ??????????? ?? 19????CR??
“_enable_minscn_cr”?11g??????????,???Oracle????CR????SCN,?Foreground Process Receive????????????(SCN??)?SCN Version CR Block??????CBC?? SCN??????CR? , ?????????Buffer Cache??????? ????SCN Version?CR????,????? ?????????? ?????Snap_Scn ?? SCN?? ?????????Current SCN, ??????CR??????????????????????, ????Buffer Cache? ?????????? CR?????????, ?????? “_db_block_max_cr_dba” ???????, ???????????20 ,??????Buffer Cache?????19????CR?; ???”_db_block_max_cr_dba” ???????6 , ?????Buffer cache????????CR ???????6??
??”_enable_minscn_cr” ??CR???MINSCN ??????, ?????????CR???????, ????? Foreground Process??????CR Request , ?? Holder Instance LMS ?build?? BEST CR ??, ?????????
