Search My Oracle Blog

Custom Search

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

Aggregated by orafaq.com

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

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016