Home » RDBMS Server » Performance Tuning » Cursor Performance Issue
Cursor Performance Issue [message #118969] Tue, 10 May 2005 01:47 Go to next message
sujit_ocp
Messages: 7
Registered: May 2005
Location: Chennai
Junior Member
Dear All,

I have two following approach of writing Procedure. Both will give same result.
I want to know which approach is better in terms of Performance.
I mean, taking into consideration Parsing,Execution and Other parameters.

Any Suggestion is highly appreciated.

Thanks and Regards,

Sujit

1)

SQL> create or replace procedure proc_comp
2 is
3 begin
4 for i in (select empno,
ename,
job,
sal
from emp
where deptno = 10) loop
5 dbms_output.put_line(i.empno || ' : '||i.ename);
6 end loop;
7 end;
8 /

Procedure created.

2)

SQL> create or replace procedure proc_comp1
2 is
3 cursor c is
4 select empno,
ename,
job,
sal
5 from emp
6 where deptno = 10;
7 begin
8 for i in c loop
9 dbms_output.put_line(i.empno || ' : '||i.ename);
10 end loop;
11 end;
12 /
Re: Cursor Performance Issue [message #118977 is a reply to message #118969] Tue, 10 May 2005 02:07 Go to previous message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
The difference is tiny, but the implicit cursor in your first procedure is slightly more efficient than the explicit cursor in your second procedure. I also find the first a bit easier to write, read, and maintain, although there are those who would disagree. If you search for explicit and implicit on asktom.oracle.com, you will find various discussions where Tom Kyte recommends implicit cursors rather than explicit cursors.

Previous Topic: Oracle Schema slowing performance
Next Topic: ARCHIVELOG GETTING FILLED
Goto Forum:
  


Current Time: Sun Jan 17 13:16:50 CST 2021