Re: Chained row analysis results and questions

| 1. The results make sense.
|

That's a good summary of the stats that you get when you have rows with more than 255 columns.
The stats vary with version of Oracle, access method, and the luck of how the rows have been inserted and updated. For some bizarre information read:
http://jonathanlewis.wordpress.com/2012/03/30/quiz-night-17/

In outline: when you have rows with more than 255 columns you can induce some very strange intra-block, and inter-block chaining depending on how you modify the rows, so you can end up with row pieces of varying sizes rather than the 255 = 5 that you're expecting.

You also get variation in "continued row" counts depending on whether the continuation is in the same block or not, and on whether you're tablescanning or accessing by index. For example, I have a demo in 11.2.0.2 where I have 1,000 rows which are chained (intra-block) I access them by a full scan. I see ZERO as the continued row count, in a case where the consistent gets show quite clearly that I have "revisited" the block in order to access the second piece.

Unfortunately the only way to figure out what's happening (probably) is to start by dumping a few blocks from the table to see what sort of variation you have in how the rows are stored.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

• Original Message ----- From: <BRIECKR1_at_nationwide.com> To: <oracle-l_at_freelists.org> Sent: Friday, May 24, 2013 4:52 PM Subject: Chained row analysis results and questions

| 2. Why were only 4670184 continued rows fetches and not 54 million? Note:
| All columns in the 2nd row piece are nulls.
|
| 3. I have the same question for this one. Why only 4670184 continued
| rows?
|
| 4. The count for fetches by rowid are what I would expect. One for each
| row of the table. From the continued row fetches I conclude that there
| are 329 migrated rows. Is my conclusion correct?
|
| 5. The number of continued row fetches (915593) confuses me. All of the
| columns (256 - 260) in the 2nd row piece are null for all rows. Why
would
| any of these row pieces be visited?
|
| 6. I have the same issues with these results as for query 5 above.
|

--
http://www.freelists.org/webpage/oracle-l