PL/SQL程序调优七

PL/SQL程序调优七（使用NOCOPY优化器提示调整PL/SQL过程调用）

       默认情况下，OUT和IN OUT参数通过值传递，IN OUT的值在子程序执行前被拷贝。在子程序执行期间，会有临时变量保存输出参数的值。如果子程序正常退出，它们的值将被拷贝到实际的参数，否则原始的参数不会被改变。

       当参数代表很大的结果集，如集合，记录，对象类型时，这些拷贝会比较慢并且使用很多内存。

       为了避免这种性能负载，可以声明NOCOPY，允许优化器通过引用传递OUT和IN OUT参数的值。如果子程序正常退出，结果同默认的OUT/IN OUT。如果子程序非正常退出IN OUT参数的值仍然会改变。为了使用这种技术，必须确保所有的异常都被恰当处理了。如下：

DECLARE
  TYPE Staff IS VARRAY(200) OF Employee;
  PROCEDURE reorganize (v_staff IN OUT NOCOPY Staff) IS ...

       两者花费的时间如下：

DECLARE
   TYPE EmpTabTyp IS TABLE OF ITO_DATA_CENTER%ROWTYPE;
   emp_tab EmpTabTyp := EmpTabTyp(NULL);  -- initialize
   t1 NUMBER;
   t2 NUMBER;
   t3 NUMBER;
   PROCEDURE get_time (t OUT NUMBER) IS
     BEGIN t := DBMS_UTILITY.get_time; END;
   PROCEDURE do_nothing1 (tab IN OUT EmpTabTyp) IS
     BEGIN NULL; END;
   PROCEDURE do_nothing2 (tab IN OUT NOCOPY EmpTabTyp) IS
     BEGIN NULL; END;
BEGIN
   SELECT * INTO emp_tab(1) FROM ITO_DATA_CENTER WHERE ROWNUM=1;
   emp_tab.EXTEND(10000, 1);  -- copy element 1 into 2..50000
   get_time(t1);
   do_nothing1(emp_tab);  -- pass IN OUT parameter
   get_time(t2);
   do_nothing2(emp_tab);  -- pass IN OUT NOCOPY parameter
   get_time(t3);
   DBMS_OUTPUT.PUT_LINE('Call Duration (secs)');
   DBMS_OUTPUT.PUT_LINE('--------------------');
   DBMS_OUTPUT.PUT_LINE('Just IN OUT: ' || TO_CHAR((t2 - t1)/100.0));
   DBMS_OUTPUT.PUT_LINE('With NOCOPY: ' || TO_CHAR((t3 - t2))/100.0);
END;
/

Call Duration (secs)

--------------------

Just IN OUT: 1.35

With NOCOPY: 0

NOCOPY的限制

       需要注意，NOCOPY是一个提示，不是指示符。在以下情况下，PL/SQL不会使用NOCOPY提示，仍使用值传递：

       ·实际的参数是关联数组的一个元素，而不是整个关联数组。

       ·子程序通过数据库链接/外部过程调用；

       ·传递实际的参数需要隐示数据类型转换；

       ·实参和形参是纪录，实参声明为一个FOR循环中的一个索引，并且相应字段上的约束不同；

       ·实参和形参是纪录，但是其中一个或者两个都是通过%ROWTYPE或%TYPE声明，但是约束不同；

       ·实参有约束，如精度要求或者NOT NULL。字符除外，并且该限制不包括集合的元素和组合类型的属性。