Oracle Scratchpad

June 8, 2010

Continued Rows

Filed under: Oracle,Troubleshooting — Jonathan Lewis @ 5:58 pm GMT Jun 8,2010

Do you think you know how Oracle records access to migrated or chained rows ? You may need to check your theories. Here’s a little demonstration that may amuse you. It uses an 8KB block size, a locally managed tablespaces (LMT) with uniform extent sizes of 1MB, and freelist management rather than automatic segment space management (ASSM).

create table t1
as
with generator as (
	select	--+ materialize
		rownum id
	from dual
	connect by
		rownum <= 10000
)
select
	cast(null as varchar2(100)) 		padding1,
	lpad(rownum,50,' ')			padding2,
	rownum					id
from
	generator	v1,
	generator	v2
where
	rownum <= 10000
;

update t1 set padding1 = rpad('x',100);
commit;

-- collect statistics (compute, no histograms)

Note that I’ve left the value for pctfree to its default of 10 – so the update of padding1 from null to rpad(‘x’,100) makes all the rows grow from about 60 bytes to about 160 bytes, which means that some of them are going to have to migrate. (Reminder: migration means the whole row is moved to another block, and a 9-byte row stub is left behind that consiss of the flag byte, the lock byte, the column count (which will be zero) and a pointer – in the form of a restricted rowid – to the new row location.)

While running the following three lines of SQL, I collected session statistics (v$sesstat) so that I could calculate the work done by each statement:

select count(id) from t1;
create index t1_i1 on t1(id);
alter index t1_i1 rebuild online;

In all three cases the optimizer chose a full tablescan to execute the statement. The statistics I want to show you are “session logical reads” and “table fetch continued row”, and they look like this:

Select

session logical reads                                                      261
table fetch continued row                                                    0

Create index

session logical reads                                                      350
table fetch continued row                                                    0

Alter index

session logical reads                                                    7,162
table fetch continued row                                                  162

So – a few questions:

    How come I don’t appear to have any continued (which means chained or migrated) rows when I count the rows or create the index, but I have continued rows when I rebuild the index ?
    How many migrated rows do I actually have ?
    Which version of Oracle am I using for this demonstration ?

And a few more for the adventurous:

    Is there a fairly easy way to find out roughly how many migrated rows I have – and what problems will other people see with your suggestion ?
    What do you think would happen if I used the “online” option for the create index ?
    What if I didn’t use the “online” option for the index rebuild ?

18 Comments »

  1. I’ll go for the first part.
    The ‘table fetch continued row’ is Oracle speak for ‘There is a pointer from the nominated position of the row to somewhere else and I have to go to that somewhere else to find the row/rest of the row’.

    Since it is scanning the entire table below the high-water mark, it will find the ‘id’ values wherever there are. It doesn’t have to interrupt the scan to jump to another bit of the table, because it will get there anyway.

    The ids were initially inserted in order (probably) because it is derived from ROWNUM. But when some rows are chained/migrated, the physical record with the id value can get shunted to the ‘end’ of the table. As such there are no longer in order when the full scan is done. I guess that, the way the index creation is done without online, a sort is done at the end and then the index segment is physically built.

    For an online index creation, concurrent insert/update/delete statements need to be accommodated. Oracle needs to avoid the ‘CREATE INDEX’ statement reading a record through a full scan, another session deleting that record two seconds later and the create index finishing with that record still represented in the index. As such, the concurrent DML statements have to maintain that index even if it is only partially created (with the CREATE INDEX building the index on the fly, rather than after a sort at the end), or all the activity has be recorded somewhere where the session creating the index can sort it out afterwards.

    I can’t make the jump to why that requires the ‘continued row’ operations.

    Comment by Gary — June 9, 2010 @ 6:12 am GMT Jun 9,2010 | Reply

  2. How come I don’t appear to have any continued (which means chained or migrated) rows when I count the rows or create the index, but I have continued rows when I rebuild the index ?
    I would agree with Gary here. In case of the first 2 statements (select count…and create index), oracle has to read all the data below HWM. Also in both cases all the blocks in the table will be accessed using full table scan. So there is no need to “locate” any particular row. Hence no value for “table fetch continued row” stat. However, in case of last statement (alter index…rebuild online), as oracle allows DML operations to take place during index rebuild, the table blocks are read one-at-a-time (I think) and oracle has to locate individual rows in order to build the index. This affects “table fetch continued row” stat value.
    I have no idea about answers to rest of the questions.

    Comment by Narendra — June 9, 2010 @ 9:32 am GMT Jun 9,2010 | Reply

  3. Which version of Oracle am I using for this demonstration ?
    9iR2 ??
    Is there a fairly easy way to find out roughly how many migrated rows I have – and what problems will other people see with your suggestion ?
    I think this can be achieved by executing a query that is guaranteed to access the table one-block-at-a-time (like forcing the query to read the index and then access the table) and then referring to the “table fetch continued row” stat. The problem that people will see is the “guarantee” to use a certain access path.
    What do you think would happen if I used the “online” option for the create index ?
    I believe the “table fetch continued row” stat will show some value if ONLINE option is used for CREATE INDEX (similar to ALTER INDEX…REBUILD ONLINE).
    What if I didn’t use the “online” option for the index rebuild ?
    I believe the “table fetch continued row” stat will NOT show any value if ONLINE option is not used for index rebuild as entire table will be locked and will be read during the “offline” rebuild.

    Comment by Narendra — June 9, 2010 @ 10:05 am GMT Jun 9,2010 | Reply

  4. Following is the demonstration that I used to find number of migrated rows. After executing query with index hint a couple of times, both times the “table fetch continued row” was incremented by same value.
    SQL> select count(*) from t1 where id is not null ;

    COUNT(*)
    ———-
    10000

    SQL> select a.sid, b.name, a.value from v$mystat a, v$statname b where a.statistic# = b.statistic# a
    nd b.name like ‘table fetch%’ ;

    SID NAME VALUE
    ———- —————————————————————- ———-
    16 table fetch by rowid 100177
    16 table fetch continued row 62028

    SQL> select /*+ INDEX(t1 t1_i1) */ count(padding1) from t1 where id is not null ;

    COUNT(PADDING1)
    —————
    10000

    SQL> select a.sid, b.name, a.value from v$mystat a, v$statname b where a.statistic# = b.statistic# a
    nd b.name like ‘table fetch%’ ;

    SID NAME VALUE
    ———- —————————————————————- ———-
    16 table fetch by rowid 110177
    16 table fetch continued row 68214

    SQL> select a.sid, b.name, a.value from v$mystat a, v$statname b where a.statistic# = b.statistic# a
    nd b.name like ‘table fetch%’ ;

    SID NAME VALUE
    ———- —————————————————————- ———-
    16 table fetch by rowid 110177
    16 table fetch continued row 68214

    SQL> select /*+ INDEX(t1 t1_i1) */ count(padding1) from t1 where id is not null ;

    COUNT(PADDING1)
    —————
    10000

    SQL> select a.sid, b.name, a.value from v$mystat a, v$statname b where a.statistic# = b.statistic# a
    nd b.name like ‘table fetch%’ ;

    SID NAME VALUE
    ———- —————————————————————- ———-
    16 table fetch by rowid 120177
    16 table fetch continued row 74400

    Comment by Narendra — June 9, 2010 @ 2:15 pm GMT Jun 9,2010 | Reply


  5. Is there a fairly easy way to find out roughly how many migrated rows I have – and what problems will other people see with your suggestion ?

    What’s wrong with the plain old CHAIN_CNT column in dba_tables? You have gathered statistics and your rows obviously should fit in a block, so all of CHAIN_CNT rows would actually be migrated. What problem would anyone see with this suggestion, I cannot fathom :-)
    Flado

    Comment by Flado — June 9, 2010 @ 3:09 pm GMT Jun 9,2010 | Reply

    • CHAIN_CNT is NOT populated by DBMS_STATS functions. Only ANALYZE TABLE will populate it, and this general frowned upon (MOS Doc ID 316897.1 )

      Also it would not differentiate between Chained Rows, rows that are too big to fit in one block and migrated rows, rows moved as a while to another block.

      Comment by Andy Campbell — June 10, 2010 @ 8:28 am GMT Jun 10,2010 | Reply

      • Joke alert!


        CHAIN_CNT is NOT populated by DBMS_STATS functions.

        True. But I can weasel my way out of this:

        The exact method of gathering statistics was not specified, so

        — collect statistics (compute, no histograms)

        could well translate to

        analyze table t1 compute statistics for all columns size 1;
        

        And I’ve already dealt with the other objection:

        … and your rows obviously should fit in a block, so all of CHAIN_CNT rows would actually be migrated.

        ;-)
        Flado

        Comment by Flado — June 10, 2010 @ 9:03 am GMT Jun 10,2010 | Reply

    • Flado,

      A problem with using analyze and checking the chain_cnt appears in another blog item: http://jonathanlewis.wordpress.com/2009/04/30/analyze-this

      The optimizer uses the chain_cnt to cost an indexed access if it has been set – even though dbms_stats() won’t set it.

      Comment by Jonathan Lewis — June 10, 2010 @ 10:51 am GMT Jun 10,2010 | Reply

  6. The first thing I’d like to say is that I don’t know all the answers about what Oracle is doing here.

    Gary and Narenda are heading in the right direction with their comments about pointers and tablescans and then concurrency – although the concurrency thing may be a little more subtle than at first sight.

    I have about 6,180 migrated rows in the table, Narenda’s indexed walk through the table will count them all – although things get a little messier if you have some rows that are chained rows (or migrated and then chained, or longer than 254 columns) because each “continued row” really means “following a pointer to find the next piece of a row”.

    Note, by the way, that “create index online” records 162 “table fetch continued row” and not 6,150 – I wrote this blog after rebuilding an index online for a table with 260 million rows. The rebuild was surprisingly slow, showed lots of “db file sequential read” waits, and recorded 6M “table fetch continued row”. Analysis of the data and code showed that there were far more than 6M migrated rows and we were spending all our time chasing them.

    Why 162 recorded when there were 6,150 – I think that in the case of the “online rebuild” Oracle is counting the blocks it visits to find continued rows, rather than the rows themselves – maybe it’s a bug, maybe it’s just a cunning trick. (If you check the number of session logical I/Os you’ll see that each continued row does show up as a buffer visit).

    Which version of Oracle: the reason I asked this is because in 9i, the results look like this on the online rebuild:

      session logical reads               7,154
      table fetch continued row           6,173
    

    There’s been a code change from 9i to 10g. (Interestingly, I think I’ve seen the same low value reported for “table fetch continued row” in other circumstances in 10g – but I can’t remember where and I haven’t found a way to create the anomaly, so my memory may be misleading.)

    Why does the online rebuild HAVE to follow the continued rows: I think it’s got to be something to do with concurrent changes and the special “materialized view log” (technically it’s an IOT that is referred to as the “journal” table) that Oracle maintains during the online rebuild.

    I haven’t worked out the details yet – but imagine the case where I don’t bother to follow a migrated row because I expect to pick it up later. When I get to the right block, what happens if the row has been updated so that it has migrated back to its original position ? Could I fail to record an index entry for it as I create the index, and then fail to capture it as I apply the materialized view log because the update in question wasn’t necessarily for the indexed column ? I wouldn’t have thought this was a problem – but it does depend on how Oracle handles read-consistency and the way it’s using the materialized view log in this special case. (Maybe I’ll look into that if I ever find a site that needs to do a lot of online index rebuilds and can’t afford to tidy out its migrated rows.)

    Why don’t I need to follow the migrated rows when I tablescan the table to count or create/rebuild the index with a locked table: I know that the migrated row will be somewhere in the table – and if the row has been migrated it will have carried the “head” rowid with it, so I will have enough information to create the index entry when I find the row.

    In passing – migrated rows and chained rows behave differently with respect to “back” pointers. A migrated row knows where it came from (the row piece carries the “head rowid”) a chained rowpiece does not – so, with suitably modified data, the count and the create index would report “table fetch continued row”.

    If you want to experiment and work out what Oracle’s doing, you’ll also need to keep an eye on the FLAG byte – looking, especailly, at the H (head) F (first row piece) and L (last row piece) bits.

    Comment by Jonathan Lewis — June 10, 2010 @ 10:49 am GMT Jun 10,2010 | Reply

  7. “A migrated row knows where it came from (the row piece carries the “head rowid”) a chained rowpiece does not”
    With this, say you have a record this is chained with the first part on block 1 and the second part on block 10. The column you are indexing is at the end of the record, so is on block 10. If you are full scanning to build the index then, when you pass block 1 you can’t create the index entry because you don’t know the column value, but when you hit block 10 you know the value but not the “head rowid”. Doesn’t that mean that for chained, as opposed to migrated, records it would have to follow the pointer from block 1 to 10 so that it knows both the rowid and value ? Or will it store the “head rowid” and “chain rowid” from block 1 and tie that up with the index value when it reaches block 10 ?

    Comment by Gary — June 10, 2010 @ 10:46 pm GMT Jun 10,2010 | Reply

    • Gary,

      “Doesn’t that mean that for chained, as opposed to migrated, records it would have to follow the pointer from block 1 to 10 so that it knows both the rowid and value ?”

      That’s what I believe happens – which is why my earlier comment says:

      so, with suitably modified data, the count and the create index would report “table fetch continued row”

      Comment by Jonathan Lewis — June 12, 2010 @ 2:33 pm GMT Jun 12,2010 | Reply

  8. I’d be curious to see what happens when doing Flashback Query. (Though not curious enough to actually try it myself. ;) )

    Comment by Jason Bucata — June 11, 2010 @ 3:41 am GMT Jun 11,2010 | Reply

    • Jason,

      (Though not curious enough to actually try it myself. ;) )

      This is exactly why I have a large document with lots of headings like “Flashback Query” with questions under them like: “possible side effects with migrated / chained rows ?”

      There are lots of vague ideas I have that might be worth checking, but aren’t worth the effort until I see a client having problems with some feature and some of the questions look as if they might be relevant.

      Comment by Jonathan Lewis — June 12, 2010 @ 2:38 pm GMT Jun 12,2010 | Reply

  9. A bit off-topic: Does it generally make sense to compare deltas for ‘session logical reads’ and ‘table fetch continued row’ for an interval to prove if chained rows are an instance-wide problem or not? E.g. I have a 9.2 DB that has 1.400 times more ‘session logical reads’ than ‘table fetch continued row’ for an high load interval of 6 hours. Can I deduct that (instance-wide) it’s not worth the effort to reorganize or go through export/import to get lower the number of chained rows?

    Comment by Marcus Mönnig — June 17, 2010 @ 2:51 pm GMT Jun 17,2010 | Reply

    • Marcus,

      Sorry, I missed this comment when you first posted it.
      Your idea has some merit – but may be open to error; but I think I might look at “table fetch by rowid” as the basis for comparison rather than “session logical reads”.

      The possible error, though, is implied by my comments about seeing low values for the “table fetch continued row” in some circumstances because Oracle is counting blocks visited for continued rows rather than the rows themselves – it’s possible that your estimates would be under-estimates. (Note that I couldn’t remember where or why I had seen the phenomenon, though).

      The other thought to bear in mind is that even if the system stats suggest that the “table fetch continued row” is a small fraction of the fetches, it’s possible that a single, critical, session is the one that’s suffering from the problem and still needs some correction. (And there’s always the issue that the fraction looks low because you have two simultaneous problems – a high count for continued fetches, and a very high count for session logical reads)

      Comment by Jonathan Lewis — July 5, 2010 @ 6:38 pm GMT Jul 5,2010 | Reply

  10. Jonathan,

    “what happens if the row has been updated so that it has migrated back to its original position ?”

    Is that possible? I thought it wasn’t.

    Comment by Joaquin Gonzalez — July 5, 2010 @ 11:51 am GMT Jul 5,2010 | Reply

    • Joaquin,

      Yes, it’s possible. Proof is left as an exercise for the reader.

      Simplest case:
      update a row to make it much longer, commit, check for migration, then update the row to make it shorts, commit, check where it ends up.

      Comment by Jonathan Lewis — July 5, 2010 @ 6:25 pm GMT Jul 5,2010 | Reply

  11. [...] difference between analyze and dbms_stats Anomalies with counting continued row activity Share this:TwitterLike this:LikeBe the first to like this post. Leave a [...]

    Pingback by I Wish « Oracle Scratchpad — December 16, 2011 @ 6:32 pm GMT Dec 16,2011 | Reply


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

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,308 other followers