Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Monday, November 5, 2007

Natively compile PL/SQL code in Apps 11i with RDBMS

In the interop note 362203.1 for using RDBMS 10.2.x with 11i it is stated:

Natively compile PL/SQL code (optional)
If you are not on the Windows Itanium platform, you can choose to run Oracle Applications 11i PL/SQL database objects in natively compiled mode with Oracle Database 10g. See the "Compiling PL/SQL Code for Native Execution" section of Chapter 11 of Oracle Database PL/SQL User's Guide and Reference 10g Release 2 (10.2).

When modifying the initialization parameters pertaining to the PL/SQL native compilation, use document 216205.1, on OracleMetaLink as a guideline. You can set the plsql_native_library_subdir_count parameter as described in the PL/SQL user's guide. Create the associated subdirectories for the given plsql_native_library_dir and plsql_native_library_subdir_count parameters.

However if you check interop note 282038.1, it recommends enabling plsql native code compilation for unix.

Natively compile PL/SQL code (optional)
If you are on a UNIX/Linux platform, you can choose to run Oracle Applications 11i PL/SQL database objects in natively compiled mode with Oracle Database 10g. See PL/SQL Native Compilation of Applications 11i on Oracle Database 10g Release 1 (10.1.0) on OracleMetaLink.
I am wondering why they made it optional in 10.2.x release.

In an SR raised with Oracle for a performance issue in Projects module, they have recommended that we set the following parameters:

Mandatory parameter current recommended

_system_trig_enabled (NOT FOUND) TRUE
o7_dictionary_accessibility TRUE FALSE
_sort_elimination_cost_ratio (NOT FOUND 5
_like_with_bind_as_equality (NOT FOUND TRUE
_fast_full_scan_enabled (NOT FOUND FALSE
_sqlexec_progression_cost (NOT FOUND 2147483647
plsql_optimize_level (NOT SET 2
plsql_code_type (NOT SET) NATIVE
plsql_native_library_dir (NOT SET)\ ?/prod11i/plsql_nativelib
_b_tree_bitmap_plans NOT FOUND FALSE
optimizer_secure_view_merging NOT SET FALSE

If you go through the 216205.1 note for init.ora parameters recommended for Apps 11i:
# PL/SQL Parameters
# The following parameters are used to enable the PL/SQL global optimizer
# and specify the compilation type.

# Release 11i environments that use Oracle Database 10g can employ either
# interpreted or compiled (native) PL/SQL code. The default is interpreted.
# While native PL/SQL compilation can improve runtime performance, this may
# be at the expense of increased downtime
, because of the need to generate
# and compile the native shared libraries. If native compilation is to be used,
# the parameter plsql_native_library_dir should be set to the path of the
# directory that will be used to store the shared libraries, and that line
# and the plsql_native_library_subdir_count line below should be uncommented.

plsql_optimize_level = 2 #MP
plsql_code_type = INTERPRETED
# plsql_native_library_dir = /prod11i/plsql_nativelib # Uncomment if using native PL/SQL
# plsql_native_library_subdir_count = 149
# Uncomment if using native PL/SQL

I am trying to understand how is downtime going to increase because of enabling native sql compilation. Will update this post once I have more information.

No comments: