Thanks.

are you saying that an SQL query that is encapsulated
in a C program will run at virtually the same speed,

but that if I convert a PLSQL program - say a cursor
loop that summarizes the data - to C, moving the loop
constructs to C, that the C program will run up to 2x?

Or are you saying something completely different?

I'm saying something totally different.

We are talking about ncomp'ed PLSQL (natively compiled plsql).  In 9i, when you compile a procedure, you can "ncomp" it.  

Normally, PLSQL is processed like this:

you write it -> we compile it into p-code -> you run it -> we interpret the p-code

With ncomping it is like this:

you write it -> we turn it INTO c -> we compile that into a .obj or .o file ->
  we link that into the database kernel -> you run it -> we just call a subroutine


It is like using a JIT compiler with java for example but this is a WAT compiler (way ahead of time).

Here is a quick and dirty example I use:

ops$tkyte@ORA9I.WORLD> @demo021   
=================================================================


make sure the settings are setup right for NCOMP'ed plsql


ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> alter system set plsql_native_make_utility = 'make';

System altered.

ops$tkyte@ORA9I.WORLD> alter system set plsql_native_make_file_name = '/export/home/oracle9i/plsql/spnc_makefile.mk';

System altered.

ops$tkyte@ORA9I.WORLD> alter session set plsql_compiler_flags = 'INTERPRETED';

Session altered.

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> set echo off
Enter to continue

=================================================================

we'll compile an interpreted one (default) first


ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> alter session set plsql_compiler_flags = 'INTERPRETED';

Session altered.

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> create or replace
  2  function factorial_interpreted( p_n in number ) return number
  3  is
  4  begin
  5      if ( p_n = 1 )
  6      then
  7          return 1;
  8      else
  9          return factorial_interpreted( p_n-1 ) * p_n;
 10      end if;
 11  end;
 12  /

Function created.

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> set echo off
Enter to continue

=================================================================

and now NATIVE (too easy)


ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> alter session set plsql_compiler_flags = 'NATIVE';

Session altered.

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> create or replace
  2  function factorial_native( p_n in number ) return number
  3  is
  4  begin
  5      if ( p_n = 1 )
  6      then
  7          return 1;
  8      else
  9          return factorial_native( p_n-1 ) * p_n;
 10      end if;
 11  end;
 12  /


Function created.

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> set echo off
Enter to continue
=================================================================

and the race is on...


ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> alter session set plsql_compiler_flags = 'INTERPRETED';

Session altered.

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> declare
  2      l_start number;
  3      l_n     number;
  4  begin
  5      l_start := dbms_utility.get_time;
  6      for i in 1 .. 1000
  7      loop
  8          l_n := factorial_interpreted( 50 );
  9      end loop;
 10      dbms_output.put_line( (dbms_utility.get_time-l_start) ||
 11          ' hsecs...' || l_n );
 12  
 13      l_start := dbms_utility.get_time;
 14      for i in 1 .. 1000
 15      loop
 16          l_n := factorial_native( 50 );
 17      end loop;
 18      dbms_output.put_line( (dbms_utility.get_time-l_start) ||
 19          ' hsecs...' || l_n );
 20  end;
 21  /
45 hsecs...30414093201713378043612608166064768844300000000000000000000000000
27 hsecs...30414093201713378043612608166064768844300000000000000000000000000

PL/SQL procedure successfully completed.

ops$tkyte@ORA9I.WORLD> /
46 hsecs...30414093201713378043612608166064768844300000000000000000000000000
26 hsecs...30414093201713378043612608166064768844300000000000000000000000000

PL/SQL procedure successfully completed.

ops$tkyte@ORA9I.WORLD> 

 

Tom kindly exlain about .obj , .o and pcode .. what are they

Hello TOM,
  While creating the ncomp i'm geting the following error. Is there any setting problems.

SQL>  alter session set plsql_compiler_flags = 'NATIVE';

Session altered.

SQL>  alter system set plsql_native_library_dir='/home';

System altered.

SQL> 
SQL>  create or replace   function factorial_native( p_n in number )
  2     return number
  3       is
  4       begin
  5           if ( p_n = 1 )
  6           then
  7               return 1;
  8           else
  9               return factorial_native( p_n-1 ) * p_n;
 10          end if;
 11      end;
 12   /

Warning: Function created with compilation errors.

SQL> sho err
Errors for FUNCTION FACTORIAL_NATIVE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PLS-00801: internal error [79704]
SQL> 


 one more doubt,
       Will it store any files in the operating system for this files.

Thanks and awaiting for your reply.

with regards,
siva 

Hello TOM,
I forgot to enter in the previous mail. I set this parameters too.

alter system set plsql_native_make_file_name =
'/opt/oracle/oraHome1/plsql/spnc_makefile.mk'
/
alter system set plsql_native_make_utility = 'make';

Thanks

with regards,
siva

thanks for the clarification... I am trying to become aware of the latest features, so this really helped.

btw, reading your message a second time leaves me with
another question - you say 0x to 2x, when I think you
mean 1x (no time improvement) to 2x (calculations take
about half the time.) Perhaps its semantical, but its
relevant to ask if its possible to LOSE performance by
ncomping a routine?

thanks again, George

Thanku Tom!
How to set the plsql_* parameters in initSID.ora

we compile it into p-code.
I'm sorry for this simple question. What is p_code?

Hi Tom,
This is on solaris 2.8/ oracle 9.2.0.1.
I read an article on OTN about native compilation of all oracle pl/sql packages such as standard, dbms*, etc. I created a new db instance with all plsql native compilation parameters set and sure enough it produced STANDARD__SYS__2.so etc objects in $ORACLE_HOME/plsql/userlibs directory and the instance seems to be OK. My questions:

1. would it help or hurt ncomp'ing all oracle packages?

2. i had to create a new db instance on this same machine. Again I set ncomp parameters. Oracle went ahead and re-compiled all the same objects under plsql/userlibs dir.. I think this is kind of redundant. I thought oracle would either name these objects specific to every instance ,or sense that natively compiled objects already exist and not overwrite them. can you please comment on this.

after I set up system as following:

alter system set plsql_native_make_file_name = '/usr/local/oracle/product/9.2.0/plsql/spnc_makefile.mk' ;
alter system set plsql_native_make_utility = 'make';
alter system set plsql_native_library_dir='/usr/local/oracle/product/9.2.0/plsql/plsql_libs';
alter system set plsql_native_library_subdir_count = 1000;

and I verified with SA that we have the Sun Workshop 6 C Compiler installed. but still, I got following error after I submitted ' alter session set plsql_compiler_flags=native;' and tried to compile the procedure:

Errors for PROCEDURE POP_EMP_APRVD_RGSTN_TMP:

LINE/COL ERROR
--------
0/0 PLS-00801: internal error [79704]

what else did I miss here? any other profile setting on OS level?

thanks!

Tom,

thanks for the information you provided. However after I changed the mode of directory to 777 for plsql_native_library_dir, and changed the plsql_native_c_compiler to the specific path where c compiler sits, I still get the error message. all the settings are as following:
NAME TYPE VALUE
------------------------------ ------- --------------
plsql_compiler_flags string INTERPRETED
plsql_native_c_compiler string /opt/SUNWspro/bin/cc
plsql_native_library_dir string /usr/local/oracle/product/9.2.0/plsql/plsql_libs
plsql_native_library_subdir_count integer 1000
plsql_native_linker string /usr/ccs/bin/ld
plsql_native_make_file_name string
/usr/local/oracle/product/9.2.0/plsql/spnc_makefile.mk
plsql_native_make_utility string make

and before recompiling, I did:
alter session set plsql_compiler_flags=native;

I have verified that the make file and native_linker is right in place. Is there any other trick to make it work?! thanks!

here is the output for the requested info on the unix DB is on:

drwxr-xr-x 37 root sys 1024 Oct 15 2002 /usr
drwxr-xr-x 18 root root 512 Nov 1 12:52 /usr/local
drwxr-xr-x 23 oracle dba 2048 Apr 21 10:21 /usr/local/oracle
drwxr-xr-x 8 oracle dba 1024 Jun 21 2002 /usr/local/oracle/product
drwxr-xr-x 50 oracle dba 1024 Jan 15 11:11 /usr/local/oracle/product/9.2.0
drwxrwxr-x 9 oracle dba 1024 Apr 9 17:49 /usr/local/oracle/product/9.2.0/plsql
drwxrwxrwx 2 oracle dba 96 Apr 17 13:10 /usr/local/oracle/product/9.2.0/plsql/plsql_libs

the account I tried to compile pl/sql in native is non-dba account. As always, thank you for looking into it.

Is it true that compiled coce of a pl/sql program unit is mapped into the PGA rather than in Shared pool (sga) in the case native compilation? If this is the case, if the session logs off that piece of compiled code is lost. How this helps in sharing? I am a little confused on this. Could you please help in clarifying this. Thanks.

Tom, I read your explanation above for native compiled code that is managed by the OS. If that is the case, can I configure a smaller shared pool compared to interpreted codes? Is my understanding correct that interpreted pl/sql codes are placed in the shared pool now that memory space is saved in the shared pool if i used native compilation? Is this an advantage with regard to smaller shared pool? Thanks.

as always, thanks for your help.. this discussion has already been helpful.

Is it possible to set Native compilation on database startup?

Thanks, George

Hi Tom,

I am testing native compilation with the exact same example you used. I had to increase the loop by 10 times because it was executing in 7 hsecs. 

Both native and interpreted execute in exactly 78 hsecs. No change. I know you said that you may get from same performance, up to 2 times performance, but still this is a case where native compilation should have made a difference.

Connected to:
Oracle9i Release 9.2.0.4.0 - Production
JServer Release 9.2.0.4.0 - Production

SQL> select object_name, object_type, param_value
  2  from dba_stored_settings where object_name like 'KUTRO%' and param_name = 'plsql_compiler_flags';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------
PARAM_VALUE
--------------------------------------------------------------------------------
KUTRO_INTER                    FUNCTION
INTERPRETED,NON_DEBUG

KUTRO_NATIVE                   FUNCTION
NATIVE,NON_DEBUG


SQL> set serverout on
SQL> alter session set plsql_compiler_flags = 'INTERPRETED';

Session altered.

SQL> declare
    l_start number;
    l_n     number;
begin
    l_start := dbms_utility.get_time;
    for i in 1 .. 10000
  2    3    4    5    6    7      loop
        l_n := kutro_native( 50 );
    end loop;
    dbms_output.put_line( (dbms_utility.get_time-l_start) ||
        ' hsecs...' || l_n );

  8    9   10   11   12   13      l_start := dbms_utility.get_time;
    for i in 1 .. 10000
    loop
        l_n := kutro_inter( 50 );
    end loop;
    dbms_output.put_line( (dbms_utility.get_time-l_start) ||
        ' hsecs...' || l_n );
 14   15   16   17   18   19   20  end;
/
 21  
77 hsecs...30414093201713378043612608166064768844300000000000000000000000000
80 hsecs...30414093201713378043612608166064768844300000000000000000000000000

PL/SQL procedure successfully completed.

SQL> SQL> /
78 hsecs...30414093201713378043612608166064768844300000000000000000000000000
79 hsecs...30414093201713378043612608166064768844300000000000000000000000000

PL/SQL procedure successfully completed.

SQL> /
73 hsecs...30414093201713378043612608166064768844300000000000000000000000000
73 hsecs...30414093201713378043612608166064768844300000000000000000000000000

PL/SQL procedure successfully completed.

SQL> 

Any comments?
 

Thanks Tom, 

I tried it, and now I can clearly see that the native one is much faster.

Here's the code I used:

alter session set plsql_compiler_flags = 'NATIVE';
create or replace function kutro_native(p_n in number,loop_times in number default null)
  return number
    is
    result number;
    begin
        if ( p_n = 1 )
        then
            return 1;
        else
          if loop_times is not null then
            for n in 1..loop_times loop
              result:=kutro_native( p_n-1) * p_n;
            end loop;
          else
            result:= kutro_native( p_n-1) * p_n;
          end if;
       end if;
       return result;
    end;
/


alter session set plsql_compiler_flags = 'INTERPRETED';
create or replace function kutro_inter(p_n in number,loop_times in number default null)
  return number
    is
    result number;
    begin
        if ( p_n = 1 )
        then
            return 1;
        else
          if loop_times is not null then
            for n in 1..loop_times loop
              result:=kutro_inter( p_n-1) * p_n;
            end loop;
          else
            result:= kutro_inter( p_n-1) * p_n;
          end if;
       end if;
       return result;
    end;
/
----------------------------------------
And the race:

set serverout on
alter session set plsql_compiler_flags = 'INTERPRETED';
declare
    l_start number;
    l_n     number;
begin
    l_start := dbms_utility.get_time;
    l_n := kutro_native( 50,10000 );
    dbms_output.put_line( (dbms_utility.get_time-l_start) ||
        ' hsecs...' || l_n );

    l_start := dbms_utility.get_time;
    l_n := kutro_inter( 50,10000 );
    dbms_output.put_line( (dbms_utility.get_time-l_start) ||
        ' hsecs...' || l_n );
end;
/

SQL> /
75 hsecs...30414093201713378043612608166064768844300000000000000000000000000
138 hsecs...30414093201713378043612608166064768844300000000000000000000000000

PL/SQL procedure successfully completed.

SQL> /
76 hsecs...30414093201713378043612608166064768844300000000000000000000000000
138 hsecs...30414093201713378043612608166064768844300000000000000000000000000

PL/SQL procedure successfully completed.

SQL> /
74 hsecs...30414093201713378043612608166064768844300000000000000000000000000
137 hsecs...30414093201713378043612608166064768844300000000000000000000000000


Just for reference this is Linux AS 2.1 with
cat /proc/cpuinfo
...
model name      : Intel(R) Xeon(TM) CPU 1.40GHz
stepping        : 1
cpu MHz         : 1397.212
cache size      : 256 KB
...

Thanks.
 

Hi Tom,

How do we determine in what mode a procedure/package/function was compiled? Whether NATIVE or INTERPRETED. We get errors trying to compile objects and then change the plsql_compiler_flags to INTERPRETED and then compiles.

Being a very computational intensive application,I was looking to convert all the code to NATIVE from the present INTERPRETED. I read somewhere that to do so at the database level, change the parameter PLSQL_COMPILER_FLAGS and the other parameter required for NATIVE settings and then run utlirp.sql to invalidate and then validate with NATIVE compilation. Is this the right thing to do? Because when this script started running, it exited with errors like :
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of
SYS.CDC_DROP_CTABLE_BEFORE
ORA-04098: trigger 'SYS.CDC_ALTER_CTABLE_BEFORE' is invalid and failed re-validation

and when I tried to compile the trigger:

3/48 PLS-00905: object SYS.DBMS_CDC_PUBLISH is invalid

What is the right approach to convert my database to NATIVE.

SQL> @utlirp
   SELECT obj# FROM trigger$ t WHERE
                    *
ERROR at line 2:
ORA-04045: errors during recompilation/revalidation of
SYS.CDC_CREATE_CTABLE_BEFORE
ORA-04098: trigger 'SYS.CDC_ALTER_CTABLE_BEFORE' is invalid and failed
re-validation



6 rows updated.


Commit complete.


System altered.


5 rows updated.


Commit complete.


System altered.


Package created.


Package body created.


Grant succeeded.


Package created.


Synonym created.


Grant succeeded.


PL/SQL procedure successfully completed.


System altered.


6 rows updated.


Commit complete.

DROP TABLE UTLIP_TEMP
*
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of
SYS.CDC_DROP_CTABLE_BEFORE
ORA-04098: trigger 'SYS.CDC_ALTER_CTABLE_BEFORE' is invalid and failed
re-validation

The script exited at this place. Thanks for the prompt response. 

SQL> alter trigger SYS.CDC_ALTER_CTABLE_BEFORE compile;

Warning: Trigger altered with compilation errors.

SQL> sho err
Errors for TRIGGER SYS.CDC_ALTER_CTABLE_BEFORE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/7      PL/SQL: Statement ignored
3/48     PLS-00905: object SYS.DBMS_CDC_PUBLISH is invalid 

I am not getting  anywhere. The trigger calls the package and the package looks at the trigger.

Compiling triggers gives error pointing to the package SYS.DBMS_CDC_PUBLISH 
SQL> alter trigger SYS.CDC_ALTER_CTABLE_BEFORE compile;

Warning: Trigger altered with compilation errors.

SQL> sho err
Errors for TRIGGER SYS.CDC_ALTER_CTABLE_BEFORE:

LINE/COL ERROR
-------- --------------------------------------------------------------
3/7      PL/SQL: Statement ignored
3/48     PLS-00905: object SYS.DBMS_CDC_PUBLISH is invalid

When compiling the package gives the error:


SQL> ALTER PACKAGE DBMS_CDC_PUBLISH COMPILE;
ALTER PACKAGE DBMS_CDC_PUBLISH COMPILE
*
ERROR at line 1:
ORA-04098: trigger 'SYS.CDC_ALTER_CTABLE_BEFORE' is invalid and failed re-validation.

Yes Tom, its a Test box thankfully.



 

I am in the process of creating db on interpreted and then having one schema under native mode.
If I have an export dump that was generated with INTERPRETED, how do I go about importing with NATIVE.
If I import first and then recompile all objects of that schema, will all objects get compiled with NATIVE. Will there be any issues with TYPE objects.

The db is on INTERPRETED mode. I did the following:
create or replace procedure p3
as
begin
dbms_lock.sleep(1);
end;
/
Procedure created.
create type t2 as object(a number);
/
create table t2_tbl of t2;

alter session set plsql_compiler_flags='NATIVE';

alter procedure p3 compile;
Procedure altered.

alter type t2 compile;

ORA-02311: cannot alter with COMPILE option a valid type with type or table dependents

So this is where the issue is.

select * from user_stored_settings where object_name in ('P3','T2') and param_name like 'plsql%';

T2 29051 TYPE plsql_compiler_flags INTERPRETED,NON_DEBUG

P3 29049 PROCEDURE plsql_compiler_flags NATIVE,NON_DEBUG

What you meant by code, is it TYPE BODY? I did add a body to the TYPE and without any table created of that TYPE, the compilation is effective. All our TYPE objects do have tables created of it. So the schema would have both INTEPRETED and NATIVE objects. Is there a way to get to a uniform compiler flag for the whole schema?

Thanks for your time Tom.

I could get the db to be on Native mode. There were issues with compiler and the plsql_native_library_subdir_count=1000. Changing this to 0 made the difference with the compiler change.

So with this db on NATIVE, if I import an INTERPRETED dump, will all the objects get compiled as NATIVE by default or do we have to compile them after import? Also as per your guideline, the TYPEs should be NATIVE after import with sytem level flag as NATIVE.

Just clarifying the point. Thanks again.

I did the import(INTERPRETED) on a NATIVE db. The object got created with INTERPRETED mode. And as I had mentioned before, when there are table dependencies, the TYPE objects compilation does not go through. While all other objects (procedures,functions etc..) gets compiled in NATIVE mode withou any problem. So what I am doing is:

a. import first without rows.
b. drop the tables of TYPE objects( after creating a create table script of the TYPE objects).
c. compile the TYPE and TYPE body and all other objects as NATIVE.
d. create back those tables.
e. import the data.

Hopefully, I expect this to work. Shall keep the results posted. Meanwhile, your opinion on the above steps ?

Yes. It's a task. But when I try to import the data only, there is a conflict of Object Identifer as the table was created locally after dropping and natively compiling the TYPE. I have read in one of your threads about going through this issue, but am unable to find it. Any suggestions at this point?

Thanks

Tom,

1. Can you compare Pro*C Code with Native Compiled PL/SQL code on performance? I've calculation intensive Pro*C code and have the same logic implemented in native compiled PL/SQL. Which one will be faster?

2. Can you compare the size of interpreted PL/SQL code with native compiled PL/SQL code?

3. Can I say that native compilation has obviated or eliminated the need of Pro*C? If no, Why?


4. Where are native compiled codes (.obj, .o etc) stored? Are they in Database or OS?

5. How to find if a procedure is native compiled? Is there any system table for the purpose?

6. Source code of a procedure is stored/viewed in/from USER_SOURCE. Where is p-code stored in the database? How to find it?

7. Will I have to re-compile native compiled procedures when the database is up-graded, for example from 9i to 10g?

Thanks in advance.

1) depends.  do I have to pull a million rows out of the database, over the network to my pro*c application running on another machine-- compute something -- and then put it back?  

maybe plsql interpreted would beat it just because of the network.
maybe not.

this is an unanswerable question, you would "benchmark" a prototype implementation.


2) no, one is pcode stored in database tables, interpreted by a Virtual machine.  the other is object code, stored in OS files (or blobs in 10g) and run by the OS.  apples and toaster ovens

3) no.  ncomped code is generally (mostly, not always) faster than interpreted by a small margin.  I can write some nasty bad pro*c code that outperforms plsql by many orders of magnitude (most recent case was to cartesian product a table with itself and compute the distance between two strings for a name match.  it would have taken years in sql, weeks in plsql, we did it overnight in pro*c running in parallel).

there will probably never be "a language", "a universal solution" for all problems.

4) os in 9i, either or in 10g

5) 
ops$tkyte@ORA9IR2.US.ORACLE.COM> alter session set plsql_compiler_flags = 'INTERPRETED';
 
Session altered.
 
ops$tkyte@ORA9IR2.US.ORACLE.COM>
ops$tkyte@ORA9IR2.US.ORACLE.COM> create or replace
  2  function factorial_interpreted( p_n in number ) return number
  3  is
  4  begin
  5          if ( p_n = 1 )
  6          then
  7                  return 1;
  8          else
  9                  return factorial_interpreted( p_n-1 ) * p_n;
 10          end if;
 11  end;
 12  /
 
Function created.
 
 
ops$tkyte@ORA9IR2.US.ORACLE.COM>
ops$tkyte@ORA9IR2.US.ORACLE.COM>
ops$tkyte@ORA9IR2.US.ORACLE.COM> alter session set plsql_compiler_flags = 'NATIVE';
 
Session altered.
 
ops$tkyte@ORA9IR2.US.ORACLE.COM>
ops$tkyte@ORA9IR2.US.ORACLE.COM> create or replace
  2  function factorial_native( p_n in number ) return number
  3  is
  4  begin
  5          if ( p_n = 1 )
  6          then
  7                  return 1;
  8          else
  9                  return factorial_native( p_n-1 ) * p_n;
 10          end if;
 11  end;
 12  /
 
Function created.
 
ops$tkyte@ORA9IR2.US.ORACLE.COM>
ops$tkyte@ORA9IR2.US.ORACLE.COM> set echo off
Enter to continue
 
=================================================================
 
and the race is on...
 
 
ops$tkyte@ORA9IR2.US.ORACLE.COM>
ops$tkyte@ORA9IR2.US.ORACLE.COM>
ops$tkyte@ORA9IR2.US.ORACLE.COM> alter session set plsql_compiler_flags = 'INTERPRETED';
 
Session altered.
 
ops$tkyte@ORA9IR2.US.ORACLE.COM>
ops$tkyte@ORA9IR2.US.ORACLE.COM> declare
  2          l_start number;
  3          l_n     number;
  4          l_e1    number;
  5          l_e2    number;
  6  begin
  7          l_start := dbms_utility.get_time;
  8          for i in 1 .. 5000
  9          loop
 10                  l_n := factorial_interpreted( 50 );
 11          end loop;
 12          l_e1 := dbms_utility.get_time-l_start;
 13          dbms_output.put_line( l_e1 || ' hsecs...' || l_n );
 14
 15          l_start := dbms_utility.get_time;
 16          for i in 1 .. 5000
 17          loop
 18                  l_n := factorial_native( 50 );
 19          end loop;
 20          l_e2 := dbms_utility.get_time-l_start;
 21          dbms_output.put_line( l_e2 || ' hsecs...' || l_n );
 22
 23          dbms_output.put_line( l_e2 || ' is ' || round(l_e2/l_e1*100,0) || '% of ' || l_e1 );
 24  end;
 25  /
30 hsecs...30414093201713378043612608166064768844300000000000000000000000000
17 hsecs...30414093201713378043612608166064768844300000000000000000000000000
17 is 57% of 30
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2.US.ORACLE.COM>
ops$tkyte@ORA9IR2.US.ORACLE.COM> set echo off
OBJECT_NAME                   : FACTORIAL_INTERPRETED
OBJECT_ID                     : 32227
OBJECT_TYPE                   : FUNCTION
PARAM_NAME                    : plsql_compiler_flags<b>
PARAM_VALUE                   : INTERPRETED,NON_DEBUG</b>
-----------------
OBJECT_NAME                   : FACTORIAL_NATIVE
OBJECT_ID                     : 32228
OBJECT_TYPE                   : FUNCTION
PARAM_NAME                    : plsql_compiler_flags<b>
PARAM_VALUE                   : NATIVE,NON_DEBUG</b>
-----------------
 
PL/SQL procedure successfully completed.


6) in idl$ tables in the sys schema, not very interesting really.

7) invalidation and recompilation generally happens as part of an upgrade yes. 

Tom,

Is it possible to native compile unanimous PL/SQL blocks?

Tom,

Can we use any C compiler ( Turbo, Borland C etc ) for Native Compilation in Windows or UNIX?

Thanks in advance.

This is good web site that i got more important information about pl/sql progrmming. I hope that this web site will help me in future. And I would like to give most expensive thanks to the web site. I hope that I will get more important help from this web site. Thanks

sudanand2001@yahoo.com

<quote>obj and o = object code. What C, and many other languages get compiled into. This is "machine code" -- the bits and bytes the OS will run.<quote>

When one accesses a pl/sql procedure compiled "native", where OS will load the C code to run it? Which memory? pga or ? It has to load it to some memory before it executes? Thanks.

If it is using os memory, how do I know how much memory I should have to process, if I am using "native"? Is there a way to setimate? Thanks.

There are many articles on *.oracle.com describing advantages of 10g native PL/SQL compilation.
Wow.
Seems, in every new 10g database i'm going to create, all PL/SQL including SYS schema, should be nativelly compiled (just by setting init.ora parameters PLSQL_CODE_TYPE=NATIVE, PLSQL_NATIVE_LIBRARY_DIR before database creation)...

BUT,
Why default value for PLSQL_CODE_TYPE still is INTERPRETTED..?

Let assume, my application use a lot of stored PL/SQL units, but algorithms are not "computationally-or-loop-intensive". Even let assume, my application does not use PL/SQL at all.

In that case, what potential problems/limitations, what DISADVANTAGES have native PL/SQL compared to interpretted?

Tom, u r simply amazing..!!!
u r born with ultimate brains...!!!
u make things look simple and easy to understand, u r my Guru..!!

So, i'm going to use interpretted mode after migration to 10g, like You did.
Some cons of native are clear for me:
1.) cost of C compiler licenses for our platform
2.) additional considerations needed regarding backup/recovery, failover, OS and Oracle upgrades/patching

Theorethically, interpretted virtual machine code (PL/SQL and Java for example) offer more robustness.

Thank You,
Sincerely

Hello Tom!

We are using 9.2.0.5.21 on IBM z/OS 1.5.
The release notes said that native compilation is not supported on this platform.

It would be a great help to know whether this means that it will not be supported on this platform in the future or simply it is not ready yet but Oracle is working on.

We are facing some heavy calculations (with no SQL) and the performance is just on the acceptable level. I think it would be a possible improvement point to ncomp our packages.

Thank you!

Hi Tom

Good morning. I have a query . Is there any TABLE or VIEW from
which we can see the p-code that has been generated for a subprogram ?
I mean , can we access the p-code (as compared to Source Code from
ALL_SOURCE etc) for a subprogram ?

Another point I'd like to bring into your consideration .I am
having one confusion for a long time . It's regarding CURSOR in ORACLE .
What actually happens in Oracle , when we declare and open a
CURSOR ? In many books ,articles and websites , i have seen that
Oracle doesn't copy the data anywhere in the MEMORY . But in some
books/articles , they are saying that when we declare/open a CURSOR ,
it's just a pointer to the table and it stores the data in a virtual
table and it's called CONTEXT AREA . If it's true , then where in the
Virtual Table resides ? What actually is that CONTEXT AREA ? And if
it's true , then ORACLE might store
the data(let's assume in the case
of a ONE-MILLION-ROW table !!!!) into that VIRTUAL TABLE.... !!!!
I think it's not logical to think that way . But , if it doesn't store the data , then ONE-MILLION times I/O contention(with datafile) must be there !!!! Here comes the confusion .
I am posting a Stored Procedure over here . Hope you have EMP table(in Oracle database) to run this procedure .What I wanted to show is
(1) I am declaring a cursor
(2) Then I am deleting the table data as well as doing a commit . That
means there is no data in the emp table .
(3) Now , when i am fetching the data through CURSOR(after deleting
the table data !!!!! ), I am still being able to get that data .

Probably the reason for this is that when I am deleting the Table data , it's going to the Rollback Segement . And this Stored Procedure is being able to retrieve the data from the Rollback Segment . But if I use TRUNCATE instead of DELETE-n-COMMIT , this Stored Procedure will produce "ORA-08103: object no longer exists" error in the output.

/************** start of SP ***************************************************/

Procedure TEST_CURSOR( P_EMPNO IN NUMBER )
IS
-- Purpose: To see where in the memory structure
-- Oracle stores data when a Cursor is declared/opened .
-- Person : Debashis Payin Date : Monday, 14 February 2005
CURSOR C_EMP IS
SELECT ENAME
FROM EMP
WHERE EMPNO = P_EMPNO ;
R_EMP C_EMP%ROWTYPE ;

BEGIN
OPEN C_EMP ;
DELETE FROM EMP; /* delete the table data */
COMMIT; /* do a COMMIT also !!!! */
LOOP
FETCH C_EMP INTO R_EMP;
EXIT WHEN C_EMP%NOTFOUND ;
DBMS_OUTPUT.PUT_LINE('The employee is : ' || R_EMP.ENAME );
END LOOP ;

CLOSE C_EMP;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error ' || SQLERRM);
END; -- Procedure TEST_CURSOR

/************** end of SP ***************************************************/


Plz note : There is nothing wrong in this procedure . I don't want any rectification to this Stored Procedure .
What i want is to know the behavior of CURSOR .



Thanks
Debahis Payin
xnutron@yahoo.co.in

Hi Tom

Good morning . Thanks for your reply . Got the confusion cleared . But still having a query . Your answer implies that , when we open a CURSOR ( be it a OPEN statement or a CURSOR FOR LOOP )Oracle generally doesn't copy the data somewhere else . But if the query involves some sorting/calculation operation on that data , Oracle might decide to store the data into some temporaray table . Ok .. now could you please tell me that where in the temporay data get stored ?

Thanks in advance

Debashis Payin
Bangalore

I am getting error while compiling plsql in native mode.

1  create or replace
 2    function factorial_native( p_n in number ) return number
 3      is
 4    begin
 5        if ( p_n = 1 )
 6       then
 7           return 1;
 8       else
 9           return factorial_native( p_n-1 ) * p_n;
10       end if;
11*  end;
12  /

arning: Function created with compilation errors.

QL> show error
rrors for FUNCTION FACTORIAL_NATIVE:

INE/COL ERROR
------- -----------------------------------------------------------------
/10     PLS-00925: native compilation failed: Unable to create C file
        (c:\ORACLE\PRODUCT\10.1.0\ORADATA\TORONTO\natlib\d156\
        FACTORIAL_NATIVE__SYS__F__53715.c)


SQL> show parameter plsql

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
plsql_code_type                      string      NATIVE
plsql_compiler_flags                 string      NATIVE, NON_DEBUG
plsql_debug                          boolean     FALSE
plsql_native_library_dir             string      c:\ORACLE\PRODUCT\10.1.0\ORADA
                                                 TA\TORONTO\natlib
plsql_native_library_subdir_count    integer     1000
plsql_optimize_level                 integer     2
plsql_v2_compatibility               boolean     FALSE
plsql_warnings                       string      ENABLE:ALL
SQL>
 

Yes the folder natlib is there in c:\ORACLE\PRODUCT\10.1.0\ORADATA\TORONTO\natlib.
However d156 directory is not created.
As per the 10g plsql user's guide, spnc_commands file is in c:\oracle\product\10.1.0\db_2\plsql directory.
I have xp home and amd athlon xp 3000 processor. I have also visual 6.0 c++ installed on the pc. IS there any specific configuration required in spnc_command file. How is d156 directory created?

Sometimes as a DBA you inherit things. If we have an application that uses a bunch of views that have functions in them (mostly converting back and forth from oracle date fields into 6 and 8 digit character fields ... sigh) there are some cpu consumption issues.

Is it possible to think about re-compiling the functions into native?

What's the impact on a view that uses those functions? Can you recompile a view native?

There are some triggers also that use the functions and I am assuming that can recompile those?

Any roadblocks or things I am not thinking about correctly?

Thanks in advance

<quote>
And use scalar subqueries to call the function, instead of:
where x = F();
use
where x = (select f() from dual)
</quote>

Can you please explain why. It seems to me that it runs slower and makes more latches. Or am I missing a point?
Thanks

ops$tkyte@ORA9IR2> create or replace function f return varchar2
  2  as
  3  begin
  4          dbms_application_info.set_client_info( userenv('client_info')+1 );
  5          /* assume we do something here that takes 0.01 seconds */
  6          return 'Hello World';
  7  end;
  8  /
 
Function created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_application_info.set_client_info(0);
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select * from scott.emp where ename = f();
 
no rows selected
 
ops$tkyte@ORA9IR2> select userenv('client_info') from dual;
 
USERENV('CLIENT_INFO')
----------------------------------------------------------------
14
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_application_info.set_client_info(0);
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select * from scott.emp where ename = (select f() from dual);
 
no rows selected
 
ops$tkyte@ORA9IR2> select userenv('client_info') from dual;
 
USERENV('CLIENT_INFO')
----------------------------------------------------------------
1
 


If the function ends up getting called once per row, that could be "bad" (context switch back and forth and back and forth).

Even if the function takes inputs:

ops$tkyte@ORA9IR2> create or replace function f(p_job in varchar2) return varchar2
  2  as
  3  begin
  4          dbms_application_info.set_client_info( userenv('client_info')+1 );
  5          /* assume we do something here that takes 0.01 seconds */
  6          return lower(p_job);
  7  end;
  8  /
 
Function created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_application_info.set_client_info(0);
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select * from scott.emp where ename = f(job);
 
no rows selected
 
ops$tkyte@ORA9IR2> select userenv('client_info') from dual;
 
USERENV('CLIENT_INFO')
----------------------------------------------------------------
14
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_application_info.set_client_info(0);
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select * from scott.emp where ename = (select f(job) from dual);
 
no rows selected
 
ops$tkyte@ORA9IR2> select userenv('client_info') from dual;
 
USERENV('CLIENT_INFO')
----------------------------------------------------------------
5


The scalar subquery caching that kicks in can be very beneficial in reducing the number of invocations.

 

Hi Tom.
We are thinking to change to native compilation one of our Database (9.2.0.4) from interpreted.
I read some not official information and also some bulletin in Metalink. But (maybe because a language issue) I still have a dude about change our own development (our PL/SQL) and the built-in ones (that ones that come with Oracle the first time you install a new database).
Most of the advices I found in the net talk abut change EVERYTHING or NOTHING. That means that we should re-compile our all development and also all those PL/SQL that comes with Oracle from the "manufactury".
Four us, this is a key information to take the right decision.

Thank you for advance, Alfredo Diaz.

I don´t think the answer in the last followup was the right one.
Anyway, if somebody else have the same dude, I got some new from Metalink Guys :

From: Oracle, Asim chowdhury 30-Jun-05 06:42
Subject: Re : What about the built-in Pl/SQL, should I change to NATIVE too ??

Correct,you need to compile the built in package also.
Please refer the note 151224.1
Also please go through the following site
</code> http://otn.oracle.com/tech/pl_sql/htdocs/ncomp_faq.html#c_compiler <code>

Regards
Asim

Tom,
I understand that natively compiled code is (should be) faster than p-code. Let's say we natively compiled custom function and use it in SQL query. What about context switch in this case? Is there any? If this is natevily compiled code, it should be just called without any context switching - or not? What is exactly context switching if there is no PLSQL code (p-code) and only linked object code?
Thanks a lot!

So - plsql context is not avoidable? There is technically no way of building functions similar to Oracle internal functions (like ln or sin for example)? Would be very nice to have!!!

Sorry - I'm confused. Are you saying there is also a context switch when you have select ln(column) from some_table? Or there is not? If there is - in this case ANY function call results in context switch and why using Oracle embedded functions is better then user ones? If there is not - back to the original question: is it possible to build user function that is acting in exactly the same way as native Oracle?

I think you perfectly understood the original question as well, but you just don't like to answer "NO" on any question like "is it possible to do {something} in Oracle" :-)

Hi Tom,
Could you please share your experience with me on the following questions:

I have a procedure which is the heart of the banking application i,e, user/application calls come from tuxedo onto the database server, where every call first executes a main Function say ProcessCust(); This is a plsql function. This function then interpets the call (which is basically having a message in iso format). The function breaks the input message which is vrchar2 and parse it. Like the first 3 characters store the action required(like opening a new customer, updating its balance, etc etc), then rest of the string contains the actual body (like if its new customer, then the text would contain fname,lname, address etc etc).
In short the function ProcessCust() parses the message(and i found lots lots if if then plsql clauses inside) and lots of substring, ltrim etc. The function then make calls to corresponding procedures like createcustomer() etc which are also plsql stored procs defined in the same database.

Now from the statspack i see that 90% of the cpu time used (under the first section of statspack) point to this function ProcessCust(). Ironically there is no other info that guides me what actually took so much cpu from this fucntion(i mean the fucntion has some bit of sql calls too, but those sqls do not appear in the subsequent section, which can only mean that the function is having lots if if then , sunstr etc, which are basically using that much cpu and statspack basically does not go into details).

Anyway, what i would like to do is to recompile that function into native. I do not want the whole db to be native compiled. So I reckon its possible to just compile that function alone into native mode. Questions:
1. With the kind of example you provided in this thread, i could have a test case(stress load which calls this function many times for all events) where i can find the elapsed time taken. I can run this for both inter and compiled. If i see significant time reduction, i will go for native. However I also would like to measure other factors.
How can i include a code in the same test that tells me How much CPU was used in first interpreted and then compiled case??is there an easy way to do that like calling an oracle function before and after the for loop test you showed, that will also give me cpu secs used????

2. Do you think cpu time will reduce in native calls? it should right??in your test, was it the case? or you think more cpu is required? i reckon .obj code should use lot less cpu than plsql interpet calls?

3. Does native plsql compilation increase the memory usage of the machine ??

4. We are on 9i rel 2, is there any major siginificant improvements in plsql native mechanism between 9i and 10g?

5, We will migrate to 10g in 6 months time, is there a document that yo can refer that 10g plsql is that much faster than 9i??

6. If all fails, how does one fine tune a case which i explained where statspack shows a function using almost 80-90 of the cpu time for the duration and no sqls are shown?is there an ydetailed level of statspack??

Regards
Daniel

Hi Tom,
What I see in the top 5 timed events of my statspack is the:
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 19,757 44.62

Then when I looked at the section for sqls orderded by buffer gets i see:
CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
7,616,928 95,061 80.1 11.5 11212.33 12510.73 3919012089
Module: fts_request@mware02 (TNS V1-V3)
begin :output_fields := precamm ( :input_fields ) ; END ;

So I reckon 11212 secs out of 1957 means this sql is the most cosuming cpu time. Now the problem is this is a function called precamm in my database which is composed of lots of if then else plus sqls. But I dont see any sqls further in statspack taking up cpu time.
So I thought of compiling this into native,

I wish i can paste here full statspack and that function(am sure programmer must have put something very bad as a code).
But i tried but there is not enough space in the reply box here, Can i please send the full statspack and the precamm function to your email??
Appreciate if you can really look into these
cheers

hi,
there are 32 tuxedo connections to the database and all those sessions are alive( i mean they dont disconnect, but remains connected). I can trace 1 such session.
But the sessons are very active i mean this db runs 400 trans per sec. How can i trace that function only??

Anyway I will put a trace on the session and tkprof to see what i get.
Cheers

Tom,
Sorry what i think you mean is use dbms profiler to trace.
I will do that.
I just happened to talk to the programmer and what i understand is that that specific function when called so many times , it is internally calling a procedure with passing a value n, where n is like 4,5,6 etv.
That procedure is called delay and its job is to create a delay in the logic flow. So the delay procedure accepsts values like 10 as seconds and then it
FOR di IN 1..sed LOOP
NULL;
END LOOP;
A random thought, could this when run so many times accumulate huge cpu? if yes could we use sleep plsql function if that does not spin cpu?
I wil in any case run a profile to see.Objective is to find the cpu consumign part in the precam function.
cheers

Hi Dani,

The loop as you have it coded in the review right above

for x in 1..n loop
null;
end loop;

will definitely chew up cpu. DBMS_LOCK should be more friendly to the cpu's.

Tom,
I have now been informed about the actual reason as to why did they code the delay and am afraid the reason is such that I can not share it on here, I means its public, i can send you the reason for your curiosity if u have an email add.
I have now given the application schema the execcute privilege on the dbms_lock proc. They will change that delay to dnms_lock.sleep (their delay varies from 10 sec to 59 secs).
Yes in actual face they still want a delay for the same reason. I wish if i had a solution for them.
I will let you know how thing s went in terms of cpu usage after the sleep proc. I mean this would take atleast a week before the chane would move to prod after uat and proper change control approval. I hope that the cpu would drastically come down and this would really go in my favour for the cpu license savings.

Hi tom,
After converting the for loop to sleep procedure, the darabase shows 53% cpu reduction. and this affects the os which reduced almost 36.2 cpu. From statspack the cpu secs were reduced to 53% exactly.
Thanks for your tip.
Questions:
When using dbms profiler, I see results for every line of the procedure with the time elapsed. What I wanted is to find out the CPU secs consumed by that procedure or(the block of code inside that procedure) and not the elapsed time which could be due to an i.o as well.
My objective is to breakup any plsqlp procedureand find out areas where the code is taking cpu time. How do i do that??

In reference to another document where i posted my question about pl.sql native, what i have noticed is that on a really high end machine, the plsql native compilation really does not provide much signifincat improvment even if the underlyign procdues contais more logic (than sql) and even if its 5000 lines of code. Now your example which shows imprvment but in reality people do not use that kind of code in databas procedurs, so really the plsql native compilation with the practical approach to say 90% of the applicationis really a myth, better to upgrade to 10g.

Cheers

Tom, Is there any Oracle meta-data table/view by which we can know the Operating System User Name(linux/unix or Windows User) of the person who last compiled an object(let's say a package) in the Oracle Database 10G via a windows client or a linux client.
Thanks,
Deepak

Hi Tom,

I have one problem when i compiling package. This is for statement that doesn't work in compiling

for i in (select d.id, d.value1 from dv_rn_tb1 d where d.id in(1,2,4,6,8,9,4))
loop
dbms_output.put_line('test'||to_char(i.value1));
end loop;

This for statemen works
for i in 1..100
loop
dbms_output.put_line('test'||to_char(i));
end loop;
runstats_pkg.rs_stop();

Message i recive is when i execute "show Error" is "No errors"

Regards
Dani

Table
create table DV_RN_TB1
(
ID INTEGER,
VALUE1 INTEGER
);

insert into dv_rn_tb1 values (1,89);
insert into dv_rn_tb1 values (2,78);
insert into dv_rn_tb1 values (3,67);
insert into dv_rn_tb1 values (4,7);
insert into dv_rn_tb1 values (5,5);
insert into dv_rn_tb1 values (6,34);
insert into dv_rn_tb1 values (7,33);
insert into dv_rn_tb1 values (8,20);
insert into dv_rn_tb1 values (9,10);
insert into dv_rn_tb1 values (10,9);
Package
create or replace package DV_TEST2 as
procedure equal_number_hsl_case;
end DV_TEST2;

create or replace package body DV_TEST2 is
procedure equal_number_hsl_case is
x number;
y number;
begin
--run this does not work
for i in (select d.id, d.value1 from dv_rn_tb1 d where d.id in(1,2,4,6,8,9,4))
loop
dbms_output.put_line('test'||to_char(i.value1));
end loop;
-- or this this works fine
for i in 1..100
loop
dbms_output.put_line('test'||to_char(i));
end loop;
end;
end DV_TEST2;

Native compiling of package return error.
SQL> alter session set plsql_code_type = 'NATIVE';
Session altered
SQL> alter package dv_test2 compile package;
Warning: Package altered with compilation errors
SQL> show errors;
No errors for PACKAGE DANI.DV_TEST2

Thanks Tom

Regards Dani

ops$tkyte%ORA9IR2> alter system set plsql_native_make_utility = 'make';

System altered.

ops$tkyte%ORA9IR2> alter system set plsql_native_make_file_name = '/home/ora9ir2/plsql/spnc_makefile.mk';

System altered.

ops$tkyte%ORA9IR2> alter session set plsql_compiler_flags = 'INTERPRETED';

Session altered.

ops$tkyte%ORA9IR2> alter system set plsql_native_library_dir = '/tmp';

System altered.

ops$tkyte%ORA9IR2> alter session set plsql_compiler_flags = 'NATIVE';

Session altered.

ops$tkyte%ORA9IR2> alter package dv_test2 compile;

Package altered.

ops$tkyte%ORA9IR2> show errors package dv_test2;
No errors.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for Linux: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

*** string too long, truncated *** (18863), ORA-06502: PL/SQL: numeric or value error: character string buffer too small
I got below error on our Production database.
Two three weeks back we got the ORA-00600 error and we raised the SR with ORACLE. The woraround they have given is, disable the four trigger related to DBMS_CDC_*

Can you please explain, how can we overcome this error?
What is the purpose of these CDC triggers?

Thanks in advance
Fayyaz

*** 2007-04-23 09:19:13.341
*** SESSION ID:(1263.5641) 2007-04-23 09:19:13.340
ORA-00604: error occurred at recursive SQL level 3
ORA-00028: your session has been killed
ORA-06512: at "SYS.DBMS_CDC_PUBLISH", line 325
ORA-06512: at line 3
CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D6BA6_3539A72C" ("C0" ROWID,"C1" VARCHAR2(15),"C2" VARCHAR2(10),"C3" VARCHAR2(10),"C4" VARCHAR2(3),"C5" VARCHAR2(3),"C6" VARCHAR2(10),"C7" VARCHAR2(10),"C8" VARCHAR2(10),"C9" VARCHAR2(3),"C10" VARCHAR2(3) ) IN_MEMORY_METADATA CURSOR_SPECIFIC_SEGMENT STORAGE (OBJNO 4254952358 ) NOPARALLEL
Current SQL statement for this session:
/* MV_REFRESH (MRG) */ MERGE INTO "SCE"."MV_PARTNER_LOCATOR" "SNA$" USING ( WITH TMPDLT$_PARTNER_PROFILE AS ( SELECT "MAS$"."RID$" "RID$" , "MAS$"."SHORT_NAME", "MAS$"."PARTNER_STATUS", "MAS$"."PARTNER_INTERFACE", "MAS$"."ACTIVE_PARTNER", "MAS$"."COMPANY_CODE", "MAS$"."OWNER_CODE", "MAS$"."OWNER_SUB_CODE", "MAS$"."PARTNER_CODE", "MAS$"."PARTNER_SUB_CODE", DECODE("MAS$"."OLD_NEW$$", 'N', 'I', 'D') "DML$$" FROM (SELECT "MAS$".*, MIN("MAS$"."SEQ$$") OVER (PARTITION BY "MAS$"."RID$") "MINSEQ$$", MAX("MAS$"."SEQ$$") OVER (PARTITION BY "MAS$"."RID$") "MAXSEQ$$" FROM (SELECT /*+ CARDINALITY(MAS$ 2) */ CHARTOROWID("MAS$"."M_ROW$$") RID$ , "MAS$"."SHORT_NAME", "MAS$"."PARTNER_STATUS", "MAS$"."PARTNER_INTERFACE", "MAS$"."ACTIVE_PARTNER", "MAS$"."COMPANY_CODE", "MAS$"."OWNER_CODE", "MAS$"."OWNER_SUB_CODE", "MAS$"."PARTNER_CODE", "MAS$"."PARTNER_SUB_CODE" , "MAS$"."SEQUENCE$$" "SEQ$$", "MAS$"."OLD_NEW$$" "OLD_NEW$$" FROM "SCE"."MLOG$_PARTNER_PROFILE" "MAS$" WHERE "MAS$".SNAPTIME$$ > :2 ) "MAS$" ) "MAS$" WHERE ((("MAS$"."OLD_NEW$$" = 'N') AND ("MAS$"."SEQ$$" = "MAS$"."MAXSEQ$$")) OR (("MAS$"."OLD_NEW$$" IN ('O', 'U')) AND ("MAS$"."SEQ$$" = "MAS$"."MINSEQ$$"))) ) , TMPDLT$_PARTNERS AS ( SELECT "MAS$"."RID$" "RID$" , "MAS$"."PARTNER_UID", "MAS$"."PARTNER_CODE", "MAS$"."PARTNER_SUB_CODE", "MAS$"."COMPANY_CODE", "MAS$"."OWNER_CODE", "MAS$"."OWNER_SUB_CODE", "MAS$"."FULL_NAME", "MAS$"."DELETE_FLAG", DECODE("MAS$"."OLD_NEW$$", 'N', 'I', 'D') "DML$$" FROM (SELECT "MAS$".*, MIN("MAS$"."SEQ$$") OVER (PARTITION BY "MAS$"."RID$") "MINSEQ$$", MAX("MAS$"."SEQ$$") OVER (PARTITION BY "MAS$"."RID$") "MAXSEQ$$" FROM (SELECT /*+ CARDINALITY(MAS$ 2) */ CHARTOROWID("MAS$"."M_ROW$$") RID$ , "MAS$"."PARTNER_UID", "MAS$"."PARTNER_CODE", "MAS$"."PARTNER_SUB_CODE", "MAS$"."COMPANY_CODE", "MAS$"."OWNER_CODE", "MAS$"."OWNER_SUB_CODE", "MAS$"."FULL_NAME", "MAS$"."DELETE_FLAG" , "MAS$"."SEQUENCE$$" "SEQ$$", "MAS$"."OLD_NEW$$" "OLD_NEW$$" FROM "SCE"."MLOG$_PARTNERS" "MAS$" WHERE "MAS$".SNAPTIME$$ > :3 ) "MAS$" ) "MAS$" WHERE ((("MAS$"."OLD_NEW$$" = 'N') AND ("MAS$"."SEQ$$" = "MAS$"."MAXSEQ$$")) OR (("MAS$"."OLD_NEW$$" IN ('O', 'U')) AND ("MAS$"."SEQ$$" = "MAS$"."MINSEQ$$"))) ) SELECT /*+ MAX_CARDINALITY(96758) */ "DV$"."GB0" "GB0", "DV$"."GB1" "GB1", "DV$"."GB2" "GB2", "DV$"."GB3" "GB3", "DV$"."GB4" "GB4", "DV$"."GB5" "GB5", "DV$"."GB6" "GB6", "DV$"."GB7" "GB7", "DV$"."GB8" "GB8", "DV$"."GB9" "GB9", "DV$"."GB10" "GB10", "DV$"."GB11" "GB11", "DV$"."GB12" "GB12", "DV$"."GB13" "GB13", "DV$"."GB14" "GB14", "DV$"."GB15" "GB15", "DV$"."GB16" "GB16", "DV$"."GB17" "GB17", "DV$"."GB18" "GB18", "DV$"."GB19" "GB19", "DV$"."GB20" "GB20", "DV$"."GB21" "GB21", "DV$"."GB22" "GB22", "DV$"."GB23" "GB23", SUM(DECODE("DV$"."DML$$", 'I', 1, -1)) "D0" FROM ( ( SELECT /*+ */ "PRE$3"."PARTNER_UID" "GB0", "PRE$3"."PARTNER_CODE" "GB1", "PRE$3"."PARTNER_SUB_CODE" "GB2", "PRE$3"."COMPANY_CODE" "GB3", "PRE$3"."OWNER_CODE" "GB4", "PRE$3"."OWNER_SUB_CODE" "GB5", "PRE$3"."FULL_NAME" "GB6", "PRE$2"."SHORT_NAME" "GB7", "PRE$2"."PARTNER_STATUS" "GB8", "PRE$2"."PARTNER_INTERFACE" "GB9", "PRE$2"."ACTIVE_PARTNER" "GB10", "MAS$1"."ADDRESS_TYPE" "GB11", "MAS$1"."ADDR_CODE" "GB12", "MAS$1"."ADDR_NUMBER" "GB13", "DLT$0"."FULL_NAME" "GB14", "DLT$0"."ADDRESS_1" "GB15", "DLT$0"."ADDRESS_2" "GB16", "DLT$0"."ADDRESS_3" "GB17", "DLT$0"."ADDRESS_4" "GB18", "DLT$0"."ADDRESS_5" "GB19", "DLT$0"."POINT_CODE" "GB20", "DLT$0"."COUNTY_STATE_CODE" "GB21", "DLT$0"."POST_CODE" "GB22", "DLT$0"."COUNTRY" "GB23", "DLT$0".DML$$ DML$$ FROM (SELECT /*+ CARDINALITY(MAS$ 2) */ CHARTOROWID("MAS$"."M_ROW$$") RID$ , "MAS$"."FULL_NAME", "MAS$"."ADDRESS_1", "MAS$"."ADDRESS_2", "MAS$"."ADDRESS_3", "MAS$"."ADDRESS_4", "MAS$"."ADDRESS_5", "MAS$"."POINT_CODE", "MAS$"."COUNTY_STATE_CODE", "MAS$"."POST_CODE", "MAS$"."COUNTRY", "MAS$"."ADDR_CODE" , DECODE("MAS$".OLD_NEW$$, 'N', 'I', 'D') DML$$ FROM "SCE"."MLOG$_ADDRESSES" "MAS$" WHERE "MAS$".SNAPTIME$$ > :1 ) "DLT$0" , (SELECT "MAS$"."ROWID" "RID$" , "MAS$"."ADDRESS_TYPE", "MAS$"."ADDR_CODE", "MAS$"."ADDR_NUMBER", "MAS$"."COMPANY_CODE", "MAS$"."PARTNER_CODE", "MAS$"."PARTNER_SUB_CODE", "MAS$"."OWNER_CODE", "MAS$"."OWNER_SUB_CODE", "MAS$"."DELETE_FLAG" FROM "SCE"."PARTNERS_ADDRESSES" "MAS$") AS OF SNAPSHOT (:4) "MAS$1" , (SELECT /*+ CARDINALITY(MAS$ 1) */ "MAS$"."RID$" "RID$" , "MAS$"."SHORT_NAME", "MAS$"."PARTNER_STATUS", "MAS$"."PARTNER_INTERFACE", "MAS$"."ACTIVE_PARTNER", "MAS$"."COMPANY_CODE", "MAS$"."OWNER_CODE", "MAS$"."OWNER_SUB_CODE", "MAS$"."PARTNER_CODE", "MAS$"."PARTNER_SUB_CODE" FROM "TMPDLT$_PARTNER_PROFILE" "MAS$" WHERE "MAS$"."DML$$" = 'D' ) "PRE$2" , (SELECT /*+ CARDINALITY(MAS$ 1) */ "MAS$"."RID$" "RID$" , "MAS$"."PARTNER_UID", "MAS$"."PARTNER_CODE", "MAS$"."PARTNER_SUB_CODE", "MAS$"."COMPANY_CODE", "MAS$"."OWNER_CODE", "MAS$"."OWNER_SUB_CODE", "MAS$"."FULL_NAME", "MAS$"."DELETE_FLAG" FROM "TMPDLT$_PARTNERS" "MAS$" WHERE "MAS$"."DML$$" = 'D' ) "PRE$3" WHERE ("DLT$0"."ADDR_CODE"="MAS$1"."ADDR_CODE" AND "MAS$1"."COMPANY_CODE"="PRE$3"."COMPANY_CODE" AND "MAS$1"."PARTNER_CODE"="PRE$3"."PARTNER_CODE" AND "MAS$1"."PARTNER_SUB_CODE"="PRE$3"."PARTNER_SUB_CODE" AND "MAS$1"."OWNER_CODE"="PRE$3"."OWNER_CODE" AND "MAS$1"."OWNER_SUB_CODE"="PRE$3"."OWNER_SUB_CODE" AND NVL("MAS$1"."DELETE_FLAG",'N')<>'Y' AND UPPER(NVL("PRE$2"."PARTNER_STATUS",'N'))<>'ON HOLD' AND "PRE$3"."COMPANY_CODE"="PRE$2"."COMPANY_CODE" AND "PRE$3"."OWNER_CODE"="PRE$2"."OWNER_CODE" AND "PRE$3"."OWNER_SUB_CODE"="PRE$2"."OWNER_SUB_CODE" AND "PRE$3"."PARTNER_CODE"="PRE$2"."PARTNER_CODE" AND "PRE$3"."PARTNER_SUB_CODE"="PRE$2"."PARTNER_SUB_CODE" AND "PRE$3"."DELETE_FLAG"<>'Y') ) UNION ALL ( SELECT /*+ */ "PRE$3"."PARTNER_UID" "GB0", "PRE$3"."PARTNER_CODE" "GB1", "PRE$3"."PARTNER_SUB_CODE" "GB2", "PRE$3"."COMPANY_CODE" "GB3", "PRE$3"."OWNER_CODE" "GB4", "PRE$3"."OWNER_SUB_CODE" "GB5", "PRE$3"."FULL_NAME" "GB6", "PRE$2"."SHORT_NAME" "GB7", "PRE$2"."PARTNER_STATUS" "GB8", "PRE$2"."PARTNER_INTERFACE" "GB9", "PRE$2"."ACTIVE_PARTNER" "GB10", "MAS$1"."ADDRESS_TYPE" "GB11", "MAS$1"."ADDR_CODE" "GB12", "MAS$1"."ADDR_NUMBER" "GB13", "DLT$0"."FULL_NAME" "GB14", "DLT$0"."ADDRESS_1" "GB15", "DLT$0"."ADDRESS_2" "GB16", "DLT$0"."ADDRESS_3" "GB17", "DLT$0"."ADDRESS_4" "GB18", "DLT$0"."ADDRESS_5" "GB19", "DLT$0"."POINT_CODE" "GB20", "DLT$0"."COUNTY_STATE_CODE" "GB21", "DLT$0"."POST_CODE" "GB22", "DLT$0"."COUNTRY" "GB23", "DLT$0".DML$$ DML$$ FROM (SELECT /*+ CARDINALITY(MAS$ 2) */ CHARTOROWID("MAS$"."M_ROW$$") RID$ , "MAS$"."FULL_NAME", "MAS$"."ADDRESS_1", "MAS$"."ADDRESS_2", "MAS$"."ADDRESS_3", "MAS$"."ADDRESS_4", "MAS$"."ADDRESS_5", "MAS$"."POINT_CODE", "MAS$"."COUNTY_STATE_CODE", "MAS$"."POST_CODE", "MAS$"."COUNTRY", "MAS$"."ADDR_CODE" , DECODE("MAS$".OLD_NEW$$, 'N', 'I', 'D') DML$$ FROM "SCE"."MLOG$_ADDRESSES" "MAS$" WHERE "MAS$".SNAPTIME$$ > :1 ) "DLT$0" , (SELECT "MAS$"."ROWID" "RID$" , "MAS$"."ADDRESS_TYPE", "MAS$"."ADDR_CODE", "MAS$"."ADDR_NUMBER", "MAS$"."COMPANY_CODE", "MAS$"."PARTNER_CODE", "MAS$"."PARTNER_SUB_CODE", "MAS$"."OWNER_CODE", "MAS$"."OWNER_SUB_CODE", "MAS$"."DELETE_FLAG" FROM "SCE"."PARTNERS_ADDRESSES" "MAS$") AS OF SNAPSHOT (:4) "MAS$1" , (SELECT "MAS$"."ROWID" "RID$" , "MAS$"."SHORT_NAME", "MAS$"."PARTNER_STATUS", "MAS$"."PARTNER_INTERFACE", "MAS$"."ACTIVE_PARTNER", "MAS$"."COMPANY_CODE", "MAS$"."OWNER_CODE", "MAS$"."OWNER_SUB_CODE", "MAS$"."PARTNER_CODE", "MAS$"."PARTNER_SUB_CODE" FROM "SCE"."PARTNER_PROFILE" "MAS$") "PRE$2" , (SELECT /*+ CARDINALITY(MAS$ 1) */ "MAS$"."RID$" "RID$" , "MAS$"."PARTNER_UID", "MAS$"."PARTNER_CODE", "MAS$"."PARTNER_SUB_CODE", "MAS$"."COMPANY_CODE", "MAS$"."OWNER_CODE", "MAS$"."OWNER_SUB_CODE", "MAS$"."FULL_NAME", "MAS$"."DELETE_FLAG" FROM "TMPDLT$_PARTNERS" "MAS$" WHERE "MAS$"."DML$$" = 'D' ) "PRE$3" WHERE ("DLT$0"."ADDR_CODE"="MAS$1"."ADDR_CODE" AND "MAS$1"."COMPANY_CODE"="PRE$3"."COMPANY_CODE" AND "MAS$1"."PARTNER_CODE"="PRE$3"."PARTNER_CODE" AND "MAS$1"."PARTNER_SUB_CODE"="PRE$3"."PARTNER_SUB_CODE" AND "MAS$1"."OWNER_CODE"="PRE$3"."OWNER_CODE" AND "MAS$1"."OWNER_SUB_CODE"="PRE$3"."OWNER_SUB_CODE" AND NVL("MAS$1"."DELETE_FLAG",'N')<>'Y' AND UPPER(NVL("PRE$2"."PARTNER_STATUS",'N'))<>'ON HOLD' AND "PRE$3"."COMPANY_CODE"="PRE$2"."COMPANY_CODE" AND "PRE$3"."OWNER_CODE"="PRE$2"."OWNER_CODE" AND "PRE$3"."OWNER_SUB_CODE"="PRE$2"."OWNER_SUB_CODE" AND "PRE$3"."PARTNER_CODE"="PRE$2"."PARTNER_CODE" AND "PRE$3"."PARTNER_SUB_CODE"="PRE$2"."PARTNER_SUB_CODE" AND "PRE$3"."DELETE_FLAG"<>'Y') AND SYS_OP_MAP_NONNULL("PRE$2"."RID$") NOT IN (SELECT /*+ CARDINALITY(MAS$ 1) ANTIJOIN */ SYS_OP_MAP_NONNULL("MAS$"."RID$") "RID$" FROM "TMPDLT$_PARTNER_PROFILE" "MAS$" WHERE "MAS$"."DML$$" = 'I' ) ) UNION ALL ( SELECT /*+ */ "PRE$3"."PARTNER_UID" "GB0", "PRE$3"."PARTNER_CODE" "GB1", "PRE$3"."PARTNER_SUB_CODE" "GB2", "PRE$3"."COMPANY_CODE" "GB3", "PRE$3"."OWNER_CODE" "GB4", "PRE$3"."OWNER_SUB_CODE" "GB5", "PRE$3"."FULL_NAME" "GB6", "PRE$2"."SHORT_NAME" "GB7", "PRE$2"."PARTNER_STATUS" "GB8", "PRE$2"."PARTNER_INTERFACE" "GB9", "PR...

Hi Tom,

I use v10:
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production


I use MinGW for C compiling. Is the enought informations.
I work under Windows XP.

Thanks Tom ,

Regards Dani

Hy Tom,

I compile in Oracle 10g.
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Maybe is this a problem.

Tom, read recently about 11g native compilation improvements. In one of your previous replies you said that it can be even worse to convert some user function that is called from SQL into natively compiled due to the double context switch (sql-plsql-native) - at least that was my understanding of your answer. Is it still true in 11g?

In one of my 11g databases , 
I changed copilation mode from  'INTERPRETED' to 'NATIVE' by executing 
dbmsupgnv in upgrade mode ... 

Still some of the packages are in INTERPRETED mode . 
Why would some be in INTERPRETED while others are in NATIVE . Please advise.





  1  SELECT TYPE, PLSQL_CODE_TYPE, COUNT(*)
  2    FROM DBA_PLSQL_OBJECT_SETTINGS
  3    WHERE PLSQL_CODE_TYPE IS NOT NULL
  4    GROUP BY TYPE, PLSQL_CODE_TYPE
  5*   ORDER BY TYPE, PLSQL_CODE_TYPE
SQL> 
SQL> /

TYPE         PLSQL_CODE_TYPE                  COUNT(*)
------------ ------------------------------ ----------
FUNCTION     NATIVE                                414
PACKAGE      INTERPRETED                          1318
PACKAGE      NATIVE                                  2
PACKAGE BODY NATIVE                               1258
PROCEDURE    NATIVE                                455
TRIGGER      NATIVE                               1075
TYPE         INTERPRETED                          2311
TYPE         NATIVE                                243
TYPE BODY    NATIVE                                224

9 rows selected.


  1  Select * from (
  2  SELECT owner , NAME , TYPE
  3   FROM all_plsql_object_settings WHERE plsql_code_type <> 'NATIVE'
  4* ) where rownum < 10
SQL> /

OWNER                          NAME                           TYPE
------------------------------ ------------------------------ ------------
SYS                            AGGXMLIMP                      TYPE
SYS                            AGGXQAVGIMP                    TYPE
SYS                            AGGXQIMP                       TYPE
SYS                            AGGXQSUMIMP                    TYPE
SYS                            ALERT_TYPE                     TYPE
SYS                            ANYDATA                        TYPE
SYS                            ANYDATASET                     TYPE
SYS                            ANYTYPE                        TYPE
SYS                            AQ$_AGENT                      TYPE

9 rows selected.





  1  Select * from (
  2  SELECT owner , NAME , TYPE
  3   FROM all_plsql_object_settings WHERE plsql_code_type = 'NATIVE'
  4* ) where rownum < 10
SQL> /

OWNER                          NAME                           TYPE
------------------------------ ------------------------------ ------------
SYS                            ANYDATA                        TYPE BODY
SYS                            ANYDATASET                     TYPE BODY
SYS                            ANYTYPE                        TYPE BODY
SYS                            APS_VALIDATE                   PROCEDURE
SYS                            AQ$_GET_SUBSCRIBERS            FUNCTION
SYS                            AQ$_JMS_BYTES_MESSAGE          TYPE BODY
SYS                            AQ$_JMS_HEADER                 TYPE BODY
SYS                            AQ$_JMS_MAP_MESSAGE            TYPE BODY
SYS                            AQ$_JMS_MESSAGE                TYPE BODY

9 rows selected.

Hai Tom,

Can you Please Explain me How a PLSQL block compiles,interprets and executes and into what code they are converted in detail?

Thanks in Advance

Hi Tom,

As I have been asked to benchmark the performance gain to our Fund Management application trade profit and loss processing ( which is quite processing intensive ) by using plsql native compilation, I have installed a Microsoft Visual Studio 2008 C++ compiler ( amd64 cl.exe )on my benchmark server. The server has Oracle 10.2.0.4 on Windows 2003 R2 Standard x64 Edition SP2.

Initially I am simply attempting to compile a basic 'Hello World' procedure and it is failing to find the dll although the dll appears to be being created ok in the appropriate folder defined by the param plsql_native_library_dir.

e.g.
alter session set plsql_code_type='NATIVE';

create or replace procedure my_test_x is
begin
dbms_output.put_line('hello world');
end;

select * from user_errors

ORA-06549: PL/SQL: failed to dynamically open shared object (DLL): The specified module could not be found.

After having problems with calling cl.exe directly in spnc_command file, I have configured spnc_command file to to call a batch file ncomp_compile.bat passing %(src) %(obj) %(dll) args and this is creating the
.dll sucessfully. The contents of this bat file are as follows :

call "C:\Program Files (x86)\Microsoft Visual Studio 9.0\VC\vcvarsall.bat" amd64
"C:\Program Files (x86)\Microsoft Visual Studio 9.0\VC\bin\amd64\cl.exe" %1 /nologo /Zm200
/Og /Oy /MD /Fo%2 /IC:\Oracle\Ora102/plsql/public
/IC:\Oracle\Ora102/plsql/include /link /nologo /dll
C:\Oracle\Ora102/lib/orapls10.lib /out:%3;
exit;

In summary, the problem I am now having is that Oracle reports error : ORA-06549: PL/SQL: failed to dynamically open shared object (DLL): The specified module could not be found.

I have tried to investigate on metalink, google etc.. and have not been able to determine what is causing this error. I am guessing that it may be an x64 issue or it may be related to environment vars. I have just raised an SR with Oracle also. There seems to be quite limited docs and forums etc on native compilation, particularly on Windows x64.

Any help or advice you can offer on what may causing this failure would be hugely appreciated.

Thanks in advance,

Scott Duncan

Hi Tom,

As I have been asked to benchmark the performance gain to our Fund Management application trade profit and loss processing ( which is quite processing intensive ) by using plsql native compilation, I have installed a Microsoft Visual Studio 2008 C++ compiler ( amd64 cl.exe )on my benchmark server. The server has Oracle 10.2.0.4 on Windows 2003 R2 Standard x64 Edition SP2.

Initially I am simply attempting to compile a basic 'Hello World' procedure and it is failing to find the dll although the dll appears to be being created ok in the appropriate folder defined by the param plsql_native_library_dir.

e.g.
alter session set plsql_code_type='NATIVE';

create or replace procedure my_test_x is
begin
dbms_output.put_line('hello world');
end;

select * from user_errors

ORA-06549: PL/SQL: failed to dynamically open shared object (DLL): The specified module could not be found.

After having problems with calling cl.exe directly in spnc_command file, I have configured spnc_command file to to call a batch file ncomp_compile.bat passing %(src) %(obj) %(dll) args and this is creating the
.dll sucessfully. The contents of this bat file are as follows :

call "C:\Program Files (x86)\Microsoft Visual Studio 9.0\VC\vcvarsall.bat" amd64
"C:\Program Files (x86)\Microsoft Visual Studio 9.0\VC\bin\amd64\cl.exe" %1 /nologo /Zm200
/Og /Oy /MD /Fo%2 /IC:\Oracle\Ora102/plsql/public
/IC:\Oracle\Ora102/plsql/include /link /nologo /dll
C:\Oracle\Ora102/lib/orapls10.lib /out:%3;
exit;

In summary, the problem I am now having is that Oracle reports error : ORA-06549: PL/SQL: failed to dynamically open shared object (DLL): The specified module could not be found.

I have tried to investigate on metalink, google etc.. and have not been able to determine what is causing this error. I am guessing that it may be an x64 issue or it may be related to environment vars. I have just raised an SR with Oracle also. There seems to be quite limited docs and forums etc on native compilation, particularly on Windows x64.

Any help or advice you can offer on what may causing this failure would be hugely appreciated.

Thanks in advance,

Scott Duncan

Hi Tom, my post is being deleted but I am confused as it seemed to me that trying to benchmark the performance gain I can get from native pl/sql is totally relevant to the original question. If I can get it to compile ok I will be delighted to feedback the results which should help to give a real world feedback to the original question. My post is as follows below, please can you re-consider that it is very relevant. Thanks.

---- original post ----

I have been asked to benchmark the performance gain to our Fund Management application trade profit and loss processing ( which is quite processing intensive ) by using plsql native compilation. I have installed a Microsoft Visual Studio 2008 C++ compiler ( amd64 cl.exe )on my benchmark server. The server has Oracle 10.2.0.4 on Windows 2003 R2 Standard x64 Edition SP2.

Initially I am simply attempting to compile a basic 'Hello World' procedure and it is failing to find the dll although the dll appears to be being created ok in the appropriate folder defined by the param plsql_native_library_dir.

e.g.
alter session set plsql_code_type='NATIVE';

create or replace procedure my_test_x is
begin
dbms_output.put_line('hello world');
end;

select * from user_errors

ORA-06549: PL/SQL: failed to dynamically open shared object (DLL): The specified module could not be found.

After having problems with calling cl.exe directly in spnc_command file, I have configured spnc_command file to to call a batch file ncomp_compile.bat passing %(src) %(obj) %(dll) args and this is creating the
.dll sucessfully. The contents of this bat file are as follows :

call "C:\Program Files (x86)\Microsoft Visual Studio 9.0\VC\vcvarsall.bat" amd64
"C:\Program Files (x86)\Microsoft Visual Studio 9.0\VC\bin\amd64\cl.exe" %1 /nologo /Zm200
/Og /Oy /MD /Fo%2 /IC:\Oracle\Ora102/plsql/public
/IC:\Oracle\Ora102/plsql/include /link /nologo /dll
C:\Oracle\Ora102/lib/orapls10.lib /out:%3;
exit;

In summary, the problem I am now having is that Oracle reports error : ORA-06549: PL/SQL: failed to dynamically open shared object (DLL): The specified module could not be found.

I have tried to investigate on metalink, google etc.. and have not been able to determine what is causing this error. I am guessing that it may be an x64 issue or it may be related to environment vars. I have just raised an SR with Oracle also. There seems to be quite limited docs and forums etc on native compilation, particularly on Windows x64.

Any help or advice you can offer on what may causing this failure would be hugely appreciated.

Thanks in advance,

Scott Duncan

After difficulty with compilers Oracle confirmed that I the Intel 8.1 C++ compiler is definitely certified for 10g R2 native PL/SQL on Windows x64.

Sure enough having failed to get mingw gcc, Visual Studio 2008 C++ and Intel 11.0 ( current version ) to work I finally got an eval of the Intel 8.1 compiler via Intel support and found that it worked.

On the upside I was surprised that it was relatively quick and easy to compile our trading portfolio Fund Management application into native pl/sql.

On the downside I was surprised to find that it made almost no difference to performance. Of course it all depends what the app is doing. we thought that as there is some significant looping computational Profit and Loss logic that we would see at least e.g. 10% improvement but we didn't.

It almost made me wonder whether it's really running as native plsql but the dictionary tables indicate that it is via :

SELECT type, plsql_code_type, count(*)
FROM dba_plsql_object_settings
GROUP BY plsql_code_type, type

I have a repeatable automated test for thousands of trades and the result is marginally different. It was a tiny bit slower for a couple of trade types ( strange ) and tiny bit faster for others. Not much different at all.

Anyway, I have posted this review just to say that it does not always make much difference at all and also just in case you may feedback that we may have missed something and it may not be running as native correctly!! One obvious thing to me was that the instructions I followed have made the pkg bodies and type bodies as native but their specs have remained as INTERPRETED. I guess that means that there may still be a lot of context switching but as there's not a lot of code in specs I wouldn't expect natively compiling specs to make much difference? Any thoughts appreciated.

Thanks,

Scott Duncan

Tom,

i). the REGEXP functions are all written as PL/SQL routines, right? and assumedly are subject to context switching like any other PL/SQL functions ??

ii). If they were to be compiled natively, (not that it would eliminate the context switching), but would they run just as fast as if they had been written in C ? I'm guessing they would.