Compression Advisor

Preamble

Compression Advisor is a new advisor coming with Oracle Database 11gR2. You can access it using DBMS_COMPRESSION package. In Oracle Database 11g: New Features for Administrators Release 2 they claim you can also use DBMS_ADVISOR but it is written exactly the opposite in Official documentation…

List of 11gR2 advisors, tested on Red Hat Enterprise Linux Server release 5.6 (Tikanga) with Oracle Database 11.2.0.2.0 Enterprise Edition:

SQL> SELECT * FROM dba_advisor_definitions;
 
ADVISOR_ID ADVISOR_NAME                     PROPERTY
---------- ------------------------------ ----------
         1 ADDM                                    1
         2 SQL ACCESS Advisor                    271
         3 Undo Advisor                            1
         4 SQL Tuning Advisor                    935
         5 SEGMENT Advisor                        67
         6 SQL Workload Manager                    0
         7 Tune MView                             31
         8 SQL Performance Analyzer              935
         9 SQL Repair Advisor                    679
        10 Compression Advisor                     3
 
10 ROWS selected.

Compression Advisor with DBMS_COMPRESSION

SQL> SET serveroutput ON SIZE 999999
SQL> SET lines 150
SQL> DECLARE
       blkcnt_cmp PLS_INTEGER;
       blkcnt_uncmp PLS_INTEGER;
       row_perblk_cmp PLS_INTEGER;
       row_perblk_uncmp PLS_INTEGER;
       cmp_ratio NUMBER;
       comptype_str VARCHAR2(30);
     BEGIN
       dbms_compression.get_compression_ratio('USER_TEMP', 'REPDB', 'MFG_LOT_QTY', NULL, dbms_compression.comp_for_oltp, blkcnt_cmp,
       blkcnt_uncmp, row_perblk_cmp, row_perblk_uncmp, cmp_ratio, comptype_str);
       dbms_output.put_line('Number of blocks used by compressed sample of the table: ' || TO_CHAR(blkcnt_cmp));
       dbms_output.put_line('Number of blocks used by uncompressed sample of the table: ' || TO_CHAR(blkcnt_uncmp));
       dbms_output.put_line('Number of rows in a block in compressed sample of the table: ' || TO_CHAR(row_perblk_cmp));
       dbms_output.put_line('Number of rows in a block in uncompressed sample of the table: ' || TO_CHAR(row_perblk_uncmp));
       dbms_output.put_line('Compression ratio (blkcnt_uncmp divided by blkcnt_cmp): ' || TO_CHAR(cmp_ratio));
       dbms_output.put_line('Compression type: ' || comptype_str);
     END;
     /
NUMBER OF blocks used BY compressed sample OF the TABLE: 635
NUMBER OF blocks used BY uncompressed sample OF the TABLE: 1751
NUMBER OF ROWS IN a block IN compressed sample OF the TABLE: 189
NUMBER OF ROWS IN a block IN uncompressed sample OF the TABLE: 68
Compression ratio (blkcnt_uncmp divided BY blkcnt_cmp): 2.7
Compression TYPE: "Compress For OLTP"
 
PL/SQL PROCEDURE successfully completed.

So we see we could divide object size by 2.7 using OLTP Compression. Problem is that Oracle do provide compression ratio advices only on OLTP and Exadata compressions that are either paid Enterprise option or pretty expensive hardware. Nothing suggested for basic compression that is included in Enterprise Edition which makes more or less useless this advisor…

Compression Advisor with DBMS_ADVISOR

So feasible using DBMS_ADVISOR for Compression Advisor ? Nothing documented on MOS or Official Documentation and no references on Internet. To test it I have using same approach as SQL Access Advisor:

DECLARE
  task_id NUMBER;
  task_name VARCHAR2(30);
  obj_id NUMBER;
BEGIN
  task_name := 'Task01';
 
  DBMS_ADVISOR.CREATE_TASK('Compression Advisor', task_id, task_name, 'Task for table REPDB.MFG_LOT_QTY');
  DBMS_ADVISOR.CREATE_OBJECT(task_name,'TABLE','REPDB','MFG_LOT_QTY',NULL,NULL,obj_id);
  DBMS_ADVISOR.SET_TASK_PARAMETER(task_name, 'MODE', 'COMPREHENSIVE');
  DBMS_ADVISOR.EXECUTE_TASK(task_name);
END;
/

To be honest I’m not really sure I should use DBMS_ADVISOR.CREATE_OBJECT but as nothing is documented… Task is successfully executed let’s display result if available:

SQL> SELECT message,more_info FROM DBA_ADVISOR_FINDINGS WHERE task_name='Task01';
 
no ROWS selected
 
SQL>  SELECT benefit_type FROM DBA_ADVISOR_RECOMMENDATIONS WHERE task_name='Task01';
 
no ROWS selected
 
SQL> SELECT DBMS_ADVISOR.GET_TASK_REPORT('Task01') FROM dual;
 
DBMS_ADVISOR.GET_TASK_REPORT('TASK01')
--------------------------------------------------------------------------------
 
 
SQL>  SELECT DBMS_ADVISOR.GET_TASK_SCRIPT('Task01') FROM dual;
ERROR:
ORA-13613: The requested operation IS NOT supported FOR this advisor object.
ORA-06512: AT "SYS.PRVT_ADVISOR", line 3350
ORA-06512: AT "SYS.DBMS_ADVISOR", line 641
ORA-06512: AT line 1
 
 
 
no ROWS selected

No result, let’s try with famous show_recm procedure found in Performance Tuning guide:

SQL> SET serveroutput ON SIZE 99999
SQL> EXEC show_recm('Task01');
=========================================
Task_name = Task01
=========END RECOMMENDATIONS============
 
PL/SQL PROCEDURE successfully completed.

Again nothing, maybe in 12c they will improved DBMS_COMPRESSION to handle all compression options and/or make DBMS_ADVISOR more generic…

About Post Author

Share the knowledge!

4 thoughts on “Compression Advisor

  1. It seems that the dbms_advisor is not an alternative of dbms_compression at least for ‘compression advisor’

  2. Hi,
    I test it in 12.1.0.1, but get the same result with your test in 11gR2
    test environments:12.1.0.1.0
    create table scott.adv1 tablespace PDB13DEF as select * from dba_objects;
    insert into scott.adv1 select * from dba_objects;
    insert into scott.adv1 select * from dba_objects;
    commit;
    select count(*) from scott.adv1;
    COUNT(*)
    ———-
    271413

    select segment_name,owner,bytes from dba_segments where segment_name=’ADV1′;
    SEGMENT_NAME OWNER BYTES
    —————————————- —————————————- ———-
    ADV1 SCOTT 38797312

    ##create task for Compression Advisor
    set serveroutput on
    declare
    advname varchar2(100);
    task_id pls_integer;
    task_name varchar2(100);
    begin
    advname:=’Compression Advisor’;
    task_name:=’comp1′;
    DBMS_ADVISOR.CREATE_TASK(advisor_name=>advname,task_id=>task_id,task_name=>task_name);
    dbms_output.put_line(task_id||’ ‘||task_name);
    end;
    /
    output:11 comp1

    ##retrieve task information just created
    col owner format a20
    col task_name format a10
    col advisor_name format a30
    select owner,task_id,task_name,advisor_name,created from dba_advisor_tasks where task_name=’comp1′;
    OWNER TASK_ID TASK_NAME ADVISOR_NAME CREATED
    ——————– ———- ———- —————————— ———
    SYS 11 comp1 Compression Advisor 07-JUL-14

    ##create object
    set serveroutput on
    declare
    v_objid number;
    task_name varchar2(100);
    object_type varchar2(100);
    schema varchar2(100);
    tabname varchar2(100);
    begin
    task_name:=’comp1′;
    object_type:=’TABLE’;
    schema:=’SCOTT’;
    tabname:=’ADV1′;
    DBMS_ADVISOR.CREATE_OBJECT(task_name=>task_name,object_type=>object_type,attr1=>schema,attr2=>tabname,attr3=>NULL,attr4=>NULL,attr5=>NULL,object_id=>v_objid);
    dbms_output.put_line(v_objid||’ ‘||task_name);
    end;
    /
    output:1 comp1

    ##retrieve the object contained in the task
    col type format a30
    col attr1 format a10
    col attr2 format a20
    col owner format a20
    col task_name format a10
    col advisor_name format a30
    select owner,object_id,type,task_name,attr1,attr2 from dba_advisor_objects where task_name=’comp1’;
    OWNER OBJECT_ID TYPE TASK_NAME ATTR1 ATTR2
    ——————– ———- —————————— ———- ———- ——————–
    SYS 1 TABLE comp1 SCOTT ADV1

    ##execute task
    variable task_name varchar2(100);
    exec :task_name:=’comp1′;
    exec DBMS_ADVISOR.EXECUTE_TASK (task_name=>:task_name);

    ##get the result of the task
    set serveroutput on
    declare
    ret_clob clob;
    task_name varchar2(100):=’comp1’;
    begin
    ret_clob:=dbms_advisor.get_task_report(task_name=>task_name);
    dbms_output.put_line(ret_clob);
    end;
    /

    Nothing output.

    ##Query the following dba_ view but also get nothings
    select * from dba_advisor_findings;
    select * from DBA_ADVISOR_RECOMMENDATIONS;

  3. Lajos Sarecz says:

    Hi,
    BASIC and Advanced Compression use the same algorithms, so the compression ratio is valid for the free of charge BASIC compression, as well.
    Regards,
    Lajos

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>