Notice how the COUNT(col_vc94) seems to have done more than 5 times as many 'consistent gets' as the COUNT(col_vc93) ? (If you note from my previous tests, col_vc94 is the 96th column in the 350 column table (which makes it the 254th column if we count backwards).

Again, we see more than 5x 'consistent gets' with col_vc94 and col_vc348 as with col_vc1 and col_vc93.

The execution plan is a FullTableScan, yet the 'consistent gets' are so much higher.

What is my concern ? Whether the 'consistent gets' are *really* that high or not, they are likely significantly influencing "performance measures" -- e.g. whether AWR uses these (higher) figures or whether my own monitoring scripts use these (higher) figures, the fact would be that they may not be real indicators of performance ?

Soon, I will test with 400+, 500+ and 800+ columns !

..

UPDATE : 31-Oct-09Here are some results with a 1,000 column table (in a 16KB tablespace). The table has been created with 1 single row :

18 October, 2009

Per MetaLink Note#1062906.6, Intra-Block Row Chaining occurs when a Table definition has more than 255 columns because each Row Piece can handle only 255 columns.

I have been running some (not conclusive ?) tests with dummy tables of 348 or so columns. In my tests, I find that if I access the 96th column or higher, then the counter for 'consistent gets' shows 2 gets for the table block. However, if I access the 95th column or lower, I have only 1 'consistent gets'.These tests have been with columns of 3bytes/5bytes and 15bytes each. With 8KB blocks and 16KB blocks. With the row being accessed via an Index and by a ROWID lookup. An Indexed lookup has 4 'consistent gets', which include 3 for the index, for the 95th column or lower but 5 'consistent gets' for the 96th column or higher.

In all my test runs, I see 2 table block gets for the 96th column and 1 get for the 95th column.

Could this be related to the table definition ? (One number and date column followed by 346 varchar2 columns) ? I don't know for sure.

This is the output for a run where the varchar columns are defined as varchar2(5) with 3 characters stored (other tests have been with a definition of varchar2(15) and 13 characters stored) :Column COL_VC93 is the 95th column in the table. Column COL_VC94 is the 96th column.