Oracle PL/SQL 優化與調整 – PL/SQL Native Compilation 說明

一.相關概述

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

Oracle PL/SQL 優化與調整 -- Bulk 說明

http://blog.csdn.net/tianlesoftware/article/details/6578351

 

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）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 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） 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_

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15880878/viewspace-719934/，如需轉載，請註明出處，否則將追究法律責任。