Chained row analysis results and questions

From: <BRIECKR1_at_nationwide.com>
Date: Fri, 24 May 2013 11:52:11 -0400
Message-ID: <OFF05E56AF.C21F6366-ON85257B75.0056D370-85257B75.00572CB8_at_lnotes-gw.ent.nwie.net>

Hello,
I am trying to make sense of what I've observed regarding row chaining in a particular table. We are using Oracle 11.1.0.7 and there is a bug in our version with the ANALYZE command that can cause loss of service. It isn't fixed until a later version that we can't upgrade to so I need to resort to my own analysis to determine where we are with chaining.

Some background on the table:

The table has 54 million rows. It has 260 columns. The average row length is 377 bytes. We use an 8k blocksize. Most of the columns that contain all nulls have been moved to the end of the table. Of these the last 6 columns contain all nulls. I mention that as I expect that these columns will appear on another block since they are past the 255 column limit for a row piece.

I've attempted to see how many rows we have that are chained and how many are migrated. I've run queries against the table and have observed how the "table fetch continued row" and "table fetch by rowid" are incremented in my session after each query. I ran queries using forced full scans and forced index scans. I confirmed that these actions occurred via the execution plans. All the queries executed were some combination of counts on one to three selected columns. I used the 1st,, 17th and 260th columns. My reasoning here was to select a column from the first and last block (hence the 1st and 260th column) and an arbitrary column from the 1st row piece (the 17th column) that was not contained in the index I would use for the test. That index was on the 1st column.

Here the results of my 6 test queries followed by my comments or questions on each:

Query                                                   table fetch table 
fetch   1st col         17th col                260th col
                                                        continued row   by 
rowid   count           count           count

1) full scan counting the 1st column                    0               3  
54499787        na              na
2) full scan counting the 260th column                  4670184 0 na na 0
3) full scan counting the 1st and 260th columns         4670184 0 54499787 
na              0 
4) index full scan counting the 17th column             329 54499787    na 
        157             na
5) index full scan counting the 260th column            915593 54499802 na 
        na              0
6) index full scan counting the 17th and 260th column   915593 54499802 na 
        157             0

Note: "na" means no count was collected by this query.

1. The results make sense. All rows came back and the 2nd row piece not referenced (i.e. no continued rows).
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.

I appreciate any feedback you have on my results and the questions above.

Thanks,

Richard

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

Received on Fri May 24 2013 - 17:52:11 CEST