26 October, 2009

Some MORE Testing on Intra-Block Row Chaining

(note : See update on 31-Oct for a single-rows test on a 1,000 column table)

Following my first round of tests on Intra-Block Row Chaining (where a table has more than 255 columns), here are some results that are more suprising :


SQL> set autotrace on
SQL> select count(*) from many_columns_table;

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


Execution Plan
----------------------------------------------------------
Plan hash value: 3712319054

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3019 (1)| 00:00:37 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| MANY_COLUMNS_TABLE | 50640 | 3019 (1)| 00:00:37 |
---------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
10370 consistent gets
0 physical reads
0 redo size
517 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 count(col_vc1) from many_columns_table;

COUNT(COL_VC1)
--------------
50640


Execution Plan
----------------------------------------------------------
Plan hash value: 3712319054

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3019 (1)| 00:00:37 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | TABLE ACCESS FULL| MANY_COLUMNS_TABLE | 50640 | 148K| 3019 (1)| 00:00:37 |
-----------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10370 consistent gets
0 physical reads
0 redo size
523 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>


So a COUNT(col_vc1) is comparable to a COUNT(*).

Next we see :


SQL> select count(col_vc93) from many_columns_table;

COUNT(COL_VC93)
---------------
50640


Execution Plan
----------------------------------------------------------
Plan hash value: 3712319054

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3025 (1)| 00:00:37 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | TABLE ACCESS FULL| MANY_COLUMNS_TABLE | 50640 | 148K| 3025 (1)| 00:00:37 |
-----------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10370 consistent gets
0 physical reads
0 redo size
524 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 count(col_vc94) from many_columns_table;

COUNT(COL_VC94)
---------------
50640


Execution Plan
----------------------------------------------------------
Plan hash value: 3712319054

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3025 (1)| 00:00:37 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | TABLE ACCESS FULL| MANY_COLUMNS_TABLE | 50640 | 148K| 3025 (1)| 00:00:37 |
-----------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
61010 consistent gets
0 physical reads
0 redo size
524 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>


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).


SQL> select count(distinct(col_vc1)) from many_columns_table;

COUNT(DISTINCT(COL_VC1))
------------------------
30892


Execution Plan
----------------------------------------------------------
Plan hash value: 1283832531

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3019 (1)| 00:00:37 |
| 1 | SORT GROUP BY | | 1 | 3 | | |
| 2 | TABLE ACCESS FULL| MANY_COLUMNS_TABLE | 50640 | 148K| 3019 (1)| 00:00:37 |
-----------------------------------------------------------------------------------------


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

SQL> select count(distinct(col_vc93)) from many_columns_table;

COUNT(DISTINCT(COL_VC93))
-------------------------
30965


Execution Plan
----------------------------------------------------------
Plan hash value: 1283832531

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3025 (1)| 00:00:37 |
| 1 | SORT GROUP BY | | 1 | 3 | | |
| 2 | TABLE ACCESS FULL| MANY_COLUMNS_TABLE | 50640 | 148K| 3025 (1)| 00:00:37 |
-----------------------------------------------------------------------------------------


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

SQL> select count(distinct(col_vc94)) from many_columns_table;

COUNT(DISTINCT(COL_VC94))
-------------------------
31029


Execution Plan
----------------------------------------------------------
Plan hash value: 1283832531

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3025 (1)| 00:00:37 |
| 1 | SORT GROUP BY | | 1 | 3 | | |
| 2 | TABLE ACCESS FULL| MANY_COLUMNS_TABLE | 50640 | 148K| 3025 (1)| 00:00:37 |
-----------------------------------------------------------------------------------------


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

SQL> select count(distinct(col_vc348)) from many_columns_table;

COUNT(DISTINCT(COL_VC348))
--------------------------
30955


Execution Plan
----------------------------------------------------------
Plan hash value: 1283832531

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3042 (1)| 00:00:37 |
| 1 | SORT GROUP BY | | 1 | 3 | | |
| 2 | TABLE ACCESS FULL| MANY_COLUMNS_TABLE | 50640 | 148K| 3042 (1)| 00:00:37 |
-----------------------------------------------------------------------------------------


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

SQL>



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-09
Here are some results with a 1,000 column table (in a 16KB tablespace). The table has been created with 1 single row :


SQL> analyze table many_columns_table compute statistics;

Table analyzed.

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

BLOCKS NUM_ROWS AVG_ROW_LEN CHAIN_CNT
---------- ---------- ----------- ----------
5 1 4013 0

SQL> analyze table many_columns_table delete statistics;

Table analyzed.

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 from user_tables where table_name = 'MANY_COLUMNS_TABLE';

BLOCKS NUM_ROWS AVG_ROW_LEN
---------- ---------- -----------
5 1 4004

SQL>

SQL> create index many_cols_ndx_1 on many_columns_table(col_number) tablespace test_16k;

Index created.

SQL>
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
----- ----- ----- -----
697 8QY RTE OOF


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

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MANY_COLUMNS_TABLE | 1 | 20 | 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
----------------------------------------------------------
0 recursive calls
0 db block gets
2 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>
SQL> select col_vc1, col_vc3,col_vc5, col_vc235 from many_columns_table where col_number = 1050;

COL_V COL_V COL_V COL_V
----- ----- ----- -----
697 8QY RTE NSP


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

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MANY_COLUMNS_TABLE | 1 | 20 | 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
----------------------------------------------------------
0 recursive calls
0 db block gets
3 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>
SQL> select col_vc1, col_vc3,col_vc5, col_vc489 from many_columns_table where col_number = 1050;

COL_V COL_V COL_V COL_V
----- ----- ----- -----
697 8QY RTE U85


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

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MANY_COLUMNS_TABLE | 1 | 20 | 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
----------------------------------------------------------
0 recursive calls
0 db block gets
4 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>
SQL> select col_vc1, col_vc3,col_vc5, col_vc744 from many_columns_table where col_number = 1050;

COL_V COL_V COL_V COL_V
----- ----- ----- -----
697 8QY RTE Z7R


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

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MANY_COLUMNS_TABLE | 1 | 20 | 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
----------------------------------------------------------
0 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>
SQL> select col_vc1, col_vc3,col_vc5, col_vc998 from many_columns_table where col_number = 1050;

COL_V COL_V COL_V COL_V
----- ----- ----- -----
697 8QY RTE JN8


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

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MANY_COLUMNS_TABLE | 1 | 20 | 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
----------------------------------------------------------
0 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>


I had as many as 3 additional 'consistent gets' !

For the same row, depending on which column I select, I see 1 or 2 or 3 or 4 consistent gets from the table.

.
.
.

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.
.
.
.