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

From: <tim_at_sagelogix.com>
Date: Fri, 11 Jun 2004 08:12:52 -0600 (MDT)
Message-ID: <1753699.1086963172914.JavaMail.oracle@ocs.sagelogix.com>

Ryan,

Should be simple to test, no?

Create a procedure that executes in this fashion, to use as a 'baseline" (i.e. no nested blocks):

    begin

         for i in 1..10000000 loop
             null;
         end loop;

    end;
    /

Then, execute a procedure that executes in this fashion (i.e. 10 nested blocks):

    begin

         for i in 1..10000000 loop
             begin begin begin begin begin begin begin begin begin begin null;
             end;   end;  end;   end;  end;   end;  end;   end;   end;   end;
         end loop;

    end;
    /

Then, bracket each execution of these (before and after, calculating and recording the deltas) with:

    select s.value from v$mystat s, v$statname n     where n.name = 'CPU used by this session'     and s.statistic# = n.statistic#;

Do it a bunch of times, record the deltas, drop the highest and lowest readings, average the remainder of the readings, etc.

Also, you might also want to try adding more significant "payload" to the loop (rather than the NULL command), such as "SELECT DUMMY FROM DUAL" or something like that.

Please report back what you find?

Hope this helps...

-Tim

attached mail follows:

---

---

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
-----------------------------------------------------------------



message/rfc822 attachment: _begin_end__blocks_and_pl_sql_performance

Received on Fri Jun 11 2004 - 10:25:56 CDT