Exadata?????????INSERT?UPDATE
- by Liu Maclean(???)
Hybrid Columnar Compression??????Exadata?????????????,??????????(advanced compression)??,Hybrid columnar compression (HCC) ???Exadata????????HCC???????????CU(compression unit?????),??CU??????????,?????????????????????????,???CU????block???????????????
???????INSERT/UPDATE??,??????????????,????UPDATE/INSERT???HCC??????????????????
hybrid columnar compression???????????????(bulk initial load)??,??????(direct load)??ALTER TABLE MOVE, IMPDP???????(append INSERT),??HCC???????????????????????
???????????????????,?????????CU????????? ??????????????HCC?????????????for OLTP??????
????????:
SQL*Plus: Release 11.2.0.2.0 Production on Wed Sep 12 06:14:53 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> grant dba to scott;
Grant succeeded.
SQL> conn scott/oracle
Connected.
SQL>
SQL> create table hcc_maclean tablespace users compress for query high as select * from dba_objects;
Table created.
1* select rowid,owner,object_name,dbms_rowid.rowid_block_number(rowid) from hcc_maclean where owner='MACLEAN'
SQL> /
ROWID OWNER OBJECT_NAME DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------ ------------------------------ -------------------- ------------------------------------
AAAThuAAEAAAHTJAOI MACLEAN SALES 29897
AAAThuAAEAAAHTJAOJ MACLEAN MYCUSTOMERS 29897
AAAThuAAEAAAHTJAOK MACLEAN MYCUST_ARCHIVE 29897
AAAThuAAEAAAHTJAOL MACLEAN MYCUST_QUERY 29897
AAAThuAAEAAAHTJAOh MACLEAN COMPRESS_QUERY 29897
AAAThuAAEAAAHTJAOi MACLEAN UNCOMPRESS 29897
AAAThuAAEAAAHTJAOj MACLEAN CHAINED_ROWS 29897
AAAThuAAEAAAHTJAOk MACLEAN COMPRESS_QUERY1 29897
8 rows selected.
select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from hcc_maclean where owner='MACLEAN';
session A:
update hcc_maclean set OBJECT_NAME=OBJECT_NAME||'DBM' where rowid='AAAThuAAEAAAHTJAOI';
session B:
update hcc_maclean set OBJECT_NAME=OBJECT_NAME||'DBM' where rowid='AAAThuAAEAAAHTJAOJ';
SQL> select sid,wait_event_text,BLOCKER_SID from v$wait_chains;
SID WAIT_EVENT_TEXT BLOCKER_SID
---------- ---------------------------------------------------------------- -----------
13 enq: TX - row lock contention 136
136 SQL*Net message from client
????session A block B,????HCC???update row??CU?????CU??????
SQL> alter system checkpoint;
System altered.
SQL> /
System altered.
SQL> alter system dump datafile 4 block 29897
2 ;
Block header dump: 0x010074c9
Object id on Block? Y
seg/obj: 0x1386e csc: 0x00.1cad7e itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x10074c8 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.001cabfa
0x02 0x000a.00a.00000430 0x00c051a7.0169.17 ---- 1 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
avsp=0x14
tosp=0x14
r0_9ir2=0x0
mec_kdbh9ir2=0x0
76543210
shcf_kdbh9ir2=----------
76543210
flag_9ir2=--R----- Archive compression: Y
fcls_9ir2[0]={ }
0x16:pti[0] nrow=1 offs=0
0x1a:pri[0] offs=0x30
block_row_dump:
tab 0, row 0, @0x30
tl: 8016 fb: --H-F--N lb: 0x2 cc: 1 ==>??CU??ITL 0x02
nrid: 0x010074ca.0
col 0: [8004]
Compression level: 02 (Query High)
Length of CU row: 8004
kdzhrh: ------PC CBLK: 1 Start Slot: 00
NUMP: 01
PNUM: 00 POFF: 7984 PRID: 0x010074ca.0
CU header:
CU version: 0 CU magic number: 0x4b445a30
CU checksum: 0xf8faf86e
CU total length: 8694
CU flags: NC-U-CRD-OP
ncols: 15
nrows: 995
algo: 0
CU decomp length: 8487 len/value length: 100111
row pieces per row: 1
num deleted rows: 1
deleted rows: 904,
START_CU:
????????????row?????:
SQL> select DBMS_COMPRESSION.GET_COMPRESSION_TYPE('SCOTT','HCC_MACLEAN','AAAThuAAEAAAHTJAOk') from dual;
DBMS_COMPRESSION.GET_COMPRESSION_TYPE('SCOTT','HCC_MACLEAN','AAATHUAAEAAAHTJAOK'
--------------------------------------------------------------------------------
4
COMP_NOCOMPRESS CONSTANT NUMBER := 1;COMP_FOR_OLTP CONSTANT NUMBER := 2;COMP_FOR_QUERY_HIGH CONSTANT NUMBER := 4;COMP_FOR_QUERY_LOW CONSTANT NUMBER := 8;COMP_FOR_ARCHIVE_HIGH CONSTANT NUMBER := 16;COMP_FOR_ARCHIVE_LOW CONSTANT NUMBER := 32;
COMP_RATIO_MINROWS CONSTANT NUMBER := 1000000;COMP_RATIO_ALLROWS CONSTANT NUMBER := -1;
?????????????,??COMP_FOR_QUERY_HIGH?4,COMP_FOR_QUERY_LOW ?8
?????????GET_COMPRESSION_TYPE??rowid????????4?????COMP_FOR_QUERY_HIGH????:
SQL> update hcc_maclean set OBJECT_NAME=OBJECT_NAME||'DBM' where owner='MACLEAN';
8 rows updated.
SQL> commit;
Commit complete.
SQL> select DBMS_COMPRESSION.GET_COMPRESSION_TYPE('SCOTT','HCC_MACLEAN',rowid) from HCC_MACLEAN where owner='MACLEAN';
DBMS_COMPRESSION.GET_COMPRESSION_TYPE('SCOTT','HCC_MACLEAN',ROWID)
------------------------------------------------------------------
1
1
1
1
1
1
1
1
8 rows selected.
??????????????COMPRESSION_TYPE?COMP_FOR_QUERY_HIGH???COMP_NOCOMPRESS,????????compress for query high?????????????????
?11g????????????????????HCC??????????? ALTER TABLE MOVE???????????????????HCC???
SQL> ALTER TABLE hcc_MACLEAN move COMPRESS FOR ARCHIVE HIGH;
Table altered.
SQL> select DBMS_COMPRESSION.GET_COMPRESSION_TYPE('SCOTT','HCC_MACLEAN',rowid) from HCC_MACLEAN where owner='MACLEAN';
DBMS_COMPRESSION.GET_COMPRESSION_TYPE('SCOTT','HCC_MACLEAN',ROWID)
------------------------------------------------------------------
16
16
16
16
16
16
16
16
8 rows selected.
