Full table scan from MTL_SYSTEM_ITEMS_B is extremely slow
I am facing performance issues with many queries that involve full table scan of mtl_system_items_b in our R12 ERP instance.
The table has only 4 million rows, but yes 401 columns (which means it will have row migration and fragmented rows as well)
But 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):