Table of contents
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…
oliseh says:
It seems that the dbms_advisor is not an alternative of dbms_compression at least for ‘compression advisor’
oliseh says:
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;
Yannick Jaquier says:
Hi Oliseh,
Thanks for sharing your experience !!
Yannick.
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