Work test of DETERMINISTIC function

SQL> set timing off;
SQL> def cnt = 10000000;
SQL> 
SQL> create or replace package detest as
  2    function test1(p in varchar2) return varchar2 DETERMINISTIC;
  3    function test2(p in varchar2) return varchar2 DETERMINISTIC;
  4  end;
  5  /

Package created.

SQL> 
SQL> create or replace package body detest as
  2    vCache varchar2( 2000 );
  3  
  4    function test1(p in varchar2) return varchar2
  5    as
  6    begin
  7      if p = 's' then
  8        return 't';
  9      else
 10        return null;
 11      end if;
 12    end;
 13  
 14    function test2(p in varchar2) return varchar2
 15    as
 16    begin
 17      if p = 's' then
 18        if vCache is null then
 19          select 't' into vCache from ALL_OBJECTS where rownum < 2;
 20        end if;
 21        return vCache;
 22      else
 23        return null;
 24      end if;
 25    end;
 26  
 27  end;
 28  /

Package body created.

SQL> 
SQL> set timing on;
SQL> 
SQL> declare
  2    vDummy varchar2(2000);
  3  begin
  4    for i in 1..&cnt
  5    loop
  6      vDummy := detest.test1('z');
  7      vDummy := detest.test1('s');
  8    end loop;
  9  end;
 10  /
old   4:   for i in 1..&cnt
new   4:   for i in 1..10000000

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.25
SQL> 
SQL> declare
  2    vDummy varchar2(2000);
  3  begin
  4    for i in 1..&cnt
  5    loop
  6      vDummy := detest.test2('z');
  7      vDummy := detest.test2('s');
  8    end loop;
  9  end;
 10  /
old   4:   for i in 1..&cnt
new   4:   for i in 1..10000000

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.39
SQL> 
SQL> set timing off;
SQL> 
SQL> create or replace package detest as
  2    function test1(p in varchar2) return varchar2;
  3    function test2(p in varchar2) return varchar2;
  4  end;
  5  /

Package created.

SQL> 
SQL> set timing on;
SQL> 
SQL> declare
  2    vDummy varchar2(2000);
  3  begin
  4    for i in 1..&cnt
  5    loop
  6      vDummy := detest.test1('z');
  7      vDummy := detest.test1('s');
  8    end loop;
  9  end;
 10  /
old   4:   for i in 1..&cnt
new   4:   for i in 1..10000000

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.77
SQL> 
SQL> declare
  2    vDummy varchar2(2000);
  3  begin
  4    for i in 1..&cnt
  5    loop
  6      vDummy := detest.test2('z');
  7      vDummy := detest.test2('s');
  8    end loop;
  9  end;
 10  /
old   4:   for i in 1..&cnt
new   4:   for i in 1..10000000

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.46
SQL> spool off