Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Questioning Oracle Documentation
"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
n+15 -> 292 n+15.0 -> 214 c5=15 -> 727 c5=TO_CHAR -> 718 c5='15' -> 2930 c2='15' -> 2829 vc='15' -> 2764
n+15 -> 288 n+15.0 -> 213 c5=15 -> 737 c5=TO_CHAR -> 705 c5='15' -> 2959 c2='15' -> 2750 vc='15' -> 2751
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
n+15 -> 303 n+15.0 -> 252 c5=15 -> 741 c5=TO_CHAR -> 1041 c5='15' -> 2618 c2='15' -> 3305 vc='15' -> 2400
n+15 -> 305 n+15.0 -> 248 c5=15 -> 750 c5=TO_CHAR -> 1038 c5='15' -> 2615 c2='15' -> 2409 vc='15' -> 2404
So why not converting is so long?
The test case was:
DECLARE
c5 CHAR(5);
i PLS_INTEGER; j PLS_INTEGER; n NUMBER := 0;
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
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
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
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
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
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
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
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 CadotReceived on Sun Jul 11 2004 - 00:28:14 CDT