Oracle Scratchpad

February 19, 2015

255 columns

Filed under: Infrastructure,Oracle,Performance — Jonathan Lewis @ 12:45 am BST Feb 19,2015

You all know that having more than 255 columns in a table is a Bad Thing ™ – and surprisingly you don’t even have to get to 255 to hit the first bad thing about wide tables. If you’ve ever wondered what sorts of problems you can have, here are a few:

  • If you’re still running 10g and gather stats on a table with more than roughly 165 columns then the query Oracle uses to collect the stats will only handle about 165 of them at a time; so you end up doing multiple (possibly sampled) tablescans to gather the stats. The reason why I can’t give you an exact figure for the number of columns is that it depends on the type and nullity of the columns – Oracle knows that some column types are fixed length (e.g. date types, char() types) and if any columns are declared not null then Oracle doesn’t have to worry about counting nulls – so for some of the table columns Oracle will be able to eliminate one or two of the related columns it normally includes in the stats-gathering SQL statement – which means it can gather stats on a few more table columns.  The 165-ish limit doesn’t apply in 11g – though I haven’t checked to see if there’s a larger limit before the same thing happens.
  • If you have more than 255 columns in a row Oracle will split it into multiple row pieces of 255 columns each plus one row piece for “the rest”; but the split counts from the end, so if you have a table with 256 columns the first row-piece has one column the second row-piece has 255 columns. This is bad news for all sorts of operations because Oracle will have to expend extra CPU chasing the the row pieces to make use of any column not in the first row piece. The optimists among you might have expected “the rest” to be in the last row piece. If you want to be reminded how bad row-chaining can get for wide tables, just have a look at an earlier blog note of mine (starting at this comment).
  • A particularly nasty side effect of the row split comes with direct path tablescans – and that’s what Oracle does automatically when the table is large. In many cases all the row pieces for a row will be in the same block; but they might not be, and if a continuation row-piece is in a different block Oracle will do a “db file sequential read” to read that block into the buffer cache and it won’t be cached (see 1st comment below).  As an indication of how badly this can affect performance, the results I got at a recent client site showed “select count(col1) from wide_table” taking 10  minutes while “select count(column40) from wide_table” took 22 minutes because roughly one row in a hundred required a single block read to follow the chain.
  • An important side effect of the split point is that you really need to put the columns you’re going to index near the start of the table to minimise the risk of this row chaining overhead when you create or rebuild an index.
  • On top of everything else, of course, it takes a surprisingly large amount of extra CPU to load a large table if the rows are chained. Another client test reported 140 CPU seconds to load 5M rows of 256 columns, but only 20 CPU seconds to load 255.

If you are going to have tables with more than 255 columns, think very carefully about column order – if you can get all the columns that are almost always null at the end of the row you may get lucky and find that you never need to create a secondary row piece. A recent client had about 290 columns in one table of 16M rows, and 150 columns were null for all 16M rows – unfortunately they had a mandatory “date_inserted” column at the end of the row, but with a little column re-arrangement they eliminated row chaining and saved (more than) 150 bytes storage per row.  Of course, if they have to add and back-fill a non-null column to the table they’re going to have to rebuild the table to insert the column “in the middle”, otherwise all new data will be chained and wasting 150 bytes per row, and any old data that gets updated will suffer a row migration/chain catastrophe.

13 Comments »

  1. Jonathan,

    we discussed migrated rows / rows with more thab one row piece in the past. I have several notes

    – the very bad thing about chained rows and direct reads thad is that finding the rest of row by “db file sequential read” is never cached. It was you who pointed out this fact in previous discussion. I have reported this behaviour in SR on MOS, the analyst confirmed this behaviour by telling me that direct reads change the context on all subsequent reads of current segment not to go to buffer cache even for random reads

    – I’m convinced that having more than one row piece per row wouldn’t have to be so bad as it is today. Why do we see so many spit row pieces in different rows when it belong to same row? when segment is re-formated by alter table move (or ctas, whatever), according to all observations it seems when we are not lucky enough, we get only on row with row pieces split into different block (if we get even number of row pieces per block, there is no “split row”. This could be only 1% or less and the impact on perfomance negligible. Update seems to be the trouble. Why doesn’t oracle move all row pieces belonging to same row when row migration kicks in? Of course, the performance of UPDATE would be slighltly hurt, but the performance of select wouldn’t suffer that much

    – from the same SR oracle analyst stated “having more than 255 columns in table is not performance improving technique”. That’s right, unfortuntely it is not written directly in manual an even experienced developers are not awared of this fact

    – there is one more bad thing regarding rows with two (or more) row pieces, again learned from your blog. If we have row with two row pieces, which fit into same block, oracle during read (which need columns from both pieces) still doubles the number of consistent gets. This seems to be still true (at least for 11.2.0.4 didn’t try on 12c). Confirmed also by event 10200.

    Regards
    Pavol

    Comment by Pavol Babel — February 19, 2015 @ 1:49 am BST Feb 19,2015 | Reply

  2. Good thing is that at least one bug is fixed in 11.2.0.4: Bug 9373758 Optimization to handle insert of >255 columns.

    Comment by Vyacheslav Rasskazov — February 19, 2015 @ 9:05 pm BST Feb 19,2015 | Reply

    • Vyacheslav,

      Interesting to note in the SR the erroneous claim (paraphrased) “… shouldn’t happen because we’re selecting from the first 255 columns.”. The informal description above (probably, assuming I made no errors) translates into: “The problem appears if you’re selecting anything after the first mod(column_count,255) columns”.

      Comment by Jonathan Lewis — February 20, 2015 @ 6:10 am BST Feb 20,2015 | Reply

  3. […] his researches he had read my article on wide rows, and had picked out of one of the comments the line: “the very bad thing about chained rows […]

    Pingback by Migrated rows | Oracle Scratchpad — May 18, 2015 @ 6:44 pm BST May 18,2015 | Reply

  4. Hi Jonathan,

    This subject of row splitting interests me very much as our database supports a Siebel app that
    has many tables with more than 255 columns. We’ve gone to great effort to order the columns in
    our tables with the most frequently accessed columns first and the columns with all null values
    at the end.

    Our goal is to minimize traversing columns and row pieces, to save space and minimize cpu. I’ve
    viewed the order of walking through the columns for a select column as piece1(col1, col2, col3 …col255),
    piece2(col256, col257 … colnnn). You mentioned that the rows are split starting from the end.
    How does that affect the ordering we’ve done? Do I need to reorder some other way to accomplish what
    I’ve described?

    In light of how Oracle splits rows I feel apprehensive about doing much table restructuring based on observed,
    but undocumented behavior only. I’d be more confident if the row splitting logic was documented as expected
    behavior or with at least a Metalink note. What are you thoughts on that as well?

    Thanks,

    Richard Brieck

    Comment by Richard Brieck — April 11, 2016 @ 10:19 pm BST Apr 11,2016 | Reply

    • Richard,

      This is one of those areas where it’s good to know what can happen (even though, as far as I can tell there’s no official documentation – and even if you find a note on Metalink (MoS) that confirms my comments that – apparently – wouldn’t be considered official documentation). Internal mechanised that have not been documented can be changed, of course, and that’s probably why Oracle Corp. chooses to avoid publishing many details about them. (In fact, in this case, I’ve found at least one note on MoS that has got it wrong, with a casual comment about the row splitting starting from the beginning.)

      Given the lack of an official statement and the level of tedious effort required to realise the potential benefits, this is an example of a case where I would take advantage of the information ONLY IF I had identified a specific table where there was a clear and significant benefit to be had if I could work out how to restructure the table correctly.

      The basic pattern of “most accessed first, nulls last” (and there’s the first conflict of interest – frequently accessed nulls should still appear early) is sound, but when considering each specific table you also have to be aware of the potential side effects of the way in which the row might be updated, and the extra (potentially extreme) chaining that might appear as a consequence (see the earlier blog linked in above).

      Comment by Jonathan Lewis — April 15, 2016 @ 10:04 am BST Apr 15,2016 | Reply

  5. Jonathan,

    Thanks for your reply. As I mentioned earlier I’ve viewed the order of walking through the columns for a selected column as piece1(col1, col2, col3 …col255),
    piece2(col256, col257 … colnnn). Would you please walk me through a row of say 300 all populated columns as to what piece is accessed first and second? What are the columns in those pieces and what order are they traversed to access column e.g. column 299? Sorry I’m having some “Who’s on first?” confusion with how this happens in reality. It seems that I would need to know this before attempting any reordering of columns.

    Thanks,

    Richard

    Comment by rbrieck — April 15, 2016 @ 4:32 pm BST Apr 15,2016 | Reply

    • Richard,

      That’s not a question with a simple generic answer, unfortunately – but I can give you a couple of examples, and raise a couple of questions:

      Example 1: If we assume that you insert the whole 300 column row in one insert then columns 1 to 45 would be in the first row-piece and columns 46 to 300 would be in the second row-piece. If you then selected column 299 (e.g. by a table access by rowid) Oracle would access the first row-piece first, which would tell it that the row-piece was the first row-piece of a chain and held only 45 columns, so (I assume) it would immediately access the second row-piece and then walk the columns until it got to the 254th column of that row-piece (which which be the 299th column of the row).

      Example 2: If you inserted a row with only 200 of the 300 columns then Oracle would insert just one row-piece of 200 columns. If you then updated that row to add columns 291 to 300 Oracle would (probably) end up with the row chained into a second block, with (again) columns 1 to 45 in the first row piece and columns 46 to 300 in the second row piece in the second block. The access would be the same as example 1, but now you’d have to jump to a new block when accessing the second row piece.

      There may be more complicated effects if the update that extended the row also resulted in a space limitation problem in the first block (e.g. you updated columns 1 to 10 to be much longer so that they were the only columns that could fit in the first block – I haven’t done any experiments to see what Oracle would do in this type of scenario, though.

      Comment by Jonathan Lewis — April 20, 2016 @ 4:39 pm BST Apr 20,2016 | Reply

      • Jonathan,

        Thanks, that clears up a lot for me. Regarding Example 1, what would happen with the addition of a 301st column (assuming all 301 have values)? Would both pieces need to be rebuilt with piece 1 now having columns 1 through 46 and piece 2 having columns 47 through 301? If so Oracle choosing to split rows in reverse seems to have created a lot of extra work compared to splitting first column to last.

        Richard

        Comment by rbrieck — April 20, 2016 @ 8:04 pm BST Apr 20,2016 | Reply

        • Richard,

          It’s very hard to predict these things, though I suspected it might do something like the disaster I described in https://jonathanlewis.wordpress.com/2012/03/30/quiz-night-17 , and that’s what happened in the test that I’ve just created (though the fact that the table I used was completely empty may have had some impact).

          I defined a table with 301 columns in 11.2.0.4 (not yet tested in 12.1.0.2)

          I inserted one row, populating only columns 1 and 200 – this gave me a complete row in block 129 (of file 7),

          I updated the row, populating column 300 – this gave me a first row piece of 45 columns in block 129 and a second row piece of 255 columns in block 130.

          I updated the row again, populating column 301 – this gave me a first row piece of 45 columns in block 129, a second row piece of ONE column in block 130 and a third row piece of 255 columns in block 130.

          Comment by Jonathan Lewis — April 20, 2016 @ 8:32 pm BST Apr 20,2016


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.