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
.
2 comments:
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
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
Post a Comment