DBMS_COMPRESSION Example
With starting Oracle 11g compression concept, this question may arise that, how to estimate the size of tables after compression. In addition to my previous posting that, I have therefore prepared this little script that calls DBMS_COMPRESSION. Typically, the documentation also gives an example.
Please notice that you can use that script already on a non-Exadata Oracle Database (from 11.2 on) also to get an estimation about that, not only about BASIC and OLTP compression. The estimation is quite good but takes much space – about as much as the tables you estimate are in size. Therefore, it may be advisable to use a scratch tablespace only for that purpose and drop it afterwards.
-- Create table
create table hr.bigtab as
select * from dba_objects;
Note : you can append same data for 100 or 1000 times with a loop to create actually a big table.
-- Check the tablespace
select * from all_tables where table_name='BIGTAB'
-- Can be moved to any tablespace if created in SYSTEM.
alter table BIGTAB move tablespace HR_TBLSPC;
-- Gather table stats
exec dbms_stats.gather_table_stats('HR','BIGTAB');
-- Now compress the table
alter table owner.table_name compress | nocompress;
OR
ALTER TABLE owner.table_name
MODIFY PARTITION partition_name COMPRESS FOR ALL OPERATIONS;
alter table hr.bigtab compress;
-- Find compression statistics
set serveroutput on
declare
v_blkcnt_cmp pls_integer;
v_blkcnt_uncmp pls_integer;
v_row_cmp pls_integer;
v_row_uncmp pls_integer;
v_cmp_ratio number;
v_comptype_str varchar2(60);
begin
dbms_compression.get_compression_ratio(
scratchtbsname => upper('HR_TBLSPC'),
ownname => 'HR',
tabname => upper('BIGTAB'),
partname => NULL,
comptype => dbms_compression.comp_for_query_high,
blkcnt_cmp => v_blkcnt_cmp,
blkcnt_uncmp => v_blkcnt_uncmp,
row_cmp => v_row_cmp,
row_uncmp => v_row_uncmp,
cmp_ratio => v_cmp_ratio,
comptype_str => v_comptype_str);
dbms_output.put_line('Estimated Compression Ratio: '||to_char(v_cmp_ratio));
dbms_output.put_line('Blocks used by compressed sample: '||to_char(v_blkcnt_cmp));
dbms_output.put_line('Blocks used by uncompressed sample: '||to_char(v_blkcnt_uncmp));
end;
/
output:
Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows
Estimated Compression Ratio: 12.3
Blocks used by compressed sample: 1199
Blocks used by uncompressed sample: 14842
PL/SQL procedure successfully completed
Compress on Partitioned Table:
The following examples show the various compression options applied at table and partition level.
-- Table compression.
CREATE TABLE sample_tab_1 (
id NUMBER(10) NOT NULL,
description VARCHAR2(50) NOT NULL,
created_date DATE NOT NULL
)
COMPRESS FOR ALL OPERATIONS;
-- Partition-level compression.
CREATE TABLE sample_tab_2 (
id NUMBER(10) NOT NULL,
description VARCHAR2(50) NOT NULL,
created_date DATE NOT NULL
)
PARTITION BY RANGE (created_date) (
PARTITION sample_tab_q1 VALUES LESS THAN (TO_DATE('01/01/2014', 'DD/MM/YYYY')) COMPRESS,
PARTITION sample_tab_q2 VALUES LESS THAN (TO_DATE('01/04/2014', 'DD/MM/YYYY')) COMPRESS FOR DIRECT_LOAD OPERATIONS,
PARTITION sample_tab_q3 VALUES LESS THAN (TO_DATE('01/07/2014', 'DD/MM/YYYY')) COMPRESS FOR ALL OPERATIONS,
PARTITION sample_tab_q4 VALUES LESS THAN (MAXVALUE) NOCOMPRESS
);Table-level compression settings are reflected in the COMPRESSION and COMPRESS_FOR columns of the [DBA|ALL|USER]_TABLES views.
SELECT table_name, compression, compress_for FROM user_tables;
TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ -------- ------------------
sample_TAB_1 ENABLED FOR ALL OPERATIONS
sample_TAB_2
2 rows selected.
SQL>Tables defined with partition-level compression and no table-level compression display NULL values in these columns.
Partition-level compression settings are reflected in the COMPRESSION and COMPRESS_FOR columns of the [DBA|ALL|USER]_TAB_PARTITIONS views.
SELECT table_name, partition_name, compression, compress_for FROM user_tab_partitions;
Compression Advisor:
Overview:
Compression Advisor provides an estimate of the compression ratio that can be realized through the use of the Oracle Advanced Compression option. This estimate is based on analysis of a sample of data and provides a good estimate of the actual results you may obtain once you implement the OLTP Table compression feature in your environment.
The Compression Advisor PL/SQL package you use is dependent upon which Oracle Database release you currently have deployed. Those customers that want to use Compression Advisor with Oracle Database 9i Release 2 through Oracle Database 11g Release 1 will use the DBMS_COMP_ADVISOR package available for download below. Customers that want to use Compression Advisor with Oracle Database 11g Release 2 through Oracle Database 12c will use the DBMS_COMPRESSION package that is included with the database.
This package can be used on Oracle Databases running Oracle Database 9i Release 2 through 11g Release 1. A compression advisor (DBMS_COMPRESSION) is included with Oracle Database 11g Release 2 and Oracle Database 12c.
Using Compression Advisor:
This procedure can be used with Oracle Database 9i Release 2 through Oracle Database 11g Release 1. Running this procedure will create tables in the default tablespace of the user running the procedure. While these tables will get dropped at the end of the procedure they will consume space while the procedure runs. Oracle recommends creating a tablespace specifically for storing these tables and assigning it as the default tablespace to the user running the procedure. The DBMS_COMP_ADVISOR advisor package is only available as a free download.
Compression Advisor consists of the DBMS_COMP_ADVISOR package containing the following procedure:
getratio(
ownername IN varchar2,
tabname IN varchar2,
sampling_percent IN number
);
where
- 'ownername' is the schema that the table belongs to
- 'tabname' is name of the table for which compression ratio is to be estimated
- 'sampling_percent' is any value between 0.000001 and 99
The output of this procedure is the estimated compression ratio.
Example:
SQL> set serveroutput on
SQL> exec dbms_comp_advisor.getratio('SH','SALES',10);
Sampling table: SH.SALES
Sampling percentage: 10%
Expected Compression ratio with Advanced Compression option: 2.96
PL/SQL procedure successfully completed.
Note: Compression Advisor concepts collected from Oracle magazine.
With starting Oracle 11g compression concept, this question may arise that, how to estimate the size of tables after compression. In addition to my previous posting that, I have therefore prepared this little script that calls DBMS_COMPRESSION. Typically, the documentation also gives an example.
Please notice that you can use that script already on a non-Exadata Oracle Database (from 11.2 on) also to get an estimation about that, not only about BASIC and OLTP compression. The estimation is quite good but takes much space – about as much as the tables you estimate are in size. Therefore, it may be advisable to use a scratch tablespace only for that purpose and drop it afterwards.
-- Create table
create table hr.bigtab as
select * from dba_objects;
Note : you can append same data for 100 or 1000 times with a loop to create actually a big table.
-- Check the tablespace
select * from all_tables where table_name='BIGTAB'
-- Can be moved to any tablespace if created in SYSTEM.
alter table BIGTAB move tablespace HR_TBLSPC;
-- Gather table stats
exec dbms_stats.gather_table_stats('HR','BIGTAB');
-- Now compress the table
alter table owner.table_name compress | nocompress;
OR
ALTER TABLE owner.table_name
MODIFY PARTITION partition_name COMPRESS FOR ALL OPERATIONS;
alter table hr.bigtab compress;
-- Find compression statistics
set serveroutput on
declare
v_blkcnt_cmp pls_integer;
v_blkcnt_uncmp pls_integer;
v_row_cmp pls_integer;
v_row_uncmp pls_integer;
v_cmp_ratio number;
v_comptype_str varchar2(60);
begin
dbms_compression.get_compression_ratio(
scratchtbsname => upper('HR_TBLSPC'),
ownname => 'HR',
tabname => upper('BIGTAB'),
partname => NULL,
comptype => dbms_compression.comp_for_query_high,
blkcnt_cmp => v_blkcnt_cmp,
blkcnt_uncmp => v_blkcnt_uncmp,
row_cmp => v_row_cmp,
row_uncmp => v_row_uncmp,
cmp_ratio => v_cmp_ratio,
comptype_str => v_comptype_str);
dbms_output.put_line('Estimated Compression Ratio: '||to_char(v_cmp_ratio));
dbms_output.put_line('Blocks used by compressed sample: '||to_char(v_blkcnt_cmp));
dbms_output.put_line('Blocks used by uncompressed sample: '||to_char(v_blkcnt_uncmp));
end;
/
output:
Compression Advisor self-check validation successful. select count(*) on both Uncompressed and EHCC Compressed format = 1000001 rows
Estimated Compression Ratio: 12.3
Blocks used by compressed sample: 1199
Blocks used by uncompressed sample: 14842
PL/SQL procedure successfully completed
Compress on Partitioned Table:
The following examples show the various compression options applied at table and partition level.
-- Table compression.
CREATE TABLE sample_tab_1 (
id NUMBER(10) NOT NULL,
description VARCHAR2(50) NOT NULL,
created_date DATE NOT NULL
)
COMPRESS FOR ALL OPERATIONS;
-- Partition-level compression.
CREATE TABLE sample_tab_2 (
id NUMBER(10) NOT NULL,
description VARCHAR2(50) NOT NULL,
created_date DATE NOT NULL
)
PARTITION BY RANGE (created_date) (
PARTITION sample_tab_q1 VALUES LESS THAN (TO_DATE('01/01/2014', 'DD/MM/YYYY')) COMPRESS,
PARTITION sample_tab_q2 VALUES LESS THAN (TO_DATE('01/04/2014', 'DD/MM/YYYY')) COMPRESS FOR DIRECT_LOAD OPERATIONS,
PARTITION sample_tab_q3 VALUES LESS THAN (TO_DATE('01/07/2014', 'DD/MM/YYYY')) COMPRESS FOR ALL OPERATIONS,
PARTITION sample_tab_q4 VALUES LESS THAN (MAXVALUE) NOCOMPRESS
);Table-level compression settings are reflected in the COMPRESSION and COMPRESS_FOR columns of the [DBA|ALL|USER]_TABLES views.
SELECT table_name, compression, compress_for FROM user_tables;
TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ -------- ------------------
sample_TAB_1 ENABLED FOR ALL OPERATIONS
sample_TAB_2
2 rows selected.
SQL>Tables defined with partition-level compression and no table-level compression display NULL values in these columns.
Partition-level compression settings are reflected in the COMPRESSION and COMPRESS_FOR columns of the [DBA|ALL|USER]_TAB_PARTITIONS views.
SELECT table_name, partition_name, compression, compress_for FROM user_tab_partitions;
output:
TABLE_NAME PARTITION_NAME COMPRESS COMPRESS_FOR
------------------------------ ------------------------------ -------- ------------------
sample_TAB_2 sample_TAB_Q1 ENABLED DIRECT LOAD ONLY
sample_TAB_2 sample_TAB_Q2 ENABLED DIRECT LOAD ONLY
sample_TAB_2 sample_TAB_Q3 ENABLED FOR ALL OPERATIONS
sample_TAB_2 sample_TAB_Q4 DISABLED
4 rows selected.
SQL>
Tablespace compression:
Default compression settings can be specified at the tablespace level using the CREATE TABLESPACE and ALTER TABLESPACE commands. The current settings are displayed in the DEF_TAB_COMPRESSION and COMPRESS_FOR columns of the DBA_TABLESPACES view.
CREATE TABLESPACE test_tbs
DATAFILE '/u02/oradata/datafiles/prod/test_ts01.dbf'
SIZE 1M
DEFAULT COMPRESS FOR ALL OPERATIONS;
SELECT def_tab_compression, compress_for
FROM dba_tablespaces
WHERE tablespace_name = 'TEST_TBS';
DEF_TAB_ COMPRESS_FOR
-------- ------------------
ENABLED FOR ALL OPERATIONS
1 row selected.
SQL>
ALTER TABLESPACE test_tbs DEFAULT NOCOMPRESS;
SELECT def_tab_compression, compress_for
FROM dba_tablespaces
WHERE tablespace_name = 'TEST_TBS';
DEF_TAB_ COMPRESS_FOR
-------- ------------------
DISABLED
1 row selected.
SQL>
DROP TABLESPACE test_tbs INCLUDING CONTENTS AND DATAFILES;
When compression is specified at multiple levels, the most specific setting is always used. As such, partition settings always override table settings, which always override tablespace settings.
The restrictions associated with table compression include:
Default compression settings can be specified at the tablespace level using the CREATE TABLESPACE and ALTER TABLESPACE commands. The current settings are displayed in the DEF_TAB_COMPRESSION and COMPRESS_FOR columns of the DBA_TABLESPACES view.
CREATE TABLESPACE test_tbs
DATAFILE '/u02/oradata/datafiles/prod/test_ts01.dbf'
SIZE 1M
DEFAULT COMPRESS FOR ALL OPERATIONS;
SELECT def_tab_compression, compress_for
FROM dba_tablespaces
WHERE tablespace_name = 'TEST_TBS';
DEF_TAB_ COMPRESS_FOR
-------- ------------------
ENABLED FOR ALL OPERATIONS
1 row selected.
SQL>
ALTER TABLESPACE test_tbs DEFAULT NOCOMPRESS;
SELECT def_tab_compression, compress_for
FROM dba_tablespaces
WHERE tablespace_name = 'TEST_TBS';
DEF_TAB_ COMPRESS_FOR
-------- ------------------
DISABLED
1 row selected.
SQL>
DROP TABLESPACE test_tbs INCLUDING CONTENTS AND DATAFILES;
When compression is specified at multiple levels, the most specific setting is always used. As such, partition settings always override table settings, which always override tablespace settings.
The restrictions associated with table compression include:
- Compressed tables can only have columns added or dropped if the COMPRESS FOR ALL OPERATIONS option was used.
- Compressed tables must not have more than 255 columns.
- Compression is not applied to lob segments.
- Table compression is only valid for heap organized tables, not index organized tables.
- The compression clause cannot be applied to hash or hash-list partitions. Instead, they must inherit their compression settings from the tablespace, table or partition settings.
- Table compression cannot be specified for external or clustered tables.
Overview:
Compression Advisor provides an estimate of the compression ratio that can be realized through the use of the Oracle Advanced Compression option. This estimate is based on analysis of a sample of data and provides a good estimate of the actual results you may obtain once you implement the OLTP Table compression feature in your environment.
The Compression Advisor PL/SQL package you use is dependent upon which Oracle Database release you currently have deployed. Those customers that want to use Compression Advisor with Oracle Database 9i Release 2 through Oracle Database 11g Release 1 will use the DBMS_COMP_ADVISOR package available for download below. Customers that want to use Compression Advisor with Oracle Database 11g Release 2 through Oracle Database 12c will use the DBMS_COMPRESSION package that is included with the database.
This package can be used on Oracle Databases running Oracle Database 9i Release 2 through 11g Release 1. A compression advisor (DBMS_COMPRESSION) is included with Oracle Database 11g Release 2 and Oracle Database 12c.
Using Compression Advisor:
This procedure can be used with Oracle Database 9i Release 2 through Oracle Database 11g Release 1. Running this procedure will create tables in the default tablespace of the user running the procedure. While these tables will get dropped at the end of the procedure they will consume space while the procedure runs. Oracle recommends creating a tablespace specifically for storing these tables and assigning it as the default tablespace to the user running the procedure. The DBMS_COMP_ADVISOR advisor package is only available as a free download.
Compression Advisor consists of the DBMS_COMP_ADVISOR package containing the following procedure:
getratio(
ownername IN varchar2,
tabname IN varchar2,
sampling_percent IN number
);
where
- 'ownername' is the schema that the table belongs to
- 'tabname' is name of the table for which compression ratio is to be estimated
- 'sampling_percent' is any value between 0.000001 and 99
The output of this procedure is the estimated compression ratio.
Example:
SQL> set serveroutput on
SQL> exec dbms_comp_advisor.getratio('SH','SALES',10);
Sampling table: SH.SALES
Sampling percentage: 10%
Expected Compression ratio with Advanced Compression option: 2.96
PL/SQL procedure successfully completed.
Note: Compression Advisor concepts collected from Oracle magazine.
This comment has been removed by a blog administrator.
ReplyDelete