RE: (Fwd) Re: DBD::Oracle Slow cursors
John, I tried a simple SQL with 100 records in the table and refcursor. The SQL response if I fire it in SQL developer or TOAD is 0.09 seconds But If I fire the same thing in Java program below is the analysis. You can clearly see that the when starting the loop for the cursor which is a sys-refcursor, its going back to the DB to fetch the rows again. Getting the connection 10:23:42:593 : After Registering Driver 10:23:42:593 : Getting Connection 10:23:43:000 : After Getting Connection Calling the SP via java program 10:23:43:000 : Calling SP 10:23:43:109 : After Calling SP 10:23:43:109 : Calling executeUpdate ... 10:23:43:125 : After Calling executeUpdate ... After Calling the SP and getting the result set from the cursor 10:23:43:125 : Calling rs Set ... 10:23:43:125 : After Calling rs Set ... 10:23:43:125 : Calling While Loop ... 10:23:43:140 : Inside While Loop ... 10:23:43:140 : DashBoard : United Kingdom 10:23:43:140 : After Calling While ... END _ From: Carlson, John W. [mailto:carlso...@llnl.gov] Sent: 18 May 2011 17:46 To: P S Jameel Ahamed; 'John Scoles'; 'Martin Evans'; dbi-users@perl.org; 'Tim Bunce' Subject: RE: (Fwd) Re: DBD::Oracle Slow cursors Try doing a very simple refcursor and see if you still have the same performance issues. John From: P S Jameel Ahamed [mailto:jaha...@idexcel.com] Sent: Wednesday, May 18, 2011 5:04 AM To: 'John Scoles'; 'Martin Evans'; dbi-users@perl.org; Carlson, John W.; 'Tim Bunce' Subject: RE: (Fwd) Re: DBD::Oracle Slow cursors John, Irrespective of I calling in Java or executing it on the SQL developer or in TOAD, I am getting the same response time when compared to executing the SQL's separately Regards P S Jameel Ahamed _ From: John Scoles [mailto:byter...@hotmail.com] Sent: 18 May 2011 12:21 To: jaha...@idexcel.com; Martin Evans; dbi-users@perl.org; carlso...@llnl.gov; Tim Bunce Subject: RE: (Fwd) Re: DBD::Oracle Slow cursors Does this have anything to do at all with DBD::Oracle??? You mentioned you are calling this with JAVA?? Where is the Perl code?? From: jaha...@idexcel.com To: martin.ev...@easysoft.com; dbi-users@perl.org; byter...@hotmail.com; carlso...@llnl.gov; tim.bu...@pobox.com Subject: RE: (Fwd) Re: DBD::Oracle Slow cursors Date: Wed, 18 May 2011 11:07:56 +0100 Hi All, First of all I thank you all in replying to my email. Below are details of one of the SP which we are having issues with. In the below SP we are passing in a Username and then for that user are fetching his counts and other information. The output parameter is a sys_refcursur. The problem is that when I fire each and every insert and select statement, Its firing in 0.2 seconds. But when retrieving these details in the java code when it come to the line to loop the cursor then it gets struck for about 2 to 3 mins and the response is coming back. We have seen this behavior in most of the places where we used this sys_refcursur. The table circuit is a huge table of million records and it has all the indexces in place and other tables as well. I have been googleing for a long time for a solution for this sys-refcursor issue and I am not sure why its going back to the DB after executing the SP while looping through the cursor. We have Oracle 10G 10.1.0.4. I have also tried to combain all the select statements into one using the UNION command and insert into the tbl_qm_dashboard. The insert fires in 1.12 seconds, but again the cursuor looping is the issue. Note : tbl_qm_dashboard is a temp table. Hope I will get an break through with all your expertise. PROCEDURE sp_select_dashboard(strUser IN VARCHAR2, rcSelectDashboard OUT SYS_REFCURSOR) IS BEGIN EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_COMP=LINGUISTIC'; EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT=BINARY_CI'; INSERT INTO tbl_qm_dashboard SELECT dashboard_type,NVL(active_cnt,0),NVL((age/DECODE(active_cnt,0,1,active_cnt)) ,0)avg_age,NVL(active_old_5days,0),NVL(closed_old_5days,0),NVL((response_old _5days/DECODE(closed_old_5days,0,1,closed_old_5days)),0) avg_response_old5days FROM ( SELECT 'AccessCircuit' dashboard_type, SUM(case when (A.ot_queue_status_id IN(1,2) AND ((C.status_id 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3))) then 1 else 0 END ) active_cnt, SUM(case when (A.ot_queue_status_id IN(1,2) AND ((C.status_id 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3))) then (SYSDATE - c.created_on) else 0 END ) age, SUM(case when (c.created_on = (sysdate - 5) AND A.ot_queue_status_id IN(1,2) AND ((C.status_id 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3))) then 1 else 0 END )active_old_5days, SUM(case when (A.LAST_UPDATED_ON = (sysdate - 5) AND A.ot_queue_status_id = 3 ) then 1 else 0 END )closed_old_5days, SUM(case when (A.LAST_UPDATED_ON = (sysdate - 5) AND A.ot_queue_status_id = 3 ) then (A.LAST_UPDATED_ON
Re: (Fwd) Re: DBD::Oracle Slow cursors
On 19/05/11 10:28, P S Jameel Ahamed wrote: I tried a simple SQL with 100 records in the table and refcursor. The SQL response if I fire it in SQL developer or TOAD is 0.09 seconds But If I fire the same thing in Java program below is the analysis. You can clearly see that the when starting the loop for the cursor which is a sys-refcursor, its going back to the DB to fetch the rows again. This mail list is intended for discussion of issues with the Perl DBI module and its associated drivers, such as DBD::Oracle. This message is completely off topic. Will you please stop spamming this list. -- Charles Jardine - Computing Service, University of Cambridge c...@cam.ac.ukTel: +44 1223 334506, Fax: +44 1223 334679
RE: (Fwd) Re: DBD::Oracle Slow cursors
-- AND (E.key_bid = 'N' OR (E.key_bid = 'Y' AND C.release_to_region_on IS NOT NULL)) AND EXISTS (SELECT d.circuit_id FROM tbl_rfq d WHERE d.circuit_id = c.circuit_id AND d.carrier_status_id IN(1,2)) AND EXISTS (SELECT M.country_code FROM tbl_user_region_country M, tbl_user N WHERE N.login_id = struser AND M.user_id = N.user_id AND M.active = 'Y' AND M.country_code = C.country_code) ); INSERT INTO tbl_qm_dashboard SELECT dashboard_type,NVL(active_cnt,0),NVL((age/DECODE(active_cnt,0,1,active_cnt)) ,0)avg_age,NVL(active_old_5days,0),NVL(closed_old_5days,0),NVL((response_old _5days/DECODE(closed_old_5days,0,1,closed_old_5days)),0) avg_response_old5days FROM ( SELECT /*+ INDEX(C IDX1_TBL_CIRCUIT ) */ 'TeamCircuit' dashboard_type, SUM(case when (A.ot_queue_status_id IN(1,2) AND ((C.status_id 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3))) then 1 else 0 END ) active_cnt, SUM(case when (A.ot_queue_status_id IN(1,2) AND ((C.status_id 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3))) then (SYSDATE - c.created_on) else 0 END ) age, SUM(case when (c.created_on = (sysdate - 5) AND A.ot_queue_status_id IN(1,2) AND ((C.status_id 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3))) then 1 else 0 END )active_old_5days, SUM(case when (A.LAST_UPDATED_ON = (sysdate - 5) AND A.ot_queue_status_id = 3 ) then 1 else 0 END )closed_old_5days, SUM(case when (A.LAST_UPDATED_ON = (sysdate - 5) AND A.ot_queue_status_id = 3 ) then (A.LAST_UPDATED_ON - c.created_on) else 0 END ) response_old_5days FROM tbl_ot_queue A, tbl_circuit C WHERE A.circuit_id = C.circuit_id AND A.circuit_current_stage = C.Circuit_Current_Stage AND EXISTS (SELECT M.country_code FROM tbl_user_region_country M, tbl_user N WHERE N.login_id = struser AND M.user_id = N.user_id AND M.active = 'Y' AND M.country_code = C.country_code) ); INSERT INTO tbl_qm_dashboard SELECT dashboard_type,NVL(active_cnt,0),NVL((age/DECODE(active_cnt,0,1,active_cnt)) ,0)avg_age,NVL(active_old_5days,0),NVL(closed_old_5days,0),NVL((response_old _5days/DECODE(closed_old_5days,0,1,closed_old_5days)),0) avg_response_old5days FROM ( SELECT /*+ INDEX(C IDX1_TBL_CIRCUIT ) */ 'TeamUnassigned' dashboard_type, SUM(case when (A.ot_queue_status_id IN(1,2) AND ((C.status_id 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3))) then 1 else 0 END ) active_cnt, SUM(case when ( A.ot_queue_status_id IN(1,2) AND ((C.status_id 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3))) then (SYSDATE - c.created_on) else 0 END ) age, SUM(case when (c.created_on = (sysdate - 5) AND A.ot_queue_status_id IN(1,2) AND ((C.status_id 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3))) then 1 else 0 END )active_old_5days, SUM(case when (A.LAST_UPDATED_ON = (sysdate - 5) AND A.ot_queue_status_id = 3 ) then 1 else 0 END )closed_old_5days, SUM(case when (A.LAST_UPDATED_ON = (sysdate - 5) AND A.ot_queue_status_id = 3 ) then (A.LAST_UPDATED_ON - c.created_on) else 0 END ) response_old_5days FROM tbl_ot_queue A, tbl_circuit C WHERE A.circuit_id = C.circuit_id AND A.circuit_current_stage = C.Circuit_Current_Stage AND A.assigned_to IS NULL AND EXISTS (SELECT M.country_code FROM tbl_user_region_country M, tbl_user N WHERE N.login_id = struser AND M.user_id = N.user_id AND M.active = 'Y' AND M.country_code = C.country_code) ); OPEN rcSelectDashBoard FOR SELECT dashboard_type as type, rec_count as count, average_age, count_above_5days, count_closed_5days, avg_response_time FROM tbl_qm_dashboard; END sp_select_dashboard; Thanks P S Jameel Ahamed -Original Message- From: Martin J. Evans [mailto:martin.ev...@easysoft.com] Sent: 17 May 2011 23:00 To: jaha...@idexcel.com; dbi-users@perl.org Subject: Re: (Fwd) Re: DBD::Oracle Slow cursors On 17/05/2011 22:18, Tim Bunce wrote: - Forwarded message from P S Jameel Ahamedjaha...@idexcel.com - Date: Tue, 17 May 2011 16:11:04 +0100 From: P S Jameel Ahamedjaha...@idexcel.com To: tim.bu...@pobox.com Subject: Re: DBD::Oracle Slow cursors X-Mailer: Microsoft Office Outlook 11 HI Tim, We are facing huge issues with the SYS_refcurors of oracle 10G when returning from Stored procedure. Is there any solution you
RE: (Fwd) Re: DBD::Oracle Slow cursors
Does this have anything to do at all with DBD::Oracle??? You mentioned you are calling this with JAVA?? Where is the Perl code?? From: jaha...@idexcel.com To: martin.ev...@easysoft.com; dbi-users@perl.org; byter...@hotmail.com; carlso...@llnl.gov; tim.bu...@pobox.com Subject: RE: (Fwd) Re: DBD::Oracle Slow cursors Date: Wed, 18 May 2011 11:07:56 +0100 Hi All, First of all I thank you all in replying to my email. Below are details of one of the SP which we are having issues with. In the below SP we are passing in a Username and then for that user are fetching his counts and other information. The output parameter is a sys_refcursur. The problem is that when I fire each and every insert and select statement, Its firing in 0.2 seconds. But when retrieving these details in the java code when it come to the line to loop the cursor then it gets struck for about 2 to 3 mins and the response is coming back. We have seen this behavior in most of the places where we used this sys_refcursur. The table circuit is a huge table of million records and it has all the indexces in place and other tables as well. I have been googleing for a long time for a solution for this sys-refcursor issue and I am not sure why its going back to the DB after executing the SP while looping through the cursor. We have Oracle 10G 10.1.0.4. I have also tried to combain all the select statements into one using the UNION command and insert into the tbl_qm_dashboard. The insert fires in 1.12 seconds, but again the cursuor looping is the issue. Note : tbl_qm_dashboard is a temp table. Hope I will get an break through with all your expertise. PROCEDURE sp_select_dashboard(strUser IN VARCHAR2, rcSelectDashboard OUT SYS_REFCURSOR) IS BEGIN EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_COMP=LINGUISTIC'; EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT=BINARY_CI'; INSERT INTO tbl_qm_dashboard SELECT dashboard_type,NVL(active_cnt,0),NVL((age/DECODE(active_cnt,0,1,active_cnt)) ,0)avg_age,NVL(active_old_5days,0),NVL(closed_old_5days,0),NVL((response_old _5days/DECODE(closed_old_5days,0,1,closed_old_5days)),0) avg_response_old5days FROM ( SELECT 'AccessCircuit' dashboard_type, SUM(case when (A.ot_queue_status_id IN(1,2) AND ((C.status_id 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3))) then 1 else 0 END ) active_cnt, SUM(case when (A.ot_queue_status_id IN(1,2) AND ((C.status_id 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3))) then (SYSDATE - c.created_on) else 0 END ) age, SUM(case when (c.created_on = (sysdate - 5) AND A.ot_queue_status_id IN(1,2) AND ((C.status_id 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3))) then 1 else 0 END )active_old_5days, SUM(case when (A.LAST_UPDATED_ON = (sysdate - 5) AND A.ot_queue_status_id = 3 ) then 1 else 0 END )closed_old_5days, SUM(case when (A.LAST_UPDATED_ON = (sysdate - 5) AND A.ot_queue_status_id = 3 ) then (A.LAST_UPDATED_ON - c.created_on) else 0 END ) response_old_5days FROM tbl_ot_queue A, tbl_circuit C, tbl_opportunity D WHERE A.assigned_to = struser AND C.opportunity_id = D.opportunity_id -- AND (D.key_bid = 'N' OR (D.key_bid = 'Y' AND C.release_to_region_on IS NOT NULL)) AND A.circuit_id = C.circuit_id AND A.circuit_current_stage = C.Circuit_Current_Stage AND EXISTS (SELECT M.country_code FROM tbl_user_region_country M, tbl_user N WHERE N.login_id = struser AND M.user_id = N.user_id AND M.active = 'Y' AND M.country_code = C.country_code) ); INSERT INTO tbl_qm_dashboard SELECT dashboard_type,NVL(active_cnt,0),NVL((age/DECODE(active_cnt,0,1,active_cnt)) ,0) avg_age,NVL(active_old_5days,0),NVL(closed_old_5days,0),NVL((response_old_5d ays/DECODE(closed_old_5days,0,1,closed_old_5days)),0) avg_response_old5days FROM ( SELECT 'AccessRFQSentSupplier' dashboard_type, SUM(case when (A.ot_queue_status_id IN(1,2) AND ((C.status_id 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3))) then 1 else 0 END ) active_cnt, SUM(case when (A.ot_queue_status_id IN(1,2) AND ((C.status_id 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3))) then (SYSDATE - c.created_on) else 0 END ) age, SUM(case when (c.created_on = (sysdate - 5) AND A.ot_queue_status_id IN(1,2) AND ((C.status_id 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3))) then 1 else 0 END )active_old_5days, SUM(case when (A.LAST_UPDATED_ON = (sysdate - 5) AND A.ot_queue_status_id = 3 ) then 1 else 0 END )closed_old_5days, SUM(case when (A.LAST_UPDATED_ON = (sysdate - 5) AND A.ot_queue_status_id = 3 ) then (A.LAST_UPDATED_ON - c.created_on) else 0 END ) response_old_5days FROM tbl_ot_queue A, tbl_circuit C, tbl_opportunity E WHERE A.assigned_to = struser AND A.circuit_id = C.circuit_id AND A.circuit_current_stage = C.Circuit_Current_Stage AND C.opportunity_id = E.opportunity_id -- AND (E.key_bid = 'N' OR (E.key_bid = 'Y' AND C.release_to_region_on IS NOT NULL)) AND EXISTS (SELECT
RE: (Fwd) Re: DBD::Oracle Slow cursors
Why are you asking for help on a Perl Mailing list then??? There is nothing we can do for you here it is PLSQL problem. I have some expreriance in PLSQL and all I can know is that sys_refcursors are allways going to be slower than direct calls with SQLPlus. Not sure how we can help you here. Perhaps you should rethink the refcursor idea or try spliting it up into two or more calls Cheers From: jaha...@idexcel.com To: byter...@hotmail.com; martin.ev...@easysoft.com; dbi-users@perl.org; carlso...@llnl.gov; tim.bu...@pobox.com Subject: RE: (Fwd) Re: DBD::Oracle Slow cursors Date: Wed, 18 May 2011 13:03:34 +0100 John, Irrespective of I calling in Java or executing it on the SQL developer or in TOAD, I am getting the same response time when compared to executing the SQL’s separately Regards P S Jameel Ahamed From: John Scoles [mailto:byter...@hotmail.com] Sent: 18 May 2011 12:21 To: jaha...@idexcel.com; Martin Evans; dbi-users@perl.org; carlso...@llnl.gov; Tim Bunce Subject: RE: (Fwd) Re: DBD::Oracle Slow cursors Does this have anything to do at all with DBD::Oracle??? You mentioned you are calling this with JAVA?? Where is the Perl code?? From: jaha...@idexcel.com To: martin.ev...@easysoft.com; dbi-users@perl.org; byter...@hotmail.com; carlso...@llnl.gov; tim.bu...@pobox.com Subject: RE: (Fwd) Re: DBD::Oracle Slow cursors Date: Wed, 18 May 2011 11:07:56 +0100 Hi All, First of all I thank you all in replying to my email. Below are details of one of the SP which we are having issues with. In the below SP we are passing in a Username and then for that user are fetching his counts and other information. The output parameter is a sys_refcursur. The problem is that when I fire each and every insert and select statement, Its firing in 0.2 seconds. But when retrieving these details in the java code when it come to the line to loop the cursor then it gets struck for about 2 to 3 mins and the response is coming back. We have seen this behavior in most of the places where we used this sys_refcursur. The table circuit is a huge table of million records and it has all the indexces in place and other tables as well. I have been googleing for a long time for a solution for this sys-refcursor issue and I am not sure why its going back to the DB after executing the SP while looping through the cursor. We have Oracle 10G 10.1.0.4. I have also tried to combain all the select statements into one using the UNION command and insert into the tbl_qm_dashboard. The insert fires in 1.12 seconds, but again the cursuor looping is the issue. Note : tbl_qm_dashboard is a temp table. Hope I will get an break through with all your expertise. PROCEDURE sp_select_dashboard(strUser IN VARCHAR2, rcSelectDashboard OUT SYS_REFCURSOR) IS BEGIN EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_COMP=LINGUISTIC'; EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT=BINARY_CI'; INSERT INTO tbl_qm_dashboard SELECT dashboard_type,NVL(active_cnt,0),NVL((age/DECODE(active_cnt,0,1,active_cnt)) ,0)avg_age,NVL(active_old_5days,0),NVL(closed_old_5days,0),NVL((response_old _5days/DECODE(closed_old_5days,0,1,closed_old_5days)),0) avg_response_old5days FROM ( SELECT 'AccessCircuit' dashboard_type, SUM(case when (A.ot_queue_status_id IN(1,2) AND ((C.status_id 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3))) then 1 else 0 END ) active_cnt, SUM(case when (A.ot_queue_status_id IN(1,2) AND ((C.status_id 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3))) then (SYSDATE - c.created_on) else 0 END ) age, SUM(case when (c.created_on = (sysdate - 5) AND A.ot_queue_status_id IN(1,2) AND ((C.status_id 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3))) then 1 else 0 END )active_old_5days, SUM(case when (A.LAST_UPDATED_ON = (sysdate - 5) AND A.ot_queue_status_id = 3 ) then 1 else 0 END )closed_old_5days, SUM(case when (A.LAST_UPDATED_ON = (sysdate - 5) AND A.ot_queue_status_id = 3 ) then (A.LAST_UPDATED_ON - c.created_on) else 0 END ) response_old_5days FROM tbl_ot_queue A, tbl_circuit C, tbl_opportunity D WHERE A.assigned_to = struser AND C.opportunity_id = D.opportunity_id -- AND (D.key_bid = 'N' OR (D.key_bid = 'Y' AND C.release_to_region_on IS NOT NULL)) AND A.circuit_id = C.circuit_id AND A.circuit_current_stage = C.Circuit_Current_Stage AND EXISTS (SELECT M.country_code FROM tbl_user_region_country M, tbl_user N WHERE N.login_id = struser AND M.user_id = N.user_id AND M.active = 'Y' AND M.country_code = C.country_code) ); INSERT INTO tbl_qm_dashboard SELECT dashboard_type,NVL(active_cnt,0),NVL((age/DECODE(active_cnt,0,1,active_cnt)) ,0) avg_age,NVL(active_old_5days,0),NVL(closed_old_5days,0),NVL((response_old_5d ays/DECODE(closed_old_5days,0,1,closed_old_5days)),0) avg_response_old5days FROM ( SELECT 'AccessRFQSentSupplier' dashboard_type, SUM(case when (A.ot_queue_status_id IN(1,2
RE: (Fwd) Re: DBD::Oracle Slow cursors
John, Irrespective of I calling in Java or executing it on the SQL developer or in TOAD, I am getting the same response time when compared to executing the SQL's separately Regards P S Jameel Ahamed _ From: John Scoles [mailto:byter...@hotmail.com] Sent: 18 May 2011 12:21 To: jaha...@idexcel.com; Martin Evans; dbi-users@perl.org; carlso...@llnl.gov; Tim Bunce Subject: RE: (Fwd) Re: DBD::Oracle Slow cursors Does this have anything to do at all with DBD::Oracle??? You mentioned you are calling this with JAVA?? Where is the Perl code?? From: jaha...@idexcel.com To: martin.ev...@easysoft.com; dbi-users@perl.org; byter...@hotmail.com; carlso...@llnl.gov; tim.bu...@pobox.com Subject: RE: (Fwd) Re: DBD::Oracle Slow cursors Date: Wed, 18 May 2011 11:07:56 +0100 Hi All, First of all I thank you all in replying to my email. Below are details of one of the SP which we are having issues with. In the below SP we are passing in a Username and then for that user are fetching his counts and other information. The output parameter is a sys_refcursur. The problem is that when I fire each and every insert and select statement, Its firing in 0.2 seconds. But when retrieving these details in the java code when it come to the line to loop the cursor then it gets struck for about 2 to 3 mins and the response is coming back. We have seen this behavior in most of the places where we used this sys_refcursur. The table circuit is a huge table of million records and it has all the indexces in place and other tables as well. I have been googleing for a long time for a solution for this sys-refcursor issue and I am not sure why its going back to the DB after executing the SP while looping through the cursor. We have Oracle 10G 10.1.0.4. I have also tried to combain all the select statements into one using the UNION command and insert into the tbl_qm_dashboard. The insert fires in 1.12 seconds, but again the cursuor looping is the issue. Note : tbl_qm_dashboard is a temp table. Hope I will get an break through with all your expertise. PROCEDURE sp_select_dashboard(strUser IN VARCHAR2, rcSelectDashboard OUT SYS_REFCURSOR) IS BEGIN EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_COMP=LINGUISTIC'; EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT=BINARY_CI'; INSERT INTO tbl_qm_dashboard SELECT dashboard_type,NVL(active_cnt,0),NVL((age/DECODE(active_cnt,0,1,active_cnt)) ,0)avg_age,NVL(active_old_5days,0),NVL(closed_old_5days,0),NVL((response_old _5days/DECODE(closed_old_5days,0,1,closed_old_5days)),0) avg_response_old5days FROM ( SELECT 'AccessCircuit' dashboard_type, SUM(case when (A.ot_queue_status_id IN(1,2) AND ((C.status_id 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3))) then 1 else 0 END ) active_cnt, SUM(case when (A.ot_queue_status_id IN(1,2) AND ((C.status_id 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3))) then (SYSDATE - c.created_on) else 0 END ) age, SUM(case when (c.created_on = (sysdate - 5) AND A.ot_queue_status_id IN(1,2) AND ((C.status_id 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3))) then 1 else 0 END )active_old_5days, SUM(case when (A.LAST_UPDATED_ON = (sysdate - 5) AND A.ot_queue_status_id = 3 ) then 1 else 0 END )closed_old_5days, SUM(case when (A.LAST_UPDATED_ON = (sysdate - 5) AND A.ot_queue_status_id = 3 ) then (A.LAST_UPDATED_ON - c.created_on) else 0 END ) response_old_5days FROM tbl_ot_queue A, tbl_circuit C, tbl_opportunity D WHERE A.assigned_to = struser AND C.opportunity_id = D.opportunity_id -- AND (D.key_bid = 'N' OR (D.key_bid = 'Y' AND C.release_to_region_on IS NOT NULL)) AND A.circuit_id = C.circuit_id AND A.circuit_current_stage = C.Circuit_Current_Stage AND EXISTS (SELECT M.country_code FROM tbl_user_region_country M, tbl_user N WHERE N.login_id = struser AND M.user_id = N.user_id AND M.active = 'Y' AND M.country_code = C.country_code) ); INSERT INTO tbl_qm_dashboard SELECT dashboard_type,NVL(active_cnt,0),NVL((age/DECODE(active_cnt,0,1,active_cnt)) ,0) avg_age,NVL(active_old_5days,0),NVL(closed_old_5days,0),NVL((response_old_5d ays/DECODE(closed_old_5days,0,1,closed_old_5days)),0) avg_response_old5days FROM ( SELECT 'AccessRFQSentSupplier' dashboard_type, SUM(case when (A.ot_queue_status_id IN(1,2) AND ((C.status_id 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3))) then 1 else 0 END ) active_cnt, SUM(case when (A.ot_queue_status_id IN(1,2) AND ((C.status_id 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3))) then (SYSDATE - c.created_on) else 0 END ) age, SUM(case when (c.created_on = (sysdate - 5) AND A.ot_queue_status_id IN(1,2) AND ((C.status_id 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3))) then 1 else 0 END )active_old_5days, SUM(case when (A.LAST_UPDATED_ON = (sysdate - 5) AND A.ot_queue_status_id = 3 ) then 1 else 0 END )closed_old_5days, SUM(case when (A.LAST_UPDATED_ON = (sysdate - 5
RE: (Fwd) Re: DBD::Oracle Slow cursors
Try doing a very simple refcursor and see if you still have the same performance issues. John From: P S Jameel Ahamed [mailto:jaha...@idexcel.com] Sent: Wednesday, May 18, 2011 5:04 AM To: 'John Scoles'; 'Martin Evans'; dbi-users@perl.org; Carlson, John W.; 'Tim Bunce' Subject: RE: (Fwd) Re: DBD::Oracle Slow cursors John, Irrespective of I calling in Java or executing it on the SQL developer or in TOAD, I am getting the same response time when compared to executing the SQL's separately Regards P S Jameel Ahamed From: John Scoles [mailto:byter...@hotmail.com] Sent: 18 May 2011 12:21 To: jaha...@idexcel.com; Martin Evans; dbi-users@perl.org; carlso...@llnl.gov; Tim Bunce Subject: RE: (Fwd) Re: DBD::Oracle Slow cursors Does this have anything to do at all with DBD::Oracle??? You mentioned you are calling this with JAVA?? Where is the Perl code?? From: jaha...@idexcel.com To: martin.ev...@easysoft.com; dbi-users@perl.org; byter...@hotmail.com; carlso...@llnl.gov; tim.bu...@pobox.com Subject: RE: (Fwd) Re: DBD::Oracle Slow cursors Date: Wed, 18 May 2011 11:07:56 +0100 Hi All, First of all I thank you all in replying to my email. Below are details of one of the SP which we are having issues with. In the below SP we are passing in a Username and then for that user are fetching his counts and other information. The output parameter is a sys_refcursur. The problem is that when I fire each and every insert and select statement, Its firing in 0.2 seconds. But when retrieving these details in the java code when it come to the line to loop the cursor then it gets struck for about 2 to 3 mins and the response is coming back. We have seen this behavior in most of the places where we used this sys_refcursur. The table circuit is a huge table of million records and it has all the indexces in place and other tables as well. I have been googleing for a long time for a solution for this sys-refcursor issue and I am not sure why its going back to the DB after executing the SP while looping through the cursor. We have Oracle 10G 10.1.0.4. I have also tried to combain all the select statements into one using the UNION command and insert into the tbl_qm_dashboard. The insert fires in 1.12 seconds, but again the cursuor looping is the issue. Note : tbl_qm_dashboard is a temp table. Hope I will get an break through with all your expertise. PROCEDURE sp_select_dashboard(strUser IN VARCHAR2, rcSelectDashboard OUT SYS_REFCURSOR) IS BEGIN EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_COMP=LINGUISTIC'; EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT=BINARY_CI'; INSERT INTO tbl_qm_dashboard SELECT dashboard_type,NVL(active_cnt,0),NVL((age/DECODE(active_cnt,0,1,active_cnt)) ,0)avg_age,NVL(active_old_5days,0),NVL(closed_old_5days,0),NVL((response_old _5days/DECODE(closed_old_5days,0,1,closed_old_5days)),0) avg_response_old5days FROM ( SELECT 'AccessCircuit' dashboard_type, SUM(case when (A.ot_queue_status_id IN(1,2) AND ((C.status_id 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3))) then 1 else 0 END ) active_cnt, SUM(case when (A.ot_queue_status_id IN(1,2) AND ((C.status_id 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3))) then (SYSDATE - c.created_on) else 0 END ) age, SUM(case when (c.created_on = (sysdate - 5) AND A.ot_queue_status_id IN(1,2) AND ((C.status_id 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3))) then 1 else 0 END )active_old_5days, SUM(case when (A.LAST_UPDATED_ON = (sysdate - 5) AND A.ot_queue_status_id = 3 ) then 1 else 0 END )closed_old_5days, SUM(case when (A.LAST_UPDATED_ON = (sysdate - 5) AND A.ot_queue_status_id = 3 ) then (A.LAST_UPDATED_ON - c.created_on) else 0 END ) response_old_5days FROM tbl_ot_queue A, tbl_circuit C, tbl_opportunity D WHERE A.assigned_to = struser AND C.opportunity_id = D.opportunity_id -- AND (D.key_bid = 'N' OR (D.key_bid = 'Y' AND C.release_to_region_on IS NOT NULL)) AND A.circuit_id = C.circuit_id AND A.circuit_current_stage = C.Circuit_Current_Stage AND EXISTS (SELECT M.country_code FROM tbl_user_region_country M, tbl_user N WHERE N.login_id = struser AND M.user_id = N.user_id AND M.active = 'Y' AND M.country_code = C.country_code) ); INSERT INTO tbl_qm_dashboard SELECT dashboard_type,NVL(active_cnt,0),NVL((age/DECODE(active_cnt,0,1,active_cnt)) ,0) avg_age,NVL(active_old_5days,0),NVL(closed_old_5days,0),NVL((response_old_5d ays/DECODE(closed_old_5days,0,1,closed_old_5days)),0) avg_response_old5days FROM ( SELECT 'AccessRFQSentSupplier' dashboard_type, SUM(case when (A.ot_queue_status_id IN(1,2) AND ((C.status_id 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3))) then 1 else 0 END ) active_cnt, SUM(case when (A.ot_queue_status_id IN(1,2) AND ((C.status_id 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3))) then (SYSDATE - c.created_on) else 0 END ) age, SUM(case when (c.created_on = (sysdate - 5
RE: (Fwd) Re: DBD::Oracle Slow cursors
When I say simple, I mean on a table with just a few rows. You may want to look at how many rows are pulled back from the database at a time on your large table (batching rows). John -Original Message- From: Carlson, John W. [mailto:carlso...@llnl.gov] Sent: Wednesday, May 18, 2011 9:46 AM To: P S Jameel Ahamed; 'John Scoles'; 'Martin Evans'; dbi-users@perl.org; 'Tim Bunce' Subject: RE: (Fwd) Re: DBD::Oracle Slow cursors Try doing a very simple refcursor and see if you still have the same performance issues. John From: P S Jameel Ahamed [mailto:jaha...@idexcel.com] Sent: Wednesday, May 18, 2011 5:04 AM To: 'John Scoles'; 'Martin Evans'; dbi-users@perl.org; Carlson, John W.; 'Tim Bunce' Subject: RE: (Fwd) Re: DBD::Oracle Slow cursors John, Irrespective of I calling in Java or executing it on the SQL developer or in TOAD, I am getting the same response time when compared to executing the SQL's separately Regards P S Jameel Ahamed From: John Scoles [mailto:byter...@hotmail.com] Sent: 18 May 2011 12:21 To: jaha...@idexcel.com; Martin Evans; dbi-users@perl.org; carlso...@llnl.gov; Tim Bunce Subject: RE: (Fwd) Re: DBD::Oracle Slow cursors Does this have anything to do at all with DBD::Oracle??? You mentioned you are calling this with JAVA?? Where is the Perl code?? From: jaha...@idexcel.com To: martin.ev...@easysoft.com; dbi-users@perl.org; byter...@hotmail.com; carlso...@llnl.gov; tim.bu...@pobox.com Subject: RE: (Fwd) Re: DBD::Oracle Slow cursors Date: Wed, 18 May 2011 11:07:56 +0100 Hi All, First of all I thank you all in replying to my email. Below are details of one of the SP which we are having issues with. In the below SP we are passing in a Username and then for that user are fetching his counts and other information. The output parameter is a sys_refcursur. The problem is that when I fire each and every insert and select statement, Its firing in 0.2 seconds. But when retrieving these details in the java code when it come to the line to loop the cursor then it gets struck for about 2 to 3 mins and the response is coming back. We have seen this behavior in most of the places where we used this sys_refcursur. The table circuit is a huge table of million records and it has all the indexces in place and other tables as well. I have been googleing for a long time for a solution for this sys-refcursor issue and I am not sure why its going back to the DB after executing the SP while looping through the cursor. We have Oracle 10G 10.1.0.4. I have also tried to combain all the select statements into one using the UNION command and insert into the tbl_qm_dashboard. The insert fires in 1.12 seconds, but again the cursuor looping is the issue. Note : tbl_qm_dashboard is a temp table. Hope I will get an break through with all your expertise. PROCEDURE sp_select_dashboard(strUser IN VARCHAR2, rcSelectDashboard OUT SYS_REFCURSOR) IS BEGIN EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_COMP=LINGUISTIC'; EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT=BINARY_CI'; INSERT INTO tbl_qm_dashboard SELECT dashboard_type,NVL(active_cnt,0),NVL((age/DECODE(active_cnt,0,1,active_cnt)) ,0)avg_age,NVL(active_old_5days,0),NVL(closed_old_5days,0),NVL((response_old _5days/DECODE(closed_old_5days,0,1,closed_old_5days)),0) avg_response_old5days FROM ( SELECT 'AccessCircuit' dashboard_type, SUM(case when (A.ot_queue_status_id IN(1,2) AND ((C.status_id 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3))) then 1 else 0 END ) active_cnt, SUM(case when (A.ot_queue_status_id IN(1,2) AND ((C.status_id 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3))) then (SYSDATE - c.created_on) else 0 END ) age, SUM(case when (c.created_on = (sysdate - 5) AND A.ot_queue_status_id IN(1,2) AND ((C.status_id 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3))) then 1 else 0 END )active_old_5days, SUM(case when (A.LAST_UPDATED_ON = (sysdate - 5) AND A.ot_queue_status_id = 3 ) then 1 else 0 END )closed_old_5days, SUM(case when (A.LAST_UPDATED_ON = (sysdate - 5) AND A.ot_queue_status_id = 3 ) then (A.LAST_UPDATED_ON - c.created_on) else 0 END ) response_old_5days FROM tbl_ot_queue A, tbl_circuit C, tbl_opportunity D WHERE A.assigned_to = struser AND C.opportunity_id = D.opportunity_id -- AND (D.key_bid = 'N' OR (D.key_bid = 'Y' AND C.release_to_region_on IS NOT NULL)) AND A.circuit_id = C.circuit_id AND A.circuit_current_stage = C.Circuit_Current_Stage AND EXISTS (SELECT M.country_code FROM tbl_user_region_country M, tbl_user N WHERE N.login_id = struser AND M.user_id = N.user_id AND M.active = 'Y' AND M.country_code = C.country_code) ); INSERT INTO tbl_qm_dashboard SELECT dashboard_type,NVL(active_cnt,0),NVL((age/DECODE(active_cnt,0,1,active_cnt)) ,0) avg_age,NVL(active_old_5days,0),NVL(closed_old_5days,0),NVL((response_old_5d ays/DECODE(closed_old_5days,0,1,closed_old_5days)),0
RE: (Fwd) Re: DBD::Oracle Slow cursors
It looks like you're doing a full table scan to pull back all rows of tbl_qm_dashboard currently--how many rows does that have? -Original Message- From: Carlson, John W. [mailto:carlso...@llnl.gov] Sent: Wednesday, May 18, 2011 10:05 AM To: P S Jameel Ahamed; 'John Scoles'; 'Martin Evans'; dbi-users@perl.org; 'Tim Bunce' Subject: RE: (Fwd) Re: DBD::Oracle Slow cursors When I say simple, I mean on a table with just a few rows. You may want to look at how many rows are pulled back from the database at a time on your large table (batching rows). John -Original Message- From: Carlson, John W. [mailto:carlso...@llnl.gov] Sent: Wednesday, May 18, 2011 9:46 AM To: P S Jameel Ahamed; 'John Scoles'; 'Martin Evans'; dbi-users@perl.org; 'Tim Bunce' Subject: RE: (Fwd) Re: DBD::Oracle Slow cursors Try doing a very simple refcursor and see if you still have the same performance issues. John From: P S Jameel Ahamed [mailto:jaha...@idexcel.com] Sent: Wednesday, May 18, 2011 5:04 AM To: 'John Scoles'; 'Martin Evans'; dbi-users@perl.org; Carlson, John W.; 'Tim Bunce' Subject: RE: (Fwd) Re: DBD::Oracle Slow cursors John, Irrespective of I calling in Java or executing it on the SQL developer or in TOAD, I am getting the same response time when compared to executing the SQL's separately Regards P S Jameel Ahamed From: John Scoles [mailto:byter...@hotmail.com] Sent: 18 May 2011 12:21 To: jaha...@idexcel.com; Martin Evans; dbi-users@perl.org; carlso...@llnl.gov; Tim Bunce Subject: RE: (Fwd) Re: DBD::Oracle Slow cursors Does this have anything to do at all with DBD::Oracle??? You mentioned you are calling this with JAVA?? Where is the Perl code?? From: jaha...@idexcel.com To: martin.ev...@easysoft.com; dbi-users@perl.org; byter...@hotmail.com; carlso...@llnl.gov; tim.bu...@pobox.com Subject: RE: (Fwd) Re: DBD::Oracle Slow cursors Date: Wed, 18 May 2011 11:07:56 +0100 Hi All, First of all I thank you all in replying to my email. Below are details of one of the SP which we are having issues with. In the below SP we are passing in a Username and then for that user are fetching his counts and other information. The output parameter is a sys_refcursur. The problem is that when I fire each and every insert and select statement, Its firing in 0.2 seconds. But when retrieving these details in the java code when it come to the line to loop the cursor then it gets struck for about 2 to 3 mins and the response is coming back. We have seen this behavior in most of the places where we used this sys_refcursur. The table circuit is a huge table of million records and it has all the indexces in place and other tables as well. I have been googleing for a long time for a solution for this sys-refcursor issue and I am not sure why its going back to the DB after executing the SP while looping through the cursor. We have Oracle 10G 10.1.0.4. I have also tried to combain all the select statements into one using the UNION command and insert into the tbl_qm_dashboard. The insert fires in 1.12 seconds, but again the cursuor looping is the issue. Note : tbl_qm_dashboard is a temp table. Hope I will get an break through with all your expertise. PROCEDURE sp_select_dashboard(strUser IN VARCHAR2, rcSelectDashboard OUT SYS_REFCURSOR) IS BEGIN EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_COMP=LINGUISTIC'; EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT=BINARY_CI'; INSERT INTO tbl_qm_dashboard SELECT dashboard_type,NVL(active_cnt,0),NVL((age/DECODE(active_cnt,0,1,active_cnt)) ,0)avg_age,NVL(active_old_5days,0),NVL(closed_old_5days,0),NVL((response_old _5days/DECODE(closed_old_5days,0,1,closed_old_5days)),0) avg_response_old5days FROM ( SELECT 'AccessCircuit' dashboard_type, SUM(case when (A.ot_queue_status_id IN(1,2) AND ((C.status_id 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3))) then 1 else 0 END ) active_cnt, SUM(case when (A.ot_queue_status_id IN(1,2) AND ((C.status_id 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3))) then (SYSDATE - c.created_on) else 0 END ) age, SUM(case when (c.created_on = (sysdate - 5) AND A.ot_queue_status_id IN(1,2) AND ((C.status_id 3) OR (C.status_id = 3 AND C.circuit_current_stage = 3))) then 1 else 0 END )active_old_5days, SUM(case when (A.LAST_UPDATED_ON = (sysdate - 5) AND A.ot_queue_status_id = 3 ) then 1 else 0 END )closed_old_5days, SUM(case when (A.LAST_UPDATED_ON = (sysdate - 5) AND A.ot_queue_status_id = 3 ) then (A.LAST_UPDATED_ON - c.created_on) else 0 END ) response_old_5days FROM tbl_ot_queue A, tbl_circuit C, tbl_opportunity D WHERE A.assigned_to = struser AND C.opportunity_id = D.opportunity_id -- AND (D.key_bid = 'N' OR (D.key_bid = 'Y' AND C.release_to_region_on IS NOT NULL)) AND A.circuit_id = C.circuit_id AND A.circuit_current_stage = C.Circuit_Current_Stage AND EXISTS (SELECT M.country_code FROM tbl_user_region_country M, tbl_user N
(Fwd) Re: DBD::Oracle Slow cursors
- Forwarded message from P S Jameel Ahamed jaha...@idexcel.com - Date: Tue, 17 May 2011 16:11:04 +0100 From: P S Jameel Ahamed jaha...@idexcel.com To: tim.bu...@pobox.com Subject: Re: DBD::Oracle Slow cursors X-Mailer: Microsoft Office Outlook 11 HI Tim, We are facing huge issues with the SYS_refcurors of oracle 10G when returning from Stored procedure. Is there any solution you found for the issue? Many Thanks in Advance Regards P S Jameel Ahamed Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message - End forwarded message -
Re: (Fwd) Re: DBD::Oracle Slow cursors
Tim Bunce schrieb am 17.05.2011 um 22:18 (+0100): - Forwarded message from P S Jameel Ahamed jaha...@idexcel.com We are facing huge issues with the SYS_refcurors of oracle 10G when returning from Stored procedure. Is there any solution you found for the issue? Care to expand a little on what precisely those issues are? And what exactly you expect the solution to solve? -- Michael Ludwig
RE: (Fwd) Re: DBD::Oracle Slow cursors
Don't use dynamic queries? You can use PL/SQL variables directly in the SQL statement w/o specifying them as bind variables. That is, pass them as parameters to your stored procedure, then just put them directly in the SQL without decoration. Return XML CLOB instead of cursor? Just some ideas. Would need to see code to know what to suggest next. I am not proposing that my solution is any faster, just different. John -Original Message- From: Tim Bunce [mailto:tim.bu...@pobox.com] Sent: Tuesday, May 17, 2011 2:18 PM To: dbi-users@perl.org Cc: jaha...@idexcel.com Subject: (Fwd) Re: DBD::Oracle Slow cursors - Forwarded message from P S Jameel Ahamed jaha...@idexcel.com - Date: Tue, 17 May 2011 16:11:04 +0100 From: P S Jameel Ahamed jaha...@idexcel.com To: tim.bu...@pobox.com Subject: Re: DBD::Oracle Slow cursors X-Mailer: Microsoft Office Outlook 11 HI Tim, We are facing huge issues with the SYS_refcurors of oracle 10G when returning from Stored procedure. Is there any solution you found for the issue? Many Thanks in Advance Regards P S Jameel Ahamed Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message - End forwarded message -
RE: (Fwd) Re: DBD::Oracle Slow cursors
What is the issue exatly? It is just slow?? Can you give us some examples code to play with. Slowness can be caused by anything from low-ban width, poor SQL, a badly partiioned DB or just too much data?? We need to know the version of DBD::Oracle you are using as well Cheers John Date: Tue, 17 May 2011 22:18:22 +0100 From: tim.bu...@pobox.com To: dbi-users@perl.org CC: jaha...@idexcel.com Subject: (Fwd) Re: DBD::Oracle Slow cursors - Forwarded message from P S Jameel Ahamed jaha...@idexcel.com - Date: Tue, 17 May 2011 16:11:04 +0100 From: P S Jameel Ahamed jaha...@idexcel.com To: tim.bu...@pobox.com Subject: Re: DBD::Oracle Slow cursors X-Mailer: Microsoft Office Outlook 11 HI Tim, We are facing huge issues with the SYS_refcurors of oracle 10G when returning from Stored procedure. Is there any solution you found for the issue? Many Thanks in Advance Regards P S Jameel Ahamed Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message - End forwarded message -
Re: (Fwd) Re: DBD::Oracle Slow cursors
On 17/05/2011 22:18, Tim Bunce wrote: - Forwarded message from P S Jameel Ahamedjaha...@idexcel.com - Date: Tue, 17 May 2011 16:11:04 +0100 From: P S Jameel Ahamedjaha...@idexcel.com To: tim.bu...@pobox.com Subject: Re: DBD::Oracle Slow cursors X-Mailer: Microsoft Office Outlook 11 HI Tim, We are facing huge issues with the SYS_refcurors of oracle 10G when returning from Stored procedure. Is there any solution you found for the issue? Many Thanks in Advance Regards P S Jameel Ahamed Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message - End forwarded message - We use reference cursors all the time and don't see any particular issues with them other than the SQL Developer report showing reused cursors which can be easily avoided. To be honest, our user space code in Perl has no select access to anything in the database and can only access tables via functions returning system reference cursors. I wonder about your statement when returning from Stored procedure as procedures don't return values other than as out parameters. Older versions of DBD::Oracle failed to inherit the rowcachesize and ora_auto_lobs which we have had problems with in the past but these are all resolved in the latest DBD::Oracle. I guess you'd need to be more specific for anyone to help or we are just guessing. Martin
