OraFAQ Forum: SQL & PL/SQL » Performance by which cursor is best

Home » SQL & PL/SQL » SQL & PL/SQL » Performance by which cursor is best

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Performance by which cursor is best [message #616373]

Mon, 16 June 2014 05:12

aaditya321
Messages: 225
Registered: January 2014
Location: Delhi

Senior Member

Hi Guys,

Please let me know performance by which one cursor we should be used like below here:
1- Normal cursor
2- Parametrized cursor
3- Sys cursor

Report message to a moderator

Re: Performance by which cursor is best [message #616374 is a reply to message #616373]

Mon, 16 June 2014 05:24

Michel Cadot
Messages: 68634
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What is a normal cursor?

Performances do not depend on cursor type but on the the query and tables and data and...

Report message to a moderator

Re: Performance by which cursor is best [message #616376 is a reply to message #616374]

Mon, 16 June 2014 05:55

aaditya321
Messages: 225
Registered: January 2014
Location: Delhi

Senior Member

Normal cursor means simple cursor As:

1> DECLARE 

2>    emp_rec emp_tbl%rowtype;

3>    CURSOR emp_cur IS 

4>    SELECT *

5>    FROM 

6>    WHERE salary > 10; 

7> BEGIN 

8>    OPEN emp_cur; 

9>    FETCH emp_cur INTO emp_rec; 

10>      dbms_output.put_line (emp_rec.first_name || '  ' || emp_rec.last_name); 

11>   CLOSE emp_cur; 

12> END;

Report message to a moderator

Re: Performance by which cursor is best [message #616377 is a reply to message #616376]

Mon, 16 June 2014 06:03

Michel Cadot
Messages: 68634
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

The other cursors are also "normal", this is what I meant.

One way to check is to execute the code with the different options and see the difference in execution time.

Note: sys cursor does not exist, there is a SYS_REFCURSOR type which is a kind of REF CURSOR.

Report message to a moderator

Re: Performance by which cursor is best [message #616378 is a reply to message #616377]

Mon, 16 June 2014 06:13

cookiemonster
Messages: 13919
Registered: September 2008
Location: Rainy Manchester

Senior Member

Parameterized explicit cursors were definitely faster than non-parameterized cursors in older versions, I don't know if that's still true.
However it's generally faster to not use an explicit cursor at all.
As for sys-refcursor - that's used for a different purpose to explicit cursors, primarily it's for passing data back to external programs, so it's pretty meaningless to compare its performance to explicit cursors.

Report message to a moderator

Re: Performance by which cursor is best [message #616381 is a reply to message #616373]

Mon, 16 June 2014 07:03

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

aaditya321 wrote on Mon, 16 June 2014 15:42

Please let me know performance by which one cursor we should be used like below here:
1- Normal cursor
2- Parametrized cursor
3- Sys cursor

Read this https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4247516500346622654 T.Kyte shows a small example as to when to use explicit cursors and ref cursors over implicit cursors(else for all cases implicit cursors are certainly faster). This should answer to your point 1 and 3. Hope by normal cursor you mean explicit cursor as you have shown in your code.

Coming to your point 2, how is parameterized cursor a different category of cursor altogether? It is an explicit cursor with IN mode of formal parameters. I have not heard of performance difference between a parameterized and no nparamterized explicit cursor in recent versions.

Report message to a moderator

Previous Topic:	how to recover data in case of truncate table and drop partition
Next Topic:	Days Between Start date/today, unless Date finished.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 16 03:04:27 CDT 2024