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 - 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 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 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 ----- > > 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 > > 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 > 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 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
