1 2 Previous Next 26 Replies Latest reply on Nov 17, 2016 9:56 AM by Dan Jankowski

    Factors affecting number of chained rows

    Dan Jankowski

      Oracle 11.2.0.4 running on Linux

      My data warehouse has a 5TB interval-list partitioned table with over 350 columns. Full partition scans of the table are becoming increasingly costly, due to the time spent performing single-block sequential reads, presumably to fetch the chained rows. I know that the number of columns in the table (>255) is usually the cause of chained rows. However, changing the structure of the table is not an option at this point.

      What is strange is that the table in the test environment has far more chained rows than the same table in the production environment, despite both being loaded with the same data. I'd like to understand why the table in one environment is more prone to chained rows than another, so I can reduce the occurrence of chained rows.

      I tried to recreate the chained rows by copying data to a new table as follows :

      1. Identify a partition of table T1 containing 80,000 rows
      2. Count chained rows in partition of T1 – 3,000 chained rows found (ANALYZE TABLE T1 SUBPARTITION(x) LIST CHAINED ROWS)
      3. Copy contents of partition to table T2 (CREATE TABLE T2 AS SELECT * FROM T1 SUBPARTITION (x))
      4. Count chained rows in T2 – 80 chained rows found
      5. Truncate original partition of T1, and reload data from T2 (INSERT INTO T1 SELECT * FROM T2)
      6. Count chained rows in T1 – 3,000 chained rows found

      What’s puzzling me is that the original table T1 has such a large number of chained rows, even when reloaded, whereas the new table T2 has only a small number of chained rows, despite containing the same data. I’ve compared the various storage attributes for the two tables, and can see no difference. I even created T2 as a partitioned table, but again no difference.

      What other factors might affect the number of chained rows in T1?

        • 1. Re: Factors affecting number of chained rows
          John Thorton

          BLOCKSIZE=?

          PCTFREE=?

          • 2. Re: Factors affecting number of chained rows
            Andrew Sayer

            Having more than 255 columns means all your rows are always chained.

            If you want to avoid this then you have to change your table structure.

            • 3. Re: Factors affecting number of chained rows
              jgarry

              Are you using any old freelist things?  What is pctfree on the tablespace and tables?  What are the data types of the columns?  Has the tablespace ever had any strange patterns of table or partition deletion (like, creating a bunch and deleting every other one)?

               

              I wonder more speculatively if there is something odd going on with the select * giving some different order to the columns, and perhaps the values or nulls in those columns might be confusing things.

               

              Also google intra-block chaining, it might be worth dumping some blocks to elucidate the truth.

              1 person found this helpful
              • 4. Re: Factors affecting number of chained rows
                Mark D Powell

                Dan, why presume the single block sequential reads are to fetch chained rows?  Check the "table fetch continued row" statistic to see how much extra work the "chained" rows cost you.

                - -

                http://blog.tanelpoder.com/2009/11/04/detect-chained-and-migrated-rows-in-oracle/

                - -

                HTH -- Mark D Powell --

                • 5. Re: Factors affecting number of chained rows
                  Jonathan Lewis

                  Insert as select and create as select use different algorithms (for dealing with rows with more than 255 columns)  - I can't remember the details, but I've got a demo somewhere, and I remember reading a MoS note some time ago about changes in code that were supposed to make Oracle use space more efficiently.

                   

                  I think CTAS tries to keep row pieces in the same block and wastes space as a side effect while insert as select treats each row piece more like a separate row so does achieves better space handling but ends up chaining more rows between blocks.

                   

                  Cross-checks

                  a) How many used blocks in your t1 subpartition and t2 table

                  b) What happens when you truncate table t2 and insert as select from the t1 subpartition ?

                   

                  More basic check first: make sure that the tablespace definitions have the same space management definitions.

                   

                  I check to see if I can find my notes and demo

                   

                  Regards

                  Jonathan Lewis

                  1 person found this helpful
                  • 6. Re: Factors affecting number of chained rows
                    rp0428

                    I’ve compared the various storage attributes for the two tables, and can see no difference.

                    Keep in mind that we can NOT see anything at all unless you post it - which you haven't done.

                     

                    What other factors might affect the number of chained rows in T1?

                    The main 'factor' for an insert is whether a row fits into a block. A main 'factor' for an update is whether the update will still fit in the block.

                     

                    There are two types of chaining: intra-block (the row pieces are in the same block) and 'out of block' (the row pieces are in different blocks.

                     

                    1. assume 8k blocks

                    2. assume four rows - two rows of length 5k bytes and two rows of length 10 bytes

                     

                    How many blocks are needed to hold all 4 rows? How many rows will be chained? Post your answer

                     

                    It depends on the order that the rows are loaded. Suppose you load one 5k row, then one 10 byte row, the other 5k row and then the other 10 byte row.

                     

                    You could wind up with two blocks each block having one 5k row and one 10 byte row and no row is chained. Or you could end up with two blocks with only one of them chained.

                     

                    This is the classic 'quiz question' about the topic from Jonathan Lewis

                    https://jonathanlewis.wordpress.com/2012/03/30/quiz-night-17/

                    That’s the one I was looking for. In fact the demonstration I use starts by populating columns col000 and col254 before executing he loop, and (on my version of Oracle) this results in 746 row pieces spread across 746 blocks – the last piece is 255 columns, and the preceding 745 pieces hold one column each. (I think in your example you would end up with two columns in the first row-piece.)

                    • 7. Re: Factors affecting number of chained rows
                      rp0428

                      Having more than 255 columns means all your rows are always chained.

                      I'm not so sure that is true.

                       

                      What about rows where columns beyond that limit haven't been populated at all?

                       

                      If it were can you explain why all of the rows aren't reported as chained? Does 'analyze' report both intra-block chained rows as well as those that span blocks?

                       

                      See Jonathan's classic article about the topic at the link I posted.

                      • 8. Re: Factors affecting number of chained rows
                        rp0428

                        I check to see if I can find my notes and demo

                         

                        I posted a link to your classic 'quiz' question about chained blocks. Not sure if that is the one you were referring to.

                         

                        Can you comment on two points:

                         

                        1. Does the analyze command report on both intra-block chained rows as well as multiple block chained rows.

                         

                        2. Isn't it considered a myth that 'all rows are always chained if a table has more than 255 columns'? As far as I know chaining of a row still depends on whether certain trailing columns are, or ever have been, populated.

                        • 9. Re: Factors affecting number of chained rows
                          Jonathan Lewis

                          T1 is defined as

                          SQL> desc t1

                          Name                                      Null?    Type

                          ----------------------------------------- -------- ----------------------------

                          COL0001                                            VARCHAR2(100)

                          COL0002                                            VARCHAR2(100)

                          COL0003                                            VARCHAR2(100)

                          COL0004                                            VARCHAR2(10)

                          COL0005                                            VARCHAR2(10)

                          ...

                          COL0363                                            VARCHAR2(10)

                          COL0364                                            VARCHAR2(10)

                          COL0365                                            VARCHAR2(10)

                           

                          The first 3 columns are populated with randomly generated strings of varying length.

                          Column 4 is populated with the insertion row number, lpadded to 10 characters

                          Columns 5 to 365 are populated with their own names (i.e. fixed 7 characters).

                          10,012 rows in total.

                           

                          Table t1a is created as pctfree 25, select * from t1 where rownum = 0;

                           

                           

                          Cut and Paste from SQL*Plus 11.2.0.4

                           

                           

                          SQL> truncate table t1a;

                          SQL> insert into t1a select * from t1;

                          SQL> commit;

                          SQL> analyze table t1a compute statistics;

                          SQL> select table_name, blocks, chain_cnt, num_rows, avg_row_len from user_tables where table_name = 'T1A';

                           

                          TABLE_NAME              BLOCKS  CHAIN_CNT  NUM_ROWS AVG_ROW_LEN

                          -------------------- ---------- ---------- ---------- -----------

                          T1A                        6676      3294      10012        3062

                           

                          SQL> truncate table t1a;

                          SQL> insert /*+ append */ into t1a select * from t1;

                          SQL> commit;

                          SQL> analyze table t1a compute statistics;

                          SQL> select table_name, blocks, chain_cnt, num_rows, avg_row_len from user_tables where table_name = 'T1A';

                           

                          TABLE_NAME              BLOCKS  CHAIN_CNT  NUM_ROWS AVG_ROW_LEN

                          -------------------- ---------- ---------- ---------- -----------

                          T1A                        9526        221      10012        3062

                           

                           

                          The effect of /*+ append */ is the same as the effect of create table.

                           

                          As you can see the insert without the /*+ append */ packs the data much better - when the second row piece of a row won't fit into a block it gets inserted into the next block - but that means lots of chained rows.

                          For the insert with the /*+ append */ Oracle tries to ensure that the entire row fits into the block, if it doesn't it picks another block rather than chaining the row (usually).

                          I don't know the conditions which make the append produce (or, at least, report) a chained row - there's probably some off boundary condition that appears occasionally.

                           

                           

                          [UPDATE]: I rigged the data to randomise the row lengths so that I didn't accidentally hit a special case. With my pctfree a typical block can hold two rows with a little free space, but there are a few places where the length of the second row doesn't quite fit within the PCTFREE. In this case Oracle does INTRA-block chaining to get as much of the rows as possible into the block, but then does INTER-block chaining to get the last column or two into the next block. This explains the 221 chained rows.

                           

                          [UPDATE 2]:  A little oddity about the insert vs.  insert /*+ append */ (or ctas) is the that insert splits the row from the end (i.e. the first piece is 110 columns, the second is 255 in my example - and then Oracle inserts the second piece first), while the ctas/append splits the row from the front (i.e. the first piece is 255 columns the second is 110 - with the first piece inserted first). This may be all it takes to explain why a simple insert ends up using less space and doing more chaining, viz: it may simply be a side effect of the splitting algorithm).

                           

                           

                          Regards

                          Jonathan Lewis

                           

                          [EDIT]:Originally I had the two descriptions about space usage back to front (as pointed out by Randolf Geist below), and I've now corrected this. 

                          [EDIT 2]: And in the edit I said that this produced the opposite result to the OP - but that was a mistake, I had demonstrated an effect that matched the OP's results.

                          • 10. Re: Factors affecting number of chained rows
                            Jonathan Lewis

                            rp0428,

                             

                            The notes I was referring to were some I hadn't previously published. Unfortunately they don't include the MoS document id that I mentioned.

                             

                            1) The analyze command doesn't report intra-block chaining (at least in recent versions), only inter-block chaining. However there are also sorts of wierd and wonderful variations in the statistic "table fetch continued rows" depending on (a) version, (b) whether it's migrated or chained, (c) whether it's intra or inter block chaining (d) whether you're doing tablescans or indexes accesses, (e) whether or not you fetch a column that is not in the first rowpiece. So I'm not going to make any statement about how the statistic current works. (Some chat about that in the comments to this blog post: https://jonathanlewis.wordpress.com/2010/06/08/continued-rows/  )

                             

                            2) This depends on the ambiguity of the language used. Any row with a popoulated column after the 255th will be split into two (or more) row pieces. Rows in a table with a definition that exceeds 255 columns need not be split into multiple row pieces - but that means those specific rows do not have a column past the 255th populated.

                             

                            As the article you linked to suggests, all sorts of nasty things can happen when you start to update rows in tables with more than 255 columns - including getting into a state where a row that doesn't appear to need multiple row-pieces ends up with multiple row pieces in the same block.

                             

                            Regards

                            Jonathan Lewis

                            • 11. Re: Factors affecting number of chained rows
                              Andrew Sayer

                              rp0428 wrote:

                               

                              Having more than 255 columns means all your rows are always chained.

                              I'm not so sure that is true.

                               

                              What about rows where columns beyond that limit haven't been populated at all?

                               

                              If it were can you explain why all of the rows aren't reported as chained? Does 'analyze' report both intra-block chained rows as well as those that span blocks?

                               

                              See Jonathan's classic article about the topic at the link I posted.

                              Good point. I would have assumed that since the chaining happens in "reverse order" i.e. the columns after 255 populate the first block and then link to the second block with the remaining columns, then the rows must still be chained otherwise when an update to a NULL valued column occurred, the row would have to split with the first row piece now becoming the second row piece. Having read the link you posted it seems that it's possible and even more nasty than I had thought.

                               

                              I'm not too sure on what analyze would count - I haven't tried too much as most of the times I've experienced this sort of issue, the table was already large enough to prevent me from wanting to let an analyze command run for days, and there was always a quick and obvious solution in splitting up the table into thinner tables. Judging by the comments we would probably need to look at the actual block dumps to know anything for certain. I don't have an environment to test on at the moment but I'm sure we'll see some interesting demos.

                               

                              (The non-population is hopefully not relevant. Even with a table with this many columns, I would expect at least some of the columns above 255 are populated for almost every row. Dan, is that the case?)

                              • 12. Re: Factors affecting number of chained rows
                                Randolf Geist

                                Jonathan Lewis wrote:

                                 

                                As you can see the insert /*+ append */ packs the data much better - when the second row piece of a row won't fit into a block it gets inserted into the next block - but that means lots of chained rows.

                                For the ordinary insert Oracle tries to ensure that the entire row fits into the block, if it doesn't it picks another block rather than chaining the row (usually).

                                Jonathan,

                                 

                                didn't your script output proof it the other way around - it's the conventional insert that packs the data much better at the price of increased row chaining, as you also confirm in your further comments?

                                 

                                Randolf

                                • 13. Re: Factors affecting number of chained rows
                                  Dan Jankowski

                                  Thanks for all the useful suggestions. There's clearly a lot to consider with regards to row chaining, which I'll tackle in due course. However, to my original conundrum, why I was seeing differences in the number of chained rows for different copies of the same data, the answer may lie in hidden columns.

                                   

                                  In addition to the 363 visible columns, it transpires the original table T1 has an additional 33 hidden columns, of the form SYS_C00nnn_yymmddhh:mi:ss$. These 33 columns are mainly VARCHAR2 up to 1800 characters, with a handful of DATE and NUMBER columns. They are not visible in DBA_TAB_COLUMNS, but can be seen in DBA_TAB_COLS. My guess is that over time, columns have been dropped from the table. Also, the table is enabled for OLTP compression (despite OLTP compression being ineffective on tables with more than 255 columns), and according to the below post, these columns would have been renamed and set to hidden when dropped (https://orastory.wordpress.com/2014/11/11/oltp-compression-drop-column-partition-exchange/ )

                                   

                                  So in my original test, when creating T2 as select * from T1, only 363 columns were created, whilst the original table T1 actually contains 396 columns. To test this, I replicated the hidden columns in a third table and then repeated the load :

                                   

                                  - T1 - original table  - 3,000 chained rows

                                  - T2 - table created using "CREATE AS SELECT" - 80 chained rows

                                  - T3 - table created with 396 columns, then dropped 33 column - 2,600 chained rows

                                   

                                  So, whilst T3 does not contain quite as many chained rows as T1, it appears that the presence of the hidden columns is a significant factor in the number of chained rows.

                                   

                                  Finally, to give an indication of the impact of chained rows, querying a larger data set in the original table (a full scan of 20 subpartitions) yielded the following statistics :

                                   

                                  Rowcount - 9.6 million

                                  Chained Rowcount - 420,000

                                   

                                  Table scan rows gotten - 140 million

                                  Table fetch continued rows - 1.7 million

                                   

                                  I/O waits (from ASH)

                                  - 99% db file sequential reads, confirmed as single-block reads

                                  - <1% direct path reads

                                   

                                   

                                  Now to figure out how to eliminate the hidden rows and bring the number of chained rows under control.

                                  • 14. Re: Factors affecting number of chained rows
                                    rp0428

                                    Did you see what I first posted?

                                    Keep in mind that we can NOT see anything at all unless you post it - which you haven't done.

                                    You keep talking about this table having so many columns and that table having different numbers but you still haven't SHOWN US anything at all.

                                     

                                    Post the actual DDL for the tables you are referring to.

                                    However, to my original conundrum, why I was seeing differences in the number of chained rows for different copies of the same data, the answer may lie in hidden columns.

                                    So far ALL of the columns are hidden from us - please post them.

                                    In addition to the 363 visible columns, it transpires the original table T1 has an additional 33 hidden columns, of the form SYS_C00nnn_yymmddhh:mi:ss$.

                                    Oracle creates hidden columns for several reasons. One reason is to represent the attributes of an object type. Suppose you create a user defined type named myType with 5 attributes and then add a column named 'myColumn' of type 'myType.

                                     

                                    Then the user-accessible column name will be 'myColumn' and will be visible in DBA_TAB_COLUMNS but there will be 5 hidden columns that represent the attributes. Those are the columns you can see in DBA_TAB_COLS.

                                    So, whilst T3 does not contain quite as many chained rows as T1, it appears that the presence of the hidden columns is a significant factor in the number of chained rows.

                                    The issue is the columns that are populated and their physical location relative to those row piece boundaries at 255 columns. While I haven't tested it I doubt that the fact the columns are hidden is a contributing factor at all.

                                     

                                    On the other hand an 'invisible' column won't even be copied by a SELECT statement. Further, if you make an invisible column visible it will now appear as the last column even though physically it might be the first column.

                                     

                                    Then if you use a SELECT * to copy the table that column that used to be physically first but logically last will now be physically last in the new table.

                                     

                                    Since that FIRST column is probably highly populated that can now cause your 300th column to be highly populated AND physically located after 200 mostly null columns. That can certainly cause havoc with row chaining.

                                    create table test_order1 (col1 number, col2 number, col3 number)

                                     

                                    desc test_order1

                                     

                                    Table TEST_ORDER1 created.

                                     

                                    Name Null Type  

                                    ---- ---- ------

                                    COL1      NUMBER

                                    COL2      NUMBER

                                    COL3      NUMBER

                                     

                                    alter table test_order1 modify col1 invisible

                                     

                                    desc test_order1

                                     

                                    Name Null Type  

                                    ---- ---- ------

                                    COL2      NUMBER

                                    COL3      NUMBER

                                     

                                    select column_name, column_id from user_tab_cols

                                    where table_name = 'TEST_ORDER1'

                                    order by column_id

                                     

                                    COL2    1

                                    COL3    2

                                    COL1   

                                     

                                    alter table test_order1 modify col1 visible

                                     

                                    Table TEST_ORDER1 altered.

                                     

                                    desc test_order1

                                     

                                    Name Null Type  

                                    ---- ---- ------

                                    COL2      NUMBER

                                    COL3      NUMBER

                                    COL1      NUMBER

                                     

                                    select column_name, column_id from user_tab_cols

                                    where table_name = 'TEST_ORDER1'

                                    order by column_id

                                     

                                    COL2    1

                                    COL3    2

                                    COL1    3

                                    -- now copy the table

                                    create table test_order2 as select * from test_order1

                                     

                                    desc test_order2

                                     

                                    Name Null Type  

                                    ---- ---- ------

                                    COL2      NUMBER

                                    COL3      NUMBER

                                    COL1      NUMBER

                                     

                                    -- check the column order

                                     

                                    COL2    1

                                    COL3    2

                                    COL1    3

                                    It is the PHYSICAL ordering of the columns that determines how/if trailing null columns affect chaining.

                                     

                                    So with invisible/visible columns just copying the data can physically alter the column order.

                                     

                                    And of course don't forget virtual columns (create manually or by Oracle to support function-based indexes). I've left out the intermediate results but you can infer them from the last example or test this sample yourself

                                    create table test_virtual (col1 number, col2 number, col3 as (col1 + col2))

                                     

                                    desc test_virtual

                                     

                                    insert into test_virtual (col1, col2) values (3, 5)

                                     

                                    select * from test_virtual

                                     

                                    create table test_virtual1 as select * from test_virtual

                                     

                                    desc test_virtual1

                                     

                                    insert into test_virtual1 (col1, col2) values (4, 6)

                                     

                                    select * from test_virtual1

                                     

                                    3    5    8

                                    4    6   

                                    So the original table may be fine in terms of storage and chained rows since there are only two physical columns. But the new table:

                                     

                                    1. has three physical columns - making the rows longer and more subject to chaining

                                    2. has a third column that no longer provides the business rule the old virtual column provided (sum of the other two columns)

                                     

                                    That virtual column is NOT hidden so the column order won't be rearranged. But when you copy the table you now have a new physical column that can affect row chaining.

                                    1 2 Previous Next