Monday, September 30, 2013

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.

  v_owner            VARCHAR2(60);
  v_segment_name      VARCHAR2(60);
  v_execute112 varchar2(4000);
  v_execute10 varchar2(4000);
  v_execute varchar2(4000);
  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'));
    v_execute10 := q'[declare
                      l_compression     NUMBER:= 10;
                      begin dbms_comp_advisor.getratio(:v_owner,:v_segment_name,l_compression);
  if substr('&db_version',1,4)='11.2' or substr('&&db_version',1,2)='12' then
   v_execute := v_execute112;
    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,
                    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, 
                        to_char(last_analyzed, 'DD-MON-RRRR')
                having  sum(dba_extents.bytes/1024/1024) > 10
              order by SizeMB desc)
              where rownum <= 50)
    v_owner := rec.owner;
    v_segment_name := rec.segment_name;
    execute immediate v_execute using in v_owner,v_segment_name;
    END LOOP; 

No comments:

Post a Comment