PERFORMANCE BULK COLLECT

[sachrath]

All ,

I have been battling with this issue since yesterday night and it seems I dont understand about BULL COLLECT completely .

Attached below is a package within which a table function is created in Oracle9i .Within this Table function a dynamic SQL query gets executed the performance of which is excellent and the result set whe I do a count on the query shoots back in 30-50 secs , but I select count from the Table Function , it runs for half an hour , which I couldnt understand .

Any help is appreciated .

CREATE OR REPLACE PACKAGE BODY OPO_PKG_PROJECT_INTERFACE IS

FUNCTION OPO_F_CRISP_OUTBOUND ( p_business_date IN DATE,
p_src_sys_cd IN VARCHAR2,
p_NTPA IN VARCHAR2 DEFAULT NULL ) RETURN T_CRISP_TABLE
PIPELINED
IS

--------------------------------------------------------
TYPE ARRAY IS TABLE OF T_CRISP_Rec;
v_DATA ARRAY;
--------------------------------------------------------
TYPE REUSABLE_CURSOR IS REF CURSOR;
GLOBAL_CURSOR REUSABLE_CURSOR;
--------------------------------------------------------
v_business_date DATE;
v_src_sys_cd VARCHAR2(50);
v_NTPA VARCHAR2(10);
--------------------------------------------------------
out_rec T_CRISP_OBJECT := T_CRISP_OBJECT( null,
null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
null,null,null,
null,null
);


BEGIN

v_business_date:=p_business_date;
v_src_sys_cd:=p_src_sys_cd;
v_NTPA:=p_NTPA;

ELSE IF v_src_sys_cd='x' THEN
OPEN GLOBAL_CURSOR FOR
'
SELECT
/*+ ALL_ROWS */
NULL AS ACT_COST_AMT
FROM
OPD.OPD_PRODUCT_BALANCE T
WHERE
T.BUSINESS_DATE = TO_DATE('''|| v_business_date || ''')
AND DECODE(BITAND(2,T.ROW_ERROR_CD),2,2,DECODE(BITAND(1,T.ROW_ERROR_CD),1,1,0)) IN (0,1)
AND NVL(TRIM(T.DEAL_ID),''~ERR'') !=''~ERR''
-- AND T.SRC_SYS_ORIG_SYS_NAME=''SUMMIT''
AND T.SETLMT_STATUS_CD = ''O''
AND T.SRC_SYS_FEED_ID IN (237,238,280,282)
)';


LOOP

FETCH GLOBAL_CURSOR BULK COLLECT INTO v_Data LIMIT 750;

FOR i IN 1 .. v_Data.COUNT LOOP

out_rec.ACT_COST_AMT := v_Data(i).ACT_COST_AMT;
PIPE ROW(out_rec);
END LOOP;
EXIT WHEN GLOBAL_CURSOR%NOTFOUND;
END LOOP;
CLOSE GLOBAL_CURSOR;


END IF;
RETURN;
END OPO_F_CRISP_OUTBOUND;
END OPO_PKG_PROJECT_INTERFACE;
/


Thx .