I had a need to turn on auditing of SYS operations and FGA on some tables. Additionally, I preferred to have the audit destination be the DB. I had moved bot the SYS.AUD$ and the SYS.FGA_LOG$ out of SYSAUX to their own tablespace. After collecting some data, I was curious the see if compressing the tables would allow me to store more data. This was on a 12.1.0.2.0 – 64bit Unix environment.
Run the GET_COMPRESSION_RATIO procedures as follows:
SET SERVEROUTPUT ON
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(32767);
BEGIN
DBMS_COMPRESSION.get_compression_ratio (
scratchtbsname => 'USERS',
ownname => 'SYS',
objname => 'AUD$',
subobjname => NULL,
comptype => DBMS_COMPRESSION.comp_advanced,
blkcnt_cmp => l_blkcnt_cmp,
blkcnt_uncmp => l_blkcnt_uncmp,
row_cmp => l_row_cmp,
row_uncmp => l_row_uncmp,
cmp_ratio => l_cmp_ratio,
comptype_str => l_comptype_str,
subset_numrows => DBMS_COMPRESSION.comp_ratio_allrows,
objtype => DBMS_COMPRESSION.objtype_table
);
DBMS_OUTPUT.put_line('Number of blocks used (compressed) : ' || l_blkcnt_cmp);
DBMS_OUTPUT.put_line('Number of blocks used (uncompressed) : ' || l_blkcnt_uncmp);
DBMS_OUTPUT.put_line('Number of rows in a block (compressed) : ' || l_row_cmp);
DBMS_OUTPUT.put_line('Number of rows in a block (uncompressed) : ' || l_row_uncmp);
DBMS_OUTPUT.put_line('Compression ratio : ' || l_cmp_ratio);
DBMS_OUTPUT.put_line('Compression type : ' || l_comptype_str);
END;
/
The output looks like:
Number of blocks used (compressed) : 252 Number of blocks used (uncompressed) : 459 Number of rows in a block (compressed) : 70 Number of rows in a block (uncompressed) : 38 Compression ratio : 1.8 Compression type : "Compress Advanced"
This indicates that we can store about twice the number of rows in the same amount of space.
