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.
.
.
.
4 comments:
Hemant,
I did the similar test during 9i days. Not sure if the things have changed since then as far as Row structure is concerns.
The way Oracle construct the rows with > 255 columns as follows ( based on block dump analysis I did back the then)
row piece 1 - 1st set of 254 columns
row piece 2 - 2nd set of 254 columns
and so on..
and each row piece read adds to the logical read.
Now does this cause performance impact?
Answer is yes and no,
No, if all the columns contains the data.
Yes, if empty columns are sandwitched between non-empty columns.
Back then, I pushed out all the empty columns (column did not have any data ) towards the end and table shrinked significantly and performance improved.
HTH.
Thanks for the information.
Unfortunately, I do not have the luxury to reorder the table in an existing "datawarehouse".
But that is useful information to keep in mind.
Hemant
Followup to the above discussion.
Could your please elaborate more about row piece.
1) 254 columsn per row peice means first 1..254 which list when i describe table?
2) If any column i am accessing fall in second row piece then, will it be single block read(DB FILE SEQUENTIAL READ)?
3) But i have observed even 119 column also leads to single block read even though table is accessing full table scan..
could you please clarify my above doubts..
Jaya Sankar,
1. Yes, if the table has more than 254 columns, a row consists of two row pieces. To count the number of columns, use a DESCRIBE or count in DBA_TAB_COLUMNS.
2. There will be an additional block get (a consistent get) when reading the second piece. You will NOT see it as a 'db file sequential read' wait because the additional read is from the buffere cache and not a physical I/O call (which the wait event is).
3.When doing a Full Table scan on a normal table (119 columns), Oracle may find some of the blocks present in the buffer cache. It may break up a multiblock read call into a smaller multiblock read plus a single block read from the buffer cache. Moreover, it will be reading the segment header as a single block read (possibly a 'db file sequential read' if it is not in the buffer cache) at least once but possibly more than once when there are multi level bitmaps in the segment header.
Post a Comment