Some Testing on Intra-Block Row Chaining

from:http://hemantoracledba.blogspot.com/2009/10/some-testing-on-intra-block-row.html

 

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.

SQL>
SQL> create tablespace test_8K  blocksize 8192
 2  datafile '/oracle_fs/Databases/ORT24FS/test_8k.dbf' size 1000M autoextend on next 100M maxsize 2000M
 3  extent management local autoallocate segment space management auto;

Tablespace created.

SQL>
SQL> drop table many_columns_table purge;
drop table many_columns_table purge
          *
ERROR at line 1: 
ORA-00942: table or view does not exist


SQL>
SQL> create table many_columns_table (
 2  col_number  number,
 3  col_date  date,
 4  col_vc1  varchar2(5),
 5  col_vc2   varchar2(5),
 6  col_vc3  varchar2(5),
 7  col_vc4  varchar2(5),
 8  col_vc5  varchar2(5),
 9  col_vc6  varchar2(5),
10  col_vc7     varchar2(5),
11  col_vc8     varchar2(5),
12  col_vc9     varchar2(5),
13  col_vc10     varchar2(5),
14  col_vc11     varchar2(5),
15  col_vc12     varchar2(5),
16  col_vc13     varchar2(5),
17  col_vc14     varchar2(5),
18  col_vc15     varchar2(5),
.....
344  col_vc341    varchar2(5),
345  col_vc342    varchar2(5),
346  col_vc343    varchar2(5),
347  col_vc344    varchar2(5),
348  col_vc345    varchar2(5),
349  col_vc346    varchar2(5),
350  col_vc347    varchar2(5),
351  col_vc348    varchar2(5)
352  )
353  tablespace test_8k
354  /

Table created.

SQL>
SQL>
SQL> insert into many_columns_table
 2  select  object_id, created,
 3  dbms_random.string('X',3),
 4  dbms_random.string('X',3),
 5  dbms_random.string('X',3),
 6  dbms_random.string('X',3),
 7  dbms_random.string('X',3),
 8  dbms_random.string('X',3),
 9  dbms_random.string('X',3),
10  dbms_random.string('X',3),
11  dbms_random.string('X',3),
12  dbms_random.string('X',3),
13  dbms_random.string('X',3),
14  dbms_random.string('X',3),
15  dbms_random.string('X',3),
16  dbms_random.string('X',3),
17  dbms_random.string('X',3),
18  dbms_random.string('X',3),
...
341  dbms_random.string('X',3),
342  dbms_random.string('X',3),
343  dbms_random.string('X',3),
344  dbms_random.string('X',3),
345  dbms_random.string('X',3),
346  dbms_random.string('X',3),
347  dbms_random.string('X',3),
348  dbms_random.string('X',3),
349  dbms_random.string('X',3),
350  dbms_random.string('X',3)
351  from dba_objects ;

50640 rows created.

SQL>
SQL>
SQL> exec dbms_stats.gather_table_stats('','MANY_COLUMNS_TABLE',estimate_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

SQL>
SQL> select blocks, num_rows, avg_row_len*num_rows*1.1/8192 from user_tables where table_name = 'MANY_COLUMNS_TABLE';

   BLOCKS   NUM_ROWS AVG_ROW_LEN*NUM_ROWS*1.1/8192
---------- ---------- -----------------------------
    11117      50640                    9546.92578

SQL> select blocks, bytes/1048576 from user_segments where segment_type = 'TABLE' and segment_name = 'MANY_COLUMNS_TABLE';

   BLOCKS BYTES/1048576
---------- -------------
    11264            88

SQL>
SQL> select count(*) from many_columns_table;

 COUNT(*)
----------
    50640

SQL> create index many_cols_ndx_1 on many_columns_table(col_number);

Index created.

SQL>
SQL> connect hemant/hemant
Connected.
SQL> set autotrace on
SQL> select col_vc1, col_vc3,col_vc5, col_vc340 from  many_columns_table where col_number = 1050;

COL_V COL_V COL_V COL_V
----- ----- ----- -----
GFL   DW0   WUN   9ZP


Execution Plan
----------------------------------------------------------
Plan hash value: 1736777747

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |    21 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MANY_COLUMNS_TABLE |     1 |    21 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MANY_COLS_NDX_1    |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("COL_NUMBER"=1050)


Statistics
----------------------------------------------------------
         1  recursive calls
         0  db block gets
         5  consistent gets
         1  physical reads
         0  redo size
       724  bytes sent via SQL*Net to client
       492  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
         1  rows processed

SQL> select col_vc1, col_vc3,col_vc5, col_vc340 from  many_columns_table where col_number = 1050;

COL_V COL_V COL_V COL_V
----- ----- ----- -----
GFL   DW0   WUN   9ZP


Execution Plan
----------------------------------------------------------
Plan hash value: 1736777747

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |    21 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MANY_COLUMNS_TABLE |     1 |    21 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MANY_COLS_NDX_1    |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("COL_NUMBER"=1050)


Statistics
----------------------------------------------------------
         1  recursive calls
         0  db block gets
         5  consistent gets
         0  physical reads
         0  redo size
       724  bytes sent via SQL*Net to client
       492  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
         1  rows processed

SQL> select col_vc1, col_vc3,col_vc5, col_vc93 from  many_columns_table where col_number = 1050;

COL_V COL_V COL_V COL_V
----- ----- ----- -----
GFL   DW0   WUN   J7N


Execution Plan
----------------------------------------------------------
Plan hash value: 1736777747

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |    21 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MANY_COLUMNS_TABLE |     1 |    21 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MANY_COLS_NDX_1    |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("COL_NUMBER"=1050)


Statistics
----------------------------------------------------------
         1  recursive calls
         0  db block gets
         4  consistent gets
         0  physical reads
         0  redo size
       723  bytes sent via SQL*Net to client
       492  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
         1  rows processed

SQL> select col_vc1, col_vc3,col_vc5, col_vc93 from  many_columns_table where col_number = 1050;

COL_V COL_V COL_V COL_V
----- ----- ----- -----
GFL   DW0   WUN   J7N


Execution Plan
----------------------------------------------------------
Plan hash value: 1736777747

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |    21 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MANY_COLUMNS_TABLE |     1 |    21 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MANY_COLS_NDX_1    |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  2 - access("COL_NUMBER"=1050)


Statistics
----------------------------------------------------------
         1  recursive calls
         0  db block gets
         4  consistent gets
         0  physical reads
         0  redo size
       723  bytes sent via SQL*Net to client
       492  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
         1  rows processed

SQL> select col_vc1, col_vc3,col_vc5, col_vc94 from  many_columns_table where col_number = 1050;

COL_V COL_V COL_V COL_V
----- ----- ----- -----
GFL   DW0   WUN   VXM


Execution Plan
----------------------------------------------------------
Plan hash value: 1736777747

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |    21 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MANY_COLUMNS_TABLE |     1 |    21 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MANY_COLS_NDX_1    |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("COL_NUMBER"=1050)


Statistics
----------------------------------------------------------
         1  recursive calls
         0  db block gets
         5  consistent gets
         0  physical reads
         0  redo size
       723  bytes sent via SQL*Net to client
       492  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
         1  rows processed

SQL> select col_vc1, col_vc3,col_vc5, col_vc94 from  many_columns_table where col_number = 1050;

COL_V COL_V COL_V COL_V
----- ----- ----- -----
GFL   DW0   WUN   VXM


Execution Plan
----------------------------------------------------------
Plan hash value: 1736777747

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |    21 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MANY_COLUMNS_TABLE |     1 |    21 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MANY_COLS_NDX_1    |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("COL_NUMBER"=1050)


Statistics
----------------------------------------------------------
         1  recursive calls
         0  db block gets
         5  consistent gets
         0  physical reads
         0  redo size
       723  bytes sent via SQL*Net to client
       492  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
         1  rows processed

SQL> set autotrace off
SQL>
SQL> select n.name, s.value from v$statname n, v$sesstat s where n.statistic#=s.statistic#
 2  and s.sid=(select sid from v$mystat where rownum = 1)
 3  and (n.name = 'consistent gets' or n.name like 'table fetch%');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
consistent gets                                                         339
table fetch by rowid                                                     23
table fetch continued row                                                 0

SQL>
SQL> select rowid from many_columns_table where col_number = 1050;

ROWID
------------------
AAANT0AAHAAAACvAAH

SQL>
SQL>
SQL>
SQL> select n.name, s.value from v$statname n, v$sesstat s where n.statistic#=s.statistic#
 2  and s.sid=(select sid from v$mystat where rownum = 1)
 3  and (n.name = 'consistent gets' or n.name like 'table fetch%');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
consistent gets                                                         344
table fetch by rowid                                                     23
table fetch continued row                                                 0

SQL> select col_vc93 from many_columns_table where rowid='AAANT0AAHAAAACvAAH';

COL_V
-----
J7N

SQL> select n.name, s.value from v$statname n, v$sesstat s where n.statistic#=s.statistic#
 2  and s.sid=(select sid from v$mystat where rownum = 1)
 3  and (n.name = 'consistent gets' or n.name like 'table fetch%');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
consistent gets                                                         345
table fetch by rowid                                                     24
table fetch continued row                                                 0

SQL> select col_vc94 from many_columns_table where rowid='AAANT0AAHAAAACvAAH';

COL_V
-----
VXM

SQL> select n.name, s.value from v$statname n, v$sesstat s where n.statistic#=s.statistic#
 2  and s.sid=(select sid from v$mystat where rownum = 1)
 3  and (n.name = 'consistent gets' or n.name like 'table fetch%');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
consistent gets                                                         347
table fetch by rowid                                                     25
table fetch continued row                                                 0

SQL> select col_vc94 from many_columns_table where rowid='AAANT0AAHAAAACvAAH';

COL_V
-----
VXM

SQL> select n.name, s.value from v$statname n, v$sesstat s where n.statistic#=s.statistic#
 2  and s.sid=(select sid from v$mystat where rownum = 1)
 3  and (n.name = 'consistent gets' or n.name like 'table fetch%');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
consistent gets                                                         349
table fetch by rowid                                                     26
table fetch continued row                                                 0

SQL> analyze table many_columns_table compute statistics;

Table analyzed.

SQL> select blocks, num_rows, chain_cnt from user_tables where table_name  ='MANY_COLUMNS_TABLE';

   BLOCKS   NUM_ROWS  CHAIN_CNT
---------- ---------- ----------
    11117      50640          0

SQL> 

I have run the same test on 16K blocks. I confirm that the Chain_Count is 0 so the rows I fetch are not chained across blocks.