本帖最后由 guoyJoe 于 2013-3-20 22:03 编辑
四、读一致性
１、什么是读一致性
会话一：
gyj@OCM> execdbms_stats.gather_table_stats(user,'T1');
PL/SQL procedure successfully completed.
gyj@OCM> alter session set isolation_level=serializable;
Session altered.
gyj@OCM> select * from t1 where id=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name |Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1 | 9 | 18 (0)| 00:00:01 |
|* 1| TABLE ACCESS FULL| T1 | 1 | 9 | 18 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
1- filter("ID"=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
22 consistent gets
0 physical reads
0 redo size
481 bytes sent via SQL*Net toclient
416 bytes received via SQL*Netfrom client
2 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts (disk)
1 rows processed
取第二次的执行计划！！！！！
再开一个会话，会话二：
gyj@OCM> begin
2 for i in 1 .. 1000
3 loop
4 update t1 set name='GGGGG'where id=1;
5 commit;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
再回到会话一，看一致性读信息：
gyj@OCM> select * from t1 where id=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name |Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1 | 9 | 18 (0)| 00:00:01 |
|* 1| TABLE ACCESS FULL| T1 | 1 | 9 | 18 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
1- filter("ID"=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1017 consistent gets
0 physical reads
0 redo size
481 bytes sent via SQL*Net toclient
416 bytes received via SQL*Netfrom client
2 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts (disk)
1 rows processed
2
一致性读从22变成了1017
２、构造CR块
步1:在会话一中发布如下声明:
gyj@OCM> create table t4 (id int,namevarchar2(10));
gyj@OCM> insert into t4values(1,'AAAAA');
1 row created.
gyj@OCM> commit;
Commit complete.
gyj@OCM> var x refcursor
gyj@OCM> exec open :x for select * fromt4;
PL/SQL procedure successfully completed.
步2:在会话二删除e表的所有记录且提交
gyj@OCM> delete t4;
1 row deleted.
gyj@OCM> commit;
Commit complete.
步3:回到会话一中输出游标X的所有行:
gyj@OCM> print x;
ID NAME
---------- ----------
1 AAAAA
结果还在！！！！
３、ORA-01555错误
gyj@OCM> create undo tablespace undotbs2 datafile'/u01/app/oracle/oradata/ocm/undotbs2.dbf' size 10M;
Tablespace created.
gyj@OCM> alter system set undo_tablespace=undotbs2;
System altered.
gyj@OCM> alter system set undo_retention=2 scope=both;
System altered.
第1步、session1: 目标是让b表报快照过旧的报错
gyj@OCM> conn gyj/gyj
Connected.
gyj@OCM> create table a (id int,cc varchar2(10));
Table created.
gyj@OCM> insert into a values(1,'hello');
1 row created.
gyj@OCM> commit;
Commit complete.
gyj@OCM> create table b(id int,ccvarchar2(10));
Table created.
gyj@OCM> insert into b values(10,'AAAAAA');
1 row created.
gyj@OCM> commit;
Commit complete.
gyj@OCM> select * from a;
ID CC
---------- ----------
1 hello
gyj@OCM> select * from b;
ID CC
---------- ----------
10 AAAAAA
gyj@OCM> var x refcursor;
gyj@OCM> exec open :x for select * fromb;
PL/SQL procedure successfully completed.
第2步、session2:修改b表,字段cc前镜像"OK"保存在UDNO段中
gyj@OCM> update b set cc='BBBBBB' where id= 10;
1 row updated.
gyj@OCM> commit;
Commit complete.
第3步、session 3:该条语句就是刷新缓存
sys@OCM> alter system flushbuffer_cache;
System altered.
第4步、再开3－5个会话:在A表上行大的事务,多运行几次以确保，回滚段被覆盖
gyj@OCM> begin
2 for i in 1..20000 loop
3 update a setcc='HELLOWWWW';
4 commit;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
第5步、session 1:在B表上执行查询(第一步的查询)
gyj@OCM> print :x
ERROR:
ORA-01555: snapshot too old: rollbacksegment number 21 with name "_SYSSMU21$" too small
五、UNDO表空间的大小估算以及创建
１、估算UNDO的大小
gyj@OCM> show parameter undo
NAME TYPE VALUE
----------------------------------------------- ------------------------------
undo_management string AUTO
undo_retention integer 2
undo_tablespace string UNDOTBS2
gyj@OCM> show parameter db_block_size
NAME TYPE VALUE
----------------------------------------------- ------------------------------
db_block_size integer 8192
gyj@OCM> show parameter undo_retention
NAME TYPE VALUE
----------------------------------------------- ------------------------------
undo_retention integer 2
gyj@OCM> select max(undoblks/((end_time - begin_time)*24*3600)) from v$undostat;
MAX(UNDOBLKS/((END_TIME-BEGIN_TIME)*24*3600))
---------------------------------------------
2.41833333
２、创建UNDO表空间
gyj@OCM> create undo tablespace undotbs3datafile '/u01/app/oracle/oradata/ocm/undotbs3.dbf' size 100M;
Tablespace created.
３、切换UNDO表空间
gyj@OCM> alter system setundo_tablespace=undotbs3;
System altered.
gyj@OCM> alter tablespace undotbs3retention guarantee;
Tablespace altered.
gyj@OCM> alter tablespace undotbs3retention noguarantee;
Tablespace altered.
gyj@OCM> select * from v$rollstat;
USN LATCH EXTENTS RSSIZE WRITES XACTS GETS WAITS OPTSIZE HWMSIZE SHRINKS WRAPS EXTENDS AVESHRINK AVEACTIVE STATUS CUREXT CURBLK
---------- ---------- ---------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ---------- ---------- --------------- ---------- ----------
0 0 6 385024 54380 0 449 0 385024 0 1 0 0 6553 ONLINE 0 5
21 0 2 122880 876372 0 1223 0 122880 0 0 0 0 0 ONLINE 0 0
22 1 2 122880 2685414 0 1839 0 122880 0 0 0 0 0 ONLINE 0 0
23 2 2 122880 2006864 0 1979 0 122880 0 0 0 0 0 ONLINE 0 1
24 0 2 122880 1242336 0 1317 0 122880 0 0 0 0 0 ONLINE 0 0
25 1 2 122880 1171432 0 1743 0 122880 0 0 0 0 0 ONLINE 0 0
26 2 2 122880 955404 0 1353 0 122880 0 0 0 0 0 ONLINE 0 1
27 0 2 122880 1176496 0 1318 0 122880 0 0 0 0 0 ONLINE 0 0
28 1 2 122880 1788000 0 1884 0 122880 0 0 0 0 0 ONLINE 0 0
29 2 2 122880 1854010 0 1867 0 122880 0 0 0 0 0 ONLINE 0 1
30 0 2 122880 1762272 0 1409 0 122880 0 0 0 0 0 ONLINE 0 1
11 rows selected.
******************************************************************************************************************************
undo一致性读深入解析一系列dump....
gyj@OCM> select * from t1 where id=1;
ID NAME
---------- -----------------------------
1 DDDDDD
gyj@OCM> var x refcursor;
gyj@OCM> exec open :x for select * from t1 where id=1;
PL/SQL procedure successfully completed.
gyj@OCM> update t1 set name='EEEEEE' where id=1;
1 row updated.
gyj@OCM> commit;
gyj@OCM> update t1 set name='FFFFFF' where id=1;
1 row updated.
gyj@OCM> commit;
Commit complete.
gyj@OCM> alter system flush buffer_cache;
System altered.
gyj@OCM> update t1 set name='HHHHHH' where id=1;
1 row updated.
gyj@OCM> commit;
Commit complete.
gyj@OCM> print x;
ID NAME
---------- ----------
1 DDDDDD
sys@OCM> alter system dump datafile 3 block 133;
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0017.017.000004a5 0x028006eb.005b.0d C--- 0 scn 0x0000.003e3b15
0x02 0x001b.015.00000377 0x02801da3.004e.1e --U- 1 fsc 0x0002.003e3b62 --EEEEEE
block_row_dump:
tab 0, row 0, @0x1f18
tl: 13 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [ 6] 45 45 45 45 45 45
__________________________________________________________________________________________________
sys@OCM> alter system dump datafile 3 block 133;
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0016.01d.00000512 0x028004c1.0071.28 --U- 1 fsc 0x0000.003ed6d6 --FFFFFF
0x02 0x001b.015.00000377 0x02801da3.004e.1e C--- 0 scn 0x0000.003e3b62
block_row_dump:
tab 0, row 0, @0x1f18
tl: 13 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [ 6] 46 46 46 46 46 ______________________________________________________________________________________________________
sys@OCM> alter system dump datafile 3 block 133;
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0016.01d.00000512 0x028004c1.0071.28 C--- 0 scn 0x0000.003ed6d6
0x02 0x001d.00d.000004de 0x028009b6.0063.0c --U- 1 fsc 0x0000.003ed9c8 --HHHHHHHH
block_row_dump:
tab 0, row 0, @0x1f18
tl: 13 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [ 6] 48 48 48 48 48 48
____________________________________________________________________________________________________________
Uba:0x028009b6.0063.0c
sys@OCM> alter system dump datafile 10 block 2486;
* Rec #0xc slt: 0x0d objn: 75350(0x00012656) objd: 75350 tblspc: 7(0x00000007)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x028009b6.0063.0b ctl max scn: 0x0000.003ed700 prv tx scn: 0x0000.003ed70e
txn start scn: scn: 0x0000.003ed700 logon user: 91
prev brb: 41945525 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L itl: xid: 0x001b.015.00000377 uba: 0x02801da3.004e.1e
flg: C--- lkc: 0 scn: 0x0000.003e3b62
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 191
ncol: 2 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x00c00085 hdba: 0x00c00082
itli: 2 ispac: 0 maxfr: 4858
vect = 3
col 1: [ 6] 46 46 46 46 46 46
_______________________________________________________________________________________________________
Uba:0x028004c1.0071.28
sys@OCM> alter system dump datafile 10 block 1217;
* Rec #0x28 slt: 0x1d objn: 75350(0x00012656) objd: 75350 tblspc: 7(0x00000007)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x028004c1.0071.27 ctl max scn: 0x0000.003ed48c prv tx scn: 0x0000.003ed498
txn start scn: scn: 0x0000.003ed297 logon user: 91
prev brb: 41944256 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L itl: xid: 0x0017.017.000004a5 uba: 0x028006eb.005b.0d
flg: C--- lkc: 0 scn: 0x0000.003e3b15
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 191
ncol: 2 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x00c00085 hdba: 0x00c00082
itli: 1 ispac: 0 maxfr: 4858
vect = 3
col 1: [ 6] 45 45 45 45 45 45
_____________________________________________________________________________________________________
uba: 0x02801da3.004e.1e
sys@OCM> alter system dump datafile 10 block 7587;
* Rec #0x1e slt: 0x15 objn: 75350(0x00012656) objd: 75350 tblspc: 7(0x00000007)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x02801da3.004e.1d ctl max scn: 0x0000.003e38e0 prv tx scn: 0x0000.003e38ee
txn start scn: scn: 0x0000.003e3b15 logon user: 91
prev brb: 41950664 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L itl: xid: 0x001b.010.00000377 uba: 0x02801da3.004e.13
flg: C--- lkc: 0 scn: 0x0000.003e3ac0
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x00c00085 hdba: 0x00c00082
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 191
ncol: 2 nnew: 1 size: 2
col 1: [ 8] 44 44 44 44 44 44
gyj@OCM> SELECT UTL_RAW.CAST_TO_VARCHAR2(replace('48 48 48 48 48 48',' ')) name FROM DUAL;
NAME
----------
HHHHHH
gyj@OCM> SELECT UTL_RAW.CAST_TO_VARCHAR2(replace('46 46 46 46 46 46',' ')) name FROM DUAL;
NAME
----------
FFFFFF
gyj@OCM> SELECT UTL_RAW.CAST_TO_VARCHAR2(replace('45 45 45 45 45 45',' ')) name FROM DUAL;
NAME
----------
EEEEEE
gyj@OCM> SELECT UTL_RAW.CAST_TO_VARCHAR2(replace('44 44 44 44 44 44',' ')) name FROM DUAL;
NAME
----------
DDDDDD
扫一扫
1021
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
