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
>
