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

最新推荐文章于 2024-09-21 10:25:52 发布

liangdiwei

最新推荐文章于 2024-09-21 10:25:52 发布

阅读量591

点赞数

文章标签： profiler returning sql integer compiler 存储

本文链接：https://blog.csdn.net/liangdiwei/article/details/5051849

版权

第十二章 PL/SQL应用程序性能调优

1、PL/SQL性能问题的缘由

应基于PL/SQL的应用程序施行效率低下时，通常是因为不好的SQL话语、编程步骤，对PL/SQL基础掌握糟糕或是乱用共享内存储器促成的。

•PL/SQL中不好的SQL话语
PL/SQL编程看上去相对照较简单，由于它们的复杂内容都掩藏在SQL话语中，SQL话语经常分担大量的工作。这乃是为何不好的SQL话语是施行效率低下的重要缘故了。如若一个程序中包孕很多不好的SQL话语，那么，无论是PL/SQL话语写的有何其美都是于事无补的。

如其SQL话语减低了我们的程序速度的话，将要按底下列表中的步骤分析一下子它们的执行计划和性能，其后从新编纂SQL话语。比如，查询优化器的揭示就可能会排除掉问题，如没有必要的全表扫描。

一.EXPLAIN PLAN话语
二.施用TKPROF的SQL Trace效能
三.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(五);
BEGIN
n := n + 15; -- converted
n := n + 15.0; -- not converted
...
END;

这边再有一个事例：

DECLARE
c CHAR(五);
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 := 零;
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，之所以它们也同样会影响性能。

•VARCHAR二变量的长度宣言
至于VARCHAR2部类，我们在内存储器应用和效率上亟需作出一个衡量。关于VARCHAR二(长度>=2000)变量，PL/SQL动态分配内存储器来寄存现实值，但至于VARCHAR二(长度<2000)变量，PL/SQL能之前分配足够的内存储器。之所以，如其我们把一样一个500字节的值放入一个VARCHAR二(2000)和一个VARCHAR二(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 pkg一 IS
PRAGMA SERIALLY_REUSABLE;
num NUMBER := 零;
PROCEDURE init_pkg_state(n NUMBER);
PROCEDURE print_pkg_state;
END pkg一;
/
CREATE PACKAGE BODY pkg一 IS
PRAGMA SERIALLY_REUSABLE;
PROCEDURE init_pkg_state(n NUMBER) IS
BEGIN
pkg一.num := n;
END;
PROCEDURE print_pkg_state IS
BEGIN
dbms_output.put_line('Num: ' || pkg一.num);
END;
END pkg一;
/
BEGIN
/* Initialize package state. */
pkg一.init_pkg_state(四);
/* On same server call, print package state. */
pkg一.print_pkg_state; -- prints 四
END;
/
-- subsequent server call
BEGIN
-- the package public variable is initialized
-- to the default value automatically
pkg一.print_pkg_state; -- prints 零
END;

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

当我们开发越来越大的PL/SQL应用程序时，便在所难免要遇到性能问题。之所以，PL/SQL为我们提供了Profiler API来辨析运行时举动并相助我们辨认性能瓶颈。PL/SQL也提供了一个Trace API用于追踪服务器端的程序施行。我们可以施用Trace来追踪子程序或异常的施行。

一、Profiler API：DBMS_PROFILER包

Profiler API由PL/SQL包DBMS_PROFILER兑现，它提供了征集并保留运行时的统计信息。这些信息会被封存在数据表中，供我们查询。比如，我们可以晓得PL/SQL每行和每个儿程序施行所花消的时间长短。

要应用Profiler，先开启一个性能测评对话，充分地运作我们的应用程序以便达到足够的代码覆盖率，其后把收集到的信息保留在数据库中，终止性能估测对话。具体方法如次：

一.调用DBMS_PROFILER包中的start_profiler进程，把一个诠释与性能估测对话联系关系。
二.运作要被估测的应用程序。
三.重复调用历程flush_data把收集到的数据保留下来并开释内存储器。
四.调用stop_profiler进程终止对话。
Profiler会追踪程序的施行，计算每行和每个儿程序所开销的时间。我们可以用收集到的数据相助改进性能。比如，我们可以集中处置那些运作慢的子程序。

•分析收集到的性能数据
下一步要判断出为啥实施某些代码段或访问某些数据结构要费用大量的时间。凭借查询出来的性能数据来找出问题点。把问题集中到那些消耗时间长的子程序和包，尽量的优化SQL话语、循环和递归函数等。

•施用追踪数据改进程序性能
应用我们的分析结果从新编撰那些施行效率低下的算法。比如，在急遽暴胀的数据中，我们可能要亟需运用二分法来顶替线性搜寻。

二、Trace API：包DBMS_TRACE

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

要应用追踪，先要开启一个追踪对话，运作程序，其后终止追踪对话。应程序执行时，追踪数据就会把搜集并保留到数据库中。在一个对话中，我们可以采取如次方法来施行追踪操作：

一.可选方法，抉择要追踪的某个特定的子程序。
二.调用DBMS_TRACE包中的set_plsql_trace开启追踪。
三.运作要追踪的应用程序。
四.调用进程clear_plsql_trace来终止追踪。
•统制追踪
追踪特大型应用程序可能会制造出大量的难以治理的数据。在开启追踪事前，我们可以取舍是不是限制要搜集的数据量。

另外，还可以抉择追踪级别。比如，如若我们可以抉择追踪所有的子程序和异常或是只追踪选出的子程序和异常。

3、PL/SQL性能优化特点

我们可以运用下边的PL/SQL特点和步骤来优化应用程序：

一.应用当地动态SQL优化PL/SQL
二.应用批量绑定优化PL/SQL
三.施用NOCOPY编译器揭示优化PL/SQL
四.应用RETURNING子句优化PL/SQL
五.施用外部程序优化PL/SQL
六.应用对象部类和聚合优化PL/SQL
这些简略易用的特点可以显明的增高应用程序的施行速度。

一、运用当地动态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 VARCHAR二(15);
my_sal NUMBER := 1000;
BEGIN
OPEN emp_cv FOR 'SELECT ename, sal FROM emp WHERE sal > :s'
USING my_sal;
...
END;

二、应用批量绑定优化PL/SQL

应SQL在聚合的循环内执行时，PL/SQL和SQL发动机间的频繁切换就会影响到施行速度。比如，下头的UPDATE话语就在FOR话语中不住发送到SQL发动机：

DECLARE
TYPE numlist IS VARRAY(20) OF NUMBER;
depts numlist := numlist(十, 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);

要想尽最大或许地拔高性能，我们就需要像下部这么编写程序：

一.如其一条INSERT、UPDATE或DELETE话语在循环内施行，而且摘引到会合中的元素，那么，就把它放到FORALL话语中去。
二.如其SELECT INTO、FETCHE INTO或RETURNING INTO子句引述了一个聚合，那就配合BULK COLLECT子句一行应用。
三.如若或许的话，尽可能在应用程序和服务器其间运用主数组传递聚合。
四.如若DML操作失败时且不是很重大的问题，就可以在FORALL话语中运用SAVE EXCEPTIONS，其后在之后的循环中施用%BULK_EXCEPTIONS属性汇报或驱除差错。
不用忽略这些小问题，由于它们可以相助我们分析流程统制和程序的依赖性。

三、施用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;

四、应用RETURNING子句优化PL/SQL

正常，应用程序亟需失去SQL操作所影响到的行信息。INSERT、UPDATE和DELETE话语都可以包孕一个RETURNING子句，这么就能回来处置过的字段信息。也就不要在INSERT、UPDATE以后或DELETE头里应用SELECT来查询影响到的数据。这么也能够减小网络流量，缩水CPU时间，急需更小量的游标和服务器内存储器需求。

鄙人面的事例中，我们就在更新雇职工资的与此同时，把现阶段雇员的名称和新的薪资赋给PL/SQL变量：

PROCEDURE update_salary(emp_id NUMBER) IS
"name" VARCHAR二(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;

五、运用外部程序优化PL/SQL

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

PL/SQL是专程用以进展SQL事务处理的。有点任务在像C这么的低阶语言中处置起来会愈加有效。

为了增高施行速度，我们可以用C语言从新编纂受计算量限制的程序。除此以外，我们还可以把这么的程序从客户端移栽到服务器端，这么可以减小网络流量，更有效地利用资源。

比如，我们用C语言写一个运用图形对象门类的步骤，把它打包到动态链接库(DLL)中，并在PL/SQL中登记，其后我们就能从应用程序中调用它。运行时，库会被动态地加载，为了保险起见，它会在一个独自的地址空间运作。

六、施用对象部类和聚合优化PL/SQL

会合门类和对象门类在对真实全世界中的实业开展数据建模时能相助我们提高效率。复杂的实业和关系会被直接投射到对象门类中。而且，一个构建很好的对象模型能够化除多表联接，减小来回往复之类，故此改进应用程序性能。

客户端程序，包括PL/SQL程序，可以宣言对象和聚合，把它们作为参数传接，存放在数据库中，检索之类。一样，对象部类还可以把数据操作进展打包，把数据维护代码从SQL脚本中移出，把PL/SQL块放入步骤中。

对象和会合在储存和检索方面愈加高效率，由于它们是作为一个通体开展操作的。一样，对象门类还能和数据库调整在一起，利用Oracle自身所提供的易扩缩性和性能改进等优点。

七、编译当地实施的PL/SQL代码

我们可以把PL/SQL历程编译利润地代码放到共享库中，这么就能拔高它的施行速度。历程还可以被转换成C代码，其后用普普通通的C编译器编译，联接到 Oracle过程中。我们可以在Oracle提供的包和我们自个儿编纂的过程中应用这项技术。这么编译出来的历程可以在各种服务器环境中工作。由于这项技术对从PL/SQL中调用的SQL话语提高效率并不明显，之所以它一般应用在计算度高而实施SQL时间未几的PL/SQL历程上。

要增高一个或多个进程的施行速度，我们可以这么施用这项技术：

一.更新makefile并为我们的系统键入适当的途径和其余值。makefile途径是$ORACLE_HOME/plsql/spnc_makefile.mk。
二.经过运用ALTER SYSTEM或ALTER SESSION下令，或经过更新初始化资料，设立参数PLSQL_COMPILER_FLAGS来包孕值NATIVE。默认设置包孕的值是INTERPRETED，我们务必把它从参数值中剔除。
三.运用下部几个步骤编译一个或多个进程：
一.施用ALTER PROCEDURE或ALTER PACKAGE下令从新编译历程或整个包。
二.剔除历程并从新创建。

三.施用CREATE OR REPLACE从新编译历程。

四.运作SQL*Plus脚本建立一组Oracle系统包。

五.用带有PLSQL_COMPILER_FLAGS=NATIVE的初始化资料创造数据库。在创造数据库时，用UTLIRP脚本运作并编译Oracle系统包。
四.要确定我们所做的方法是不是有效，可以查询数据词典来查看进程是不是是被编译为当地施行，查询用的视图是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当地编译的举动：

一.PLSQL_COMPILER_FLAGS
二.PLSQL_NATIVE_LIBRARY_DIR (由于保险缘故，不能施用ALTER SESSION开展设立)
三.PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT
四.PLSQL_NATIVE_MAKE_UTILITY
五.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过程中被作为共享库直接运作

本文来源：
我的异常网
 Java Exception
Dotnet Exception
Oracle Exception