Re: benchmarking, which statement is faster

Hi Daniel,

There is something wrong with your "benchmarking". Your script should be like this:

---

timing start
DECLARE
CURSOR my_cur is
   SELECT user
   FROM dual;
my_rec   my_cur%ROWTYPE;
i   INTEGER;
x   VARCHAR2(30);
BEGIN
   FOR i in 1..100000
   LOOP
      OPEN my_cur;
      FETCH my_cur INTO my_rec;
      x := my_rec.user || 'A';
      close my_cur;
   END LOOP;
END;
/
timing stop
timing start
DECLARE
i   INTEGER;
x   VARCHAR2(30);
BEGIN
   FOR i in 1..100000
   LOOP
      SELECT user
      INTO x
      FROM dual;
      x := x || 'A';
   END LOOP;
END;
/
timing stop

---

The result is like this:

---

Connected to:
Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production
With the Partitioning option
JServer Release 9.0.1.0.0 - Production

SQL> @e:\temp\a

PL/SQL procedure successfully completed.

Elapsed: 00:00:14.61

PL/SQL procedure successfully completed.

Elapsed: 00:00:11.37

---



In comp.databases.oracle.server Daniel A. Morgan <Daniel.Morgan_at_attws.com> wrote:


> Thomas Kyte wrote:

> 

>> In article <3B5F1DB7.42F4A196_at_attws.com>, "Daniel says...
>> >
>> >Thomas Kyte wrote:
>> >
>> >> In article <3B5DD1ED.F0C996D2_at_attws.com>, "Daniel says...
>> >> >
>> >> >DS wrote:
>> >> >
>> >> >> hi all,
>> >> >>
>> >> >> has anybody benchmarked or has any idea which of the
>> >> >> following statements will execute faster?
>> >> >>
>> >> >
>> >> >Explicit cursors are preferable to implicit cursors. If not the first
>> >> >time they execute, thereafter. In addition they also solve space
>> >> >problems with temp and rollback by allowing incremental commits.
>> >> >
>> >>
>> >> explicit cursors in PLSQL rot.
>> >>
>> >> They are slower.
>> >> They are harder to code.
>> >> There is NO performace benefit to them the first time, the second time or any
>> >> time.
>> >>
>> >>If an insert into is to be a single transaction (as they almost always are), you
>> >> need to have sufficient rollback configured.  Incremental commits are a time
>> >> waster and introduce bugs (how many people make their "incremental" things
>> >>"restartable" -- they always seem to forget that.  When they blow up, they are
>> >> left with a mess)
>> >>
>> >> Temp space -- well, you would need EXACTLY the same amount for an explicit
>> >> cursor as you would an implicit -- there would be no difference there (if we
>> >> gotta sort one of them, we gotta sort the other).
>> >>
>> >> INSER INTO SELECT * FROM
>> >>
>> >> Over
>> >>
>> >> a cursor
>> >>
>> >> wins anytime.....
>> >>
>> >> see
>> >>http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1017644816229
>> >> for more details
>> >>
>> >> >Daniel A. Morgan
>> >> >
>> >>
>> >> --
>> >> Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/
>> >> Expert one on one Oracle, programming techniques and solutions for Oracle.
>> >> http://www.amazon.com/exec/obidos/ASIN/1861004826/
>> >> Opinions are mine and do not necessarily reflect those of Oracle Corp
>> >
>> >Beg to disagree here Tom. The day I find a developer at any company where I
>> >consult
>> >using anything other than explicit for pulling records in a loop they will not
>> >longer be on the project. And the other DBAs in my group agree.
>> >
>> >Daniel A. Morgan
>> >
>>
>> Everyone is free to disagree, doesn't make them right.  Even if they have a
>> quorum.
>>
>> however, if you read:
>>
>> http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1017644816229
>>
>> It conclusively shows that
>>
>> insert into select * from
>>
>> beats (kills, outperforms, etc)
>>
>> loop
>>    fetch
>>    insert
>> end
>>
>> It makes sense. Why in the heck would you even consider pulling the records back
>> to PLSQL only to send them back to the database.
>>
>> If you read:
>>
>> http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1544606261686
>>
>> it conclusively shows that SELECT ... INTO from t is faster, better, cheaper,
>> easier, more readable then
>>
>> open
>> fetch
>> close
>>
>> Until and unless you can post an example of any case where explicit cursors beat
>> an implicit cursor.... I am suspicious of your conclusion.
>>
>> What you believe to be true is fine.
>>
>> What is truly true and real -- thats a different matter all together.
>>
>> What is the basis in reality and fact for your conclusion?  Where is the test
>> case, the sample, the empirical proof?
>>
>> I myself would take the developer that demands on using explicit cursors,
>> educate them as to the power and beauty of the implicit cursor -- and only upon
>> finding them ignoring fact remove them.
>>
>> --
>> Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/
>> Expert one on one Oracle, programming techniques and solutions for Oracle.
>> http://www.amazon.com/exec/obidos/ASIN/1861004826/
>> Opinions are mine and do not necessarily reflect those of Oracle Corp


> 

> Then by all means please explain to me the beauty of the second query using an

> implicit cursor over the former with an explicit cursor.

> 

> DECLARE

> 

> CURSOR my_cur is

>    SELECT user

>    FROM dual;

> 

> my_rec   my_cur%ROWTYPE;

> 

> i   INTEGER;

> x   VARCHAR2(30);

> 

> BEGIN

>    OPEN my_cur;

>    FOR i in 1..1000000

>    LOOP

>       FETCH my_cur INTO my_rec;

>       x := my_rec.user || 'A';

>    END LOOP;

> END;

> /

> 

> Elapsed: 00:00:03.04

> ========================================

> DECLARE

> 

> i   INTEGER;

> x   VARCHAR2(30);

> 

> BEGIN

>    FOR i in 1..1000000

>    LOOP

>       SELECT user

>       INTO x

>       FROM dual;

>       x := x || 'A';

>    END LOOP;

> END;

> /

> 

> Elapsed: 00:01:94.35

> 

> I hope to learn something.

> 

> Of course I'd also like to learn what Elapsed: 00:01:94.35 means too.

> 

> Daniel A. Morgan

> 



-- 
http://www.attunity.com
http://www.asl.com.hk/products/ElectronicStockTradingSolution.html