O'Reilly logo

Oracle Database 11gR2 Performance Tuning Cookbook by Ciro Fiorillo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Using native compilation

In this recipe, we will see how to instruct the database to compile our stored procedures in native form—rather than interpreted—to speed up the execution time.

Getting ready

To be sure that our database is not using native compilation by default, we can execute the following command from a SQL*Plus Session:

SHOW PARAMETER PLSQL_CODE_TYPE

If the result is NATIVE, we can execute the following statement to return to the original default value:

ALTER SYSTEM SET PLSQL_CODE_TYPE = INTERPRETED;

How to do it...

The following steps will demonstrate how to use native compilation:

  1. Connect to the SH schema:
    CONNECT sh@TESTDB/sh
    
  2. Create the function C_N_K, which calculates the number of k-combinations in a set of n elements:
    CREATE OR REPLACE ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required