Slowness/Application freeze in Shop orders material planning?

Slowness/Application freeze in Shop orders material planning?

Has anyone encountered situations where the Shop orders material planning can intermittently start acting up and causing the Application to lock up or freeze in a post Apps10 UPD12 environment?

It happened in one internal customer environment when using NA Project Explorer after quite a bit of trying. shop material alloc table had over 10M rows.

The statement which locks up is as follows:

SELECT NVL(SUM(NVL(QTY_DEMAND,0)), 0), NVL(SUM(NVL(QTY_RESERVED,0)), 0) FROM (SELECT TRUNC(DATE_REQUIRED) DATE_REQUIRED, NVL(QTY_DEMAND,0) QTY_DEMAND, NVL(QTY_RESERVED,0) QTY_RESERVED FROM ORDER_SUPPLY_DEMAND_EXT WHERE CONTRACT = :B7 AND PART_NO = :B6 AND (CONFIGURATION_ID = :B5 OR :B5 IS NULL) AND ((:B4 = 'TRUE' AND PROJECT_ID = '*') OR (:B3 = 'TRUE' AND PROJECT_ID != '*' AND (PROJECT_ID = :B2 OR :B2 IS NULL) AND (ACTIVITY_SEQ = :B1 OR :B1 IS NULL))) UNION ALL SELECT TRUNC(DATE_REQUIRED) DATE_REQUIRED, (QTY_REQUIRED-QTY_ISSUED)*(-1) QTY_DEMAND, QTY_ASSIGNED*(-1) QTY_RESERVED FROM SHOP_MATERIAL_ALLOC_TAB WHERE ORDER_NO = :B11 AND RELEASE_NO = :B10 AND SEQUENCE_NO = :B9 AND LINE_ITEM_NO = :B8 AND (CONFIGURATION_ID = :B5 OR :B5 IS NULL) AND ((:B4 = 'TRUE' AND NVL(DECODE(SUPPLY_CODE, 'IO', '*', PROJECT_ID), '*') = '*') OR (:B3 = 'TRUE' AND NVL(DECODE(SUPPLY_CODE, 'IO', '*', PROJECT_ID), '*') != '*' AND (NVL(DECODE(SUPPLY_CODE, 'IO', '*', PROJECT_ID), '*') = :B2 OR :B2 IS NULL) AND (DECODE(SUPPLY_CODE, 'IO', TO_NUMBER(NULL), ACTIVITY_SEQ) = :B1 OR :B1 IS NULL))) AND ROWSTATE IN ('Planned','Released', 'Reserved', 'Issued', 'Parked') AND QTY_REQUIRED - QTY_ISSUED > 0 AND PART_OWNERSHIP = 'COMPANY OWNED' AND INCLUDE_AS_DEMAND = 'TRUE' UNION ALL SELECT TRUNC(:B12 ) DATE_REQUIRED, (QTY_REQUIRED-QTY_ISSUED) QTY_DEMAND, QTY_ASSIGNED QTY_RESERVED FROM SHOP_MATERIAL_ALLOC_TAB WHERE ORDER_NO = :B11 AND RELEASE_NO = :B10 AND SEQUENCE_NO = :B9 AND LINE_ITEM_NO = :B8 AND ROWSTATE IN ('Planned','Released', 'Reserved', 'Issued', 'Parked') AND QTY_REQUIRED - QTY_ISSUED > 0 AND PART_OWNERSHIP = 'COMPANY OWNED' AND INCLUDE_AS_DEMAND = 'TRUE') WHERE TRUNC(DATE_REQUIRED) <= NVL(:B12 , :B13 )