Blog - comments

Hello David,I think Oracle is behaving differently with 12.1.0.2.x. At least in my case I was able t...
David D'Acquisto

Bravo, il fallait y penser Mery Christmas

Eric

Great !! Thank you very much.

SEK

Great !! Thank you very much.

SEK
Franck, thank you for the thorough explanation on diagnosing these types issues. It showed me exac...
TimL
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; 

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

  • Guest
    Randhir Kumar Monday, 01 December 2014

    I tried to get compression ratio for indexes in 12c database.it's failing with the below error.
    SQL> DECLARE

    2 blkcnt_cmp pls_integer;

    3 blkcnt_uncmp pls_integer;

    4 row_cmp pls_integer;

    5 row_uncmp pls_integer;

    6 cmp_ratio pls_integer;

    7 comptype_str varchar2(100);

    8 BEGIN

    9 DBMS_COMPRESSION.GET_COMPRESSION_RATIO

    10 (

    11 scratchtbsname => 'TBSP_INDEX',

    12 ownname => 'SCOTT',

    13 objname => 'NAME_CMP1_IX',

    14 subobjname => NULL,

    15 comptype => dbms_compression.COMP_INDEX_ADVANCED_LOW,

    16 blkcnt_cmp => blkcnt_cmp,

    17 blkcnt_uncmp => blkcnt_uncmp,

    18 row_cmp => row_cmp,

    19 row_uncmp => row_uncmp,

    20 cmp_ratio => cmp_ratio,

    21 comptype_str => comptype_str,

    22 subset_numrows => dbms_compression.COMP_RATIO_MINROWS,

    23 objtype => dbms_compression.OBJTYPE_INDEX

    24 );

    25 END ;

    26 /

    DECLARE

    *

    ERROR at line 1:

    ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

    ORA-06512: at "SYS.PRVT_COMPRESSION", line 2199

    ORA-06512: at "SYS.PRVT_COMPRESSION", line 243

    ORA-06512: at "SYS.DBMS_COMPRESSION", line 218

    ORA-06512: at line 9


    Any suggestion

  • Guest
    pierre sicot Monday, 01 December 2014

    Hello,

    In oracle 12c the dbms_compression.get_compression_ratio has changed, you should use :
    dbms_compression.get_compression_ratio(
    scratchtbsname =>'USERS', -- tablespace
    ownname =>'PSI', -- owner
    objname =>'TEST1', -- table name
    subobjname =>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('Blocks Uncompressed:'||blockcount_nocomp);
    dbms_output.put_line('Rows Compressed :'||row_comp);
    dbms_output.put_line('Rowis Uncompressed:'||row_nocomp);
    end;

    The changed fields are tabname --> objname, and partname--> subobjname

    Regards

    Pierre SICOT

Leave your comment

Guest Friday, 19 December 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