Forum Stats

  • 3,872,750 Users
  • 2,266,468 Discussions
  • 7,911,293 Comments

Discussions

Factors affecting number of chained rows

Dan Jankowski
Dan Jankowski Member Posts: 375 Silver Badge
edited Nov 17, 2016 4:56AM in General Database Discussions

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?

jgarryJonathan LewisPS_orclNerdDejan T.
«13

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Nov 15, 2016 1:30PM

    BLOCKSIZE=?

    PCTFREE=?

    PS_orclNerd
  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited Nov 15, 2016 1:34PM

    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.

  • jgarry
    jgarry Member Posts: 13,844 Gold Crown
    edited Nov 15, 2016 1:44PM

    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.

    Dan Jankowski
  • Mark D Powell
    Mark D Powell Member Posts: 5,914 Blue Diamond
    edited Nov 15, 2016 1:59PM

    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 --

    jgarry
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,116 Blue Diamond
    edited Nov 15, 2016 3:40PM

    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

    Dan Jankowski
  • Unknown
    edited Nov 15, 2016 4:52PM
    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.)
  • Unknown
    edited Nov 15, 2016 4:55PM
    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.

  • Unknown
    edited Nov 15, 2016 4:59PM
    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.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,116 Blue Diamond
    edited Nov 16, 2016 3:52PM

    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.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,116 Blue Diamond
    edited Nov 15, 2016 6:15PM

    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

This discussion has been closed.