Exadata上的HCC测试(EHCC)—2—:DBMS_COMPRESSION.GET_COMPRESSION_RATIO

联系:QQ(5163721)

标题:Exadata上的HCC测试(EHCC)—2—:DBMS_COMPRESSION.GET_COMPRESSION_RATIO

作者:Lunar©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]

Exadata上的HCC测试(EHCC)——1

Exadata上的HCC测试(EHCC)—3—分区表的压缩

今天累了不多说了,官方文档都有:


11.2


使用DBMS_COMPRESSION时需要注意,不能两个会话同时使用,否则会报错,类似(2个会话会分别报下面的两个错误):

ERROR at line 1:
ORA-00955: name is already used by an existing object
ORA-06512: at "SYS.PRVT_COMPRESSION", line 774
ORA-06512: at "SYS.DBMS_COMPRESSION", line 214
ORA-06512: at line 26
*
ERROR at line 1:
ORA-08103: object no longer exists
ORA-06512: at "SYS.PRVT_COMPRESSION", line 774
ORA-06512: at "SYS.DBMS_COMPRESSION", line 214
ORA-06512: at line 26

貌似从MOS还是哪里找到的脚本,很好用,其主要是利用了DBMS_COMPRESSION.GET_COMPRESSION_RATIO进行压缩率的测算:

SQL>  declare
  2      
  3      /*
  4      COMP_NOCOMPRESS
  5      COMP_FOR_OLTP
  6      COMP_FOR_QUERY_HIGH 
  7      COMP_FOR_QUERY_LOW
  8      COMP_FOR_ARCHIVE_HIGH
  9      COMP_FOR_ARCHIVE_LOW
 10      */
 11  
 12      lunar_scratchtbsname    varchar2(32) := 'LUNAR';
 13      lunar_ownname       varchar2(32) := 'LUNAR';
 14      lunar_tabname       varchar2(32) := 'LUNARTEST';
 15      lunar_partname      varchar2(32) := null;
 16      lunar_comptype      number(10,0) := dbms_compression.COMP_FOR_ARCHIVE_HIGH;
 17      lunar_blkcnt_cmp        pls_integer;
 18      lunar_blkcnt_uncmp      pls_integer;
 19      lunar_row_cmp       pls_integer;
 20      lunar_row_uncmp         pls_integer;
 21      lunar_cmp_ratio         number;
 22      lunar_comptype_str      varchar2(4000);
 23  
 24  begin
 25  
 26      DBMS_COMPRESSION.GET_COMPRESSION_RATIO (
 27          scratchtbsname  => lunar_scratchtbsname,
 28          ownname     => lunar_ownname,
 29          tabname     => lunar_tabname,
 30          partname    => lunar_partname,
 31          comptype    => lunar_comptype,
 32          blkcnt_cmp  => lunar_blkcnt_cmp,
 33          blkcnt_uncmp    => lunar_blkcnt_uncmp,
 34          row_cmp     => lunar_row_cmp,
 35          row_uncmp   => lunar_row_uncmp,
 36          cmp_ratio   => lunar_cmp_ratio,
 37          comptype_str    => lunar_comptype_str
 38      );
 39  
 40      dbms_output.put_line('Blocks compressed:           ' || lunar_blkcnt_cmp);
 41      dbms_output.put_line('Blocks uncompressed:         ' || lunar_blkcnt_uncmp);
 42      dbms_output.put_line('Rows per block compressed:   ' || lunar_row_cmp);
 43      dbms_output.put_line('Rows per block uncompressed: ' || lunar_row_uncmp);
 44      dbms_output.put_line('Compression Ratio:           ' || lunar_cmp_ratio);
 45      dbms_output.put_line('Comment:                     ' || lunar_comptype_str);
 46  
 47  end;
 48  /

select name,value/1024/1024 as mb from v$statname natural join v$mystat
where name = 'cell physical IO interconnect bytes returned by smart scan';

select name,value/1024/1024 as mb from v$statname 
natural join v$mystat where name in
(
'physical read total bytes',
'cell physical IO interconnect bytes',
'cell physical IO interconnect bytes returned by smart scan'
);
Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows
Blocks compressed:           31
Blocks uncompressed:         8994
Rows per block compressed:   32258
Rows per block uncompressed: 111
Compression Ratio:           290.1
Comment:                     "Compress For Archive High"

PL/SQL procedure successfully completed.

Elapsed: 00:00:40.02
SQL> SQL>   2  
NAME                                                                     MB
---------------------------------------------------------------- ----------
cell physical IO interconnect bytes returned by smart scan                0

Elapsed: 00:00:00.00
SQL> SQL>   2    3    4    5    6    7  
NAME                                                                     MB
---------------------------------------------------------------- ----------
physical read total bytes                                        947.539063
cell physical IO interconnect bytes                              2842.55469
cell physical IO interconnect bytes returned by smart scan                0

Elapsed: 00:00:00.00
SQL> 

上面的例子是Compress For Archive High,其他依此类推,测试用例参见:《Exadata上的HCC测试(EHCC)——1》

–Compress For Archive Low:

Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows
Blocks compressed:           41
Blocks uncompressed:         8994
Rows per block compressed:   24390
Rows per block uncompressed: 111
Compression Ratio:           219.3
Comment:                     "Compress For Archive Low"

–COMP_FOR_OLTP:

Table Compress Type:         COMP_FOR_OLTP
Blocks compressed:           1558
Blocks uncompressed:         2688
Rows per block compressed:   192
Rows per block uncompressed: 111
Compression Ratio:           1.7
Comment:                     "Compress For OLTP"

–Compress For Query High:

Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows
Blocks compressed:           41
Blocks uncompressed:         8994
Rows per block compressed:   24390
Rows per block uncompressed: 111
Compression Ratio:           219.3
Comment:                     "Compress For Query High"

–Compress For Query Low:

Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows
Blocks compressed:           1928
Blocks uncompressed:         8994
Rows per block compressed:   519
Rows per block uncompressed: 111
Compression Ratio:           4.6
Comment:                     "Compress For Query Low"

–COMP_NOCOMPRESS:

Table Compress Type:         COMP_NOCOMPRESS
Blocks compressed:           0
Blocks uncompressed:         0
Rows per block compressed:   0
Rows per block uncompressed: 0
Compression Ratio:           0
Comment:

LUNAR@mylove>set lines 155
LUNAR@mylove>compute sum of totalsize_megs on report
LUNAR@mylove>break on report
LUNAR@mylove>col owner for a10
LUNAR@mylove>col segment_name for a30
LUNAR@mylove>col segment_type for a10
LUNAR@mylove>col totalsize_megs for 999,999.9
LUNAR@mylove>col compression_ratio for 999.9 
LUNAR@mylove>select owner, segment_name, segment_type type,
  2  sum(bytes/1024/1024) as totalsize_megs,
  3  &original_size/sum(bytes/1024/1024) as compression_ratio
  4  from dba_segments
  5  where owner='LUNAR'
  6  and segment_name like 'LUNAR%'
  7  and segment_type ='TABLE'
  8  group by owner, segment_name, tablespace_name, segment_type
  9  order by 5;
Enter value for original_size: 1472
old   3: &original_size/sum(bytes/1024/1024) as compression_ratio
new   3: 1472/sum(bytes/1024/1024) as compression_ratio

OWNER      SEGMENT_NAME                   TYPE               TOTALSIZE_MEGS COMPRESSION_RATIO
---------- ------------------------------ ------------------ -------------- -----------------
LUNAR      LUNARTEST                      TABLE                     1,472.0               1.0
LUNAR      LUNAR_COMP_OLTP                TABLE                       904.0               1.6
LUNAR      LUNAR_COMP_BASIC               TABLE                       736.0               2.0
LUNAR      LUNAR_COMP_QUERY_LOW           TABLE                       304.0               4.8
LUNAR      LUNAR_COMP_QUERY_HIGH          TABLE                         8.0             184.0
LUNAR      LUNAR_COMP_ARCHIVE_LOW         TABLE                         8.0             184.0
LUNAR      LUNAR_COMP_ARCHIVE_HIGH        TABLE                         5.0             294.4
                                                             --------------
sum                                                                 3,437.0

7 rows selected.

Elapsed: 00:00:00.08
LUNAR@mylove>
此条目发表在 POC和性能调整 分类目录,贴了 , , 标签。将固定链接加入收藏夹。

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>