{"id":73260,"date":"2013-01-31T13:53:21","date_gmt":"2013-01-31T13:53:21","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/compression-in-oracle-part-3-oltp-compression\/"},"modified":"2021-07-14T13:07:45","modified_gmt":"2021-07-14T13:07:45","slug":"compression-in-oracle-part-3-oltp-compression","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/compression-in-oracle-part-3-oltp-compression\/","title":{"rendered":"Compression in Oracle \u2013 Part 3: OLTP Compression"},"content":{"rendered":"<p>In <a href=\"https:\/\/allthingsoracle.com\/compression-in-oracle-part-2-read-only-data\/\">part two<\/a> of this series we examined the effect of updates on compressed data when using basic compression, and saw how Oracle will do some \u201cdecompression\u201d of a row before updating it with the effect that highly compressed rows might easily become so much larger that even a small amount of change could lead to a large number of rows migrating to different blocks. On the positive side we saw that Oracle kept this decompression to a minimum \u2013 expanding only those tokens that contained the columns being updated.<\/p>\n<p>There is even a special case that a \u201cno-change\u201d update doesn\u2019t do any decompression, although a comment from <a title=\"Randolf Geist\" href=\"https:\/\/www.red-gate.com\/simple-talk\/author\/randolf-geist\/\">Randolf Geist<\/a> on the previous article pointed out that this special case wasn\u2019t implemented until 11.2.0.3; and a follow-up from \u201cDBA\u201d showed that the special case code seems to fail sometimes \u2013 possibly if you update a large number of rows in a block.<\/p>\n<p>We also saw that Oracle made no attempt to \u201c re-compress\u201d the row after update even if there were other preexisting tokens that could be used to reduce the size of the row.<\/p>\n<p>In this article we move on to (separately licensed) \u201cOLTP\u201d compression (originally known as \u201ccompress for all operations\u201d). We will re-run some of our tests to see how differently Oracle can behave with this option in play. I\u2019ll be using 11.2.0.3 in my tests, with a locally managed tablespace, uniform 1MB extents, and (for reasons that I will mention later) <em><strong>freelist<\/strong><\/em> management rather than automatic segment space management (<em><strong>ASSM<\/strong><\/em>).<\/p>\n<h2>PCTFREE<\/h2>\n<p>Our very first test of basic compression involved defining a table with compression, and seeing what the data looked like on an initial load. We\u2019ll do the same again, but use the option \u201ccompress for OLTP\u201d (or \u201ccompress for all operations\u201d for those using a slightly older version of Oracle). We\u2019ll start by creating a table with 50,000 rows selected from view all_objects, and check to see if different strategies produce different results. The strategies I used in the original article were:<\/p>\n<ul>\n<li>Create table as select from <em><strong>all_objects<\/strong><\/em><\/li>\n<li>Create table with compression as select from all_objects<\/li>\n<li>Create empty table with compression enabled, insert rows<\/li>\n<li>Create empty table with compression enabled, insert rows with append hint<\/li>\n<li>Create table (no compression) as select; enable compression; move table<\/li>\n<\/ul>\n<p>To this I\u2019ve added one more test, creating the table then inserting one row at a time, with commits, from a pl\/sql loop. The results we get through a select from view <em><strong>user_tables<\/strong><\/em> after each test are as follows:<\/p>\n<table style=\"border: 1px; margin-bottom: 20px;\">\n<tbody>\n<tr>\n<td style=\"width: 111.75pt; border: solid windowtext 1pt; padding: 0 5.4pt;\" valign=\"top\" width=\"186\">\n<p class=\"awbasetext\">Test<\/p>\n<\/td>\n<td style=\"width: 2cm; border: solid windowtext 1pt; border-left: none; padding: 0 5.4pt;\" valign=\"top\" width=\"95\">\n<p class=\"awbasetext\">BLOCKS<\/p>\n<\/td>\n<td style=\"width: 70.85pt; border: solid windowtext 1pt; border-left: none; padding: 0 5.4pt;\" valign=\"top\" width=\"118\">\n<p class=\"awbasetext\">PCT_FREE<\/p>\n<\/td>\n<td style=\"width: 77.95pt; border: solid windowtext 1pt; border-left: none; padding: 0 5.4pt;\" valign=\"top\" width=\"130\">\n<p class=\"awbasetext\">COMPRESSION<\/p>\n<\/td>\n<td style=\"width: 3cm; border: solid windowtext 1pt; border-left: none; padding: 0 5.4pt;\" valign=\"top\" width=\"142\">\n<p class=\"awbasetext\">COMPRESS_FOR<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td style=\"width: 111.75pt; border: solid windowtext 1pt; border-top: none; padding: 0 5.4pt;\" valign=\"top\" width=\"186\">\n<p class=\"awbasetext\">1 (CTAS)<\/p>\n<\/td>\n<td style=\"width: 2cm; border-top: none; border-left: none; border-bottom: solid windowtext 1pt; border-right: solid windowtext 1pt; padding: 0 5.4pt;\" valign=\"top\" width=\"95\">\n<p class=\"awbasetext\">714<\/p>\n<\/td>\n<td style=\"width: 70.85pt; border-top: none; border-left: none; border-bottom: solid windowtext 1pt; border-right: solid windowtext 1pt; padding: 0 5.4pt;\" valign=\"top\" width=\"118\">\n<p class=\"awbasetext\">10<\/p>\n<\/td>\n<td style=\"width: 77.95pt; border-top: none; border-left: none; border-bottom: solid windowtext 1pt; border-right: solid windowtext 1pt; padding: 0 5.4pt;\" valign=\"top\" width=\"130\">\n<p class=\"awbasetext\">DISABLED<\/p>\n<\/td>\n<td style=\"width: 3cm; border-top: none; border-left: none; border-bottom: solid windowtext 1pt; border-right: solid windowtext 1pt; padding: 0 5.4pt;\" valign=\"top\" width=\"142\">\u00a0<\/td>\n<\/tr>\n<tr>\n<td style=\"width: 111.75pt; border: solid windowtext 1pt; border-top: none; padding: 0 5.4pt;\" valign=\"top\" width=\"186\">\n<p class=\"awbasetext\">2 (CTAS compress)<\/p>\n<\/td>\n<td style=\"width: 2cm; border-top: none; border-left: none; border-bottom: solid windowtext 1pt; border-right: solid windowtext 1pt; padding: 0 5.4pt;\" valign=\"top\" width=\"95\">\n<p class=\"awbasetext\">211<\/p>\n<\/td>\n<td style=\"width: 70.85pt; border-top: none; border-left: none; border-bottom: solid windowtext 1pt; border-right: solid windowtext 1pt; padding: 0 5.4pt;\" valign=\"top\" width=\"118\">\n<p class=\"awbasetext\">10<\/p>\n<\/td>\n<td style=\"width: 77.95pt; border-top: none; border-left: none; border-bottom: solid windowtext 1pt; border-right: solid windowtext 1pt; padding: 0 5.4pt;\" valign=\"top\" width=\"130\">\n<p class=\"awbasetext\">ENABLED<\/p>\n<\/td>\n<td style=\"width: 3cm; border-top: none; border-left: none; border-bottom: solid windowtext 1pt; border-right: solid windowtext 1pt; padding: 0 5.4pt;\" valign=\"top\" width=\"142\">\n<p class=\"awbasetext\">OLTP<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td style=\"width: 111.75pt; border: solid windowtext 1pt; border-top: none; padding: 0 5.4pt;\" valign=\"top\" width=\"186\">\n<p class=\"awbasetext\">3 Insert<\/p>\n<\/td>\n<td style=\"width: 2cm; border-top: none; border-left: none; border-bottom: solid windowtext 1pt; border-right: solid windowtext 1pt; padding: 0 5.4pt;\" valign=\"top\" width=\"95\">\n<p class=\"awbasetext\">227<\/p>\n<\/td>\n<td style=\"width: 70.85pt; border-top: none; border-left: none; border-bottom: solid windowtext 1pt; border-right: solid windowtext 1pt; padding: 0 5.4pt;\" valign=\"top\" width=\"118\">\n<p class=\"awbasetext\">10<\/p>\n<\/td>\n<td style=\"width: 77.95pt; border-top: none; border-left: none; border-bottom: solid windowtext 1pt; border-right: solid windowtext 1pt; padding: 0 5.4pt;\" valign=\"top\" width=\"130\">\n<p class=\"awbasetext\">ENABLED<\/p>\n<\/td>\n<td style=\"width: 3cm; border-top: none; border-left: none; border-bottom: solid windowtext 1pt; border-right: solid windowtext 1pt; padding: 0 5.4pt;\" valign=\"top\" width=\"142\">\n<p class=\"awbasetext\">OLTP<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td style=\"width: 111.75pt; border: solid windowtext 1pt; border-top: none; padding: 0 5.4pt;\" valign=\"top\" width=\"186\">\n<p class=\"awbasetext\">4 Insert append<\/p>\n<\/td>\n<td style=\"width: 2cm; border-top: none; border-left: none; border-bottom: solid windowtext 1pt; border-right: solid windowtext 1pt; padding: 0 5.4pt;\" valign=\"top\" width=\"95\">\n<p class=\"awbasetext\">211<\/p>\n<\/td>\n<td style=\"width: 70.85pt; border-top: none; border-left: none; border-bottom: solid windowtext 1pt; border-right: solid windowtext 1pt; padding: 0 5.4pt;\" valign=\"top\" width=\"118\">\n<p class=\"awbasetext\">10<\/p>\n<\/td>\n<td style=\"width: 77.95pt; border-top: none; border-left: none; border-bottom: solid windowtext 1pt; border-right: solid windowtext 1pt; padding: 0 5.4pt;\" valign=\"top\" width=\"130\">\n<p class=\"awbasetext\">ENABLED<\/p>\n<\/td>\n<td style=\"width: 3cm; border-top: none; border-left: none; border-bottom: solid windowtext 1pt; border-right: solid windowtext 1pt; padding: 0 5.4pt;\" valign=\"top\" width=\"142\">\n<p class=\"awbasetext\">OLTP<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td style=\"width: 111.75pt; border: solid windowtext 1pt; border-top: none; padding: 0 5.4pt;\" valign=\"top\" width=\"186\">\n<p class=\"awbasetext\">5a Compress enabled<\/p>\n<\/td>\n<td style=\"width: 2cm; border-top: none; border-left: none; border-bottom: solid windowtext 1pt; border-right: solid windowtext 1pt; padding: 0 5.4pt;\" valign=\"top\" width=\"95\">\n<p class=\"awbasetext\">714<\/p>\n<\/td>\n<td style=\"width: 70.85pt; border-top: none; border-left: none; border-bottom: solid windowtext 1pt; border-right: solid windowtext 1pt; padding: 0 5.4pt;\" valign=\"top\" width=\"118\">\n<p class=\"awbasetext\">10<\/p>\n<\/td>\n<td style=\"width: 77.95pt; border-top: none; border-left: none; border-bottom: solid windowtext 1pt; border-right: solid windowtext 1pt; padding: 0 5.4pt;\" valign=\"top\" width=\"130\">\n<p class=\"awbasetext\">ENABLED<\/p>\n<\/td>\n<td style=\"width: 3cm; border-top: none; border-left: none; border-bottom: solid windowtext 1pt; border-right: solid windowtext 1pt; padding: 0 5.4pt;\" valign=\"top\" width=\"142\">\n<p class=\"awbasetext\">OLTP<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td style=\"width: 111.75pt; border: solid windowtext 1pt; border-top: none; padding: 0 5.4pt;\" valign=\"top\" width=\"186\">\n<p class=\"awbasetext\">5b Move<\/p>\n<\/td>\n<td style=\"width: 2cm; border-top: none; border-left: none; border-bottom: solid windowtext 1pt; border-right: solid windowtext 1pt; padding: 0 5.4pt;\" valign=\"top\" width=\"95\">\n<p class=\"awbasetext\">211<\/p>\n<\/td>\n<td style=\"width: 70.85pt; border-top: none; border-left: none; border-bottom: solid windowtext 1pt; border-right: solid windowtext 1pt; padding: 0 5.4pt;\" valign=\"top\" width=\"118\">\n<p class=\"awbasetext\">10<\/p>\n<\/td>\n<td style=\"width: 77.95pt; border-top: none; border-left: none; border-bottom: solid windowtext 1pt; border-right: solid windowtext 1pt; padding: 0 5.4pt;\" valign=\"top\" width=\"130\">\n<p class=\"awbasetext\">ENABLED<\/p>\n<\/td>\n<td style=\"width: 3cm; border-top: none; border-left: none; border-bottom: solid windowtext 1pt; border-right: solid windowtext 1pt; padding: 0 5.4pt;\" valign=\"top\" width=\"142\">\n<p class=\"awbasetext\">OLTP<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td style=\"width: 111.75pt; border: solid windowtext 1pt; border-top: none; padding: 0 5.4pt;\" valign=\"top\" width=\"186\">\n<p class=\"awbasetext\">6 Insert (row by row)<\/p>\n<\/td>\n<td style=\"width: 2cm; border-top: none; border-left: none; border-bottom: solid windowtext 1pt; border-right: solid windowtext 1pt; padding: 0 5.4pt;\" valign=\"top\" width=\"95\">\n<p class=\"awbasetext\">230<\/p>\n<\/td>\n<td style=\"width: 70.85pt; border-top: none; border-left: none; border-bottom: solid windowtext 1pt; border-right: solid windowtext 1pt; padding: 0 5.4pt;\" valign=\"top\" width=\"118\">\n<p class=\"awbasetext\">10<\/p>\n<\/td>\n<td style=\"width: 77.95pt; border-top: none; border-left: none; border-bottom: solid windowtext 1pt; border-right: solid windowtext 1pt; padding: 0 5.4pt;\" valign=\"top\" width=\"130\">\n<p class=\"awbasetext\">ENABLED<\/p>\n<\/td>\n<td style=\"width: 3cm; border-top: none; border-left: none; border-bottom: solid windowtext 1pt; border-right: solid windowtext 1pt; padding: 0 5.4pt;\" valign=\"top\" width=\"142\">\n<p class=\"awbasetext\">OLTP<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>The most obvious point we can see here is that for OLTP compression <em><strong>pctfree<\/strong> <\/em>defaults to the same 10 that is does for non-compressed tables (which explains why we see 211 blocks in many cases compared to the 189 blocks we saw with basic compression); we can also see that even normal inserts result in data being compressed on arrival \u2013 recall that for basic compression we had to use direct path loading; however we also note that the compression we get with normal inserts isn\u2019t quite as good as the compression we get with direct path loading \u2013 and that\u2019s something worth reviewing. It\u2019s also worth reminding ourselves that simply enabling compression doesn\u2019t change existing data (test 5a), we do have to recreate the table to compress the data. The single row insert strategy produced results that were very close to the array insert level of compression, so I didn\u2019t pursue that method in subsequent tests.<\/p>\n<p>There are various statistics relating to compression that appeared in 11g that can be helpful when we\u2019re trying to understand what\u2019s happening (or has happened) in some type of batch process, and I took a snapshot of the session statistics during the simple insert\u00a0test (number 3), with the following significant results:<\/p>\n<pre>Statistic Name                                Value\r\n----------------------------------------- ---------\r\nheap block compress                           1,521\r\nHSC OLTP Space Saving                     3,501,381\r\nHSC OLTP Compressed Blocks                      227\r\nHSC Compressed Segment Block Changes          3,841\r\nHSC OLTP Non Compressible Blocks                226\r\nHSC OLTP inline compression                   1,521\r\nHeap Segment Array Inserts                    2,320\r\n<\/pre>\n<p>Some of these numbers are easy to explain \u2013 our final table has 227 blocks, which accounts for the \u201cHSC OLTP Compressed Blocks\u201d; (it\u2019s a reasonable guess that HSC is \u201cheap segment compression\u201d); and the table would have been 712 blocks if we hadn\u2019t been using compression which, at 8KB * (714 \u2013 227), equates (very roughly and allowing for the pctfree) with the 3.5MB of \u201cHSC OLTP Space Saving\u201d.<\/p>\n<p>We then have to explain the appearance of the \u201cheap block compress\u201d and \u201cHSC OLTP inline compression\u201d, which both record values of 1,521. It\u2019s not very well known but \u201cheap block compress\u201d (which first appeared in 10g) has nothing to do with compression \u2013 it simply counts the number of times that a block has been \u201ctidied\u201d to move all the available free space up into the free space gap by pushing all the row pieces down to the bottom of the block.<\/p>\n<p>If you delete a few rows from a block, or update a few rows so that they become longer and have to be moved into the free space gap, you leave holes in the row heap. If Oracle needs to do something that requires more space than is currently in the free space gap it can re-arrange the contents of the block, moving the rows downwards to the end of the block (adjusting the row directory as it does so) so that all the holes \u201cbubble up\u201d into the free space gap. This is the action recorded as a \u201cheap block compress\u201d. This also explain why a block dump shows two measures of free space, the \u201ctosp\u201d (total space free in block) and the \u201cavsp\u201d (available space in the free space gap) \u2013 ignoring a couple of anomalies the tosp is the avsp plus the sum of all the little holes. (see also: <a title=\"Heap block compress\" href=\"http:\/\/jonathanlewis.wordpress.com\/2010\/03\/30\/heap-block-compress\/\" target=\"_blank\" rel=\"noopener\">jonathanlewis.wordpress.com\/2010\/03\/30\/heap-block-compress<\/a>)<\/p>\n<p>So our bulk insert has caused Oracle to tidy a block 1,521 times\u00a0\u2013 and since we have only 227 blocks in total this is an important clue about how OLTP compression works. There are (or, at least, ought to be) two mechanisms to consider \u2013 inserting rows and updating rows so that they increase in size.<\/p>\n<p>While inserting rows, Oracle doesn\u2019t worry about compressing them until an insert would push the block\u2019s space usage beyond the pctfree limit; at this point Oracle \u201cpauses\u201d to run its compression algorithm on the data currently in the block (recording the second statistic \u201cHSC OLTP incline compression\u201d). With a little luck this will reduce the volume of data, leaving enough space below the pctfree mark for the new row to be inserted. (Note \u2013 existing data is compressed before the new row is inserted: the new row (or rows, on an array insert) won\u2019t be compressed until compression is triggered by another insert that takes the block over the limit.) This is what we see in the statistics \u2013 we get a few rows into a block, then compress it, add a few more, then re-compress it, add a few more, and so on.<\/p>\n<p>We might expect the mechanism for updates to be similar in principle, though\u00a0with a difference in its target. The SQL Reference manual (E10592-04 p16-34), under \u201cCreate Table\u201d tells us:<em> \u201cWhen you enable table compression by specifying COMPRESS FOR OLTP, you enable OLTP table compression. Oracle Database compresses data during all DML operations on the table.\u201d<\/em> The question then is what triggers compression on updates (or deletes). The answer seems to be \u201cnothing\u201d \u2013 it looks as if OLTP compression is only triggered by inserts crossing the pctfree threshold. (This observation is also noted by Randolf Geist on his blog at here: <a title=\"ASSM bug reprise\" href=\"http:\/\/oracle-randolf.blogspot.co.uk\/2011\/05\/assm-bug-reprise-part-2.html\" target=\"_blank\" rel=\"noopener\">oracle-randolf.blogspot.co.uk\/2011\/05\/assm-bug-reprise-part-2<\/a>).<\/p>\n<p>Of the various attempts I made to trigger compression on updates, the final test I tried was to create a block with two rows holding a column of 100 \u2018Y\u2019s (giving me one token), and 10 rows holding a column of 60 \u2018X\u2019s (giving me another token) and various other rows to fill the block to the limit of pctfree. The numbers of rows and sizes of columns were carefully calculated to test several options. I then tried the following \u2013 recreating the data for each test:<\/p>\n<ul>\n<li>updated all the X rows to the appropriate Y\u2019s value<\/li>\n<li>update 9 of the X rows, commit, update the last X row<\/li>\n<li>update 9 of the X rows, commit, delete 100 \u201cspare\u201d rows, commit, update last X row<\/li>\n<\/ul>\n<p>In the first two cases the 10th \u201cX\u201d row migrated as it was updated \u2013 Oracle didn\u2019t recompress to save space, even though there was a suitable token that it could have re-used. In the last case a \u201cheap block compress\u201d took place, tidying the block so that all the free space bubbled up to the free space gap so that Oracle could use it for the updated row, but again, recompression didn\u2019t take place.<\/p>\n<p>Sadly it seems that \u201ccompress for OLTP\u201d (formerly \u201ccompress for all operations\u201d) doesn\u2019t compress for all operations, it compresses only for inserts, and the benefits it has over basic compression are that (a) it leaves 10% of the block free for updates, and (b) it doesn\u2019t require direct path inserts to trigger compression. Given the limitations on how it works you may find that the problems it brings might make it something you want to avoid.<\/p>\n<h2>Problems<\/h2>\n<p>As we have seen, compress for OLTP doesn\u2019t work for any operation other than inserts. But even then it doesn\u2019t seem to work effectively. Our simple example of \u201cinsert as select\u201d resulted in a table with 227 blocks in it, compared to 211 blocks when we used a direct path insert. When I dumped the first few blocks of the table, I found that the last 7 or 8 rows in each block hadn\u2019t been compressed, and that the free space in the block was actually larger than the 10% indicated by the <em><strong>pctfree<\/strong> <\/em>\u2013 somewhere along the line the logic wasn\u2019t compressing quite as much as we would hope. (It\u2019s possible that Oracle has an algorithm that says something like \u201cIf I recompress the block the space saving is likely to be less than x% so I won\u2019t do it\u201d; you could imagine that when you have 8 rows uncompressed in a block currently holding 240 rows then the extra space gained by compression would probably appear to be quite small, especially when you also factor in the CPU time required to apply the compression algorithm.<\/p>\n<p>You may recall, however, that I started this article with the comment that I was using freelist management rather than ASSM \u2013 when I repeated my simple <em>\u201cinsert 50,000 rows from all_objects\u201d<\/em>\u00a0 using ASSM the size of the resulting table jumped from 227 blocks to 250 blocks. Of these 250 blocks, it looked as if 33 had had no compression applied at all, and a further 15 had stopped applying compression after they were roughly half full. Compression and ASSM don\u2019t seem to work particularly well together \u2013 of course, 250 blocks is still a lot better than the 714 blocks needed without compression, but that\u2019s not really the point.<\/p>\n<p>Since updates result in token expansion, and recompression doesn\u2019t happen on updates, you have to worry about how well your data has compressed, and how much it will be updated. Taking my copy of <em><strong>all_objects<\/strong><\/em> again, a typical block of non-compressed data held somewhere between 66 and 70 rows; but when compressed (for OLTP) the blocks held anything between 156 and 301 rows with a little over half of them holding 220 to 230 rows.<\/p>\n<p>Looking on the pessimistic side, you have three times as many rows per block, which means you are three times as likely to do an update to a compressed block compared to an uncompressed block \u2013 and the better the compression you\u2019ve achieved the worse these odds get. However, the real threat appears when you think about the compression method and the update strategy. In the first block of my table I had nineteen tokens that covered 11 consecutive columns \u2013 that means one byte in a \u201creal\u201d row representing 11 columns of data \u2013 and if you update <em><strong>just one<\/strong><\/em> of the those columns Oracle expands the one byte to the full eleven columns ! Checking the usage counts on the tokens I could see that of the 242 rows in that particular block, 182 of them referenced one of these tokens \u2013 that\u2019s a lot of rows where a \u201csingle column update\u201d could turn into an 11 column expansion, leading to a large amount of row migration. Because you could have a lot more processes updating data then inserting data, you may also the the small number of target blocks where the rows migrate to also end up suffering from more collisions, showing up as increased time spent on buffer busy waits.<\/p>\n<p>Another unexpected consequence of compression is that when a row migrates out of a block it\u2019s likely to increase the block\u2019s free space by a very small amount (because it was a row that had been compressed to a few tokens) so, unlike \u201cnormal\u201d migration you\u2019re unlikely to find one row migration protecting the next few updates from migrating as well.<\/p>\n<p>To date I\u2019ve only heard complaints about OLTP compression (there\u2019s an element of self-selection there as no-one ever calls me to look at their system because it\u2019s running so well and has no problems). A common thread in the complaints I have heard, though, is about the\u00a0significant amount of row migration (once it has been noticed), the extra CPU, and\u00a0\u201cbuffer busy waits\u201d.<\/p>\n<p>You can minimize the migration, of course, by setting a suitable value for <em><strong>pctfree<\/strong><\/em> \u2013 but with the cunning tricks that Oracle uses to maximize compression it\u2019s very hard to decide on a good value unless you know the data and the business operation very well.<\/p>\n<p>The thing you need to hope for is that the data that\u2019s not going to change is very repetitive and that the columns that are subject to updates are virtually unique so that they don\u2019t end up sharing tokens with long collections of columns \u2013 but you probably won\u2019t be able to see that until after you\u2019ve tried compressing and analyzing a large volume of data. Unfortunately I\u2019ve seen a lot of applications where every table has a column with a name like <em><strong>last_updated_by<\/strong><\/em> that is very repetitious but very likely to change over time; it is also quite likely to get into a multi-column token, so that a significant expansion takes place even when the \u201creal\u201d data change was against columns that you weren\u2019t expecting to be compressed. (Of course, as with basic compression, if the column is \u201cupdated\u201d but doesn\u2019t actually change then token expansion won\u2019t occur.)<\/p>\n<h2>Summary<\/h2>\n<p>Compression for OLTP is (according to the manuals) supposed to be able to compress during updates \u2013 but it doesn\u2019t (at least, as far as I can tell); this means that you can easily end up suffering a large number of row migrations on updates, which can result in extra random I\/Os, buffer busy waits, and increased CPU and latch activity.<\/p>\n<p>If you\u2019re going to use OLTP compression you need to work out (probably by trial and error) a suitable value of <em><strong>pctfree<\/strong> <\/em>for each table that keeps row migration to an acceptable level.<\/p>\n<p>Since OLTP compression does allow normal inserts to trigger compression, though, you might work out a strategy with partitioned tables that uses OLTP compression and a large setting for <em><strong>pctfree<\/strong><\/em> for \u201cnew\u201d partitions and then rebuilds older partitions using basic compression. If you can work out a good strategy for using OLTP compression, though, think carefully about making a choice between <em><strong>freelist<\/strong><\/em> management and <em><strong>ASSM<\/strong><\/em> \u2013 there seem to be some undesirable side effects that appear when you mix OLTP compression with ASSM.<\/p>\n<p>There is a catalogue of all five items in this series (and a few others) at <em><strong><a href=\"https:\/\/jonathanlewis.wordpress.com\/2015\/08\/05\/compression\/\">this URL<\/a><\/strong><\/em><\/p>\n","protected":false},"excerpt":{"rendered":"<p>In part two of this series we examined the effect of updates on compressed data when using basic compression, and saw how Oracle will do some \u201cdecompression\u201d of a row before updating it with the effect that highly compressed rows might easily become so much larger that even a small amount of change could lead to a large number of&hellip;<\/p>\n","protected":false},"author":101205,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[],"coauthors":[39048],"class_list":["post-73260","post","type-post","status-publish","format-standard","hentry","category-oracle-databases"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73260","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/101205"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73260"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73260\/revisions"}],"predecessor-version":[{"id":75222,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73260\/revisions\/75222"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73260"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73260"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73260"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73260"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}