设为首页收藏本站

Botang唐波's Oracle Station

【活动演讲2015】“Oracle WDP(福建)2015技术峰会”:《霸王龙的体量、迅猛龙的体型和速度:Oracle Database 11g数据压缩技术》

2015-7-14 15:02| 发布者: admin| 查看: 12284| 评论: 0

摘要: Oracle WDP(福建)技术峰会,我的演讲《霸王龙的体量、迅猛龙的体型和速度:Oracle Database 11g数据压缩技术》。



所有演讲材料下载

COMPRESS.pdf

所有脚本:

--sys--
create tablespace tbs_nocompression datafile size 10M autoextend on;
create tablespace tbs_basic datafile size 10M autoextend on default compress basic;
create tablespace tbs_oltp datafile size 10M autoextend on default compress for oltp;
create tablespace tbs_query datafile size 10M autoextend on default compress for query;
create tablespace tbs_archive datafile size 10M autoextend on default compress for archive;
select  t.tablespace_name, t.def_tab_compression, t.compress_for  from dba_tablespaces t
 where t.tablespace_name in ('TBS_NOCOMPRESSION', 'TBS_BASIC', 'TBS_OLTP',
                                                   'TBS_QUERY',
                                                   'TBS_ARCHIVE');

--hr--                                                   
create table t_nocompression (a varchar2(200)) tablespace tbs_nocompression;
create table t_basic (a varchar2(200)) tablespace tbs_basic;
create table t_oltp (a varchar2(200)) tablespace tbs_oltp;

--hr-error--
create table t_query (a varchar2(200)) tablespace tbs_query;
create table t_archive (a varchar2(200)) tablespace tbs_archive; 

--hr--
begin
   for i in 1..400
   loop
      insert into t_nocompression values('AAAAAAAAAAAAAAAAAAAA');
   end loop;
   commit;
end;

--hr--
begin
   for i in 1..400
   loop
      insert into t_basic values('AAAAAAAAAAAAAAAAAAAA');
   end loop;
   commit;
end;

--hr--
begin
   for i in 1..400
   loop
      insert into t_oltp values('AAAAAAAAAAAAAAAAAAAA');
   end loop;
   commit;
end; 

--hr--
select  t.TABLE_NAME, t.PCT_FREE , t.COMPRESSION , t.COMPRESS_FOR, t.TABLESPACE_NAME
 from user_tables t where t.TABLE_NAME   in ('T_NOCOMPRESSION','T_BASIC','T_OLTP');
select  count(*)  , substr(rowid, 10, 6 ) from hr.t_nocompression  group by  substr(rowid, 10, 6 );
select  count(*)  , substr(rowid, 10, 6 ) from hr.t_basic  group by  substr(rowid, 10, 6 );     
select  count(*)  , substr(rowid, 10, 6 ) from hr.t_oltp  group by  substr(rowid, 10, 6 ); 

--sys--
select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
      from hr.T_NOCOMPRESSION  group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);

--sys--
select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid)
      from hr.T_BASIC group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid);

--sys--     
select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_OLTP',row_id => rowid)
      from hr.T_OLTP group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_OLTP',row_id => rowid);

--hr--     
create table t_basic2 (a varchar2(200)) tablespace tbs_nocompression compress;
create table t_oltp2 (a varchar2(200)) tablespace tbs_nocompression compress for oltp;

--hr-error--
create table t_query2 (a varchar2(200)) tablespace tbs_nocompression compress for query;
create table t_archive2 (a varchar2(200)) tablespace tbs_nocompression compress for archive;    

--hr--
select  t.TABLE_NAME, t.PCT_FREE , t.COMPRESSION , t.COMPRESS_FOR, t.TABLESPACE_NAME
 from user_tables t where t.TABLE_NAME   in ('T_NOCOMPRESSION','T_BASIC','T_OLTP',  'T_BASIC2','T_OLTP2' );

--hr--
begin
   for i in 1..400
   loop
      insert into t_basic2 values('AAAAAAAAAAAAAAAAAAAA');
   end loop;
   commit;
end;

--hr--
begin
   for i in 1..400
   loop
      insert into t_oltp2 values('AAAAAAAAAAAAAAAAAAAA');
   end loop;
   commit;
end;

--hr--
select  count(*)  , substr(rowid, 10, 6 ) from hr.t_basic2  group by  substr(rowid, 10, 6 );
select  count(*)  , substr(rowid, 10, 6 ) from hr.t_oltp2  group by  substr(rowid, 10, 6 );

--sys--
select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC2',row_id => rowid)
      from hr.T_BASIC2 group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC2',row_id => rowid);

--sys--     
select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_OLTP2',row_id => rowid)
      from hr.T_OLTP2 group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_OLTP2',row_id => rowid);

--hr--
alter table t_nocompression compress for oltp;
select  t.TABLE_NAME, t.PCT_FREE , t.COMPRESSION , t.COMPRESS_FOR, t.TABLESPACE_NAME
 from user_tables t where t.TABLE_NAME   in ('T_NOCOMPRESSION','T_BASIC','T_OLTP',  'T_BASIC2','T_OLTP2' );
select  count(*)  , substr(rowid, 10, 6 ) from hr.t_nocompression  group by  substr(rowid, 10, 6 );    

--sys--
select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
   from hr.T_NOCOMPRESSION  group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);  

--hr--
begin
   for i in 1..400
   loop
      insert into t_nocompression values('AAAAAAAAAAAAAAAAAAAA');
   end loop;
   commit;
end;

--hr--
select  count(*)  , substr(rowid, 10, 6 ) from hr.t_nocompression  group by  substr(rowid, 10, 6 );

--sys--
select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
      from hr.T_NOCOMPRESSION  group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);

--hr--
alter table t_nocompression move tablespace tbs_nocompression;
select  count(*)  , substr(rowid, 10, 6 ) from hr.t_nocompression  group by  substr(rowid, 10, 6 );  

--sys--
select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
      from hr.T_NOCOMPRESSION  group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);

--hr--
select  count(*)  , substr(rowid, 10, 6 ) from hr.t_basic  group by  substr(rowid, 10, 6 ); 
insert /*+ append */ into t_basic select  * from  t_basic;
commit;
select  count(*)  , substr(rowid, 10, 6 ) from hr.t_basic  group by  substr(rowid, 10, 6 );

--sys--
select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid)
      from hr.T_BASIC group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid);

--hr--
alter table t_basic move tablespace  TBS_NOCOMPRESSION;
select  count(*)  , substr(rowid, 10, 6 ) from hr.t_basic  group by  substr(rowid, 10, 6 );     

--sys--
select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid)
      from hr.T_BASIC group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid);

--sys-evolution--
drop table hr.t_basic_big;
create table hr.t_basic_big compress as select  * from dba_source;

--sys-advisor--
declare
  v_blkcnt_cmp number;
  v_blkcnt_uncmp  number;
  v_row_cmp number;
  v_row_uncmp number;
  v_cmp_ratio number;
  v_comptype_str  varchar2(200);
BEGIN
DBMS_COMPRESSION.GET_COMPRESSION_RATIO(scratchtbsname => 'USERS',
                                                                                             ownname =>'HR',
                                                                                             tabname =>'T_BASIC_BIG',
                                                                                             partname =>null,
                                                                                             comptype => 2,
                                                                                             blkcnt_cmp => v_blkcnt_cmp,
                                                                                             blkcnt_uncmp =>  v_blkcnt_uncmp,
                                                                                             row_cmp =>v_row_cmp,
                                                                                             row_uncmp => v_row_uncmp,
                                                                                             cmp_ratio =>  v_cmp_ratio,
                                                                                             comptype_str =>v_comptype_str);                                                 
DBMS_OUTPUT.PUT_LINE('Blk count compressed = ' || v_blkcnt_cmp);
DBMS_OUTPUT.PUT_LINE('Blk count uncompressed = ' || v_blkcnt_uncmp);
DBMS_OUTPUT.PUT_LINE('Row count per block compressed = ' || v_row_cmp);
DBMS_OUTPUT.PUT_LINE('Row count per block uncompressed = ' || v_row_uncmp);
DBMS_OUTPUT.PUT_LINE('ratio: '||v_cmp_ratio);
DBMS_OUTPUT.PUT_LINE('Compression type = ' || v_comptype_str);
end;

--sys--
select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC_BIG',row_id => rowid)
      from hr.T_BASIC_BIG group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC_BIG',row_id => rowid); 

--hr--
create table t_basic_col( a  number , b varchar2(20))  compress ;
insert into t_basic_col values ( 1,'A') ;
commit;
create table t_oltp_col( a  number , b varchar2(20))  compress  for oltp;
insert into t_oltp_col values ( 1,'A') ;
commit;
select  * from  t_basic_col;
select  * from t_oltp_col;
alter table  t_basic_col drop column b;
alter table  t_basic_col drop ( b);
alter table t_oltp_col drop column b;
select  * from t_oltp_col;

--sys-lob--
drop directory dir1;
create directory dir1 as '/home/oracle';
grant all on directory dir1 to hr;

--hr-lob--
create table t_lob_bf( name varchar2(20) , image blob )
lob(image) store as basicfile;

create table t_lob_sf( name varchar2(20) , image blob )
lob(image) store as securefile ( compress high deduplicate lob);

declare
 v_f BFILE;
 v_b blob;
BEGIN
 for i in 1..100
 loop
  INSERT INTO t_lob_bf values('oracle.jpg',EMPTY_BLOB ()) RETURN image into v_b;
  v_f := BFILENAME ('DIR1', 'oracle.jpg');
  DBMS_LOB.FILEOPEN  (v_f, DBMS_LOB.FILE_READONLY);
  DBMS_LOB.LOADFROMFILE (v_b, v_f, DBMS_LOB.GETLENGTH (v_f));
  DBMS_LOB.FILECLOSE (v_f);
  commit;
 end loop;
end;


declare
 v_f BFILE;
 v_b blob;
BEGIN
 for i in 1..100
 loop
  INSERT INTO t_lob_sf values('oracle.jpg',EMPTY_BLOB ()) RETURN image into v_b;
  v_f := BFILENAME ('DIR1', 'oracle.jpg');
  DBMS_LOB.FILEOPEN  (v_f, DBMS_LOB.FILE_READONLY);
  DBMS_LOB.LOADFROMFILE (v_b, v_f, DBMS_LOB.GETLENGTH (v_f));
  DBMS_LOB.FILECLOSE (v_f);
  commit;
 end loop;
end;

select  * from user_segments where segment_name in
  (select  segment_name  from user_lobs) ;
   
所用图片:oracle.jpg



9

鲜花

握手

雷人

路过

鸡蛋

刚表态过的朋友 (9 人)

QQ|手机版|Botang唐波's Oracle Station    

GMT+8, 2017-6-16 16:30 , Processed in 0.105605 second(s), 21 queries .

返回顶部