[20180725]exadata的hcc压缩与dml更新.txt

来源：这里教程网时间：2026-03-03 11:49:33 作者：

[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 --//不知道为什么???

编辑推荐：

返回列表

[20180725]exadata的hcc压缩与dml更新.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推