Caching SQL

 
-- you need to run the following grant as sysdba to allow this example to work:
-- grant execute on dbms_lock to 

create procedure a_way_to_make_this_slow_fn as
begin
   dbms_lock.sleep(10); --for 10 seconds
end;
/

create procedure lightning_quick as
begin
  null;
end;
/

create procedure on_second_execution as
begin
  null;
end;
/

create function use_the 
  return number result_cache as
begin
  a_way_to_make_this_slow_fn();
  lightning_quick();
  on_second_execution();

  return 1;
end;
/

var x number;

set timing on

begin :‌x := use_the; end;
/
begin :‌x := use_the; end;
/

set timing off
drop function use_the ;
drop procedure a_way_to_make_this_slow_fn;

drop procedure lightning_quick;

drop procedure on_second_execution;

-- remember to revoke the privilege granted if it wasn't already!
-- revoke execute on dbms_lock from

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)