[20180725]exadata的hcc压缩与dml更新.txt
--//看了exadata方面的资料,了解hcc压缩,这个功能仅仅适合静态的数据(历史数据),一般不会更新,
--//而且采用行列混合的压缩模式,这样dml后会锁定许多行(如果压缩率很高的话,记录数很多),简单测试看看.
1.环境:
ZWS@dbcn1> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
ZWS@dbcn1> @ &r/s
ZWS@xxxx(4153,23201)> create table empx compress for archive low as select * from scott.emp ;
Table created.
ZWS@xxxx(4153,23201)> alter table empx move compress for archive low ;
Table altered.
ZWS@xxxx(4153,23201)> select rowid,empx.* from empx where rownum<=4;
ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
AAAhskAAEAAA9n7AAA 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
AAAhskAAEAAA9n7AAB 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
AAAhskAAEAAA9n7AAC 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
AAAhskAAEAAA9n7AAD 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
ZWS@xxxx(4153,23201)> @ &r/rowid AAAhskAAEAAA9n7AAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
138020 4 252411 0 0x103D9FB 4,252411 alter system dump datafile 4 block 25241
ZWS@xxxx(4153,23201)> alter system checkpoint ;
System altered.
ZWS@xxxx(4153,23201)> alter system dump datafile 4 block 252411 ;
System altered.
--//看看转储,可以发现与普通的数据块没什么区别.
Block header dump: 0x0103d9fb
Object id on Block? Y
seg/obj: 0x21b24 csc: 0x05.2c1e9eba itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x103d9f8 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0079.009.000cfeb1 0x00000000.0000.00 C-U- 0 scn 0x0005.2c1e958c
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x0103d9fb
data_block_dump,data header at 0x7f70dc1e207c
===============
tsiz: 0x1f80
hsiz: 0x2e
pbl: 0x7f70dc1e207c
76543210
flag=--------
ntab=1
nrow=14
frre=-1
fsbo=0x2e
fseo=0x1d49
avsp=0x1d1b
tosp=0x1d1b
0xe:pti[0] nrow=14 offs=0
0x12:pri[0] offs=0x1f5a
0x14:pri[1] offs=0x1f2f
0x16:pri[2] offs=0x1f04
0x18:pri[3] offs=0x1edb
0x1a:pri[4] offs=0x1eae
0x1c:pri[5] offs=0x1e85
0x1e:pri[6] offs=0x1e5c
0x20:pri[7] offs=0x1e34
0x22:pri[8] offs=0x1e0e
0x24:pri[9] offs=0x1de3
0x26:pri[10] offs=0x1dbd
0x28:pri[11] offs=0x1d97
0x2a:pri[12] offs=0x1d70
0x2c:pri[13] offs=0x1d49
block_row_dump:
tab 0, row 0, @0x1f5a
tl: 38 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 3] c2 4a 46
col 1: [ 5] 53 4d 49 54 48
col 2: [ 5] 43 4c 45 52 4b
col 3: [ 3] c2 50 03
col 4: [ 7] 77 b4 0c 11 01 01 01
col 5: [ 2] c2 09
col 6: *NULL*
col 7: [ 2] c1 15
--//也许数据压缩率太小.再增加一些数据看看.
insert into empx select * from empx;
insert into empx select * from empx;
insert into empx select * from empx;
insert into empx select * from empx;
commit ;
ZWS@xxxx(4153,23201)> select count(*) from empx;
COUNT(*)
----------
224
ZWS@xxxx(4153,23201)> update empx set HIREDATE=sysdate where rowid ='AAAhskAAEAAA9n7AAA';
1 row updated.
ZWS@xxxx(4153,23201)> commit ;
Commit complete.
--//这样这条数据与别的不同在hiredate字段上.
ZWS@xxxx(4153,23201)> alter table empx move compress for archive low ;
Table altered.
ZWS@xxxx(4153,23201)> select rowid,empx.* from empx where rownum<=4;
ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
AAAhslAAEAAA9nzAAA 7369 SMITH CLERK 7902 2018-07-27 08:49:00 800 20
AAAhslAAEAAA9nzAAB 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
AAAhslAAEAAA9nzAAC 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
AAAhslAAEAAA9nzAAD 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
ZWS@xxxx(4153,23201)> @ &r/rowid AAAhslAAEAAA9nzAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
138021 4 252403 0 0x103D9F3 4,252403 alter system dump datafile 4 block 252403
--//再次做转储,可以发现数据已经压缩.不贴出转储内容了.
2.测试:
--//session 1:
ZWS@xxxx(4153,23201)> update empx set ename=lower(ename) where empno=7369 and rownum=1;
1 row updated.
--//不提交!!
--//session 2:
ZWS@xxxx> @ &r/s
ZWS@xxxx(6647,29083)>
ZWS@xxxx(6647,29083)> select * from empx where empno=7934 for update nowait;
select * from empx where empno=7934 for update nowait
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
--//可以发现采用HCC压缩模式,会锁定许多行,不适合oltp模式,类似位图索引会锁定许多行.
--//session 1:
ZWS@xxxx(4153,23201)> commit;
Commit complete.
ZWS@xxxx(4153,23201)> select rowid,empx.* from empx where empno=7369 ;
ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
AAAhslAAEAAA9nzAAO 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
AAAhslAAEAAA9nzAAc 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
AAAhslAAEAAA9nzAAq 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
AAAhslAAEAAA9nzAA4 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
AAAhslAAEAAA9nzABG 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
AAAhslAAEAAA9nzABU 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
AAAhslAAEAAA9nzABi 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
AAAhslAAEAAA9nzABw 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
AAAhslAAEAAA9nzAB+ 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
AAAhslAAEAAA9nzACM 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
AAAhslAAEAAA9nzACa 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
AAAhslAAEAAA9nzACo 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
AAAhslAAEAAA9nzAC2 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
AAAhslAAEAAA9nzADE 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
AAAhslAAEAAA9nzADS 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
AAAhslAAEAAA9n1AAA 7369 smith CLERK 7902 2018-07-27 08:49:00 800 20
16 rows selected.
--//可以发现rowid发生了变化,注意最后一条(修改的记录跑到最后,大小写以及hiredate的时间).实际上修改后,oracle解压放到新数据块.转储看看.
ZWS@xxxx(4153,23201)> @ &r/rowid AAAhslAAEAAA9n1AAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
138021 4 252405 0 0x103D9F5 4,252405 alter system dump datafile 4 block 252405;
ZWS@xxxx(4153,23201)> alter system dump datafile 4 block 252405;
System altered.
Block header dump: 0x0103d9f5
Object id on Block? Y
seg/obj: 0x21b22 csc: 0x05.2c1de571 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x103d9f0 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0031.021.0011e5cf 0x06c04282.a591.0e C--- 0 scn 0x0005.2c1c962c
0x02 0x005d.01c.00068a0e 0x06c04f11.3a50.09 --U- 1 fsc 0x0000.2c1de7a0
bdba: 0x0103d9f5
data_block_dump,data header at 0x7f70dc1e2064
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x7f70dc1e2064
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f72
avsp=0x1f5e
tosp=0x1f5e
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f72
block_row_dump:
tab 0, row 0, @0x1f72
tl: 38 fb: --H-FL-- lb: 0x2 cc: 8
col 0: [ 3] c2 4a 46
col 1: [ 5] 73 6d 69 74 68
col 2: [ 5] 43 4c 45 52 4b
col 3: [ 3] c2 50 03
col 4: [ 7] 78 76 07 1b 09 2c 18
col 5: [ 2] c2 09
col 6: *NULL*
col 7: [ 2] c1 15
end_of_block_dump
--//与普通数据块没有什么不同,实际上如果修改记录增加,最后这块会压缩为compress for oltp模式.
3.通过原来的rowid查询看看:
ZWS@xxxx(4153,23201)> select rowid,empx.* from empx where rowid='AAAhslAAEAAA9nzAAA';
ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
AAAhslAAEAAA9n1AAA 7369 smith CLERK 7902 2018-07-27 08:49:00 800 20
--//注意看显示的rowid与查询中谓词的rowid不一样,显示的是最新的rowid.
ZWS@xxxx(4153,23201)> select rowid,empx.* from empx where rowid='AAAhslAAEAAA9n1AAA';
ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
AAAhslAAEAAA9n1AAA 7369 smith CLERK 7902 2018-07-27 08:49:00 800 20
--//出现一个奇特现象,在谓词中的查询rowid与select显示的rowid不一致.实际上就是发生行迁移.
--//也就是HCC压缩的表经过dml后,数据解压移动到新块,采用compress for oltp模式.
4.当然你现在再修改这样就不会出现阻塞的情况.
--//session 1:
ZWS@xxxx(4153,23201)> update empx set job=lower(job) where rowid='AAAhslAAEAAA9nzAAA';
1 row updated.
--//不提交.
--//session 2:
ZWS@xxxx(6647,29083)> select * from empx where empno=7934 and rownum=1 for update nowait;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
ZWS@xxxx(4153,23201)> select rowid,empx.* from empx where rowid='AAAhslAAEAAA9n1AAA';
ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
AAAhslAAEAAA9n1AAA 7369 smith clerk 7902 2018-07-27 08:49:00 800 20
5.不过我的测试很奇怪看不出发生了行迁移:
ZWS@xxxx(6647,29083)> @ &r/viewsess 'table fetch continued row'
NAME STATISTIC# VALUE SID
---------------------------------------------------------------------- ---------- ---------- ----------
table fetch continued row 417 0 6647
ZWS@xxxx(6647,29083)> select rowid,empx.* from empx where rowid='AAAhslAAEAAA9nzAAA';
ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
AAAhslAAEAAA9n1AAA 7369 smith clerk 7902 2018-07-27 08:49:00 800 20
ZWS@xxxx(6647,29083)> @ &r/viewsess 'table fetch continued row'
NAME STATISTIC# VALUE SID
---------------------------------------------------------------------- ---------- ---------- ----------
table fetch continued row 417 0 6647
--//不知道为什么???
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2168527/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-2168527/
776
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
