Compression Advisor script - from 9i to 12c

A compression advisor (through DBMS_COMPRESSION) comes packaged with Oracle Database 11g Release 2 and Oracle Database 12c.

Oracle has made available a package you can use with Oracle Database 9i Release 2 through Oracle Database 11g release 1 - this is DBMS_COMP_ADVISOR available for download here.

This script is made to run for all versions, 9i to 12c, and requires only the database version as an input.

DECLARE

  v_owner            VARCHAR2(60);

  v_segment_name      VARCHAR2(60);

  v_execute112 varchar2(4000);

  v_execute10 varchar2(4000);

  v_execute varchar2(4000);

BEGIN       

  v_execute112 :=  q'[DECLARE

                      l_blkcnt_cmp       pls_integer;

                      l_blkcnt_uncmp     pls_integer;

                      l_row_cmp          pls_integer;

                      l_row_uncmp        pls_integer;

                      l_cmp_ratio        NUMBER;

                      l_comptype_str     VARCHAR2(60);

        begin dbms_compression.get_compression_ratio(

        -- input parameters

        scratchtbsname   => 'SYSAUX',       -- scratch tablespace

        ownname          => :v_owner,            -- owner of the table

        tabname          => :v_segment_name,    -- table name

        partname         => NULL,            -- partition name

        comptype         => dbms_compression.COMP_FOR_OLTP,      -- compression algorithm

        -- output parameters

        blkcnt_cmp       => l_blkcnt_cmp,    -- number of compressed blocks

        blkcnt_uncmp     => l_blkcnt_uncmp,  -- number of uncompressed blocks

        row_cmp          => l_row_cmp,       -- number of rows in a compressed block

        row_uncmp        => l_row_uncmp,     -- number of rows in an uncompressed block

        cmp_ratio        => l_cmp_ratio,     -- compression ratio

        comptype_str     => l_comptype_str   -- compression type

      ); 

      dbms_output.put_line('SegmentOwner: '||:v_owner||' SegmentName: '||:v_segment_name||' - '||' Ratio: '||to_char(l_cmp_ratio,'99.99'));

      end;]';

    v_execute10 := q'[declare

                      l_compression     NUMBER:= 10;

                      begin dbms_comp_advisor.getratio(:v_owner,:v_segment_name,l_compression);

                      end;]';

  if substr('&db_version',1,4)='11.2' or substr('&&db_version',1,2)='12' then

   v_execute := v_execute112;

  else 

    v_execute := v_execute10;

  end if;

  FOR rec IN (select segment_name, owner

            from (

              select * from

                    (select dba_tables.owner, segment_name, segment_type, dba_extents.tablespace_name, round(sum(dba_extents.bytes/1024/1024),2) SizeMB,

                            dba_tables.num_rows 

                    from dba_extents, dba_tables

                     where segment_type = 'TABLE' 

                     and  dba_tables.table_name = dba_extents.segment_name

                     and dba_tables.compression = 'DISABLED'

                group by dba_tables.owner, segment_name, segment_type, dba_extents.tablespace_name, 

                        dba_tables.num_rows,  

                        to_char(last_analyzed, 'DD-MON-RRRR')

                having  sum(dba_extents.bytes/1024/1024) > 10

              )

              order by SizeMB desc)

              where rownum <= 50)

  

   LOOP

    v_owner := rec.owner;

    v_segment_name := rec.segment_name;

    execute immediate v_execute using in v_owner,v_segment_name;

    END LOOP; 

END;

/