Home » RDBMS Server » Performance Tuning » Compression benefits and options (Oracle 11.2.0.4 on Linux)
Compression benefits and options [message #634655] Thu, 12 March 2015 10:39 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Compression benefits and options [message #634660 is a reply to message #634657] Thu, 12 March 2015 11:47 Go to previous messageGo to next message
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
Re: Compression benefits and options [message #634661 is a reply to message #634660] Thu, 12 March 2015 11:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes.

Re: Compression benefits and options [message #634662 is a reply to message #634661] Thu, 12 March 2015 11:54 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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.
Re: Compression benefits and options [message #634719 is a reply to message #634669] Fri, 13 March 2015 12:38 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Thank you John!
Re: Compression benefits and options [message #634723 is a reply to message #634719] Fri, 13 March 2015 20:10 Go to previous messageGo to next message
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
Re: Compression benefits and options [message #634773 is a reply to message #634723] Sun, 15 March 2015 12:27 Go to previous message
orausern
Messages: 826
Registered: December 2005
Senior Member
Exactly what I was looking for. Thanks a million Kevin and the other experts!!

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


Current Time: Fri Mar 29 04:14:13 CDT 2024