PLSQL程序调优系列八

PLSQL程序调优系列八（编译PL/SQL代码本地执行）

       可以通过编译PL/SQL过程到本地码驻留在共享库内提高性能，这些过程将会被编译成C代码，然后使用C编译器变异并链接到Oracle进程。

       可以使用本地编译编译Oracle内置过程或者用户自定义过程。这种方式可以在MTS和RAC下工作。

准备工作

       必须首先在测试环境下试验，然后考虑是否在生产系统下使用。

       通常在配置数据库为本地编译前备份数据库，如果发现编译花费的时间超过了得到的性能好处，可以转储备份的数据库，因为重新编议回推断器模式花费的时间会更长。

       某些步骤需要DBA权限，必须在服务器上改变某些初始化参数，创建一个目录，并且这个目录在服务器实例的数据文件附近。服务器同时需要C编译器，在RAC下，每个节点都需要一个编译器。具体的预要求在Oracle的安装文档中有详细的解释。

决定是否使用PL/SQL的本地编译

       对于计算为主的应用程序，PL/SQL本地编译提供了最好的性能，特别是数据仓库应用程序和在服务器端转换显示的数据的应用程序。

       由于该技术不能提高SQL的速度，因此特别适合于那些主要时间不是花费在SQL处理上的应用程序。可以通过比较本地编译和推断器编译查看性能。

       编译程序单元花费的时间将比默认的编辑更长的时间，因此在开发过程中，对于经常需要重新编译的程序应该关闭本地编译。

PL/SQL本地编译的工作机制

       如果不使用本地编译，PL/SQL程序单元将编译为中间码，机器可读的形式。机器码存储在DD中并在运行时被推断。使用PL/SQL本地编译，PL/SQL语句将转换为C代码以绕过运行时推断，得到更好的运行时性能。

       PLSQL_初始化参数设置PL/SQL本地编译的环境，PL/SQL使用$ORACLE_HOME/plsql/spnc_commands命令文件，和操作系统支持的C编译器以及链接器编译和链接C代码到共享库。

       共享库存储在DD中，因此它们被自动备份以防止被删除。这些共享库文件被拷贝到文件系统并在PL/SQL子程序被调用时加载和运行。如果文件在数据库关闭时被删除，或者改变了保存库的目录，它们将会被自动提取。

       虽然仅仅调用SQL的PL/SQL可能没有提高性能或者很少，但是本地编译的PL/SQL至少等于相应的推断模式的性能，即大于等于。编译的代码与相应的推断模式的库调用相同，即行为完全相同。

依赖性，失效和重新生效

       在过程被编译并转化到共享库后，它们将自动被链接到Oracle进程。不需要重启数据库或者移动共享库到其他位置。可以在存储过程之间来回调用，不管它们全部是默认模式还是本地编译模式，或者混合模式。

       重新编译在无效的PL/SQL模块上自动进行。例如，如果一个本地编译的PL/SQL模块以来的对象被改变了，子程序将会失效。当下一次相同的子程序被重新调用时，数据库将自动重编译子程序。由于PLSQL_CODE_TYPE设置存储在每个子程序的库单元内，自动重编译将使用这个存储设置的代码类型。

       只有当重新编译是重新生效的一部分时，存储设置才会有用。如果PL/SQL子程序显示通过CREATE OR REPLACE or ALTER...COMPILE编译，将使用当前的会话设置。产生的共享库存储在SYSTEM表空间中，当本地编译的过程第一次执行时，相应的库文件将被从数据库拷贝到PLSQL_NATIVE_LIBRARY_DIR声明的目录。

RAC和本地编译

       每个节点都需要C编译器并且进行恰当的配置，以及到$ORACLE_HOME/plsql/spnc_commands的路径。

       在RAC中使用PLSQL本地编译时，共享库文件的原始拷贝存储在数据库中，并且他们会被自动传播到所有节点，不需要额外的工作。

       确保所有的RAC节点使用相同的配置，并且确保各个节点的PLSQL_NATIVE_LIBRARY_DIR设置都相同。

本地编译的限制

       ·PL/SQL的DEBUG工具不能处理编译为本地执行的过程；

       ·当大量的过程/包被编译为本地执行时，将所有共享对象存储在一个目录中将会影响系统性能。

spnc_commands文件

       spnc_commands文件在$ORACLE_HOME/plsql目录下，包含了编译和链接到每个程序的命令模板。spnc_commands命令中包含了默认C编译器的位置，只有一个编译器可以用来编译PL/SQL模块，不要使用不同的编译器编译PL/SQL模块。

       可以查看spnc_commands确认命令模板的正确性。不应该改变该文件除非SA改变了C的安装路径。

PL/SQL本地编译的初始化参数设置

       查看当前设置可以使用：SHOW PARAMETERS PLSQL

       PLSQL_NATIVE_LIBRARY_DIR初始化参数

       这是系统级别唯一要求的参数设置，声明包含编译的PL/SQL代码的共享库的全路径和目录名，该值必须被显示设置并指向已存在，可访问的目录；该目录不能包含变量如ORACLE_HOME。

       如：PLSQL_NATIVE_LIBRARY_DIR='/oracle/oradata/db1/natlib'

       或：ALTER SYSTEM SET PLSQL_NATIVE_LIBRARY_DIR='/oracle/oradata/db1/natlib'；

       根据OFA，Oracle推荐将该目录设置为数据文件所在目录的子目录；

       PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT初始化参数

       该参数声明PLSQL_NATIVE_LIBRARY_DIR目录中可以创建的子目录的数量。

       如：PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT=1000;

       PLSQL_CODE_TYPE初始化参数

       PLSQL_CODE_TYPE声明PL/SQL被编译为本地或者推断器模式；该参数可以是系统级别，会话级别，以及具体的PL/SQL模块。如果编译整个数据库为NATIVE，Oracle推荐将PLSQL_CODE_TYPE在系统级别设置。

ALTER SESSION SET PLSQL_CODE_TYPE='NATIVE';
ALTER SESSION SET PLSQL_CODE_TYPE='INTERPRETED';

ALTER PROCEDURE hello_native COMPILE PLSQL_CODE_TYPE=NATIVE REUSE

SETTINGS;

       需要注意，包声明和包体不需要编译为相同的模式。

设置PL/SQL库本地子目录

       默认情况下，PL/SQL存储在一个子目录。但是如果文件很多，OS可能比较难以在一个目录中处理。Oracle推荐将文件放在多个子目录下。

       如果要迁移一个数据库到新的位置，或者要设置一个测试数据库，需要使用以下查询确定使用的PL/SQL程序单元数。

       如果要设置本地库子目录，首先创建顺序的子目录，可以使用以下脚本：

SPOOL make_dirs
BEGIN
  FOR j IN 0..1000 -- change to the number of directories needed
  LOOP
    DBMS_OUTPUT.PUT_LINE ( 'mkdir d' || TO_CHAR(j) );
  END LOOP;
END;
/
SPOOL OFF

       然后设置PLSQL_NATIVE_LIBARY_SUBDIR_COUNT参数：

       ALTER SYSTEM SET PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT=1000;

 

设置和测试PL/SQL本地编译

       设置和测试一个/多个子程序使用本地编译的过程如下：

·设置必要的参数；

·编译一个/多个子程序；

CREATE OR REPLACE创建和重编译子程序；

ALTER PROCEDURE, ALTER FUNCTION, or ALTER PACKAGE…COMPILE PLSQL_CODE_TYPE=NATIVE REUSE SETTINGS;

使用预定义初始化参数PLSQL_CODE_TYPE=NATIVE创建一个数据库；

如下：

-- PLSQL_NATIVE_LIBRARY_DIR must be set to an existing, accessible directory

SET SERVEROUTPUT ON FORMAT WRAPPED

 

CREATE OR REPLACE PROCEDURE hello_native AS

BEGIN

  DBMS_OUTPUT.PUT_LINE('Hello world. Today is ' || TO_CHAR(SYSDATE) || '.');

END hello_native;

/

 

ALTER PROCEDURE hello_native COMPILE PLSQL_CODE_TYPE=NATIVE REUSE SETTINGS;

SHOW ERRORS

-- check for a file HELLO_NATIVE... in the PLSQL_NATIVE_LIBRARY_DIR directory

CALL hello_native();

·为了确保能够正确工作，可以查询DD确定过程被编译为本地执行。ALL_PLSQL_OBJECT_SETTINGS视图的PLSQL_CODE_TYPE列为NATIVE。

设置一个新的数据库为PL/SQL本地编译

·确保spnc_commands有正确的命令模板；

·确保PL/SQL本地库目录在每个数据库已创建；

  必须在每个数据库上设置了PL/SQL库；

  创建一个目录；

·设置必要的参数；

  如果使用DBCA，确保PLSQL_NATIVE_LIBRARY_DIR指向可访问的目录并且PLSQL_CODE_TYPE为NATIVE。

更改数据库的配置

1．  确保配置了以下条件：
确保spnc_commands具有正确的命令模板并且安装了恰当的C编译器；
设置了PLSQL_NATIVE_LIBRARY_DIR参数；
设置了PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT；
可以编译单个PL/SQL单元；

2．  关闭应用程序服务，监听，数据库；
关闭所有应用程序服务，包括FORMS，WEB服务器等；
关闭TNS监听；
正常或者IMMEDIATE关闭数据库；

3．  在数据库初始化参数文件中设置PLSQL_CODE_TYPE为TRUE；

4．  使用UPGRADE选项启动数据库；

5．  执行以下过程列出所有INVALID的PL/SQL单元。

SELECT o.OWNER, o.OBJECT_NAME, o.OBJECT_TYPE
  FROM DBA_OBJECTS o, DBA_PLSQL_OBJECT_SETTINGS s
 WHERE o.OBJECT_NAME = s.NAME
   AND o.STATUS = 'INVALID';

如果任何内置的对象无效，尝试使它们重新生效：

ALTER PACKAGE OLAPSYS.DBMS_AWM COMPILE BODY REUSE SETTINGS;

       6．执行以下查询确定NATIVE和INTERPRETED的对象的数量

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;

plsql_code_type为NULL的对象可以忽略。

7．以SYS运行$ORACLE_HOME/rdbms/admin/dbmsupgnv.sql更新DD中所有PL/SQL单元的plsql_code_type为NATIVE，然后使用TRUE排除包声明。该过程必须在数据库在UPGRADE模式时完成。

8．关闭并且NORMAL启动数据库，执行ALTER SYSTEM ENABLE RESTRICTED SESSION；

9．运行$ORACLE_HOME/rdbms/admin/utlrp.sql命令，该脚本重编译所有PL/SQL模块，如果非正常终止，则重新运行。

       10．在编译完全成功后，确保没有新的无效PL/SQL，使用第五步的脚本；

11．运行第6步，确保除TYPE 和包声明外，全部PL/SQL单元为NATIVE，如果运行了dbmsupgin.sql，则全部为INTERPRETED。

12．执行ALTER SYSTEM DISABLE RESTRICTED SESSION;