[20180814]慎用查看表压缩率脚本.txt

[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是可行的,但是里面的数据不能看.