联系：QQ(5163721)
标题：Exadata上的HCC测试（EHCC）—2—：DBMS_COMPRESSION.GET_COMPRESSION_RATIO
作者：Lunar©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]
今天累了不多说了，官方文档都有：
使用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>
