I’ve been doing a lot of testing with COMPRESS FOR ALL OPERATIONS (OLTP compression) recently and spent a bit of time with the Compression Advisor. This post documents a few items I noticed along the way.
I picked up instructions on how to run the Compression Advisor from My Oracle Support note “Compression Advisory in 11GR2: Using DBMS_COMPRESSION [ID 762974.1]“. The note advises me to create a new tablespace and informs me that two temporary segments are created.
Please create a tablespace before running compression advisor as it creates two temporary tables and will do significant amount of I/O traffic against them.
Below is a sneak preview of the temporary segments that will be created.
create table "ACME".DBMS_TABCOMP_TEMP_UNCMP tablespace "SCRATCH" nologging as select /*+ DYNAMIC_SAMPLING(0) FULL("ACME"."ACCS") */ * from "ACME"."ACCS" sample block( 99) mytab create table "ACME".DBMS_TABCOMP_TEMP_CMP organization heap tablespace "SCRATCH" compress for all operations nologging as select /*+ DYNAMIC_SAMPLING(0) */ * from "ACME".DBMS_TABCOMP_TEMP_UNCMP mytab
DBMS_COMPRESSION runs using invoker rights so the privileges of the user I log on as are relevant.
select procedure_name, authid from dba_procedures where owner = 'SYS' and object_name = 'DBMS_COMPRESSION'; PROCEDURE_NAME AUTHID ------------------------------ ------------ INCREMENTAL_COMPRESS CURRENT_USER GET_COMPRESSION_TYPE CURRENT_USER GET_COMPRESSION_RATIO CURRENT_USER CURRENT_USER
Taking all of the above into account I’ve created a new SCRATCH tablespace for my temporary segments and will run the analysis logged in as myself and not the schema user who owns the segment I want to compress. As we at ORAganism are so security minded ;-) I am not permitted to have the standard “dba” role and have a cut down one with only the permissions I need.
create tablespace scratch datafile '+data' size 50m; create role nj_dba; create user nj identified by nj; grant create session, create any table, drop any table, select any table to nj_dba; grant execute on sys.dbms_monitor to nj_dba; grant nj_dba to nj;
Also according to note “ORA-01536: space quota exceeded for tablespace along with DBMS_COMPRESSION.GET_COMPRESSION_RATIO [ID 970324.1]” I need to give the owner of the segment being analysed a quota on my scratch tablespace.
To get rid of the errors, the user specified as the second parameter in the DBMS_COMPRESSION.GET_COMPRESSION_RATIO need to have … some QUOTA over the scratch tablespace specified by the first parameter.
alter user acme quota unlimited on scratch;
I have created my script to call the advisor, dbms_comp.sql, as below.
set serveroutput on set feedback on set verify off declare blkcnt_cmp BINARY_integer; blkcnt_uncmp BINARY_integer; row_cmp BINARY_integer; row_uncmp BINARY_integer; cmp_ratio number; comptype_str varchar2(60); begin dbms_compression.get_compression_ratio( scratchtbsname => upper('&3.') , ownname => upper('&1.') , tabname => upper('&2.') , partname => null , comptype => dbms_compression.comp_for_oltp , blkcnt_cmp => blkcnt_cmp , blkcnt_uncmp => blkcnt_uncmp , row_cmp => row_cmp , row_uncmp => row_uncmp , cmp_ratio => cmp_ratio , comptype_str => comptype_str , subset_numrows => &4. ); DBMS_OUTPUT.PUT_LINE('Block count compressed = ' || blkcnt_cmp); DBMS_OUTPUT.PUT_LINE('Block count uncompressed = ' || blkcnt_uncmp); DBMS_OUTPUT.PUT_LINE('Row count per block compressed = ' || row_cmp); DBMS_OUTPUT.PUT_LINE('Row count per block uncompressed = ' || row_uncmp); --DBMS_OUTPUT.PUT_LINE('Compression type = ' ||comptype_str); DBMS_OUTPUT.PUT_LINE('Compression ratio = '||round(blkcnt_uncmp/blkcnt_cmp,1)||' to 1'); DBMS_OUTPUT.PUT_LINE('Compression % benefit = '||round((blkcnt_uncmp-blkcnt_cmp)/blkcnt_uncmp*100,1)); --DBMS_OUTPUT.PUT_LINE('Compression ratio org= '||cmp_ratio); end; / set verify on
So we should be good to go:
conn nj/nj @orcl> @dbms_comp acme accs scratch 200000 declare * ERROR at line 1: ORA-20000: Compression Advisor tablespace "SCRATCH" does not exist ORA-06512: at "SYS.PRVT_COMPRESSION", line 776 ORA-06512: at "SYS.DBMS_COMPRESSION", line 214 ORA-06512: at line 9
With SQL Trace enabled I see the following in the trace file.
PARSING IN CURSOR #139655103675152 len=64 dep=1 uid=37 oct=3 lid=37 tim=1357852093619559 hv=814796271 ad='67de3b40' sqlid='g6pggnss91mgg' SELECT count(*) FROM user_tablespaces WHERE tablespace_name= :b1 END OF STMT ... BINDS #139655103675152: Bind#0 ... value="SCRATCH"
Even though the temporary segments are created in the same schema as the object being analysed my own account also needs a quota on the scratch tablespace. No segments are created in my schema but I need the quota in order to get past the check on “user_tablespaces”.
alter user nj quota unlimited on scratch;
Let’s try again:
@orcl> @dbms_comp acme accs scratch 200000 declare * ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "SYS.PRVT_COMPRESSION", line 776 ORA-06512: at "SYS.DBMS_COMPRESSION", line 214 ORA-06512: at line 9
I couldn’t find anything about this in M.O.S or find any evidence from SQL Trace. However the power of trial and error tracked the missing privilege down to “ANALYZE ANY” (I could have granted OEM_MONITOR to provide this privilege but we’re all about the principle of least privilege today).
grant ANALYZE ANY to NJ_DBA;
Let’s try again… again:
@orcl> @dbms_comp acme accs scratch 200000 Block count compressed = 1612 Block count uncompressed = 1632 Row count per block compressed = 123 Row count per block uncompressed = 121 Compression ratio = 1 to 1 Compression % benefit = 1.2 PL/SQL procedure successfully completed. Elapsed: 00:00:09.82
Sadly my table is not going to benefit from compression but it’s a success nonetheless. So the full list of required privileges is:
grant CREATE SESSION to NJ_DBA; grant ANALYZE ANY to NJ_DBA; grant CREATE ANY TABLE to NJ_DBA; grant DROP ANY TABLE to NJ_DBA; grant SELECT ANY TABLE to NJ_DBA;
You also need a quota for your schema user and an unnecessary quota for the executing user.
If you use VPD then don’t forget to take that into account.
And one last point, the temporary tables are created in thje target schema and the names are not session/object specific so don’t try to run Compress Advisor calls in parallel:
@orcl> @dbms_comp acme accs scratch 200000 declare * ERROR at line 1: ORA-00955: name is already used by an existing object ORA-06512: at "SYS.PRVT_COMPRESSION", line 776 ORA-06512: at "SYS.DBMS_COMPRESSION", line 214 ORA-06512: at line 9