Since Exadata migration bad performances on MTL_SYSTEM_iTEMS_B
Hi,
EBS: 12.2.6
DB: 12.1.0.2
We have migrated our env. to Exadata/exalogic. Since then we have some performance issues which are all related to queries on table MTL_SYSTEM_ITEMS_B.
There is plenty of indexes on these table but basically the optimizer keeps avoiding using it to favor a table access storage full table.
For example in BMCDEL program, we have a query like:
explain plan for SELECT count(*) FROM MTL_SYSTEM_ITEMS WHERE BASE_ITEM_ID = 1331754 and organization_id =104;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1724281435
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 10772 (7)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 17 | | |