| Ref cursor very slow [message #414305] |
Tue, 21 July 2009 08:37  |
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 #414309 is a reply to message #414308] |
Tue, 21 July 2009 08:51  |
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 |
cookiemonster
Messages: 13963
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 #414384 is a reply to message #414305] |
Tue, 21 July 2009 23:10 |
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
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
