Forum Stats

  • 3,872,216 Users
  • 2,266,407 Discussions
  • 7,911,107 Comments

Discussions

Advanced Compression and Physical I/O

sunir
sunir Member Posts: 19
edited Dec 1, 2010 6:32PM in Advanced Compression
Excerpt from AC twp.

"As stated above, the Table Compression feature has no adverse impact on read operations. However, compression requires additional work to be performed while writing the data making it unavoidable to eliminate performance overhead for write operations. Oracle has put in a significant amount of work to minimize such overhead for OLTP Table Compression. Oracle compresses a block in batch mode rather than compressing data every single time a write operation takes place. A newly initialized block remains uncompressed until data in the block reaches an internally controlled threshold. When a transaction causes the data in the block to reach this threshold, all contents of the block are compressed. Subsequently, as more data is added to the block and the threshold is again reached, the entire block
is recompressed to achieve the highest level of compression. This process repeats until Oracle determines that the block can no longer benefit from further compression. Only transactions that trigger the compression of the block will experience the minimal compression overhead. Therefore, a majority of OLTP transactions on compressed blocks will have the exact same performance as they would with uncompressed blocks.
"

How much physical disk i/o is this going to create?
Does it increase physical i/o because OLTP transactions are written uncompressed then re-written compressed when block thresholds are reached?

Background: Loading (using conventional path) hundreds of millions of records per day (200GB a day). Data is for OLTP.

thanks

Sunir
Tagged:
«1

Answers

  • damorgan
    damorgan Member Posts: 14,464 Bronze Crown
    edited Nov 23, 2009 8:46PM
    Knowing whether you are referring to 11gR1 or 11gR2 would be helpful but it appears you are asking about 11gR1 so I will answer on that basis.

    I don't know what "Excerpt from AC twp." means but either it is incorrect or your interpretation of it is incorrect.

    Here's what I am keying in on:
    "As stated above, the Table Compression feature has no adverse impact on read operations.
    However, compression requires additional work to be performed while writing the data making it 
    unavoidable to eliminate performance overhead for write operations.
    {code}
    
    With respect to read operations this is nonsense. While the amount of disk i/o is minimized the amount of CPU is increased. It must be because the block must be uncompressed. So while the overall result may, or may not, be better performance on a CPU bound system this statement is clearly not true.
    
    The second statement about writes is also not correct. An insert statement with Advanced Compression is just a normal insert statement and the data is written, as you allude to later in uncompressed form. So there can not be an impact. What does happen is that at a later time Oracle may read the block, compress, and rewrite it, and that will, of course, require resources. Some i/o and some CPU.
    
    Nothing is free. Spending CPU makes sense if you need to decrease I/O or increase storage density.
    
    I am a strong advocate of Advanced Compression and Hybrid Columnar Compression for many things but what is happening should be described properly.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • sunir
    sunir Member Posts: 19
    Hi
    Thanks for the reply...

    Excerpt from AC TWP - Advanced Compression Technical White Paper.

    DB is 11g R1.

    I am trying to find out how much extra Disk I/O is going to happen to compress data in blocks. The database is very much an insert data. Reads (SQL select) is minimal.

    My interpretation is that data is stored onto disk and the read and stored again in compressed form - meaning that there are 2 disk i/o operations for the block. Is this correct? I am not concerned with the CPU increase (I understand that bit), but more on the events on physical disk and although it mentions that disk i/o is reduced due to reading compressed blocks, I am trying to find out what happens to disk i/o in the bit in between being uncompressed and compressed (if that makes sense).

    Thanks
  • mrmessin
    mrmessin Member Posts: 287
    The impact on insert opertions depends on the type of inserting. The impact for individual insert statements is not high in of iteself however the overall impact over a large number of inserts can be.

    Example inserting 90,998 completes in 1.51 seconds however that same set of inserts into an advanced compressed table is 2.04 seconds. an impact of .53 seconds on 90,998 rows.

    Another example using a larger data set yeailded the following, 889,888 rows 19.79 seconds while the advanced compressed table 53.53 seconds. This shows that the impact here is significant. Over 2x the amount of time.

    However for select statements the results have mostly been positive with reduced physical I/O and execution times, esp. when physical I/O is used more, however we did see the overall CPU utilization increase. Therefore as long as CPU is not a constraining factor the overall query performance improves in a lot of cases.

    So the impact on the inserts will directly depend on volume, as the volume increases so does the CPU usage which shows that the insert process impact increases esp once the CPU becomes constrained.

    The best advice that can be given is to take you application and execute a load test with advanced compression and without advanced compression and measure the impact on you environment and application as environmental and application factors can affect the overall impact.
    mrmessin
  • sunir
    sunir Member Posts: 19
    Thank you for the answers,

    the increase in time - how much is attributed to CPU and how much to I/O that is the question i am trying to answer, I have no issues on the CPU side, but am concerned about doubling I/O duirng the background compression.

    sunir
  • damorgan
    damorgan Member Posts: 14,464 Bronze Crown
    edited Dec 2, 2009 1:03AM
    What are you doing?
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
    With the Partitioning, Oracle Label Security, OLAP, Data Mining,
    Oracle Database Vault and Real Application Testing options
    
    SQL> set timing on
    
    SQL> CREATE TABLE uncomp AS
      2  SELECT /*+ APPEND */ *
      3  FROM airplanes;
    
    Table created.
    
    Elapsed: 00:00:02.68
    SQL> CREATE TABLE comp COMPRESS AS
      2  SELECT /*+ APPEND */ *
      3  FROM airplanes;
    
    Table created.
    
    Elapsed: 00:00:00.64
    
    SQL> 
    SQL> INSERT /*+ APPEND */ INTO uncomp
      2  SELECT * FROM airplanes;
    
    250000 rows created.
    
    Elapsed: 00:00:00.57
    SQL> INSERT /*+ APPEND */ INTO comp
      2  SELECT * FROM airplanes;
    
    250000 rows created.
    
    Elapsed: 00:00:00.54
    SQL> 
    The source table, airplanes, contains 250,000 rows.

    I am unable to duplicate anything that looks like your numbers.
  • damorgan
    damorgan Member Posts: 14,464 Bronze Crown
    Take a look at what I just posted ... what increase in time?
  • sunir
    sunir Member Posts: 19
    Hi

    Thanks for reply. I have managed to get the answer I was looking for through other means. It was to do with block aging in the SGA and how that is affected by compression. It turns out there isn't any affect.

    thanks Again

    sunir
  • mrmessin
    mrmessin Member Posts: 287
    If you run a full select on airplanes 2 times in a row the select alone will take less time do not know if prime of buffer cache has impacted the results here for the second execution as it can make some difference.

    Also your execution is not with advanced compression your example shows using table compression available since 9i

    You are also doing an append operation which is available for normal table compression and not actually fully testing advanced compress which is for normal DML operations?

    Normal OLTP would not use an append for inserts without wasting a lot of space.

    Also append hint would also does make a difference in the timing difference with compression and if using the append hint on all insert operations then you technically are not using advanced compression as normal table compression available since 9i would work when all insert operations are appends.

    As you have shown the append hint certainly lowers if not nearly eliminates the impact of compression, but the idea of using advanced compression is with normal insert and update operations.

    Here is a very small test showing an example and compare against append and non-append operations and the advanced compression feature impact.

    This is after a full select on dba tables to prime the buffer cache to eliminate second executions having a impact.

    SQL>
    SQL> -- insert into a table compressed and load with data
    SQL> INSERT INTO test_compression_compressed
    2 select * from dba_tables ;

    2960 rows created.

    Elapsed: 00:00:00.73

    SQL>
    SQL> -- insert into a table compressed and load with data
    SQL> insert into test_compression_uncompressed
    2 select * from dba_tables ;

    2960 rows created.

    Elapsed: 00:00:00.37

    SQL>
    SQL> insert /*+ APPEND */ into test_compression_uncompressed
    2 select * from dba_tables ;

    2960 rows created.

    Elapsed: 00:00:00.37
    SQL>
    SQL> -- now use the append
    SQL> INSERT /*+ APPEND */ INTO test_compression_compressed
    2 select * from dba_tables ;

    2960 rows created.

    Elapsed: 00:00:00.39

    SQL> update test_compression_uncompressed
    2 set tablespace_name = 'XYZ' ;

    8878 rows updated.

    Elapsed: 00:00:00.25
    SQL>
    SQL> update test_compression_compressed
    2 set tablespace_name = 'XYZ' ;

    8879 rows updated.

    Elapsed: 00:00:03.18
  • damorgan
    damorgan Member Posts: 14,464 Bronze Crown
    I suggest you read the docs. Specifically on the relationship between Advanced Compression and Direct Load.

    You might while you are doing that consider that I was the person that did the A/C session at OpenWorld this year so I've just a wee bit of experience with the subject. <g> Nov. 3 I presented the paper at Oracle User Group Finland and three days ago at UKOUG.

    No one is, or should be, using A/C with a couple of thousand rows. A/C requires the purchase of a license above and beyond your EE license so anyone using it is working with many tens or hundreds of millions of rows. Testing with a few thousand has no real meaning. Consider for example what the OP originally wrote:
    "Does it increase physical i/o because OLTP transactions are written uncompressed then re-written compressed when block thresholds are reached?
    Background: Loading (using conventional path) hundreds of millions of records per day (200GB a day). Data is for OLTP."
    With respect to your update scenario it of course will take longer. Anyone choosing a massive update of compressed data didn't invest enough time reading the docs and understanding the technology and I would argue should never have implemented the feature. You might note that the O/P did not ask about it so I would conclude the O/P understand the proper utilization of compression.

    Hope this helps.
  • mrmessin
    mrmessin Member Posts: 287
    I have read the documentation, I too presented at open world this year on partitioning and advanced compression. I have done a lot of testing and research in the Oracle 11g R2 Beta Program with advanced compression as well therefore I do not feel I am ignorant on the subject.

    I have also implemented this for a few clients as well.

    One of the main things Oracle communicated on advanced compression when it was introduced was the capability of advanced compression to be used for oltp as compression was maintain for normal DML. Compressed tables with direct loading has been around quite sometime and does not require the Advanced compresion license to utilize. So if you are only going to do direct loads into a table you are not using advanced compression and therefore do not need the license.

    I mean no disrespect to you I understand your point and I want to make sure that the understanding of more then a single insert with append aspect of advanced compression is communicated. I am trying to provide as much information on the technology as possible and this includes its utilization in many ways. In my humble opinion I do not feel that utilizing the APPEND operation is a true test of Advanced compression for the reasons I have already indicated such Inserts with append and that being the only operations is not Advanced Compression it is table compression that you have been able to utilize since 9i and does not require the advanced compresison licesne.

    I appologize if I have miscommunicated my intentions it was not to necessary contradict your finds but to add additional information in regards to advanced compression.
This discussion has been closed.