Blog - comments

Hi Martin, No unfortunately I've no bug opened. SR were closed as resolved by customer. Increasing n...
Did you succeed in getting support to open a bug for this? Which agent versions are affected?Best re...
Martin Decker
Hi Christopher, It's there. I't not an option that you check at install. Just use it by setting inme...
Hello, Thanks for the nice blog. I tried the latest 12c download available in Oracle's website and I...
Christopher Bernard
-- Here is a quick script to display which objects are locked in Share. Parameters: owner tablename....
Blog Pierre Sicot DBMS compression: determining a table's compression ratio

dbi services Blog

Welcome to the dbi services Blog! This blog focuses on IT infrastructure - featuring news, troubleshooting, and tips & tricks. It covers database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, 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 the 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; 

161068

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

2 where segment_name = 'TEST1'; 

SEGMENT_NAME BYTES/1024/1024 

       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:

 

declare

comp_ratio number;

comptype varchar2(300);

blockcount_comp number;

blockcount_nocomp number;

row_comp number;

row_nocomp number;

begin

dbms_compression.get_compression_ratio(

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('BlocksUncompressed:'||blockcount_nocomp);

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%';

SEGMENT_NAME    BYTES/1024/1024

       TEST1                 27

  TEST1_COMP                  5

 

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

Rate this blog entry:
0

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.

Comments

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

Leave your comment

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

Contact

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