[20180814]慎用查看表压缩率脚本.txt
–//最近看exadata方面书籍,书中提供1个脚本,查看某些表采用那些压缩模式压缩比能达到多少.
–//通过调用DBMS_COMPRESSION.get_compression_ratio确定压缩比.例子如下:
–//测试版本11.2.0.4.
declare
blockct_comp number;
blockct_uncomp number;
rows_comp number;
rows_uncomp number;
comp_rat number;
comp_type varchar2(40);
begin
dbms_compression.get_compression_ratio(‘&&tblspc’,’&&ownr’,’&&tblname’,null, dbms_compression.comp_for_oltp, blockct_comp, blockct_uncomp,rows_comp,rows_uncomp, comp_rat, comp_type);
dbms_output.put_line(‘Compression type: ‘||comp_type||’ Compression ratio (est):’||comp_rat);
dbms_compression.get_compression_ratio(‘&&tblspc’,’&&ownr’,’&&tblname’,null, dbms_compression.comp_for_query_low, blockct_comp, blockct_uncomp,rows_comp,rows_uncomp, comp_rat, comp_type);
dbms_output.put_line(‘Compression type: ‘||comp_type||’ Compression ratio (est):’||comp_rat);
dbms_compression.get_compression_ratio(‘&&tblspc’,’&&ownr’,’&&tblname’,null, dbms_compression.comp_for_query_high, blockct_comp, blockct_uncomp,rows_comp,rows_uncomp, comp_rat, comp_type);
dbms_output.put_line(‘Compression type: ‘||comp_type||’ Compression ratio (est):’||comp_rat);
dbms_compression.get_compression_ratio(‘&&tblspc’,’&&ownr’,’&&tblname’,null, dbms_compression.comp_for_archive_low, blockct_comp, blockct_uncomp,rows_comp,rows_uncomp, comp_rat, comp_type);
dbms_output.put_line(‘Compression type: ‘||comp_type||’ Compression ratio (est):’||comp_rat);
dbms_compression.get_compression_ratio(‘&&tblspc’,’&&ownr’,’&&tblname’,null, dbms_compression.comp_for_archive_high,blockct_comp, blockct_uncomp,rows_comp,rows_uncomp, comp_rat, comp_type);
dbms_output.put_line(‘Compression type: ‘||comp_type||’ Compression ratio (est):’||comp_rat);
end;
/
–//好奇心我想看看生产系统一张大表能达到多少.我执行上面的脚本,结果等大约2-3分钟没有结果出来,我马上中断处理.
–//我当时想既然大表可能分析数据量大,换1个点的表看看.
–//结果执行后包如下错误:
ERROR at line 1:
ORA-20000: YOU CAN NOT TRUNCATE or DROP CMP3$97116 TABLE!
ORA-06512: at line 6
ORA-06512: at “SYS.PRVT_COMPRESSION”, line 1136
ORA-06512: at “SYS.PRVT_COMPRESSION”, line 1114
ORA-20000: YOU CAN NOT TRUNCATE or DROP CMP1$97116 TABLE!
ORA-06512: at “SYS.DBMS_COMPRESSION”, line 214
ORA-06512: at line 9
–//BTW:我们生产系统有系统触发器,禁止用户drop和truncate表.这样导致脚本运行报错.
–//我看了一下CMP3$97116,CMP1$97116表结果,和分析表结构一致.
CREATE TABLE xxxxxx_yyy.CMP4$97116
(
ZYH NUMBER(18) NOT NULL,
….
YB_DBZ VARCHAR2(4 BYTE)
)
TABLESPACE xxxxxx_yyy
RESULT_CACHE (MODE DEFAULT)
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOLOGGING
COMPRESS FOR OLTP
~~~~~~~~~~~~~~~
NOCACHE
NOPARALLEL
MONITORING;
–//可以看出DBMS_COMPRESSION.get_compression_ratio操作很简单,先建立与分析表一样的表结构以及对应压缩模式的表,然后
–//导入数据后比较分析压缩比.
–//这样要耗费大量表空间与资源做这个工作,在生产系统要小心谨慎.
–//我事后认真看了<深入理解ORACLE Exadata> P98页.而是讲样本数据插入一个临时表中.同时压缩版本的临时表也被创建,比较压缩
–//版本和非压缩版本的大小就可以得到压缩率.
–//(注:我看到的不是临时表,而是真实的表,看上面的表定义.或许作者理解的临时表非我理解的临时表)
–//我不知道取样比例是多少,总之在生产系统执行该脚本还是要小心.
–//另外书P101提到 压缩助手的一大亮点是能够在非exadata平台上运行,在真正迁移数据到exadata平台之前,它能够提供足够的信息
–//帮助你做出合理的选择.这么讲非exadata平台还是能够建立hcc压缩模式的相关数据,只不过你不能查看.
–//我曾经在dg上查看压缩表信息,链接[20150727]exadata压缩HCC与dataguard.txt=>http://blog.itpub.net/267265/viewspace-1753362/
XXXX@zzzzdg2> select * from t where rownum<=1;
select * from t where rownum<=1
*
ERROR at line 1:
ORA-64307: Exadata Hybrid Columnar Compression is not supported for tablespaces on this storage type
–//在家里测试的结果.
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
—————————— ————– ——————————————————————————– ———-
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production 0
create table t as select * from all_objects ;
–//反复插入,提示要大于1000000rows才可以.
SCOTT@test01p> select count(*) from t;
COUNT(*)
———-
1437952
–//占用192M.
Compression type: “Compress Advanced” Compression ratio (est):3.5
Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows
Compression type: “Compress Query Low” Compression ratio (est):8.6
Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows
Compression type: “Compress Query High” Compression ratio (est):16.3
Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows
Compression type: “Compress Archive Low” Compression ratio (est):16.6
Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows
Compression type: “Compress Archive High” Compression ratio (est):21.7
PL/SQL procedure successfully completed.
–//如果单独执行如下:
–//在sys用户下建立触发器禁止drop表.
CREATE OR REPLACE TRIGGER SYS.TRI_PREVENT_DROP_TRUNCATE
BEFORE TRUNCATE OR DROP ON DATABASE
BEGIN
IF ora_dict_obj_type = ‘TABLE’ AND ora_dict_obj_owner = ‘SCOTT’ and ORA_DICT_OBJ_NAME not like ‘SYS\_JOURNAL\_%’ escape ‘\’
THEN
raise_application_error (-20000, ‘YOU CAN NOT TRUNCATE or DROP ‘ || ora_dict_obj_name || ‘ TABLE!’);
END IF;
END;
/
–//如果单独执行如下:
/* Formatted on 2018/8/14 8:49:08 (QP5 v5.269.14213.34769) */
set serveroutput on
DECLARE
blockct_comp NUMBER;
blockct_uncomp NUMBER;
rows_comp NUMBER;
rows_uncomp NUMBER;
comp_rat NUMBER;
comp_type VARCHAR2 (40);
BEGIN
DBMS_COMPRESSION.get_compression_ratio
(
‘&&tblspc’
,’&&ownr’
,’&&tblname’
,NULL
,DBMS_COMPRESSION.comp_archive_high
,blockct_comp
,blockct_uncomp
,rows_comp
,rows_uncomp
,comp_rat
,comp_type
);
DBMS_OUTPUT.put_line
(
‘Compression type: ‘
|| comp_type
|| ‘ Compression ratio (est):’
|| comp_rat
);
END;
/
–//注:12c参数DBMS_COMPRESSION.comp_archive_high与11g不同.11g写成DBMS_COMPRESSION.comp_for_archive_high
–//由于触发器建立,报错如下:
SCOTT@test01p> @ exadata/comp_radio12x.sql
old 11: ‘&&tblspc’
new 11: ‘USERS’
old 12: ,’&&ownr’
new 12: ,’SCOTT’
old 13: ,’&&tblname’
new 13: ,’T’
Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows
DECLARE
*
ERROR at line 1:
ORA-20000: YOU CAN NOT TRUNCATE or DROP CMP1$107873 TABLE!
ORA-06512: at line 4
ORA-06512: at “SYS.PRVT_COMPRESSION”, line 2134
ORA-06512: at “SYS.PRVT_COMPRESSION”, line 1108
ORA-20000: YOU CAN NOT TRUNCATE or DROP CMP1$107873 TABLE!
ORA-06512: at “SYS.PRVT_COMPRESSION”, line 237
ORA-06512: at “SYS.DBMS_COMPRESSION”, line 215
ORA-06512: at line 9
SCOTT@test01p> select owner,object_name,CREATED from dba_objects where owner=user and object_name like ‘CMP%’;
OWNER OBJECT_NAME CREATED
——————– ——————– ——————-
SCOTT CMP4$107873 2018-08-14 20:58:05
SCOTT CMP3$107873 2018-08-14 20:57:57
SCOTT CMP2$107873 2018-08-14 20:57:51
SCOTT CMP1$107873 2018-08-14 20:57:48
–//这次测试建立4张表.
SCOTT@test01p> select * from CMP4$107873;
select * from CMP4$107873
*
ERROR at line 1:
ORA-64307: Exadata Hybrid Columnar Compression is not supported for tablespaces on this storage type
–//可以发现oracle建立hcc表在非exadata是可行的,但是里面的数据不能看.