RE: 'begin end' blocks and pl/sql performance

From: Guang Mei <gmei_at_incyte.com>
Date: Fri, 11 Jun 2004 11:39:31 -0400
Message-ID: <NJEDKDKJDGAKAEKKNEEJMELPDEAA.gmei@incyte.com>

>From my quick test below (on Oracle 9204), it does not seem to make any
difference when looping upto 1M times.

Guang

---

set timing on

declare
  x number:=1;
  l_cnt number:=1000000;
begin
  if (x=1) then
    for y in 1 .. l_cnt loop
      null;
    end loop;
  else
    for y in 1 .. l_cnt loop

      begin
        null;
      end;

    end loop;
  end if;
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.80
SQL> SQL> / PL/SQL procedure successfully completed.

Elapsed: 00:00:00.77
SQL> / PL/SQL procedure successfully completed.

Elapsed: 00:00:00.77
SQL> / PL/SQL procedure successfully completed.

Elapsed: 00:00:00.77

declare
  x number:=2;
  l_cnt number:=1000000;
begin
  if (x=1) then
    for y in 1 .. l_cnt loop
      null;
    end loop;
  else
    for y in 1 .. l_cnt loop

      begin
        null;
      end;

    end loop;
  end if;
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.78
SQL> SQL> / PL/SQL procedure successfully completed.

Elapsed: 00:00:00.77
SQL> / PL/SQL procedure successfully completed.

Elapsed: 00:00:00.77
SQL> / PL/SQL procedure successfully completed.

Elapsed: 00:00:00.77

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of ryan.gaffuri_at_cox.net
> Sent: Friday, June 11, 2004 10:57 AM
> To: oracle-l_at_freelists.org
> Subject: 'begin end' blocks and pl/sql performance
>
>
> Can extra begin end blocks inside your pl/sql affect performance?
> If so, how?
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Jun 11 2004 - 10:36:51 CDT