Search My Oracle Blog

Custom Search

18 October, 2009

Some Testing on Intra-Block Row Chaining

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.


.

UPDATE : For more discussions on this issue follow the Oracle-L thread
.
.
Second UPDATE : More tests at Some MORE Testing on Intra-Block Row Chaining.
.
.
.

2 comments:

olivier said...

Hi Hemant,
I know this is an old (but very good) post. however i do not manage to figure out why intra-block row chaining can result in one additional logical read. After all, even if intra-block chained, it still fits into a single block. So why one earth one addition logical read is needed ?
Thanks
Olivier

Hemant K Chitale said...

Olivier,
The chaining results in a pointer to an address. The address does, yes, happen to be in the same block but, nevertheless, Oracle has to do another logical read to access the address.

Hemant

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com