Re: SQL run time changed after DB upgrade but execution plan did not change
- From: Stéphane Faroult <sfaroult@xxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 26 Apr 2013 22:39:56 +0200
Looks like having been coded by the lowest bidder.
--
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;
> /
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
