Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Questioning Oracle Documentation

Re: Questioning Oracle Documentation

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Sun, 11 Jul 2004 07:28:14 +0200
Message-ID: <40f0cf80$0$29377$626a14ce@news.free.fr>

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> a écrit dans le message de news:ccppad$6r6$1_at_sparta.btinternet.com...
>
> Is the example the same in the 9i manual ?
>
> I believe 10g has mechanisms for promoting constant
> assignments to a position outside the loop - but possibly
> this only works in a restricted number of cases.
>
> You might try:
> n := 15
> n := 15.0
>
> c as a varchar2()
> c as char(2) -- to match the size of '15'
>
>
> --
> Regards
>
> Jonathan Lewis
>
> http://www.jlcomp.demon.co.uk
>
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> The Co-operative Oracle Users' FAQ
>
> http://www.jlcomp.demon.co.uk/seminar.html
> Optimising Oracle Seminar - schedule updated May 1st
>
>
> "Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
> news:1089492156.252777_at_yasure...
> > In Tuning PL/SQL Applications for Performance
> >
> http://download-west.oracle.com/docs/cd/B13789_01/appdev.101/b10807/12_tune.htm#LNPLS012
> > I found the following and decided it could make a good demo for my
> students.
> >
> > So here's what I wrote:
> >
> > DECLARE
> >
> > c CHAR(5);
> > i PLS_INTEGER;
> > j PLS_INTEGER;
> > n NUMBER := 0;
> >
> > BEGIN
> > BEGIN
> > i := dbms_utility.get_time();
> >
> > FOR i IN 1 .. 10000000
> > LOOP
> > n := n + 15;
> > END LOOP;
> >
> > j := dbms_utility.get_time() - i;
> > dbms_output.put_line('n+15 = ' || TO_CHAR(j));
> > END;
> >
> > BEGIN
> > i := dbms_utility.get_time();
> >
> > FOR i IN 1 .. 10000000
> > LOOP
> > n := n+15.0;
> > END LOOP;
> >
> > j := dbms_utility.get_time() - i;
> > dbms_output.put_line('n+15.0 = ' || TO_CHAR(j));
> > END;
> >
> > BEGIN
> > i := dbms_utility.get_time();
> >
> > FOR i IN 1 .. 10000000
> > LOOP
> > c := 15;
> > END LOOP;
> >
> > j := dbms_utility.get_time() - i;
> > dbms_output.put_line('c=15 = ' || TO_CHAR(j));
> > END;
> >
> > BEGIN
> > i := dbms_utility.get_time();
> >
> > FOR i IN 1 .. 10000000
> > LOOP
> > c := TO_CHAR(15);
> > END LOOP;
> >
> > j := dbms_utility.get_time() - i;
> > dbms_output.put_line('c=TO_CHAR = ' || TO_CHAR(j));
> > END;
> >
> > BEGIN
> > i := dbms_utility.get_time();
> >
> > FOR i IN 1 .. 10000000
> > LOOP
> > c := '15';
> > END LOOP;
> >
> > j := dbms_utility.get_time() - i;
> > dbms_output.put_line('c=''15'' = ' || TO_CHAR(j));
> > END;
> > END;
> > /
> >
> > Can anyone hazard a guess as to why I am getting the following
> > result?
> >
> > n+15 = 201
> > n+15.0 = 204
> > c=15 = 38
> > c=TO_CHAR = 304
> > c='15' = 307
> >
> > Why is the implicit conversion, that we are being warned
> > not to do the fastest?
> >
> > 10.1.0.2 on Win2K w/ 1GB RAM
> >
> > Daniel Morgan
> >
>
>

The example is the same in 9i.
I made these tests on 9iR2 and 8iR3 adding the cases char(2), varchar2(5) and even to_char(to_number('15')) and get the following results (WinNT4SP6, PII360Mhz)

9.2.0.4:

n+15       ->  295
n+15.0     ->  227
c5=15      ->  732
c5=TO_CHAR ->  704
c5='15'    -> 2929
c2='15'    -> 2744
vc='15'    -> 2749

c5=TO_CHAR(TO_NUMBER('15')) -> 821
-
n+15       ->  292
n+15.0     ->  214
c5=15      ->  727
c5=TO_CHAR ->  718
c5='15'    -> 2930
c2='15'    -> 2829
vc='15'    -> 2764

c5=TO_CHAR(TO_NUMBER('15')) -> 827
-
n+15       ->  288
n+15.0     ->  213
c5=15      ->  737
c5=TO_CHAR ->  705
c5='15'    -> 2959
c2='15'    -> 2750
vc='15'    -> 2751

c5=TO_CHAR(TO_NUMBER('15')) -> 823

8.1.7.4:

n+15       ->  309
n+15.0     ->  255
c5=15      ->  747
c5=TO_CHAR -> 1048
c5='15'    -> 2610
c2='15'    -> 2405
vc='15'    -> 2388

c5=TO_CHAR(TO_NUMBER('15')) -> 1070
-
n+15       ->  303
n+15.0     ->  252
c5=15      ->  741
c5=TO_CHAR -> 1041
c5='15'    -> 2618
c2='15'    -> 3305
vc='15'    -> 2400

c5=TO_CHAR(TO_NUMBER('15')) -> 1071
-
n+15       ->  305
n+15.0     ->  248
c5=15      ->  750
c5=TO_CHAR -> 1038
c5='15'    -> 2615
c2='15'    -> 2409
vc='15'    -> 2404

c5=TO_CHAR(TO_NUMBER('15')) -> 1071

So why not converting is so long?

The test case was:
DECLARE c5 CHAR(5);

i PLS_INTEGER;
j PLS_INTEGER;
n NUMBER := 0;

c2 CHAR(2);
vc VARCHAR2(5);

BEGIN    BEGIN
     i := dbms_utility.get_time();

     FOR i IN 1 .. 1000000
     LOOP
       n := n + 15;
     END LOOP;

     j := dbms_utility.get_time() - i;
     dbms_output.put_line('n+15       ->' || TO_CHAR(j,'9999'));
   END;    BEGIN
     i := dbms_utility.get_time();
     FOR i IN 1 .. 1000000
     LOOP
       n := n+15.0;
     END LOOP;

     j := dbms_utility.get_time() - i;
     dbms_output.put_line('n+15.0     ->' || TO_CHAR(j,'9999'));
   END;    BEGIN
     i := dbms_utility.get_time();
     FOR i IN 1 .. 1000000
     LOOP
       c5 := 15;
     END LOOP;

     j := dbms_utility.get_time() - i;
     dbms_output.put_line('c5=15      ->' || TO_CHAR(j,'9999'));
   END;    BEGIN
     i := dbms_utility.get_time();
     FOR i IN 1 .. 1000000
     LOOP
       c5 := TO_CHAR(15);
     END LOOP;

     j := dbms_utility.get_time() - i;
     dbms_output.put_line('c5=TO_CHAR ->' || TO_CHAR(j,'9999'));
   END;    BEGIN
     i := dbms_utility.get_time();
     FOR i IN 1 .. 10000000
     LOOP
       c5 := '15';
     END LOOP;

     j := dbms_utility.get_time() - i;
     dbms_output.put_line('c5=''15''    ->' || TO_CHAR(j,'9999'));
   END;    BEGIN
     i := dbms_utility.get_time();
     FOR i IN 1 .. 10000000
     LOOP
       c2 := '15';
     END LOOP;

     j := dbms_utility.get_time() - i;
     dbms_output.put_line('c2=''15''    ->' || TO_CHAR(j,'9999'));
   END;    BEGIN
     i := dbms_utility.get_time();
     FOR i IN 1 .. 10000000
     LOOP
       vc := '15';
     END LOOP;

     j := dbms_utility.get_time() - i;
     dbms_output.put_line('vc=''15''    ->' || TO_CHAR(j,'9999'));
   END;    BEGIN
     i := dbms_utility.get_time();
     FOR i IN 1 .. 1000000
     LOOP
       c5 := TO_CHAR(TO_NUMBER('15'));
     END LOOP;

     j := dbms_utility.get_time() - i;
     dbms_output.put_line('c5=TO_CHAR(TO_NUMBER(''15'')) ->' || TO_CHAR(j,'9999'));
   END; END;
/
--
Regards
Michel Cadot
Received on Sun Jul 11 2004 - 00:28:14 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US