Blog - comments

Thx helped us a lot, lsnrctl reload didn't help stop and start worked. Reconnect the SQL sessions b...

Very useful article. Thanks Franck !

Thanks a lot Franck. I agree to use FRA and RMAN deletion policy to manage standby site archived log...
I still say that you don't have to delete archivelogs because they are managed by oracle. That's the...
I don't know any documentation about those EC and ECJ. And I'm sorry I don't know the consequence of...
Blog Pierre Sicot DBMS compression: determining a table's compression ratio

dbi services Blog

Welcome to the dbi services Blog! This IT blog focuses on database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, EMC Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on our blog postings.

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.

DBMS compression: determining a table's compression ratio

According to the Oracle documentation, the dbms_compression procedure allows to determine the compression ratio for a table without creating it. Let's have a look at it.

We choose a table with a lot of records and an estimated size of 27 Mo:


SQL> select count(*) from test1; 


SQL> select segment_name, bytes/1024/1024 from user_segments

2 where segment_name = 'TEST1'; 


       TEST1              27


Let's see if the dbms_compression.get_ratio is correct for the Oracle procedure. We use a script named get_ratio.sql which calculates the compression ratio:



comp_ratio number;

comptype varchar2(300);

blockcount_comp number;

blockcount_nocomp number;

row_comp number;

row_nocomp number;



scratchtbsname =>'USERS', -- tablespace

ownname =>'PSI', -- owner

tabname =>'TEST1', -- table name

partname =>null, -- partition name

comptype =>2, -- compression type 2 = OLTP

blkcnt_cmp =>blockcount_comp, -- number of blocks compressed

blkcnt_uncmp =>blockcount_nocomp, -- number of blocks non compressed

row_cmp =>row_comp, -- number of rows compressed

row_uncmp =>row_nocomp, -- number of rows non compressed

cmp_ratio =>comp_ratio, -- compression ratio

comptype_str =>comptype); -- compression type

dbms_output.put_line('Compression Ratio :'||comp_ratio);

dbms_output.put_line('Block Count :'||blockcount_comp);

dbms_output.put_line('Compression Type :'||comptype);

dbms_output.put_line('Blocks Compressed :'||blockcount_comp);


dbms_output.put_line('Rows Compressed :'||row_comp);

dbms_output.put_line('Rowis Uncompressed:'||row_nocomp);end;


SQL> @get_ratio

Compression Ratio :5.3

Block Count :361

Compression Type :"Compress For OLTP"

Blocks Compressed :361

Blocks Uncompressed:1922

Rows Compressed :263

Rowis Uncompressed:49


When we analyze the results, this is what we get: Blocks Compressed 361, Blocks Uncompressed 1922. This means we should have a gain of 80 % (361 /1922 = 0,18).


We create a compressed tablespace:


SQL> create tablespace users_compress datafile '/u01/database/DB112/users_compress.dbf'size 100M reuse AUTOEXTEND ON NEXT 1280K MAXSIZE 32767M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO default compress for all operations;


We create the TEST1_COMPRESS table in the compressed tablespace:


SQL> create table test1_comp tablespace users_compress2 as select * from test1;


We now calculate the effective sizes of the TEST1% tables:


SQL> select segment_name,bytes/1024/1024 from user_segments

2 where segment_name like 'TEST1%';


       TEST1                 27

  TEST1_COMP                  5


Obviously, we have the same ratio, this procedure seems to work perfectly!

Rate this blog entry:

Pierre Sicot is Senior Consultant at dbi services. He has more than fifteen years of experience in Oracle database administration, engineering, and optimization. His specialty is the installation, migration, optimization, and security of Oracle databases in production environments. Prior to joining dbi services, Pierre was consultant at Trivadis in Lausanne. He also worked at ilem Group as a database administrator and also as a Consultant for different customers. Pierre holds a engineering diploma of ESME Sudria in France. His branch-related experience covers Energy, Banking, Public Administration, etc.


  • No comments made yet. Be the first to submit a comment

Leave your comment

Guest Friday, 28 November 2014
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)


Contact us now!

Send us your request!

Our workshops

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

Expert insight from insiders!

Fixed Price Services

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

A safe investment: our IT services at fixed prices!

Your flexible SLA

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

ISO 20000 certified & freely customizable!

dbi services Newsletter