Re: SQL run time changed after DB upgrade but execution plan did not change

-- 
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>

On 04/26/2013 10:24 PM, Hameed, Amir wrote:


> We recently upgraded one of our ERP databases from 11.1.0.7 to 11.2.0.3 on Solaris 10. The following SQL statement, which used to run in a few minutes, is now taking forever to run and is not even finishing (I have fudged the table names and data values for privacy reasons):

> DECLARE

> CURSOR s_cur IS

> SELECT * FROM T1 ;

>

> TYPE fetch_array IS TABLE OF s_cur%ROWTYPE;

> s_array fetch_array;

> BEGIN

>

>    OPEN s_cur;

>    LOOP

>      FETCH s_cur BULK COLLECT INTO s_array ; --LIMIT 100

>

>      FORALL I IN 1..S_ARRAY.COUNT

>     INSERT

>          INTO T2

>        SELECT 999,

>               S_ARRAY(I).SUBINVENTORY_CODE,

>               s_array(I).INVENTORY_ITEM_ID,

>               (select max(TRANSACTION_DATE)

>                  from MTL_MATERIAL_TRANSACTIONS MMT

>                 WHERE MMT.TRANSACTION_TYPE_ID IN (11, 22, 33, 44, 55)

>                   AND ORGANIZATION_ID    = 123

>                   AND INVENTORY_ITEM_ID  = S_ARRAY(I).INVENTORY_ITEM_ID

>                   and SUBINVENTORY_CODE  = s_array(I).SUBINVENTORY_CODE)

>          FROM DUAL;

>      COMMIT;

>      EXIT WHEN s_cur%NOTFOUND;

>    END LOOP;

>    CLOSE s_cur;

>    COMMIT;

> END;

> /

>




--
http://www.freelists.org/webpage/oracle-l