I am facing performance issues with many queries that involve full table scan of mtl_system_items_b in our R12.2.3 ERP instance using 11.2.0.4 version of oracle database.
The table has only 4 million rows, but yes 401 columns (which means it will have row migration and fragmented rows as well).
But ..I don't really see any chained rows.
01:13:25 SQL> Select table_name, chain_cnt, round(chain_cnt/num_rows*100, 2) pct_chained, avg_row_len, pct_free, pct_used
From dba_tables where table_name like 'MTL_SYSTEM_ITEMS_B';
TABLE_NAME CHAIN_CNT PCT_CHAINED AVG_ROW_LEN PCT_FREE PCT_USED
------------------------------ ---------- ----------- ----------- ---------- ----------
MTL_SYSTEM_ITEMS_B 0 0 600 10
Elapsed: 00:00:08.15
01:21:13 SQL> 01:21:13 SQL> analyze table inv.MTL_SYSTEM_ITEMS_B LIST CHAINED ROWS into chained_rows;
Table analyzed.
Elapsed: 00:05:22.72
01:27:25 SQL> Select table_name, chain_cnt, round(chain_cnt/num_rows*100, 2) pct_chained, avg_row_len, pct_free, pct_used
From dba_tables
where table_name like 'MTL_SYSTEM_ITEMS_B';
TABLE_NAME CHAIN_CNT PCT_CHAINED AVG_ROW_LEN PCT_FREE PCT_USED
------------------------------ ---------- ----------- ----------- ---------- ----------
MTL_SYSTEM_ITEMS_B 0 0 600 10
Elapsed: 00:00:06.70
Also even a simple query like the one below takes 52 mins on my production instance:
select count(1) from mtl_system_items_b where item_type = 'UX'
Plan hash value: 1724281435
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 165K(100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | TABLE ACCESS FULL| MTL_SYSTEM_ITEMS_B | 127K| 499K| 165K (2)| 00:33:06 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ITEM_TYPE"='UX')
The wait event all this while was 'db_file_sequential_read' . Please see the attached sql monitor report.
Questions : Why would there be db_file_sequential_read for Full table scan and not scattered read?
Could I do something to tune this ? The table would definitely be fragmented as it has 401 columns but still with just 4 million rows 52 mins just seems too high.
Thanks,
Aditi