Oracle PL/SQL Built For Speed - Page 2

Compiling

Now that we have set the initialization parameter for NATIVE compiles, whenever we CREATE a new procedure or package, all procedures and packages will be compiled in C. If packages or procedures already exist and you want to compile them to NATIVE C, all you need to do is issue the ALTER [PROCEDURE | PACKAGE] {procedure_name|package_name} COMPILE command. Listing D gives a quick example of what happens for a newly created procedure. As you can see, the first time that it is complied it gives a message that the object does not exist in the subdirectory. This is fine since it is only trying to create a backup of the source that this created procedure is going to replace. Listing E shows that we can take a look at the newly created DLL in the directory. After subsequent calls to re-create the procedure, you can see in Listing F that Oracle will make backup copies of the DLLs. Whenever we re-compile this procedure, it will always compile to NATIVE C. It does not matter what the current settings of the init parameters have been changed to. The only way to re-compile into INTERPRETIVE is to drop the procedure, reset the init parameters for INTERPRETIVE, and CREATE the object.

LISTING D:
First creation of a simple procedure

SQL> CREATE OR REPLACE PROCEDURE testc
  2  AS
  3  BEGIN
  4    DBMS_OUTPUT.PUT_LINE('Hello world');
  5  END;
  6  /
mv: cannot stat '/u01/app/oracle/product/9.2/plsql/lib/d10/TESTC__SYS__0.so': 
	No such file or directory
Procedure created.
SQL> set serveroutput on
SQL> exec testc
Hello world

PL/SQL procedure successfully completed.

LISTING E
Directory listing of the newly created procedure

sh-2.05$ ls -1 /u01/app/oracle/product/9.2/plsql/lib/d10/TESTC__SYS__0.so
/u01/app/oracle/product/9.2/plsql/lib/d10/TESTC__SYS__0.so

LISTING F
Directory listing of backup copies made by Oracle

sh-2.05$ ls -1 /u01/app/oracle/product/9.2/plsql/lib/d10/TESTC__SY
/u01/app/oracle/product/9.2/plsql/lib/d10/TESTC__SYS__0.so
/u01/app/oracle/product/9.2/plsql/lib/d10/TESTC__SYS__0.so.11141
/u01/app/oracle/product/9.2/plsql/lib/d10/TESTC__SYS__0.so.11149
/u01/app/oracle/product/9.2/plsql/lib/d10/TESTC__SYS__0.so.11157

Confirmation of NATIVE Compile

We should now confirm to ourselves that what we have compiled is truly in NATIVE C. To do this we just need to issue the following SQL to look at the object we have compiled and validate that it has a NATIVE value returned. Listing G shows the simple SQL that will verify a procedure named TESTC was effectively compiled to NATIVE C.

LISTING G:
SQL to verify NATIVE C compile

SELECT object_name,param_value
  FROM user_stored_settings
 WHERE object_name = 'TESTC'
AND param_name = 'plsql_compiler_flags' 

OBJECT_NAME                    PARAM_VALUE
------------------------------ -------------------------
TESTC                          NATIVE,NON_DEBUG

Start Your Engines

If your procedures or packages are laden with logic that seems to run on forever, you should take a look at compiling these to NATIVE C. It will speed up your execution by making code jumps more efficient, function calls quicker, and use of memory more efficient; NATIVE C will give you that boost of energy you have always wanted to give your code. Just remember that DML operations are not affected since table access is a constant and compiling to NATIVE C will not do anything for it. If you want to take the next step, you can also compile the Oracle supplied packages and procedures to give the Oracle engine a boost. If you want to go that route, Oracle supplies a script called utlirp.sql that is located in $ORCLE_HOME/rdbms/admin that will invalidate and recompile all Oracle PL/SQL modules. Go ahead, start your engines.

» See All Articles by Columnist James Koopmann