1 Reply Latest reply: Jun 27, 2012 10:30 AM by TonyM RSS

    Schema Level Table Compression Ratio

    -Richard
      I modified the below script to provide Schema Level Compression Savings; feel free to modify it to show database level compression savings
      http://tylermuth.wordpress.com/2010/10/21/formatting-output-of-11-2-compression-advisor-dbms_compression/

      set serveroutput on;

      drop type Table_Compression_Stats;
      drop type Table_Compression_Stat;

      create or replace type table_compression_stat as object (
      table_name varchar2(100),
      partition_name varchar2(30),
      comments varchar2(50),
      current_size_mb number,
      num_rows number,
      oltp_mb number,
      query_high_mb number,
      query_low_mb number,
      archive_high_mb number,
      archive_low_mb number,
      oltp_ratio number,
      query_high_ratio number,
      query_low_ratio number,
      archive_high_ratio number,
      archive_low_ratio number);
      /


      create type table_compression_stats as table of table_compression_stat;
      /


      drop table compression_ratio_values;

      create table compression_ratio_values
      (
      table_name VARCHAR2(100),
      blkcnt_cmp NUMBER,
      blkcnt_uncmp NUMBER,
      row_cmp NUMBER,
      row_uncmp NUMBER,
      cmp_ratio NUMBER,
      comptype_str VARCHAR2(100)
      );

      create or replace procedure get_compression_stats
      authid current_user
      is
      pragma autonomous_transaction;
      l_stats_obj Table_Compression_Stat;
      v_blkcnt_cmp pls_integer;
      v_blkcnt_uncmp pls_integer;
      v_row_cmp pls_integer;
      v_row_uncmp pls_integer;
      v_cmp_ratio number;
      v_comptype_str varchar2(60);
      begin
      for c1 in (SELECT t1.table_name,p.partition_name
      FROM user_tables t1, user_tab_partitions p
      where t1.table_name = p.table_name(+)
      and t1.compression = 'DISABLED'
                and t1.num_rows !=0 -- Bug 11886736
      )
      loop
      l_stats_obj := Table_Compression_Stat(null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);
      l_stats_obj.table_name := c1.table_name;
      dbms_compression.get_compression_ratio(
      scratchtbsname => 'USERS', -- scratch tablespace
      ownname => USER,
      tabname => c1.table_name,
      partname => NULL,
      comptype => dbms_compression.COMP_FOR_OLTP,
      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);

      Insert into
      compression_ratio_values
      values
      (
      c1.table_name,
      v_blkcnt_cmp,
      v_blkcnt_uncmp,
      v_row_cmp,
      v_row_uncmp,
      v_cmp_ratio,
      v_comptype_str
      )
      ;
      commit;

      end loop; -- c1
      exception when others then
      dbms_output.put_line(SQLERRM);
      dbms_output.put_line(l_stats_obj.table_name);
      end get_compression_stats;
      /

      show errors
      /

      EXECUTE get_compression_stats;


      select avg(cmp_ratio) "Avg Schema Compression Ratio" from compression_ratio_values
      /


      select sum(bytes)/1024/1024 MB from user_segments where segment_type = 'TABLE'
      /


      WITH
      X AS
      (
      select sum((seg.bytes/1024/1024)/comp.cmp_ratio) post
      from user_segments seg, compression_ratio_values comp
      where
      seg.segment_type = 'TABLE'
      and
      comp.table_name = seg.segment_name
      ),
      Y AS
      (
      select sum(seg.bytes)/1024/1024 MB from user_segments seg, compression_ratio_values comp
      where
      seg.segment_type = 'TABLE'
      and
      comp.table_name = seg.segment_name
      )
      SELECT Y.MB "Schema Tables Before Compression(MB)", X.post "Schema Tables After Compression(MB)"
      FROM X,Y
      /