PL/SQL用户指南与参考--PL/SQL应用程序性能调优（转）

PL/SQL应用程序性能调优

<!-- InstanceEndEditable --><!-- InstanceBeginEditable name="EditRegion2" -->

一、PL/SQL性能问题的原由

当基于PL/SQL的应用程序执行效率低下时，通常是由于糟糕的SQL语句、编程方法，对PL/SQL基础掌握不好或是滥用共享内存造成的。

• PL/SQL中糟糕的SQL语句

PL/SQL编程看起来相对比较简单，因为它们的复杂内容都隐藏在SQL语句中，SQL语句常常分担大量的工作。这就是为什么糟糕的SQL语句是执行效率低下的主要原因了。如果一个程序中包含很多糟糕的SQL语句，那么，无论PL/SQL语句写的有多么好都是无济于事的。

如果SQL语句降低了我们的程序速度的话，就要按下面列表中的方法分析一下它们的执行计划和性能，然后重新编写SQL语句。例如，查询优化器的提示就可能会排除掉问题，如没有必要的全表扫描。

1. EXPLAIN PLAN语句
2. 使用TKPROF的SQL Trace功能
3. Oracle Trace功能

• 不好的编程习惯

通常，不好的编程习惯也会给程序带来负面影响。这种情况下，即使是有经验的程序员写出的代码也可能妨碍性能发挥。

对于给定的一项任务，无论所选的程序语言有多么合适，编写质量较差的子程序(例如，一个很慢的分类或检索函数)可能毁掉整个性能。假设有一个需要被应用程序频繁调用的查询函数，如果这个函数不是使用哈希或二分法，而是直接使用线性查找，就会大大影响效率。不好的程序指的是那些含有从未使用过的变量的，传递没有必要的参数的，把初始化或计算放到不必要的循环中执行的程序等等。

• 内置函数的重复

PL/SQL提供了许多高度优化过的函数，如REPLACE、TRANSLATE、SUBSTR、INSTR、RPAD和LTRIM等。不要手工编写我们自己的版本，因为内置函数已经是很高效的了。即使内置函数的功能远远超过我们的需要，也不要手工实现它们功能的子集。

• 低效的流程控制语句

在计算逻辑表达式值的时候，PL/SQL使用短路的计算方式。也就是说，一旦结果可以被确定下来，PL/SQL就会停止剩余的表达式计算。例如，下面的OR表达式，当sal比1500小的时候，操作符左面的值就是TRUE，所以PL/SQL就不会再计算操作符右边表达式的值：

IF (sal < 1500) OR (comm IS NULL) THEN
  ...
END IF;

现在，思考下面的AND表达式：

IF credit_ok(cust_id) AND (loan < 5000) THEN
  ...
END IF;

在上面的函数中，布尔函数credit_ok总是被调用。但是，如果我们向下面这样调换两个表达式的位置：

IF (loan < 5000) AND credit_ok(cust_id) THEN
  ...
END IF;

那么，函数只有在表达式loan < 5000的值为TRUE的时候才会被调用，这种情况也适用于EXIT-WHEN语句。

• 隐式的数据类型转换

运行时，PL/SQL能把结构化不同的数据类型进行隐式的转换。比如说，把PLS_INTEGER变量赋给一个NUMBER变量，由于它们的内在表现形式不一样，所以就会引起隐式地数据类型转换。

避免隐式的类型转换可以改善性能。如下面的例子，15是一个有符号的4字节数字，在加法运算之前，PL/SQL必须把它转换成Oracle的数字类型。但是，浮点数15.0使用22字节的Oracle数字表现，所以就没有必要进行转换。

DECLARE
  n   NUMBER;
  c   CHAR(5);
BEGIN
  n := n + 15;   -- converted
  n := n + 15.0;   -- not converted
  ...
END;

这里还有一个例子：

DECLARE
  c   CHAR(5);
BEGIN
  c := 25;   -- converted
  c := '25';   -- not converted
  ...
END;

• 不适当的数字类型声明

数据类型NUMBER和它的子类型都是22字节，数据库格式的数字，它们便于移植并且能适应于不同的长度与精度。当我们需要声明一个整型变量时，就要使用PLS_INTEGER，它是最高效的数字类型。这是因为PLS_INTEGER所需的内存要比INTEGER和NUMBER类型要少。同样， PLS_INTEGER使用机器运算，所以它的运算速度要比BINARY_INTEGER、INTEGER或NUMBER快得多。

此外，INTEGER、NATURAL、NATURALN、POSITIVE、POSITIVEN和SIGNTYPE都是受约束的子类型。所以，它们的变量需要在运行时检查精度，这就会影响到效率。

• 不必要的NOT NULL约束

PL/SQL中，使用NOT NULL约束也能导致性能损耗。如下例所示：

PROCEDURE calc_m IS
  m   NUMBER NOT NULL := 0;
  a   NUMBER;
  b   NUMBER;
BEGIN
  ...
  m := a + b;
  ...
END;

因为m是受NOT NULL约束的，表达式a + b的值就会赋给临时变量，然后PL/SQL会对这个临时变量作判空测试。如果变量不是空，它的值就能赋给m，否则就会出现异常。但是，如果m不是有约束限制的话，结果值就会直接赋给m。更高效的写法如下：

PROCEDURE calc_m IS
  m   NUMBER;   -- no constraint
  a   NUMBER;
  b   NUMBER;
BEGIN
  ...
  m := a + b;
  IF m IS NULL THEN   -- enforce constraint programmatically
  ...
  END IF;
END;

注意，NATURALN和POSTIVEN都是NOT NULL，所以它们也同样会影响性能。

• VARCHAR2变量的长度声明

对于VARCHAR2类型，我们在内存使用和效率上需要做出一个权衡。对于VARCHAR2(长度>=2000)变量，PL/SQL动态分配内存来存放实际值，但对于VARCHAR2(长度<2000)变量，PL/SQL会预先分配足够的内存。所以，如果我们把同样一个500字节的值放入一个VARCHAR2(2000)和一个VARCHAR2(1999)变量中，后者会多占用1499个字节的内存。

• 滥用PL/SQL程序中的共享内存

第一次调用打包子程序时，整个包会被加载到共享内存池。所以，以后调用包内相关子程序时，就不再需要读取磁盘了，这样会加快我们的代码会执行速度。但是，如果包从内存中清除之后，我们在重新引用它的时候，就必须重新加载它。

我们可以通过正确地设置共享内存池大小来改善性能。一定要确保共享内存有足够空间来存放被频繁使用的包，但空间也不要过大，以免浪费内存。

• 保持包(Pinned Packages)

另外一个改善性能的方法就是把频繁使用的包保持到共享内存池中。当一个包被保持下来后，它就不会被Oracle通常所采用的最少最近使用(LRU) 算法清除。不管池有多满或是我们访问包有多么频繁，包始终会被保持在池中的。我们可以利用系统包DBMS_SHARED_POOL把包保持下来。

• 可连续重用包

为了帮助我们管理内存的使用，PL/SQL提供了编译指示SERIALLY_REUSABLE，它能让我们把某些包标记为可连续重用的 (serially reusable)。如果一个包的状态只在服务器呼叫时间内所需要，那么我们就可以对这个包使用这个标记了(例如，一个对服务器的OCI调用或是服务器对服务器RPC)。

对于这样的包所分配的内存会放到系统全局区(SGA)中，而不是分配到独立的用户所使用的用户全局区(UGA)。那样，包的工作区就可以被反复使用。当服务器调用结束的时候，内存就会被还给共享池。每次包被重用时，它的公共变量就会被初始化为它们的默认值或NULL。

一个包所需的工作区最大个数就是当前使用这个包的用户数，这个数字通常要小于登录用户数。SGA内存的增长量要大于UGA内存的缩减量。并且，如果Oracle要回收SGA内存的话，它就会把没有使用的工作区进行过期处理。

对于没有包体的包来说，我们可以使用在包说明中使用下面语法编写编译指示：

PRAGMA SERIALLY_REUSABLE;

对于有包体的包来说，我们必须在说明和包体中编写编译指示。我们不能只在包体中编写编译指示。下面的例子演示了如何在一个连续重用包中使用一个公共变量：

CREATE PACKAGE pkg1 IS
  PRAGMA SERIALLY_REUSABLE;
  num   NUMBER := 0;
  PROCEDURE init_pkg_state(n NUMBER);
  PROCEDURE print_pkg_state;
END pkg1;
/
CREATE PACKAGE BODY pkg1 IS
PRAGMA SERIALLY_REUSABLE; 
PROCEDURE init_pkg_state(n NUMBER) IS
BEGIN
  pkg1.num := n;
END;
PROCEDURE print_pkg_state IS
BEGIN
  dbms_output.put_line('Num: ' || pkg1.num);
END;
END pkg1;
/
BEGIN
  /* Initialize package state. */
  pkg1.init_pkg_state(4);
  /* On same server call, print package state. */
  pkg1.print_pkg_state;   -- prints 4
END;
/
-- subsequent server call
BEGIN
  -- the package public variable is initialized
  -- to the default value automatically
  pkg1.print_pkg_state;   -- prints 0
END;

二、确定PL/SQL的性能问题

当我们开发越来越大的PL/SQL应用程序时，就难免要碰到性能问题。所以，PL/SQL为我们提供了Profiler API来剖析运行时行为并帮助我们辨识性能瓶颈。PL/SQL也提供了一个Trace API用来跟踪服务器端的程序执行。我们可以使用Trace来跟踪子程序或异常的执行。

1、Profiler API：DBMS_PROFILER包

Profiler API由PL/SQL包DBMS_PROFILER实现，它提供了收集并保存运行时的统计信息。这些信息会被保存在数据表中，供我们查询。例如，我们可以知道PL/SQL每行和每个子程序执行所花费的时间长短。

要使用Profiler，先开启一个性能评测会话，充分地运行我们的应用程序以便达到足够的代码覆盖率，然后把收集到的信息保存在数据库中，停止性能评测会话。具体步骤如下：

1. 调用DBMS_PROFILER包中的start_profiler过程，把一个注释与性能评测会话关联。
2. 运行要被评测的应用程序。
3. 反复调用过程flush_data把收集到的数据保存下来并释放内存。
4. 调用stop_profiler过程停止会话。

Profiler能跟踪程序的执行，计算每行和每个子程序所花费的时间。我们可以用收集到的数据帮助改善性能。例如，我们可以集中处理那些运行慢的子程序。

• 分析收集到的性能数据

下一步要判断出为什么执行某些代码段或访问某些数据结构要花费大量的时间。借助查询出来的性能数据来找到问题点。把问题集中到那些耗费时间长的子程序和包，尽可能的优化SQL语句、循环和递归函数等。

• 使用跟踪数据改善程序性能

使用我们的分析结果重新编写那些执行效率低下的算法。例如，在急剧膨胀的数据中，我们可能要需要使用二分法来替代线性搜索。

2、Trace API：包DBMS_TRACE

在大而复杂的应用程序中，很难跟踪子程序的调用。如果使用跟踪API，我们就能看到子程序的执行顺序。跟踪API是由包DBMS_TRACE实现的，并提供了跟踪子程序或异常的服务。

要使用跟踪，先要开启一个跟踪会话，运行程序，然后停止跟踪会话。当程序执行时，跟踪数据就会把收集并保存到数据库中。在一个会话中，我们可以采用如下步骤来执行跟踪操作：

1. 可选步骤，选择要跟踪的某个特定的子程序。
2. 调用DBMS_TRACE包中的set_plsql_trace开启跟踪。
3. 运行要跟踪的应用程序。
4. 调用过程clear_plsql_trace来停止跟踪。

• 控制跟踪

跟踪大型应用程序可能会制造出大量的难以管理的数据。在开启跟踪之前，我们可以选择是否限制要收集的数据量。

此外，还可以选择跟踪级别。例如，如果我们可以选择跟踪全部的子程序和异常或是只跟踪选定的子程序和异常。

三、PL/SQL性能优化特性

我们可以使用下面的PL/SQL特性和方法来优化应用程序：

1. 使用本地动态SQL优化PL/SQL
2. 使用批量绑定优化PL/SQL
3. 使用NOCOPY编译器提示优化PL/SQL
4. 使用RETURNING子句优化PL/SQL
5. 使用外部程序优化PL/SQL
6. 使用对象类型和集合优化PL/SQL

这些简单易用的特性可以显著的提高应用程序的执行速度。

1、使用本地动态SQL优化PL/SQL

有些程序必须要执行一些只有在运行时才能确定下来的SQL语句，这些语句被称为动态SQL语句。以前，要执行动态SQL语句就必须使用包DBMS_SQL。现在，我们可以在PL/SQL中直接使用被称为本地动态SQL的接口来执行各种动态SQL语句。

本地动态SQL更容易使用，并且执行速度也要比DBMS_SQL包快。在下面的例子中，我们声明一个游标变量，然后把它与一个能返回数据表emp记录的动态的SELECT语句关联起来：

DECLARE
  TYPE empcurtyp IS REF CURSOR;
  emp_cv   empcurtyp;
  my_ename VARCHAR2(15);
  my_sal   NUMBER := 1000;
BEGIN
  OPEN emp_cv FOR 'SELECT ename, sal FROM emp WHERE sal > :s'
    USING my_sal;
  ...
END;

2、使用批量绑定优化PL/SQL

当SQL在集合的循环内执行时，PL/SQL和SQL引擎间的频繁切换就会影响到执行速度。例如，下面的UPDATE语句就在FOR语句中不断发送到SQL引擎：

DECLARE
  TYPE numlist IS VARRAY(20) OF NUMBER;
  depts numlist := numlist(10, 30, 70, .. .);   -- department numbers
BEGIN
  ...
  FOR i IN depts.FIRST .. depts.LAST LOOP
    ...
    UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(i);
  END LOOP;
END;

在这样的情况下，如果SQL语句影响到四行或更多行数据时，使用批量绑定就会显著地提高性能。例如，下面的UPDATE语句可以一次就把整个嵌套表的数据发送到SQL引擎中：

FORALL i IN depts.FIRST..depts.LAST
  UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(i);

要想尽最大可能地提高性能，我们就需要像下面这样编写程序：

1. 如果一条INSERT、UPDATE或DELETE语句在循环内执行，并且引用到集合中的元素，那么，就把它放到FORALL语句中去。
2. 如果SELECT INTO、FETCHE INTO或RETURNING INTO子句引用了一个集合，那就配合BULK COLLECT子句一起使用。
3. 如果可能的话，尽量在应用程序和服务器之间使用主数组传递集合。
4. 如果DML操作失败时且不是很重大的问题，就可以在FORALL语句中使用SAVE EXCEPTIONS，然后在以后的循环中使用%BULK_EXCEPTIONS属性报告或清除错误。

不要忽略这些小问题，因为它们可以帮助我们分析流程控制和程序的依赖性。

3、使用NOCOPY编译器提示优化PL/SQL

默认情况下，OUT和IN OUT模式的参数都是按值传递的。也就是说，一个IN OUT实参会把它的副本拷贝到对应的形参中。然后，如果程序执行正确的话，这个值又会重新赋给OUT和IN OUT的实参。

但实参是集合、记录和对象实例这样的大的数据结构时，生成一个副本会极大地降低执行效率并消耗大量内存的。为了解决这个问题，我们可以使用编译器提示NOCOPY，它能让编译器把OUT和IN OUT参数按引用传递。下例中，我们就能让编译器按引用传递IN OUT参数my_unit：

DECLARE
  TYPE platoon IS VARRAY(200) OF soldier;
  PROCEDURE reorganize(my_unit IN OUT NOCOPY platoon) IS   ...
  BEGIN
    ...
  END;
END;

4、使用RETURNING子句优化PL/SQL

通常，应用程序需要得到SQL操作所影响到的行信息。INSERT、UPDATE和DELETE语句都可以包含一个RETURNING子句，这样就能返回处理过的字段信息。也就不用在INSERT、UPDATE之后或DELETE之前使用SELECT来查询影响到的数据。这样也能够减少网络流量，缩短CPU时间，需要更少量的游标和服务器内存需求。

在下面的例子中，我们就在更新雇员工资的同时，把当前雇员的姓名和新的工资赋给PL/SQL变量：

PROCEDURE update_salary(emp_id NUMBER) IS
  "name"    VARCHAR2(15);
  new_sal   NUMBER;
BEGIN
  UPDATE emp 
     SET sal = sal * 1.1
   WHERE empno = emp_id RETURNING ename, sal INTO "name", new_sal;
  -- Now do computations involving name and new_sal
END;

5、使用外部程序优化PL/SQL

PL/SQL提供了调用其他语言编写的程序的接口。PL/SQL可以从程序中调用其他语言所编写的标准库。这就能够提高可重用性、高效性和程序的模块化。

PL/SQL是专门用来进行SQL事务处理的。有些任务在像C这样的低阶语言中处理起来会更加有效。

为了提高执行速度，我们可以用C语言重新编写受计算量限制的程序。此外，我们还可以把这样的程序从客户端移植到服务器端，这样可以减少网络流量，更有效地利用资源。

例如，我们用C语言写一个使用图形对象类型的方法，把它封装到动态链接库(DLL)中，并在PL/SQL中注册，然后我们就能从应用程序中调用它。运行时，库会被动态地加载，为了安全起见，它会在一个单独的地址空间运行。

6、使用对象类型和集合优化PL/SQL

集合类型和对象类型在对真实世界中的实体进行数据建模时能帮助我们提高效率。复杂的实体和关系会被直接映射到对象类型中。并且，一个构建良好的对象模型能够消除多表连接，减少来回往返等等，从而改善应用程序性能。

客户端程序，包括PL/SQL程序，可以声明对象和集合，把它们作为参数传递，存放在数据库中，检索等等。同样，对象类型还可以把数据操作进行封装，把数据维护代码从SQL脚本中移出，把PL/SQL块放入方法中。

对象和集合在存储和检索方面更加高效，因为它们是作为一个整体进行操作的。同样，对象类型还能和数据库整合在一起，利用Oracle本身所提供的易扩缩性和性能改善等优点。

7、编译本地执行的PL/SQL代码

我们可以把PL/SQL过程编译成本地代码放到共享库中，这样就能提高它的执行速度。过程还可以被转换成C代码，然后用普通的C编译器编译，连接到 Oracle进程中。我们可以在Oracle提供的包和我们自己编写的过程中使用这项技术。这样编译出来的过程可以在各种服务器环境中工作。因为这项技术对从PL/SQL中调用的SQL语句提高效率并不明显，所以它通常应用在计算度高而执行SQL时间不多的PL/SQL过程上。

要提高一个或多个过程的执行速度，我们可以这样使用这项技术：

1. 更新makefile并为我们的系统键入适当的路径和其他值。makefile路径是$ORACLE_HOME/plsql/spnc_makefile.mk。
2. 通过使用ALTER SYSTEM或ALTER SESSION命令，或通过更新初始化文件，设置参数PLSQL_COMPILER_FLAGS来包含值NATIVE。默认设置包含的值是INTERPRETED，我们必须把它从参数值中删除。
3. 使用下面几个方法编译一个或多个过程：
   1. 使用ALTER PROCEDURE或ALTER PACKAGE命令重新编译过程或整个包。
   2. 删除过程并重新创建。
      
   3. 使用CREATE OR REPLACE重新编译过程。
      
   4. 运行SQL*Plus脚本建立一组Oracle系统包。
      
   5. 用含有PLSQL_COMPILER_FLAGS=NATIVE的初始化文件创建数据库。在创建数据库时，用UTLIRP脚本运行并编译Oracle系统包。
4. 要确定我们所做的步骤是否有效，可以查询数据词典来查看过程是否是被编译为本地执行，查询用的视图是USER_STORED_SETTINGS、 DBA_STORED_SETTINGS和ALL_STORED_SETTINGS。例如，要查看MY_PROC的状态，我们可以输入：

   SELECT param_value
     FROM user_stored_settings
    WHERE param_name = 'PLSQL_COMPILER_FLAGS'
      AND object_name = 'MY_PROC';

   PARAM_VALUE字段值为NATIVE时，代表过程是被编译本地执行的，否则就是INTERPRETED。

过程编译后就会被转到共享库，它们会被自动地连接到Oracle进程中。我们不需要重新启动数据库，或是把共享库放到另外一个地方。我们可以在存储过程之间反复调用它们，不管它们是以默认方式(interpreted)编译，本地执行方式编译还是采用两种混合的编译方式。

因为PLSQL_COMPILER_FLAGS设置是保存在每个过程的库单元里的，当被编译成本地执行的过程失效时，在重新编译的时候还会采用原先的编译方式。

我们可以通过ALTER SYSTEM或ALTER SESSION命令，或通过设置初始化文件中的参数来控制PL/SQL本地编译的行为：

1. PLSQL_COMPILER_FLAGS
2. PLSQL_NATIVE_LIBRARY_DIR (因为安全原因，不能使用ALTER SESSION进行设置)
3. PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT
4. PLSQL_NATIVE_MAKE_UTILITY
5. PLSQL_NATIVE_MAKE_FILE_NAME

编译本地执行的PL/SQL过程举例：

CONNECT scott/tiger;
SET serveroutput ON;
ALTER SESSION SET plsql_native_library_dir='/home/orauser/lib';
ALTER SESSION SET plsql_native_make_utility='gmake';
ALTER SESSION SET plsql_native_make_file_name='/home/orauser/spnc_makefile.mk';
ALTER SESSION SET plsql_compiler_flags='NATIVE';

CREATE OR REPLACE PROCEDURE hello_native_compilation AS
BEGIN
  dbms_output.put_line('hello world');
  SELECT SYSDATE FROM dual;
END;

过程编译时，我们可以看到各种被执行的编译和连接命令。然后过程就马上可以被调用，直接在Oracle进程中被作为共享库直接运行。