DanBrother的部落格

PL/SQL Interpreted & Native Compilation Comparison Testing

conn testuser
Enter password:
Connected.

set feedback on
create or replace procedure PROC_TEST_A
is
v_start_time number;
v_plsql_unit varchar2(30) default null;
v_ct simple_integer default 0;
v_code_type varchar2(100) default null;

begin
   v_start_time:=DBMS_UTILITY.get_time;
   v_plsql_unit:=$$PLSQL_UNIT; -- get current PL/SQL unit name
   for i in 1..100000000 loop
         v_ct:=v_ct+1;
   end loop;

   select plsql_code_type into v_code_type
   from user_plsql_object_settings
   where name = v_plsql_unit;

  dbms_output.put_line('-----------------------------------------------------------------');
  dbms_output.put_line(v_plsql_unit||' => '||v_code_type||' elapsed time:'||(DBMS_UTILITY.get_time-
  v_start_time)||' hsecs');
end;
/

-- Create PROC_TEST_B by copying content from PROC_TEST_A
declare
v_text long;
begin
    select replace(dbms_metadata.get_ddl('PROCEDURE','PROC_TEST_A','TESTUSER'),
    'PROC_TEST_A','PROC_TEST_B')
     into v_text from dual;
    execute immediate v_text;
end;
/

ALTER PROCEDURE PROC_TEST_A COMPILE PLSQL_CODE_TYPE=INTERPRETED;
ALTER PROCEDURE PROC_TEST_B COMPILE PLSQL_CODE_TYPE=NATIVE;

col name format a12
col plsql_code_type format a20
select name,type,plsql_code_type from user_plsql_object_settings
where name in ('PROC_TEST_A','PROC_TEST_B');
>>
NAME                  TYPE                         PLSQL_CODE_TYPE
-----------------  -------------------- --------------------
PROC_TEST_A   PROCEDURE           INTERPRETED
PROC_TEST_B   PROCEDURE           NATIVE

set SERVEROUTPUT on FEEDBACK off SQLPROMPT "-"
exec PROC_TEST_A
exec PROC_TEST_B

-----------------------------------------------------------------
PROC_TEST_A => INTERPRETED elapsed time:156 hsecs
-----------------------------------------------------------------
PROC_TEST_B => NATIVE elapsed time:17 hsecs

PS. hsecs means 100th of a second.