Oracle PL/SQL 优化与调整 – PL/SQL Native Compilation 说明

本文主要是介绍Oracle PL/SQL 优化与调整 – PL/SQL Native Compilation 说明，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

一.相关概述

在之前的Bulk的那篇blog里提到了Oracle 的2个引擎：PL/SQL 引擎和SQL 引擎。

Oracle PL/SQL 优化与调整 -- Bulk 说明

http://www.cndba.cn/Dave/article/1534

Oracle 使用2个引擎来执行SQL和代码块：SQL 引擎和PL/SQL 引擎， Oracle 使用这2个引擎来执行PL/SQL blocks 和 subprograms。那么在执行的时候，PL/SQL 引擎把DML 语句发送给SQL 引擎，然后由SQL 引擎执行，执行完毕后，SQL 引擎把结果集在发送给PL/SQL 引擎。因此在不同引擎之间切换就需要进行context switch，过多的context switch是会影响SQL性能的。而bulk 就是从减少引擎之间context switches的方式来提高sql的效率。把对SQL 进行打包处理。

这里看另一个处理角度，使用PL/SQLNative Compilation(PL/SQL的本地编译)。

官网的链接如下：

PL/SQL Native Compilation

http://docs.oracle.com/cd/E11882_01/server.112/e23633/changes.htm#UPGRD12523

Compiling PL/SQL Units for Native Execution

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/tuning.htm

1.1 PL/SQL Native Compilation

Starting withOracle Database 10g Release 1 (10.1), the configuration of initializationparameters and the command setup for native compilation has been simplified.The important parameters now are PLSQL_NATIVE_LIBRARY_DIR and PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT.

The parametersrelated to the compiler, linker, and make utility have been made obsolete.Native compilation is turned on and off by a separate initializationparameter, PLSQL_CODE_TYPE, rather than being one of several options inthe PLSQL_COMPILER_FLAGS parameter, which is now deprecated.The spnc_commands file, located in the ORACLE_HOME/plsqldirectory, contains the commands and options for compiling and linking, ratherthan a makefile.

--在Oracle10g以后，配置native compilation 相对简单很多，最重要的参数是：PLSQL_NATIVE_LIBRARY_DIR 和 PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT.

关联complier，linker 的参数已经过期。Native compilation 通过一个PLSQL_CODE_TYPE 参考来控制启动和关闭。在10g 之前是使用PLSQL_COMPILER_FLAGS参数。

spnc_commands 文件存在ORACLE_HOME/plsql 目录下。如：

[oracle@rac1plsql]$ pwd

/u01/app/oracle/product/10.2.0/db_1/plsql

[oracle@rac1plsql]$ ls

admin include lib mesg public spnc_commands

[oracle@rac1plsql]$ ll

total 24

drwxr-x--- 2oracle oinstall 4096 Mar 22 2011 admin

drwxr-x--- 2oracle oinstall 4096 Mar 22 2011 include

drwxr-x--- 2oracle oinstall 4096 May 12 2011 lib

drwxr-x--- 2oracle oinstall 4096 Mar 22 2011 mesg

drwxr-x--- 2oracle oinstall 4096 Mar 22 2011 public

-rw-r--r-- 1oracle oinstall 2065 Jun 3 2005 spnc_commands

另一篇官网也提到了Oracle 10g 开始，PLSQL_COMPILER_FLAGS参数被标记为Obsolete, 该参数由 PLSQL_CODE_TYPE 和PLSQL_DEBUG参数代替。具体参考：

A Behavior Changes After Upgrading toOracle Database 11gR2

http://docs.oracle.com/cd/E11882_01/server.112/e23633/changes.htm#UPGRD12573

1.2 Compiling PL/SQL Units for Native Execution

You can usually speed up PL/SQL units bycompiling them into native code (processor-dependent system code), which isstored in the SYSTEM tablespace.

You can natively compile any PL/SQL unit ofany type, including those that Oracle Database supplies.

可以将PL/SQL单元编译到native code里，这样可以提高PL/SQL的速度，编译后的PL/SQL单元存放在SYSTEM 表空间里。可以对任何PL/SQL unit 使用nativecompile。

Natively compiled program units work in all server environments, including shared serverconfiguration (formerly called "multithreaded server") and OracleReal Application Clusters (Oracle RAC).

--Natively compiled 程序单元在所有服务端运行，包括sharedserver 配置和RAC 环境。

On mostplatforms, PL/SQL native compilation requires no special set-up or maintenance.On some platforms, the DBA might want to do some optional configuration.

--在大多数平台下，PL/SQL native compilation 不需要特别的安装和维护，在少数平台下，DBA 可能需要做一些配置。

You can test tosee how much performance gain you can get by enabling PL/SQL nativecompilation.

--可以启动PL/SQL native compilation 测试对性能的提升。

If you havedetermined that PL/SQL native compilation will provide significant performancegains in database operations, Oracle recommends compiling the entire databasefor native mode, which requires DBA privileges. This speeds up both your owncode and calls to the PL/SQL packages that Oracle Database supplies.

--如果提升较大，Oracle 建议使用native mode，这个需要DBA权限。

Topics

(1) DeterminingWhether to Use PL/SQL Native Compilation

(2) HowPL/SQL Native Compilation Works

(3) Dependencies,Invalidation, and Revalidation

(4) SettingUp a New Database for PL/SQL Native Compilation*

(5) Compilingthe Entire Database for PL/SQL Native or Interpreted Compilation*

* Requires DBA privileges.

1.2.1 DeterminingWhether to Use PL/SQL Native Compilation

--决定是否使用PL/SQL Native Compilation

Whether tocompile a PL/SQL unit for native or interpreted mode depends on where you arein the development cycle and on what the program unit does.

--使用native 或者 interpreted 模式决定于开发周期和程序单元。

While you aredebugging program units and recompiling them frequently, interpreted mode hasthese advantages:

--如果是debug 程序单元，且经常recompiling，那么使用interpreted模式有如下好处：

（1）You can usePL/SQL debugging tools on program units compiled for interpreted mode (but notfor those compiled for native mode).

（2）Compiling forinterpreted mode is faster than compiling for native mode.

After thedebugging phase of development, in determining whether to compile a PL/SQL unitfor native mode, consider:

--除了上面提到的development 模式，是否使用native 模式，还需要考虑如下因素：

（1）PL/SQL nativecompilation provides the greatest performance gains for computation-intensiveprocedural operations. Examples are data warehouse applications andapplications with extensive server-side transformations of data for display.

（2）PL/SQL nativecompilation provides the least performance gains for PL/SQL subprograms thatspend most of their time running SQL.

（3）When manyprogram units (typically over 15,000) are compiled for native execution, andare simultaneously active, the large amount of shared memory required mightaffect system performance.

--当很多program units已native execution 编译，当他们同时执行时，就会消耗大量的shared 内存，从而影响系统性能。

1.2.2 How PL/SQLNative Compilation Works

Without nativecompilation, the PL/SQL statements in a PL/SQL unit are compiled into anintermediate form, system code, which is stored in the catalog and interpretedat run time.

--没有使用native compilation时，PL/SQL 单元里的PL/SQL语句会被编译成中间码，系统码（机器可读的形式），其存储在catalog里并在运行时被解释。

With PL/SQLnative compilation, the PL/SQL statements in a PL/SQL unit are compiled intonative code and stored in the catalog. The native code need not be interpretedat run time, so it runs faster.

--如果使用native compilation，那么PL/SQL 单元里的PL/SQL语句就会使用被编译成native code（C代码），然后存储在catalog里。 Native code 在运行时不需要进行解释，所以运行很快，性能会得到提升。

Because nativecompilation applies only to PL/SQL statements, a PL/SQL unit that uses only SQLstatements might not run faster when natively compiled, but it does run atleast as fast as the corresponding interpreted code. The compiled code and theinterpreted code make the same library calls, so their action is the same.

--Native compilation 仅使用于PL/SQL 语句，所以一个PL/SQL单元在使用natively compiled 时可能并不会块多少，但是至少大于等于使用interpretedcode。

Compiled code 和 interpreted code 使用相同的的library calls，所以他们的行为是相同的。

The first time anatively compiled PL/SQL unit runs, it is fetched from the SYSTEM tablespaceinto shared memory. Regardless of how many sessions invoke the program unit,shared memory has only one copy it. If a program unit is not being used, theshared memory it is using might be freed, to reduce memory load.

--当用natively compiled PL/SQL 单元第一次运行时，其会从SYSTEM 表空间里抽取到shared memory里。不管多有sessions 调用这个程序单元，shared memory 只有一份copy。如果程序单元没有使用，那么其可能会从shared memory中释放表，以节省内存空间。

Nativelycompiled subprograms and interpreted subprograms can invoke each other.

--Natively compiled 子程序和 interpreted 子程序之间可以相互调用。

PL/SQL nativecompilation works transparently in an Oracle Real Application Clusters (OracleRAC) environment.

The PLSQL_CODE_TYPE compilationparameter determines whether PL/SQL code is natively compiled or interpreted.

-- PLSQL_CODE_TYPE 参数控制PL/SQL 的模式是nativelycompiled 还是interpreted。

1.2.3 Dependencies,Invalidation, and Revalidation

Recompilation isautomatic with invalidated PL/SQL modules. For example, if an object on which anatively compiled PL/SQL subprogram depends changes, the subprogram isinvalidated. The next time the same subprogram is called, the databaserecompiles the subprogram automatically. Becausethe PLSQL_CODE_TYPE setting is stored inside the library unit foreach subprogram, the automatic recompilation uses this stored setting for codetype.

--当一个PL/SQL 模块失效后会自动进行recompilation。PLSQL_CODE_TYPE的值保存在library unit里，自动recompilation 会使用这个参数值。

Explicitrecompilation does not necessarily use thestored PLSQL_CODE_TYPE setting.

显示的recompilation 不需要使用 PLSQL_CODE_TYPE设置。

For the conditions under which explicit recompilation uses stored settings, see "PL/SQLUnits and Compilation Parameters".

1.2.4 Setting Up aNew Database for PL/SQL Native Compilation

If you have DBAprivileges, you can set up a new database for PL/SQL native compilation bysetting the compilation parameter PLSQL_CODE_TYPE to NATIVE. Theperformance benefits apply to the PL/SQL packages that Oracle Databasesupplies, which are used for many database operations.

--如果是一个新的实例，并且有DBA的权限，可以设置PLSQL_CODE_TYPE为NATIVE来启用Nativecompilation。

Note:

If you compilethe whole database as NATIVE, Oracle recommends that youset PLSQL_CODE_TYPE at the system level.

--如果在整个数据库都设置为native，那么建议在系统级别进行设置。

1.2.5 Compilingthe Entire Database for PL/SQL Native or Interpreted Compilation

If you have DBAprivileges, you can recompile all PL/SQL modules in an existing databaseto NATIVE or INTERPRETED, using the dbmsupgnv.sql and dbmsupgin.sql scripts respectively during the processexplained in this section. Before making the conversion, review "DeterminingWhether to Use PL/SQL Native Compilation".

--如果是对已经创建好的DB修改模式到Native 或者 interpreted，那么需要使用dbmsupgnv.sql和 dbmsupgin.sql脚本。

During theconversion to native compilation, TYPE specifications are notrecompiled by dbmsupgnv.sql to NATIVE because thesespecifications do not contain executable code.

--转到到native compilation时，dbmsupgnv.sql 不会recompiledTYPE specifications到Native 模式，因为这个specifications 不包含执行代码。

Packagespecifications seldom contain executable code so the run-time benefits ofcompiling to NATIVE are not measurable. You can usethe TRUE command-line parameter withthe dbmsupgnv.sql script to exclude package specs from recompilationto NATIVE, saving time in the conversion process.

--Package specifications 很少包含执行代码，所以编译到Native的效率不能估量。可以在命令行执行dbmsupgnv.sql 脚本，从而排除recompilationpackage specs到native，节省转换的时间。

When convertingto interpreted compilation, the dbmsupgin.sql script does not acceptany parameters and does not exclude any PL/SQL units.

--转换到interpreted compilation时，dbmsupgin.sql 脚本不接收任何参数也不排除任何PL/SQL 单元。

Note:

The followingprocedure describes the conversion to native compilation. If you must recompileall PL/SQL modules to interpretedcompilation, make these changes in the steps.

--以下过程描述了转换到native compilation, 如果必须将所有的PL/SQL 模块转换到interpretedcomplilation，那么在转换到native 的基础上改变如下3个步骤即可。

(1) Skip the first step.

(2) Setthe PLSQL_CODE_TYPE compilation parameterto INTERPRETED rather than NATIVE.

(3) Substitute dbmsupgin.sql forthe dbmsupgnv.sql script.

1.Ensure that a test PL/SQL unit can becompiled. For example:

ALTER PROCEDUREmy_proc COMPILE PLSQL_CODE_TYPE=NATIVE REUSE SETTINGS;

2.Shut down application services, thelistener, and the database.

（1）Shut down allof the Application services including the Forms Processes, Web Servers, ReportsServers, and Concurrent Manager Servers. After shutting down all of theApplication services, ensure that all of the connections to the database wereterminated.

（2）Shut down theTNS listener of the database to ensure that no new connections are made.

（3）Shut down thedatabase in normal or immediate mode as the user SYS. Se

3.Set PLSQL_CODE_TYPE to NATIVE inthe compilation parameter file. If the database is using a server parameterfile, then set this after the database has started.

The valueof PLSQL_CODE_TYPE does not affect the conversion of the PL/SQL unitsin these steps. However, it does affect all subsequently compiled units, soexplicitly set it to the desired compilation type.

4.Start up the database in upgrade mode,using the UPGRADE option. For information aboutSQL*Plus STARTUP, see SQL*PlusUser's Guide and Reference.

5.Run this code to list the invalid PL/SQLunits. You can save the output of the query for future reference with the SQL SPOOL statement:

-- To save the output of the query to afile:

SPOOL pre_update_invalid.log

SELECT o.OWNER, o.OBJECT_NAME,o.OBJECT_TYPE

FROM DBA_OBJECTS o,DBA_PLSQL_OBJECT_SETTINGS s

WHERE o.OBJECT_NAME = s.NAME ANDo.STATUS='INVALID';

--查看无效的PL/SQL单元

-- To stop spooling the output: SPOOL OFF

If any Oracle supplied units are invalid,try to validate them by recompiling them. For example:

ALTER PACKAGE SYS.DBMS_OUTPUT COMPILE BODYREUSE SETTINGS;

--编译无效的单元。

If the units cannot be validated, save thespooled log for future resolution and continue.

6.Run this query todetermine how many objects arecompiled NATIVE and INTERPRETED (to save the output, usethe SQL SPOOL statement):

SELECT TYPE, PLSQL_CODE_TYPE, COUNT(*)

FROM DBA_PLSQL_OBJECT_SETTINGS

WHERE PLSQL_CODE_TYPE IS NOT NULL

GROUP BY TYPE, PLSQL_CODE_TYPE

ORDER BY TYPE, PLSQL_CODE_TYPE;

Any objects witha NULL plsql_code_type are special internal objects and can beignored.

7.Runthe $ORACLE_HOME/rdbms/admin/dbmsupgnv.sql script as the user SYS toupdate the plsql_code_type setting to NATIVE in thedictionary tables for all PL/SQL units. This process also invalidates theunits. Use TRUE with the script to exclude packagespecifications; FALSE to include the package specifications.

This update mustbe done when the database is in UPGRADE mode. The script isguaranteed to complete successfully or rollback all the changes.

--在UPGRADE 模式下使用SYS用户来执行 $ORACLE_HOME/rdbms/admin/dbmsupgnv.sql 脚本，更新plsql_code_type设置到数据字典表。

8.Shut down the database and restartin NORMAL mode.

9.Before you runthe utlrp.sql script, Oracle recommends that no other sessions areconnected to avoid possible problems. You can ensure this with this statement:

ALTER SYSTEMENABLE RESTRICTED SESSION;

10.Run the $ORACLE_HOME/rdbms/admin/utlrp.sql scriptas the user SYS. This script recompiles all the PL/SQL modules using adefault degree of parallelism. See the comments in the script for informationabout setting the degree explicitly.

If for anyreason the script is abnormally terminated, rerunthe utlrp.sql script to recompile any remaining invalid PL/SQLmodules.

11.After the compilation completessuccessfully, verify that there are no invalid PL/SQL units using the query instep 5.You can spool the output of the query tothe post_upgrade_invalid.log file and compare the contents withthe pre_upgrade_invalid.log file, if it was created previously.

12.Reexecute the query in step 6.If recompiling with dbmsupgnv.sql, confirm that all PL/SQL units,except TYPE specifications and package specifications if excluded,are NATIVE. If recompiling withdbmsupgin.sql, confirm that all PL/SQLunits are INTERPRETED.

13.Disable the restricted session mode forthe database, then start the services that you previously shut down. To disablerestricted session mode, use this statement:

ALTER SYSTEMDISABLE RESTRICTED SESSION;

二.相关参数说明

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise EditionRelease 10.2.0.4.0 - Prod

PL/SQL Release 10.2.0.4.0 - Production

CORE 10.2.0.4.0 Production

TNS for Linux: Version 10.2.0.4.0 -Production

NLSRTL Version 10.2.0.4.0 - Production

SQL> show parameter plsql

NAME TYPE VALUE

----------------------------------------------- ------------------------------

plsql_ccflags string

plsql_code_type string INTERPRETED

plsql_compiler_flags string INTERPRETED, NON_DEBUG

plsql_debug boolean FALSE

plsql_native_library_dir string

plsql_native_library_subdir_count integer 0

plsql_optimize_level integer 2

plsql_v2_compatibility boolean FALSE

plsql_warnings string DISABLE:ALL

PLSQL_NATIVE_LIBRARY_DIR and PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT.

The parameters related to the compiler,linker, and make utility have been made obsolete. Native compilation is turnedon and off by a separate initialization parameter, PLSQL_CODE_TYPE,

2.1 PLSQL_OPTIMIZE_LEVEL

http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams194.htm#REFRN10255

Property	Description
Parameter type	Integer
Default value	2
Modifiable	ALTER SESSION, ALTER SYSTEM
Range of values	0 to 3

PLSQL_OPTIMIZE_LEVEL specifiesthe optimization level that will be used to compile PL/SQL library units. Thehigher the setting of this parameter, the more effort the compiler makes tooptimize PL/SQL library units.

-- PLSQL_OPTIMIZE_LEVEL 指定optimization 的级别，其值越高，compiler 的性能也越优化。在Oracle 10g中该参数只能从0到2，在Oracle11g里，增加了3. 默认值2.

Values:

（1）0

Maintains theevaluation order and hence the pattern of side effects, exceptions, and packageinitializations of Oracle9i and earlier releases. Also removes the newsemantic identity of BINARY_INTEGER and PLS_INTEGER and restoresthe earlier rules for the evaluation of integer expressions. Although code willrun somewhat faster than it did in Oracle9i, use of level 0 will forfeit mostof the performance gains of PL/SQL in Oracle Database 10g.

（2）1

Applies a widerange of optimizations to PL/SQL programs including the elimination ofunnecessary computations and exceptions, but generally does not move sourcecode out of its original source order.

（3）2

Applies a widerange of modern optimization techniques beyond those of level 1 includingchanges which may move source code relatively far from its original location.

（3）3

Applies a widerange of optimization techniques beyond those of level 2, automaticallyincluding techniques not specifically requested.

Generally,setting this parameter to 2 pays off in better execution performance.If, however, the compiler runs slowly on a particular source module or ifoptimization does not make sense for some reason (for example, during rapidturnaround development), then setting this parameter to 1 will resultin almost as good a compilation with less use of compile-time resources.

The value ofthis parameter is stored persistently with the library unit.

2.2 PLSQL_CODE_TYPE

http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams192.htm#REFRN10253

Property	Description
Parameter type	String
Syntax	PLSQL_CODE_TYPE = { INTERPRETED \| NATIVE }
Default value	INTERPRETED
Modifiable	ALTER SESSION, ALTER SYSTEM

PLSQL_CODE_TYPE specifies thecompilation mode for PL/SQL library units.

--该参数指定compilation模型。有如下值：

Values:

（1）INTERPRETED

PL/SQL libraryunits will be compiled to PL/SQL by tecode format. Such modules are executed bythe PL/SQL interpreter engine.

--声明PL/SQL库单元被编译成PL/SQL字节码格式，这些模块由PL/SQL解释引擎执行；

（2）NATIVE

PL/SQL libraryunits (with the possible exception of top-level anonymous PL/SQL blocks) willbe compiled to native (machine) code. Such modules will be executed nativelywithout incurring any interpreter overhead.

-- PL/SQL库单元被编译成本地机器码；

When the valueof this parameter is changed, it has no effect on PL/SQL library units thathave already been compiled. The value of this parameter is stored persistentlywith each library unit.

--修改该参数不影响已经compiled 的units。

If a PL/SQLlibrary unit is compiled native, all subsequent automatic recompilations ofthat library unit will use native compilation.

2.3 PLSQL_CCFLAGS

http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams191.htm#REFRN10261

Property	Description
Parameter type	String
Syntax	PLSQL_CCFLAGS = '<v1>:<c1>,<v2>:<c2>,...,<vn>:<cn>'
Default value	Empty string
Modifiable	ALTER SESSION, ALTER SYSTEM
Range of values	Any string literal that satisfies the internal syntax
Examples	ALTER SESSION SET PLSQL_CCFLAGS = 'DeBug:TruE'; ALTER SESSION SET PLSQL_CCFLAGS = 'debug:TRUE';

PLSQL_CCFLAGS providesa mechanism that allows PL/SQL programmers to control conditional compilationof each PL/SQL library unit independently.

--PLSQL_CCFLAGS提供一个机制允许PL/SQL 程序控制每个PL/SQL library 单元独立编译的条件。有如下值：

Values:

（1）<vi>has the form of an unquoted PL/SQL identifier. It is unrestricted and can be areserved word or a keyword. The text is insensitive to case. Each one is knownas a flag or flag name. Each <vi> can occur more than once in the string,each occurrence can have a different flag value, and the flag values can be ofdifferent kinds.

（2）<ci> isone of the following: a PL/SQL boolean literal, a PLS_INTEGER literal,or the literal NULL. The text is insensitive to case. Each one is known as aflag value and corresponds to a flag name.

You can defineany allowable value for PLSQL_CCFLAGS. However, Oracle recommends thatthis parameter be used for controlling the conditional compilation of debuggingor tracing code. It is recommended that the following identifiers not be usedas flag name values:

（1）Names ofOracle parameters (for example, NLS_LENGTH_SEMANTICS)

（2）Identifierswith any of the following prefixes: PLS_, PLSQL_, PLSCC_, ORA_, ORACLE_, DBMS_, SYS_

-------------------------------------------------------------------------------------------------------

QQ:492913789

Email:ahdba@qq.com

Blog: http://www.cndba.cn/dave

Weibo: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

Linkedin: http://cn.linkedin.com/in/tianlesoftware

-------加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请----

DBA1 群：62697716(满); DBA2 群：62697977(满) DBA3 群：62697850(满)