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:[email protected]]
Sent: 17 May 2011 23:00
To: [email protected]; [email protected]
Subject: Re: (Fwd) Re: DBD::Oracle Slow cursors
On 17/05/2011 22:18, Tim Bunce wrote:
> ----- Forwarded message from P S Jameel Ahamed<[email protected]> -----
>
> Date: Tue, 17 May 2011 16:11:04 +0100
> From: P S Jameel Ahamed<[email protected]>
> To: [email protected]
> 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
