必须说明的是，以下内容是属于学习笔记的部分，虽然PDF的E文也看得懂，但是还是觉得翻译为自己的国文看起来更加舒畅一些。
此处原文见<
>的第十一章节的内容"Tuning PL/SQL Applications for Performance"－－调整Pl/sql应用程序的性能。
一，和PL/SQL编译相关的初始化参数
自然这里的初始化参数指的就是init文件中的参数。
PLSQL_CCFLAGS 主要用于条件编译中
PLSQL_CODE_TYPE 可以选择的值是NATIVE还是INTERPRETED
PLSQL_DEBUG true/false，true的时候，代码别编译为Interpreted类型以便调试。。
PLSQL_NATIVE_LIBRARY_DIR 全编译时候存放库的路径
PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT 这和上一个是配对使用的，也是用于全编译。
PLSQL_OPTIMIZE_LEVEL 特别介绍以下，不过oracle建议我们一般不要修改。
－－－－－－－－－－－－－－－－－－－－－
原文不晦涩，也就不翻译了。
PLSQL_OPTIMIZE_LEVEL specifies the optimization level that will be used to
compile PL/SQL library units. The higher the setting of this parameter, the more effort
the compiler makes to optimize PL/SQL library units.
Values:
■ 0（主要为了向下兼容）
Maintains the evaluation order and hence the pattern of side effects, exceptions,
and package initializations of Oracle9i and earlier releases. Also removes the new
semantic identity of BINARY_INTEGER and PLS_INTEGER and restores the earlier
rules for the evaluation of integer expressions. Although code will run somewhat
faster than it did in Oracle9i, use of level 0 will forfeit most of the performance
gains of PL/SQL in Oracle Database 10g.
■ 1（一般化，不过一般不做特殊优化）
Applies a wide range of optimizations to PL/SQL programs including the
elimination of unnecessary computations and exceptions, but generally does not
move source code out of its original source order.
■ 2（和1类似，但是可能会修改你的源码，在生成的库中，代码的顺序会变化）
Applies a wide range of modern optimization techniques beyond those of level 1
including changes which may move source code relatively far from its original
location.
Generally, setting this parameter to 2 pays off in better execution performance. If,
however, the compiler runs slowly on a particular source module or if optimization
does not make sense for some reason (for example, during rapid turnaround
development), then setting this parameter to 1 will result in almost as good a
compilation with less use of compile-time resources.
The value of this parameter is stored persistently with the library unit.
最后一句话：在生成的库中总是会存储这个参数的值。
－－－－－－－－－－－－－－－－－－－－－
PLSQL_WARNINGS 属于编译的参数，控制可以输出的参数，可以基于session和system的级别修改
NLS_LENGTH_SEMANTICS. 这个参数比较玄妙，这是用于控制类型为char和varchar2的列使用字节还是
字来存储相关的数据，默认是用单字节(byte),也可以是character，不影响已经存
在的数据，不影响SYSTEM和sys用户的对象。
关于这些参数的定义可以见<>,非常的全，至于如何修改它们，则可以参考<>,这两部书都是特大部头。
二，如何优化（或者是优化的原理是什么）
有一点很关键的是，10g之后oracle会重新排列代码（相应的plsql_optimize_level＝2)（应该来说，通常重新排列之后的算法是更好的，当然也许会存在更不好的，这个以后再说）。
需要注意的是，你可以自己按照需要调整优化级别参数。
对于已经编译好的对象，可以通过视图ALL_PLSQL_OBJECT_SETTINGS来知道优化的级别以及相关编译信息。
其次，就是选择优化的时机。如果在进行优化前，先考虑一些基本的也许会更好一些。
三、关于避免PLSQL性能问题的指导方针
3.1 避免CPU过载
A.使SQL语句尽可能的高效
- 是否有合适的索引，是否正确的使用了索引
- 是否有最新的统计数据，常常使用DBMS_STATS包来重新统计。
- 使用EXPLAIN PLAN语句或者TKPROF工具分析语句的性能
- 如果需要，就重写sql语句。例如可以使用强制提示
- 考虑用FORALL替代普通的循环来执行INSERT等语句；如果需要轮询查询结果，考虑用BULK COLLECT语句
B. 使函数更有效率
如果在SQL查询中使用函数，那么考虑建立一个基于函数的索引；尽可能的把需要使用函数的行的数量
降低。（由于很多的内容稍后会有比较详细的描述，所以这里不再赘述了)
C. 优化循环
D 尽可能地使用内建的字符串函数来处理字符串，例如搜索等等。
E 对于条件表达式，应该把消耗最小的排在前面，这样有的时候后面复杂的测试可能可以避免。
F 尽量减少数据类型的转换
G 用PLS_INTEGER进行整数运算
H 用BINARY_FLOAT 和BINARY_DOUBLE进行浮点运算
3.2 避免内存过载
A 定义VARCHAR2变量类型的时候，不妨大方一些，这反而可以编译溢出。
B 把相关的过程放在一个包裹中（PACKAGE），可以减少不必要的I/O .
C 把常常用到的包裹钉在共享内存池中,Pin packages in the shared memory pool..。可以用
dbms_shared_pool 包裹来处理，具体参考< reference>>
D 避免编译中出现的警告。有警告提示，则应该按照提示去改正。
四，记录并跟踪PL/SQL程序
4.1 使用记录API:包裹dbms_profiler.这个包括能够提供收集并保存运行时统计数据的功能。最终的数据被保存在数据库的表格中。
4.2 使用跟踪API：包裹DBMS_TRACE.当让要讲究跟踪的策略。对于这点，我觉得使用PL/SQL DEVELOPER很方便。
五，减少DML语句循环的消耗、在查询中使用BULK SQL语句
PL/SQL引擎把FORALL中的DML语句成批的发送给SQL引擎。SQL引擎通过BULK collect会批量的返回数据。这两种方式都会大大的减少两个引擎之间的通讯成本和管理成本。
SQL语句中PL/SQL变量的绑定有三个途径：
1：内部绑定：通过INSERT 或者UPDATE语句来完成。
2：外部绑定：通过INSERT,UPDATE,DELETE 中的RETURNING语句来完成。
3：定义 ：通过SELECT或者FETCH语句来完成。
此外的一种就是大量绑定（BULK BINDING)
5.1 使用FORALL 语句
5.1.1 FORALL是如何作用到ROLLBACKS（回滚)
在异常之前的语句都可以提交，异常和异常之后的无法提交。
5.1.2 使用%bulk_rowcount属性来计算FORALL影响到的行
5.1.3 使用%BULK_EXCEPTIONS属性来处理FORALL异常
我们需要一个例子，可以从例子中得到许多有益的总结：(其中的employees的格式和数据来源于hr）
DECLARE
TYPE empid_tab IS TABLE OF employees.employee_id%TYPE;
emp_sr empid_tab;
-- create an exception handler for ORA-24381
errors NUMBER;
dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(dml_errors, -24381);
BEGIN
SELECT employee_id BULK COLLECT INTO emp_sr FROM emp_temp
WHERE hire_date < to_date('1994-12-30','yyyy-mm-dd');
-- add '_SR' to the job_id of the most senior employees
FORALL i IN emp_sr.FIRST..emp_sr.LAST SAVE EXCEPTIONS
UPDATE emp_temp SET job_id = job_id || '_SR'
WHERE emp_sr(i) = emp_temp.employee_id;
-- If any errors occurred during the FORALL SAVE EXCEPTIONS,
-- a single exception is raised when the statement completes.
EXCEPTION
WHEN dml_errors THEN -- Now we figure out what failed and why.
FOR I IN emp_sr.FIRST..emp_sr.LAST loop
if sql%bulk_rowcount(i)<>0 then
dbms_output.put_line('The row '||i||' has '||sql%bulk_rowcount(i)||' Updated !');
else
dbms_output.put_line('The row '||i||' has no rows Updated !');
end if;
end loop;
errors := SQL%BULK_EXCEPTIONS.COUNT;
DBMS_OUTPUT.PUT_LINE('Number of statements that failed: ' || errors);
FOR i IN 1..errors LOOP
DBMS_OUTPUT.PUT_LINE('Error #' || i || ' occurred during '||
'iteration #' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
DBMS_OUTPUT.PUT_LINE('Error message is ' ||
SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
rollback;
END;
|
The row 1 has 1 Updated !
The row 2 has 1 Updated !
The row 3 has 1 Updated !
The row 4 has 1 Updated !
The row 5 has 1 Updated !
The row 6 has 1 Updated !
The row 7 has no rows Updated !
The row 8 has 1 Updated !
The row 9 has 1 Updated !
The row 10 has 1 Updated !
The row 11 has 1 Updated !
The row 12 has 1 Updated !
The row 13 has no rows Updated !
Number of statements that failed: 2
Error #1 occurred during iteration #7
Error message is ORA-12899: value too large for column (actual: , maximum: )
Error #2 occurred during iteration #13
Error message is ORA-12899: value too large for column (actual: , maximum: )
|
从例子可以看出，sql%bulk_rowcount表示的是每一个sql语句影响到多少行。元素个数等于循环数.
sql%bulk_exceptions则存储了有错误发生的行的信息，实际的bulk_exceptions的元素
个数等于错误个数。
5.2 用BULK COLLECT 从查询结果中提取数据到集合中
关于这个没有什么特别好说明。就是一个returning语句稍微介绍一下：
DELETE FROM emp_temp WHERE department_id = 30 RETURNING employee_id, last_name BULK
COLLECT INTO enums, names;
其次就是可以使用limit语句，以免内存过载.
第三，FORALL和bulk collect into 可以结合起来，但是SELECT ...BULK COLLECT除外，也就是说只能
用returning.
六，编写精壮计算程序
用pls_integer和binary_integer做整数运算，用BINARY_FLOAT和BINARY_DOUBLE做浮点运算。
七，使用execute immediate 和cursor变量来调整性能
尽量不要用dbms_sql来执行动态sql了（个人任务这个包过时的可能性比较大），现在用execute immedaite能
够很好的执行各种动态的sql，关于这个可以参考我的另外一篇文章:动态执行sql的几个方式
DBMS_SQL的速度和紧凑性上都比execute immediate来的差。
八,在过程调用中使用NOCOPY编译提示指令
IN/OUT的过程传参，一般情况是按值来进行的，这就是说在调用子过程前，需要一个中间变量，然后把值复制给这个中间变量，临了还要拷贝回来，如果参数的数据许多且结构复杂，那么可能就需要消耗许多的时间。
使用NOCOPY指令，这样可以强制调用过程的时候使用地址传递参数。这样的好处是快，但是也有个坏处，如果子程序异常结束了，那么参数也可能发生了改变。
例子：
DECLARE
TYPE EmpTabTyp IS TABLE OF employees%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 employees WHERE employee_id = 100;
emp_tab.EXTEND(49999, 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;
限制性（比较常见的）：
通过DBLINK调用的过程，或者是外部过程例如DLL之类的，那么这个HINT无效。
实参需要一个隐式数据转换的