Home » Infrastructure » Windows » Ref cursor very slow (oracle 9i)
Ref cursor very slow [message #414305] Tue, 21 July 2009 08:37 Go to next message
kashifchughtai
Messages: 125
Registered: October 2007
Senior Member
Hi All,

I am using a refcursor to return the results. although sql statment executes very fast but in .net application when getting the records from cursor it takes around 30 seconds for (5000 recrods).

is there anyway to optimize the fetching process from cursor.

thanks
Re: Ref cursor very slow [message #414308 is a reply to message #414305] Tue, 21 July 2009 08:43 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you run it sqlplus how long does it take?
Re: Ref cursor very slow [message #414309 is a reply to message #414308] Tue, 21 July 2009 08:51 Go to previous messageGo to next message
kashifchughtai
Messages: 125
Registered: October 2007
Senior Member
i tried to execute the procedure in test window in plsql developer and it executes within a second but when i open the returned cursor it takes around 20 second to show me all the records.
Re: Ref cursor very slow [message #414310 is a reply to message #414305] Tue, 21 July 2009 08:59 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Calling a procedure that opens a ref cursor isn't really executing the select statement in any meaningfull sense. That only happens when you fetch it.
So post the procedure with the ref_cursor along with an explain plan, and while you're doing that have a look at the sticky at the top of the performance tuning forum.
Re: Ref cursor very slow [message #414316 is a reply to message #414305] Tue, 21 July 2009 09:41 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>when i open the returned cursor it takes around 20 second to show me all the records.

Most likely multiple round trips across network occur

ALTER SESSION SET SQL_TRACE=TRUE;
-- invoke REF Cursor here
ALTER SESSION SET SQL_TRACE=FALSE;

process trace file with TKPROF & post results here
Re: Ref cursor very slow [message #414324 is a reply to message #414305] Tue, 21 July 2009 10:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not fetch the row one by one.
1/ Use array fetch
2/ Use bulk collect

Regards
Michel
Re: Ref cursor very slow [message #414384 is a reply to message #414305] Tue, 21 July 2009 23:10 Go to previous messageGo to next message
kashifchughtai
Messages: 125
Registered: October 2007
Senior Member
sql statment is :

SELECT cb.code, cb.descr, cb.eff_date, cb.level_in_hierarchy, cb.csj_assoc_code, cb.discont_date,
cb.csj_comment, cb.level_of_severity, cb.add_context, cb.poc_relevant, cb.airport_relevant, cb.poc_code, cb.add_relevant,
cb.flight_relevant, cb.usr_created, cb.created, cb.usr_changed, cb.changed, cb.csj_type, cb.class_relevant, cb.sector_relevant,
cb.department_relevant, cb.staff_relevant, cb.audit_outcome_reqd, cb.sec_id, cb.remarks_reqd,
(SELECT RV_ABBREVIATION FROM CG_REF_CODES WHERE  RV_DOMAIN = 'CONTACT SUBJECT TYPES' and RV_LOW_VALUE=cb.csj_type) CR_Type,
(Select rv_abbreviation from Freeway_Ref_Codes where  RV_DOMAIN  = 'ADDRESS CONTEXT' and rv_low_value=cb.add_context) Type,
(SELECT DESCR FROM POINT_OF_CONTACTS where code = cb.poc_code) poc_code_desc ,nvl(length(cb.csj_assoc_code),0)+1 cr_code_LEVEL,cb.code old_code  
FROM CONTACT_SUBJECTS cb where  upper(cb.CSJ_TYPE) != 'A'  
ORDER BY 1


and its plan is :

SELECT STATEMENT, GOAL = ALL_ROWS			Cost=20	Cardinality=223	Bytes=19401
 TABLE ACCESS BY INDEX ROWID	Object owner=xxxx	Object name=FREEWAY_REF_CODES	Cost=2	Cardinality=1	Bytes=35
  INDEX RANGE SCAN	Object owner=xxxx	Object name=X_FREEWAY_REF_CODES_1	Cost=1	Cardinality=1	
 TABLE ACCESS BY INDEX ROWID	Object owner=xxxx	Object name=FREEWAY_REF_CODES	Cost=2	Cardinality=1	Bytes=35
  INDEX RANGE SCAN	Object owner=xxxx	Object name=X_FREEWAY_REF_CODES_1	Cost=1	Cardinality=1	
 TABLE ACCESS BY INDEX ROWID	Object owner=xxxx	Object name=POINT_OF_CONTACTS	Cost=1	Cardinality=1	Bytes=24
  INDEX UNIQUE SCAN	Object owner=xxxx	Object name=POC_PK	Cost=0	Cardinality=1	
 SORT ORDER BY			Cost=20	Cardinality=223	Bytes=19401
  TABLE ACCESS FULL	Object owner=xxxx	Object name=CONTACT_SUBJECTS	Cost=19	Cardinality=223	Bytes=19401


according to the plan CONTACT_SUBJECTS table is fully scanned and it think its right because there are only 8000 records from which it fetch around 5000 records.

kindly assist

Re: Ref cursor very slow [message #414388 is a reply to message #414384] Tue, 21 July 2009 23:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The problem is not in the query, it is in the application.
Do what I said.

Regards
Michel
Re: Ref cursor very slow [message #414408 is a reply to message #414305] Wed, 22 July 2009 00:59 Go to previous messageGo to next message
kashifchughtai
Messages: 125
Registered: October 2007
Senior Member
michal ,

after querying it we just return the results to .net application where we show these results.

do u mean that its a problem in .net application?
Re: Ref cursor very slow [message #414412 is a reply to message #414408] Wed, 22 July 2009 01:13 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.

Regards
Michel
Previous Topic: OLE2 For word doc
Next Topic: ODP. NET: error on reconnecting the network cable.
Goto Forum:
  


Current Time: Thu Mar 28 18:55:07 CDT 2024