OraFAQ Forum: Performance Tuning » Compression benefits and options

Home » RDBMS Server » Performance Tuning » Compression benefits and options (Oracle 11.2.0.4 on Linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Compression benefits and options [message #634655]

Thu, 12 March 2015 10:39

orausern
Messages: 826
Registered: December 2005

Senior Member

Hi,

I am on Oracle 11.2.0.4 on Linux. We have a data warehouse of about 2 TB where , there are a lot of read-only tables. I suggested the use of basic compression and we are able to get compression ratio of 6 to 1. I heard that with advance compression, which is a licensed version, we can get better compression. Is it true? And if yes, how much more compression ratio , we can expect to get through that? Also does it degrade the performance of the queries or the DMLs?

Thanks,
OrauserN

[Updated on: Thu, 12 March 2015 10:39]

Report message to a moderator

Re: Compression benefits and options [message #634657 is a reply to message #634655]

Thu, 12 March 2015 11:32

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You can use Compression Advisor (free Oracle tool) or dbms_compression.get_compression_ratio procedure to estimate the compression ratio you can have on your table.

Report message to a moderator

Re: Compression benefits and options [message #634660 is a reply to message #634657]

Thu, 12 March 2015 11:47

orausern
Messages: 826
Registered: December 2005

Senior Member

Thanks Mike. Actually I used the Basic Compression feature that is present from Oracle 10g. Now if we have to go for Advance Compression, it will be extra cost right?

Thanks,
OrauserN

Report message to a moderator

Re: Compression benefits and options [message #634661 is a reply to message #634660]

Thu, 12 March 2015 11:49

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Yes.

Report message to a moderator

Re: Compression benefits and options [message #634662 is a reply to message #634661]

Thu, 12 March 2015 11:54

orausern
Messages: 826
Registered: December 2005

Senior Member

Got it. So as far as Basic Compression is concerned, there is only one option (to define the tables with the keyword "COMPRESS") and there are no other special options for Basic Compression. Is that too right?

Thanks a lot,
OrauseN

[Updated on: Thu, 12 March 2015 11:54]

Report message to a moderator

Re: Compression benefits and options [message #634669 is a reply to message #634655]

Thu, 12 March 2015 15:34

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

As far as I know, the the basic and advanced compression algorithms are the same. Actually, it isn't compression at all. It is de-duplication.

Report message to a moderator

Re: Compression benefits and options [message #634719 is a reply to message #634669]

Fri, 13 March 2015 12:38

orausern
Messages: 826
Registered: December 2005

Senior Member

Thank you John!

Report message to a moderator

Re: Compression benefits and options [message #634723 is a reply to message #634719]

Fri, 13 March 2015 20:10

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Excluding INDEX COMPRESSION, there are three basic compression types for an Oracle database.

BASIC (comes with all Oracle versions)
OLTP (requires advanced option (additional fee))
HCC (requires advanced option (additional fee)) (though I hear this is possible on non-EXADATA, I have never done it on non-EXADATA, so you would be doing this on a Oracle EXADATA database).

HCC has four versions so in the end there are six types of compression you can compare. Certainly your mileage may vary, but shown here are typical compression results.

COMPRESSION_TYPE COMPRESSION_RATIO
---------------- -----------------
BASIC            3X
OLTP             3X
HCC Query Low    6X
HCC Query High   9X
HCC Archive Low  12X
HCC Archive High 15X

Also, in many cases, there may be only a little difference in the first five forms. The fact that you are getting 6X using BASIC compression is pretty good. I would suggest that unless you are in desperate need of significant compression ratios, stick with BASIC and don't bother with the other stuff.

Kevin

Report message to a moderator

Re: Compression benefits and options [message #634773 is a reply to message #634723]

Sun, 15 March 2015 12:27

orausern
Messages: 826
Registered: December 2005

Senior Member

Exactly what I was looking for. Thanks a million Kevin and the other experts!!

Report message to a moderator

Previous Topic:	Information to provide when asking for tuning help.
Next Topic:	CPU consumption calculation for sqls

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 06 13:19:14 CST 2025