[20180725]exadata的hcc壓縮與dml更新.txt
[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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle Hybrid Columnar Compression(HCC) 混合列壓縮Oracle
- Exadata混合列壓縮功能與INSERT語句BF
- 混合列壓縮(HCC)在OLAP及OLTP場景中的測試
- [20180730]exadata與行連結.txt
- [20210331]Disk file operations IO與exadata.txt
- [20180725]index skip-scan operation.txtIndex
- Linux tar分卷壓縮與解壓縮Linux
- Linux 常用的壓縮與解壓縮命令詳解Linux
- [20240325]FORCE_MATCHING_SIGNATURE與DML.txt
- [20201007]exadata儲存索引.txt索引
- [20190120]行連結遷移與dml.txt
- Linux下檔案的壓縮與解壓Linux
- 告警壓縮與降噪
- 大資料中批次壓縮與獨立壓縮的比較 - Bozho大資料
- NET中SharpZipLib 的使用(一)【壓縮與解壓】
- Nginx網路壓縮 CSS壓縮 圖片壓縮 JSON壓縮NginxCSSJSON
- [20181023]12c網路資料壓縮.txt
- [20180814]慎用檢視錶壓縮率指令碼.txt指令碼
- Linux科研武器庫 - 檔案壓縮與解壓縮 - zip / unzipLinux
- Linux中檔案的壓縮和解壓縮Linux
- [20200111]淺談exadata oltp系統的優化.txt優化
- linux 下壓縮與解壓資料夾Linux
- linux 高效壓縮工具之xz的壓縮解壓使用Linux
- Nginx R31 doc-11-Compression and Decompression 壓縮與解壓縮Nginx
- 用ASP實現線上壓縮與解壓縮功能程式碼
- 檔案壓縮和解壓縮
- NET中SharpZipLib 的使用(二)【Web中壓縮與解壓】Web
- [20181112]11g 日誌傳輸壓縮模式.txt模式
- ppt怎麼壓縮，ppt壓縮的技巧分享
- PAT-B 1078 字串壓縮與解壓【字串】字串
- Python實現壓縮和解壓縮Python
- JS壓縮方法及批量壓縮JS
- linux下壓縮解壓縮命令Linux
- linux壓縮和解壓縮命令整理Linux
- 深度神經網路的壓縮與加速神經網路
- Linux下的tar壓縮解壓縮命令詳解Linux
- [20210519]是否可能導致DML失效.txt
- Linux常用命令之檔案壓縮與解壓縮命令詳解Linux