📄 第十二章 pl-sql应用程序性能调优 - pl-sql用户指南与参考 - whatiswhat.htm
字号:
<LI>不必要的NOT NULL约束 </LI></UL>
<P>PL/SQL中，使用NOT NULL约束也能导致性能损耗。如下例所示： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>PROCEDURE</STRONG> calc_m <STRONG>IS</STRONG><BR> m <STRONG>NUMBER</STRONG> <STRONG>NOT</STRONG> <STRONG>NULL</STRONG> := 0;<BR> a <STRONG>NUMBER</STRONG>;<BR> b <STRONG>NUMBER</STRONG>;<BR><STRONG>BEGIN</STRONG><BR> ...<BR> m := a + b;<BR> ...<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>因为m是受NOT NULL约束的，表达式a +
b的值就会赋给临时变量，然后PL/SQL会对这个临时变量作判空测试。如果变量不是空，它的值就能赋给m，否则就会出现异常。但是，如果m不是有约束限制的话，结果值就会直接赋给m。更高效的写法如下：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>PROCEDURE</STRONG> calc_m <STRONG>IS</STRONG><BR> m <STRONG>NUMBER</STRONG>; <EM>-- no constraint</EM><BR> a <STRONG>NUMBER</STRONG>;<BR> b <STRONG>NUMBER</STRONG>;<BR><STRONG>BEGIN</STRONG><BR> ...<BR> m := a + b;<BR> <STRONG>IF</STRONG> m <STRONG>IS</STRONG> <STRONG>NULL</STRONG> <STRONG>THEN</STRONG> <EM>-- enforce constraint programmatically</EM><BR> ...<BR> <STRONG>END</STRONG> <STRONG>IF</STRONG>;<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>注意，NATURALN和POSTIVEN都是NOT NULL，所以它们也同样会影响性能。 </P>
<UL>
<LI>VARCHAR2变量的长度声明 </LI></UL>
<P>对于VARCHAR2类型，我们在内存使用和效率上需要做出一个权衡。对于VARCHAR2(长度>=2000)变量，PL/SQL动态分配内存来存放实际值，但对于VARCHAR2(长度<2000)变量，PL/SQL会预先分配足够的内存。所以，如果我们把同样一个500字节的值放入一个VARCHAR2(2000)和一个VARCHAR2(1999)变量中，后者会多占用1499个字节的内存。
</P>
<UL>
<LI>滥用PL/SQL程序中的共享内存 </LI></UL>
<P>第一次调用打包子程序时，整个包会被加载到共享内存池。所以，以后调用包内相关子程序时，就不再需要读取磁盘了，这样会加快我们的代码会执行速度。但是，如果包从内存中清除之后，我们在重新引用它的时候，就必须重新加载它。</P>
<P>我们可以通过正确地设置共享内存池大小来改善性能。一定要确保共享内存有足够空间来存放被频繁使用的包，但空间也不要过大，以免浪费内存。
</P>
<UL>
<LI>保持包(Pinned Packages) </LI></UL>
<P>另外一个改善性能的方法就是把频繁使用的包保持到共享内存池中。当一个包被保持下来后，它就不会被Oracle通常所采用的最少最近使用(LRU)
算法清除。不管池有多满或是我们访问包有多么频繁，包始终会被保持在池中的。我们可以利用系统包DBMS_SHARED_POOL把包保持下来。
</P>
<UL>
<LI>可连续重用包 </LI></UL>
<P>为了帮助我们管理内存的使用，PL/SQL提供了编译指示SERIALLY_REUSABLE，它能让我们把某些包标记为可连续重用的
(serially
reusable)。如果一个包的状态只在服务器呼叫时间内所需要，那么我们就可以对这个包使用这个标记了(例如，一个对服务器的OCI调用或是服务器对服务器RPC)。</P>
<P>对于这样的包所分配的内存会放到系统全局区(SGA)中，而不是分配到独立的用户所使用的用户全局区(UGA)。那样，包的工作区就可以被反复使用。当服务器调用结束的时候，内存就会被还给共享池。每次包被重用时，它的公共变量就会被初始化为它们的默认值或NULL。
</P>
<P>一个包所需的工作区最大个数就是当前使用这个包的用户数，这个数字通常要小于登录用户数。SGA内存的增长量要大于UGA内存的缩减量。并且，如果Oracle要回收SGA内存的话，它就会把没有使用的工作区进行过期处理。
</P>
<P>对于没有包体的包来说，我们可以使用在包说明中使用下面语法编写编译指示：</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>PRAGMA</STRONG> SERIALLY_REUSABLE;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>对于有包体的包来说，我们必须在说明和包体中编写编译指示。我们不能只在包体中编写编译指示。下面的例子演示了如何在一个连续重用包中使用一个公共变量：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>CREATE</STRONG> <STRONG>PACKAGE</STRONG> pkg1 <STRONG>IS</STRONG><BR> <STRONG>PRAGMA</STRONG> SERIALLY_REUSABLE;<BR> num <STRONG>NUMBER</STRONG> := 0;<BR> <STRONG>PROCEDURE</STRONG> init_pkg_state(n <STRONG>NUMBER</STRONG>);<BR> <STRONG>PROCEDURE</STRONG> print_pkg_state;<BR><STRONG>END</STRONG> pkg1;<BR>/<BR><STRONG>CREATE</STRONG> <STRONG>PACKAGE</STRONG> <STRONG>BODY</STRONG> pkg1 <STRONG>IS</STRONG><BR><STRONG>PRAGMA</STRONG> SERIALLY_REUSABLE; <BR><STRONG>PROCEDURE</STRONG> init_pkg_state(n <STRONG>NUMBER</STRONG>) <STRONG>IS</STRONG><BR><STRONG>BEGIN</STRONG><BR> pkg1.num := n;<BR><STRONG>END</STRONG>;<BR><STRONG>PROCEDURE</STRONG> print_pkg_state <STRONG>IS</STRONG><BR><STRONG>BEGIN</STRONG><BR> dbms_output.put_line(<EM>'Num: '</EM> || pkg1.num);<BR><STRONG>END</STRONG>;<BR><STRONG>END</STRONG> pkg1;<BR>/<BR><STRONG>BEGIN</STRONG><BR> <EM>/* Initialize package state. */</EM><BR> pkg1.init_pkg_state(4);<BR> <EM>/* On same server call, print package state. */</EM><BR> pkg1.print_pkg_state; <EM>-- prints 4</EM><BR><STRONG>END</STRONG>;<BR>/<BR><EM>-- subsequent server call</EM><BR><STRONG>BEGIN</STRONG><BR> <EM>-- the package public variable is initialized</EM><BR> <EM>-- to the default value automatically</EM><BR> pkg1.print_pkg_state; <EM>-- prints 0</EM><BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P class=title1>二、确定PL/SQL的性能问题</P>
<P>当我们开发越来越大的PL/SQL应用程序时，就难免要碰到性能问题。所以，PL/SQL为我们提供了Profiler
API来剖析运行时行为并帮助我们辨识性能瓶颈。PL/SQL也提供了一个Trace
API用来跟踪服务器端的程序执行。我们可以使用Trace来跟踪子程序或异常的执行。 </P>
<P class=title2>1、Profiler API：DBMS_PROFILER包</P>
<P>Profiler
API由PL/SQL包DBMS_PROFILER实现，它提供了收集并保存运行时的统计信息。这些信息会被保存在数据表中，供我们查询。例如，我们可以知道PL/SQL每行和每个子程序执行所花费的时间长短。
</P>
<P>要使用Profiler，先开启一个性能评测会话，充分地运行我们的应用程序以便达到足够的代码覆盖率，然后把收集到的信息保存在数据库中，停止性能评测会话。具体步骤如下：
</P>
<OL>
<LI>调用DBMS_PROFILER包中的start_profiler过程，把一个注释与性能评测会话关联。
<LI>运行要被评测的应用程序。
<LI>反复调用过程flush_data把收集到的数据保存下来并释放内存。
<LI>调用stop_profiler过程停止会话。 </LI></OL>
<P>Profiler能跟踪程序的执行，计算每行和每个子程序所花费的时间。我们可以用收集到的数据帮助改善性能。例如，我们可以集中处理那些运行慢的子程序。
</P>
<UL>
<LI>分析收集到的性能数据 </LI></UL>
<P>下一步要判断出为什么执行某些代码段或访问某些数据结构要花费大量的时间。借助查询出来的性能数据来找到问题点。把问题集中到那些耗费时间长的子程序和包，尽可能的优化SQL语句、循环和递归函数等。
</P>
<UL>
<LI>使用跟踪数据改善程序性能 </LI></UL>
<P>使用我们的分析结果重新编写那些执行效率低下的算法。例如，在急剧膨胀的数据中，我们可能要需要使用二分法来替代线性搜索。
</P>
<P class=title2>2、Trace API：包DBMS_TRACE</P>
<P>在大而复杂的应用程序中，很难跟踪子程序的调用。如果使用跟踪API，我们就能看到子程序的执行顺序。跟踪API是由包DBMS_TRACE实现的，并提供了跟踪子程序或异常的服务。
</P>
<P>要使用跟踪，先要开启一个跟踪会话，运行程序，然后停止跟踪会话。当程序执行时，跟踪数据就会把收集并保存到数据库中。在一个会话中，我们可以采用如下步骤来执行跟踪操作：
</P>
<OL>
<LI>可选步骤，选择要跟踪的某个特定的子程序。
<LI>调用DBMS_TRACE包中的set_plsql_trace开启跟踪。
<LI>运行要跟踪的应用程序。
<LI>调用过程clear_plsql_trace来停止跟踪。 </LI></OL>
<UL>
<LI>控制跟踪 </LI></UL>
<P>跟踪大型应用程序可能会制造出大量的难以管理的数据。在开启跟踪之前，我们可以选择是否限制要收集的数据量。
</P>
<P>此外，还可以选择跟踪级别。例如，如果我们可以选择跟踪全部的子程序和异常或是只跟踪选定的子程序和异常。
</P>
<P class=title1>三、PL/SQL性能优化特性</P>
<P>我们可以使用下面的PL/SQL特性和方法来优化应用程序： </P>
<OL>
<LI>使用本地动态SQL优化PL/SQL
<LI>使用批量绑定优化PL/SQL
<LI>使用NOCOPY编译器提示优化PL/SQL
<LI>使用RETURNING子句优化PL/SQL
<LI>使用外部程序优化PL/SQL
<LI>使用对象类型和集合优化PL/SQL </LI></OL>
<P>这些简单易用的特性可以显著的提高应用程序的执行速度。 </P>
<P class=title2>1、使用本地动态SQL优化PL/SQL</P>
<P>有些程序必须要执行一些只有在运行时才能确定下来的SQL语句，这些语句被称为动态SQL语句。以前，要执行动态SQL语句就必须使用包DBMS_SQL。现在，我们可以在PL/SQL中直接使用被称为本地动态SQL的接口来执行各种动态SQL语句。
</P>
<P>本地动态SQL更容易使用，并且执行速度也要比DBMS_SQL包快。在下面的例子中，我们声明一个游标变量，然后把它与一个能返回数据表emp记录的动态的SELECT语句关联起来：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> <STRONG>TYPE</STRONG> empcurtyp <STRONG>IS</STRONG> <STRONG>REF</STRONG> <STRONG>CURSOR</STRONG>;<BR> emp_cv empcurtyp;<BR> my_ename <STRONG>VARCHAR2</STRONG>(15);<BR> my_sal <STRONG>NUMBER</STRONG> := 1000;<BR><STRONG>BEGIN</STRONG><BR> <STRONG>OPEN</STRONG> emp_cv <STRONG>FOR</STRONG> <EM>'SELECT ename, sal FROM emp WHERE sal > :s'</EM><BR> USING my_sal;<BR> ...<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P class=title2>2、使用批量绑定优化PL/SQL</P>
<P>当SQL在集合的循环内执行时，PL/SQL和SQL引擎间的频繁切换就会影响到执行速度。例如，下面的UPDATE语句就在FOR语句中不断发送到SQL引擎：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> <STRONG>TYPE</STRONG> numlist <STRONG>IS</STRONG> VARRAY(20) <STRONG>OF</STRONG> <STRONG>NUMBER</STRONG>;<BR> depts numlist := numlist(10, 30, 70, .. .); <EM>-- department numbers</EM><BR><STRONG>BEGIN</STRONG><BR> ...<BR> <STRONG>FOR</STRONG> i <STRONG>IN</STRONG> depts.FIRST .. depts.LAST <STRONG>LOOP</STRONG><BR> ...<BR> <STRONG>UPDATE</STRONG> emp <STRONG>SET</STRONG> sal = sal * 1.10 <STRONG>WHERE</STRONG> deptno = depts(i);<BR> <STRONG>END</STRONG> <STRONG>LOOP</STRONG>;<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>在这样的情况下，如果SQL语句影响到四行或更多行数据时，使用批量绑定就会显著地提高性能。例如，下面的UPDATE语句可以一次就把整个嵌套表的数据发送到SQL引擎中：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>FORALL</STRONG> i <STRONG>IN</STRONG> depts.FIRST..depts.LAST<BR> <STRONG>UPDATE</STRONG> emp <STRONG>SET</STRONG> sal = sal * 1.10 <STRONG>WHERE</STRONG> deptno = depts(i);
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>要想尽最大可能地提高性能，我们就需要像下面这样编写程序： </P>
<OL>
<LI>如果一条INSERT、UPDATE或DELETE语句在循环内执行，并且引用到集合中的元素，那么，就把它放到FORALL语句中去。
<LI>如果SELECT INTO、FETCHE INTO或RETURNING
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -