Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Aug 7, 2003

Oracle PL/SQL Built For Speed - Page 2

By James Koopmann

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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM